ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » Workflow Engines - IBM MQ Workflow & Business Process Choreographer » DELETE RECORDS IN AUDIT_TRAIL TABLE

Post new topic  Reply to topic
 DELETE RECORDS IN AUDIT_TRAIL TABLE « View previous topic :: View next topic » 
Author Message
yao
PostPosted: Wed Aug 12, 2009 8:09 pm    Post subject: DELETE RECORDS IN AUDIT_TRAIL TABLE Reply with quote

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
View user's profile Send private message
hos
PostPosted: Wed Aug 12, 2009 11:07 pm    Post subject: Reply with quote

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
View user's profile Send private message
yao
PostPosted: Thu Aug 13, 2009 8:43 pm    Post subject: Reply with quote

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
View user's profile Send private message
hos
PostPosted: Thu Aug 13, 2009 11:08 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » Workflow Engines - IBM MQ Workflow & Business Process Choreographer » DELETE RECORDS IN AUDIT_TRAIL TABLE
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.