| Author |
Message
|
| Pats21 |
 Posted: Sat Feb 17, 2007 8:00 pm Post subject: [SOLVED] MQWF DB2 databse size increase |
 |
|
Disciple
Joined: 08 Sep 2006
Posts: 154
|
Hi,
We are using IBM MQ Workflow v3.4 along with DB2 database.
I have observered that the size of the runtime database is increasing.
My first thought is that the Audit_Trial is ON.
I checked in the buildtime and observered that the Audit_Trial is set to FULL at the Process Model Level.
I cross verified this by exporting the .fdl from the runtime.
I also know that there is a utility available to clear this Audit_Trial table called "fmcsclad". However, my doubts are:
1. Is Audit_Trial property set to FULL the only reason of the increase in size of the runtime database?
2. If I need to run the "fmcsclad" utility, do I need to stop MQWF or can I run this utility when MQWF is running?
3. Is there anything in special which I need to take care of before running this utility?
Thanks in Advance for any help provided.
Regards,
Pats
Last edited by Pats21 on Tue Oct 09, 2007 8:43 pm; edited 1 time in total |
|
| Back to top |
|
 |
| tsrisudh |
| Posted: Sat Feb 17, 2007 10:27 pm Post subject: |
|
|
Centurion
Joined: 11 Aug 2005
Posts: 113
|
Yes having the audit on increases the DB size dramatically, i do not know he exact usage if the tool so cant help you there
_________________
Srisudhir Tadepalli |
|
| Back to top |
|
|
| vijaycr |
| Posted: Sun Feb 18, 2007 7:16 am Post subject: |
|
|
Acolyte
Joined: 11 Oct 2006
Posts: 62
Location: Singapore
|
Yes Full audit trail increases, but not the only reason for the size increasing. Check if ur clean up server is working and is scheduled to run at least once a day.
fmcsclad does not require the MQWF server to be stopped, but is advisable to be run during periods of low or no activity. |
|
| Back to top |
|
|
| Pats21 |
| Posted: Thu Feb 22, 2007 5:04 pm Post subject: |
|
|
Disciple
Joined: 08 Sep 2006
Posts: 154
|
First of all thanks for all your suggestions, I really appreciate that.
However, I am facing a problem or rather a doubt.
I have starting running the fmcsclad utility to clear the audit_trail table.
The filesize of the database is around 18.2GB and he audit_trail holds data from March 2004.
I had started the utility using the following command at 1.30PM yesterday:-
fmcsclad -y UALDEV -u db2admin -p ***** -d 2006-08-22 -x all
To clear the data before Aug 2006, however the utility is still running  .
Its been nearly 22 hours now.
After issuing the above command, the command prompt shows the below messages and its been so since the past 22 hours.
****************************************************
System group name : UANZ
System name : WEB099
Userid : DB2ADMIN
Password : ********
Date and Time : 2006-08-22
Delete option : all
- FMC37010I Deleting all finished processes older than '2006-08-22 00:00:00'...
*******************************************************
My question is :-
1. Does it take so long to clear the data?
2. Is there anything else that I have to take care off?
Any help is appreciated.
Regards,
Pats... |
|
| Back to top |
|
|
| Pats21 |
| Posted: Thu Feb 22, 2007 5:18 pm Post subject: |
|
|
Disciple
Joined: 08 Sep 2006
Posts: 154
|
| Also to mention here, I have not stopped the MQWF server. |
|
| Back to top |
|
|
| jmac |
| Posted: Thu Feb 22, 2007 5:21 pm Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
I am not a DB2 expert, so probably a DBA could answer your question about taking that long, but you are deleting a LOT of data, so I guess it is possible. Have you been noticing Deadlocks? That would definitely hurt.
Also, 3.4 is out of support since last April, you really should consider an upgrade, and I am pretty certain that 3.5 adds some parameters that will help you speed up deletes to the Audit table. Have a look in the Admin guide and I think you will find them (I don't remember what the parameter is at the moment), you could also try searching on this forum, I think I recollect having answered a similar question in the past about speeding up audit cleanup.
GOOD LUCK
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| Pats21 |
| Posted: Thu Mar 01, 2007 1:01 am Post subject: |
|
|
Disciple
Joined: 08 Sep 2006
Posts: 154
|
Hi,
I had started the fmcsclad utility 5 hours ago.
I am checking the no. of records in the audit_trail table using the query
"select count(*) from fmc.audit.trail"
I have observered that the no. of records are decreasing gradually in this table, however the filesize of the table is not at all decreasing.
I am checking the filesize of the table in the location
"<MQWF-Install-Dir>\rt_db\db2\<RT-tablename>"
Can anyone let me know why the filesize is not decreasing?
My main motive to clear the audit_trail table is to get diskspace on my server and that is not getting achieved
Thanks,
Pats |
|
| Back to top |
|
|
| tsrisudh |
| Posted: Thu Mar 01, 2007 1:20 am Post subject: |
|
|
Centurion
Joined: 11 Aug 2005
Posts: 113
|
I guess the filesize will decrease only when the transaction completes successfully. It is that way because the DB has to rollback in case there is an error in between the process.
I think this is the reason, but an DBA will be a better person to answer this.
_________________
Srisudhir Tadepalli |
|
| Back to top |
|
|
| tsrisudh |
| Posted: Mon Mar 05, 2007 5:38 am Post subject: |
|
|
Centurion
Joined: 11 Aug 2005
Posts: 113
|
Pats21, so did the process complete successfully, and how much time did it finally take?
Any idea how many record it deleted from the AUDIT_TRAIL table?
_________________
Srisudhir Tadepalli |
|
| Back to top |
|
|
| tsrisudh |
| Posted: Tue Mar 06, 2007 3:20 am Post subject: |
|
|
Centurion
Joined: 11 Aug 2005
Posts: 113
|
I too ran the process and it deleted 120000 records in about 18 hours, any idea how we can speed up this process
_________________
Srisudhir Tadepalli |
|
| Back to top |
|
|
| jmac |
| Posted: Tue Mar 06, 2007 6:31 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
There is some doc in the Admin guide regarding improving cleanup server performance.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| Pats21 |
| Posted: Mon Mar 19, 2007 1:15 am Post subject: |
|
|
Disciple
Joined: 08 Sep 2006
Posts: 154
|
Till now we have not completely run the utility till completion.
We are working on it.
will let you all know once we get some more info. |
|
| Back to top |
|
|
| Pats21 |
| Posted: Tue Oct 09, 2007 8:41 pm Post subject: |
|
|
Disciple
Joined: 08 Sep 2006
Posts: 154
|
Hi All,
The no. of records in our Audit_Trail table were very high ... hence the fmcsclad utility was taking time ...
I think the utility executes fine ... and can be used to clear the Audit_Trail table.
I would suggest to all to run this utility on periodic basics to keep the Audit_Trail table under control or else it might give problems, because we faced it. Our WF services dint start when the audit table got huge as the server was unable to make any further entries into the table.
We had to rename the table and create a new table with the same settings. This helped.
Pats ... |
|
| Back to top |
|
|
| Pats21 |
| Posted: Tue Jul 01, 2008 11:51 pm Post subject: |
|
|
Disciple
Joined: 08 Sep 2006
Posts: 154
|
Hi All,
I understand that the Cleanup Server deletes the records from the WORK_ITEM and PROCESS_INST tables for the instances that are at FINISHED state.
I wanted to know whether the Cleanup server will clear the AUDIT_TRAIL table as well for the records it is clearing from the above mentioned table.
Thanks,
Pats ... |
|
| Back to top |
|
|
| vijaycr |
| Posted: Wed Jul 02, 2008 12:24 am Post subject: |
|
|
Acolyte
Joined: 11 Oct 2006
Posts: 62
Location: Singapore
|
Clean up server doesnot touch the Audit_trail.
You need to use the fmcsclad utilty to clean up audittrail |
|
| Back to top |
|
|
|
|
