MQSeries.net :: View topic

yao · Posted: Thu Aug 13, 2009 8:43 pm Post subject:

Hi,

The MQWF's audit_trail table becomes very big. We want to write a script to delete records in it.

The question is how do we determine which recods will not be used by anyone.

One IBM expert told us that MQWF components never query audit_trail table. But when we run get snapshot on the
db2 instance, we found that someone does query the audit_trail table. The sql statement is as below:

-----------------------------------------------------------------------------------
Dynamic SQL Snapshot Result

Database name = ACQLFDB

Database path = /wfdbdata/acqlf/rt_db/db2inst1/NODE0000/SQL00001/

Number of executions = 1
Number of compilations = 1
Worst preparation time (ms) = 7
Best preparation time (ms) = 7
Rows deleted = Not Collected
Rows inserted = Not Collected
Rows read = Not Collected
Rows updated = Not Collected
Rows written = Not Collected
Statement sorts = Not Collected
Total execution time (sec.ms) = Not Collected
Total user cpu time (sec.ms) = Not Collected
Total system cpu time (sec.ms) = Not Collected
Statement text = SELECT FMC.AUDIT_TRAIL.TOP_LVL_PROC_NAME,
ACTIVITY_NAME,
ACTIVITY_STATE,
EVENT
FROM FMC.AUDIT_TRAIL,
(SELECT TOP_LVL_PROC_NAME,MAX(CREATED) AS CRE_TIME
FROM FMC.AUDIT_TRAIL
WHERE TOP_LVL_PROC_NAME IN ('000000610094656002') AND
EVENT IN (21006,21007,21009,21011,21022,21023,21027,21080,21081)
GROUP BY TOP_LVL_PROC_NAME) AUDIT_TRAIL_2
WHERE FMC.AUDIT_TRAIL.TOP_LVL_PROC_NAME = AUDIT_TRAIL_2.TOP_LVL_PROC_NAME AND
FMC.AUDIT_TRAIL.CREATED = AUDIT_TRAIL_2.CRE_TIME
-----------------------------------------------------------------------------------

So to avoid conflict with other processes which query the audit_trail table, how should we write the where sentence .

By the way , it will be very appreciated if any one can tell us what does the above sql Statement mean.

Thanks in advance

best regard.