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 » IBM MQ File Transfer Edition » FTE DB Logger DB Clean up

Post new topic  Reply to topic
 FTE DB Logger DB Clean up « View previous topic :: View next topic » 
Author Message
bobbee
PostPosted: Wed Aug 15, 2012 5:50 am    Post subject: FTE DB Logger DB Clean up Reply with quote

Knight

Joined: 20 Sep 2001
Posts: 541
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
View user's profile Send private message Send e-mail AIM Address
w1ndy
PostPosted: Wed Dec 19, 2012 3:21 am    Post subject: Re: FTE DB Logger DB Clean up Reply with quote

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
View user's profile Send private message Send e-mail
bobbee
PostPosted: Wed Dec 19, 2012 4:41 am    Post subject: Reply with quote

Knight

Joined: 20 Sep 2001
Posts: 541
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
View user's profile Send private message Send e-mail AIM Address
w1ndy
PostPosted: Wed Dec 19, 2012 5:45 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
bobbee
PostPosted: Wed Dec 19, 2012 6:38 am    Post subject: Reply with quote

Knight

Joined: 20 Sep 2001
Posts: 541
Location: Tampa

Merry Christmas, Ho Ho Ho and all that other stuff!!!!!
Back to top
View user's profile Send private message Send e-mail AIM Address
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » IBM MQ File Transfer Edition » FTE DB Logger DB Clean up
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.