| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| FTE DB Logger DB Clean up |
« View previous topic :: View next topic » |
| Author |
Message
|
| bobbee |
 Posted: Wed Aug 15, 2012 5:50 am Post subject: FTE DB Logger DB Clean up |
 |
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
Has anyone written SQL to clean up the Logger DB? My customer has 4 million records on the DB. They have a Web App that interfaces with the DB for queries. I have approached Hursley for these scripts to clean up their DB and have said it is a customers responsibility. I never heard this from WMB development about the Broker and Config managers DB when they were there. My feeling is this is a product required DB and the product support should supply the scripts.
Anyway, anybody approached this? |
|
| Back to top |
|
 |
| w1ndy |
| Posted: Wed Dec 19, 2012 3:21 am Post subject: Re: FTE DB Logger DB Clean up |
|
|
Apprentice
Joined: 19 Jan 2011
Posts: 38
|
| bobbee wrote: |
Has anyone written SQL to clean up the Logger DB? My customer has 4 million records on the DB. They have a Web App that interfaces with the DB for queries. I have approached Hursley for these scripts to clean up their DB and have said it is a customers responsibility. I never heard this from WMB development about the Broker and Config managers DB when they were there. My feeling is this is a product required DB and the product support should supply the scripts.
Anyway, anybody approached this? |
Didn't get as far as writing the SQL , but it would be a necessary evil as far as I can see. It isn't really a product required DB as you have the choice not to log (which I ended up doing due to the masses of records). Might implement the file based logging with 7.5 though it might be easier to manage.
Windy |
|
| Back to top |
|
|
| bobbee |
| Posted: Wed Dec 19, 2012 4:41 am Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
I should have posted these a while back, sorry. Try these.
DELETE FROM FTELOG.AUTH_EVENT WHERE TIME < SYSDATE - 90;
DELETE FROM FTELOG.TRANSFER_CALLS WHERE TRANSFER_ID IN (SELECT t.TRANSFER_ID FROM FTELOG.TRANSFER_EVENT te, FTELOG.TRANSFER t WHERE t.COMPLETE_ID = te.ID AND te.ACTION_TIME < SYSDATE - 90);
DELETE FROM FTELOG.FILE_SPACE_ENTRY WHERE TRANSFER_ITEM_ID IN(SELECT ti.ID FROM FTELOG.TRANSFER_ITEM ti, FTELOG.TRANSFER_EVENT te, FTELOG.TRANSFER t WHERE ti.TRANSFER_ID = t.TRANSFER_ID AND t.COMPLETE_ID = te.ID AND te.ACTION_TIME < SYSDATE - 90);
DELETE FROM FTELOG.TRANSFER_ITEM WHERE TRANSFER_ID IN(SELECT t.TRANSFER_ID FROM FTELOG.TRANSFER_EVENT te, FTELOG.TRANSFER t WHERE t.COMPLETE_ID = te.ID AND te.ACTION_TIME < SYSDATE - 90);
DELETE FROM FTELOG.TRANSFER_CORRELATOR WHERE ID NOT IN (SELECT SOURCE_CORRELATOR_ID FROM FTELOG.TRANSFER_ITEM WHERE SOURCE_CORRELATOR_ID IS NOT NULL) AND ID NOT IN (SELECT DESTINATION_CORRELATOR_ID FROM FTELOG.TRANSFER_ITEM WHERE DESTINATION_CORRELATOR_ID IS NOT NULL);
DELETE FROM FTELOG.TRANSFER_EXIT WHERE TRANSFER_ID IN (SELECT t.TRANSFER_ID FROM FTELOG.TRANSFER_EVENT te, FTELOG.TRANSFER t WHERE t.COMPLETE_ID = te.ID AND te.ACTION_TIME < SYSDATE - 90);
DELETE FROM FTELOG.TRANSFER_STATS WHERE TRANSFER_ID IN (SELECT t.TRANSFER_ID FROM FTELOG.TRANSFER_EVENT te, FTELOG.TRANSFER t WHERE t.COMPLETE_ID = te.ID AND te.ACTION_TIME < SYSDATE - 90);
DELETE FROM FTELOG.TRANSFER WHERE TRANSFER_ID IN (SELECT t.TRANSFER_ID FROM FTELOG.TRANSFER_EVENT te, FTELOG.TRANSFER t WHERE t.COMPLETE_ID = te.ID AND te.ACTION_TIME < SYSDATE - 90);
DELETE FROM FTELOG.TRIGGER_CONDITION WHERE TRANSFER_EVENT_ID NOT IN (SELECT te.ID FROM FTELOG.TRANSFER_EVENT te, FTELOG.TRANSFER t WHERE t.START_ID = te.ID);
DELETE FROM FTELOG.TRANSFER_EVENT WHERE ID NOT IN (SELECT te.ID FROM FTELOG.TRANSFER_EVENT te, FTELOG.TRANSFER t WHERE t.COMPLETE_ID = te.ID) AND ID NOT IN (SELECT te.ID FROM FTELOG.TRANSFER_EVENT te, FTELOG.TRANSFER t WHERE t.START_ID = te.ID);
DELETE FROM FTELOG.TRANSFER_CD_NODE WHERE ID NOT IN (SELECT SOURCE_CD_NODE_ID FROM FTELOG.TRANSFER_EVENT WHERE SOURCE_CD_NODE_ID IS NOT NULL) AND ID NOT IN (SELECT DESTINATION_CD_NODE_ID FROM FTELOG.TRANSFER_EVENT WHERE DESTINATION_CD_NODE_ID IS NOT NULL);
DELETE FROM FTELOG.CALL_RESULT WHERE CALL_ID IN (SELECT cr.CALL_ID FROM FTELOG.CALL_REQUEST cr WHERE cr.ACTION_TIME < SYSDATE - 90);
DELETE FROM FTELOG.CALL_ARGUMENT WHERE CALL_ID IN (SELECT cr.CALL_ID FROM FTELOG.CALL_REQUEST cr WHERE cr.ACTION_TIME < SYSDATE - 90);
DELETE FROM FTELOG.CALL WHERE ID IN (SELECT cr.CALL_ID FROM FTELOG.CALL_REQUEST cr WHERE cr.ACTION_TIME < SYSDATE - 90);
DELETE FROM FTELOG.CALL_REQUEST WHERE ACTION_TIME < SYSDATE - 90;
DELETE FROM FTELOG.METADATA WHERE TRANSFER_EVENT_ID IS NOT NULL AND TRANSFER_EVENT_ID NOT IN (SELECT TRANSFER_EVENT_ID FROM FTELOG.METADATA WHERE TRANSFER_EVENT_ID IS NOT NULL AND TRANSFER_EVENT_ID IN (SELECT ID FROM FTELOG.TRANSFER_EVENT WHERE ID IS NOT NULL));
DELETE FROM FTELOG.METADATA WHERE STANDALONE_CALL_ID IS NOT NULL AND STANDALONE_CALL_ID NOT IN (SELECT STANDALONE_CALL_ID FROM FTELOG.METADATA WHERE STANDALONE_CALL_ID IS NOT NULL AND STANDALONE_CALL_ID IN (SELECT ID FROM FTELOG.CALL_REQUEST WHERE ID IS NOT NULL));
DELETE FROM FTELOG.MONITOR_EXIT_RESULT WHERE ACTION_ID IN (SELECT mer.ACTION_ID FROM FTELOG.MONITOR_ACTION ma, FTELOG.MONITOR_EXIT_RESULT mer WHERE mer.ACTION_ID = ma.ID AND ma.TIME < SYSDATE - 90);
DELETE FROM FTELOG.MONITOR_METADATA WHERE ACTION_ID IN (SELECT mm.ACTION_ID FROM FTELOG.MONITOR_ACTION ma, FTELOG.MONITOR_METADATA mm WHERE mm.ACTION_ID = ma.ID AND ma.TIME < SYSDATE - 90);
DELETE FROM FTELOG.MONITOR_ACTION WHERE TIME < SYSDATE - 90; |
|
| Back to top |
|
|
| w1ndy |
| Posted: Wed Dec 19, 2012 5:45 am Post subject: |
|
|
Apprentice
Joined: 19 Jan 2011
Posts: 38
|
| bobbee wrote: |
I should have posted these a while back, sorry. Try these.
; |
Hey Bobbee, thanks very much for these.
Kind regards
Windy |
|
| Back to top |
|
|
| bobbee |
| Posted: Wed Dec 19, 2012 6:38 am Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
| Merry Christmas, Ho Ho Ho and all that other stuff!!!!! |
|
| 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
|
|
|
|
