|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
|
|
DELETE RECORDS IN AUDIT_TRAIL TABLE |
« View previous topic :: View next topic » |
Author |
Message
|
yao |
Posted: Wed Aug 12, 2009 8:09 pm Post subject: DELETE RECORDS IN AUDIT_TRAIL TABLE |
|
|
Newbie
Joined: 11 Jun 2009 Posts: 5
|
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. |
|
Back to top |
|
|
hos |
Posted: Wed Aug 12, 2009 11:07 pm Post subject: |
|
|
Chevalier
Joined: 03 Feb 2002 Posts: 470
|
Hi,
1. the IBM expert is correct, MQWF does not read / query the audit trail
2. you should use the existing audit trail cleanup feature fmcsclad to cleanup your audit trail. For details see the Administration Guide.
3. the query you are seeing is most probably a customer written application that searches for a specific business situation. You should definitely be aware for what purposes the audit trail is used in your scenario before you can decide what is no longer needed. |
|
Back to top |
|
|
yao |
Posted: Thu Aug 13, 2009 8:43 pm Post subject: |
|
|
Newbie
Joined: 11 Jun 2009 Posts: 5
|
Hi hos
Thank you for your quick reply.
Beside deleting records in audit_trail, we also use cleanup server to delete workitems.
But cleanup server needs almost several seconds to delete just one workitem. We have about 300,000 workitems now. So it needs nearly 10 days to clean all workitems. Does anyone know how we should do to make the cleanup server worked a little quickly.
Best regard |
|
Back to top |
|
|
hos |
Posted: Thu Aug 13, 2009 11:08 pm Post subject: |
|
|
Chevalier
Joined: 03 Feb 2002 Posts: 470
|
See Chapter 1 in Administration Guide: How to improve performance of the Cleanup Server. Reading the Administration Guide is highly recommended when you are in duty to keep MQWF alive. |
|
Back to top |
|
|
|
|
|
|
Page 1 of 1 |
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|
|