| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| purge WMB* tables (record and replay) |
« View previous topic :: View next topic » |
| Author |
Message
|
| ruimadaleno |
 Posted: Wed May 21, 2014 5:26 am Post subject: purge WMB* tables (record and replay) |
 |
|
Master
Joined: 08 May 2014
Posts: 274
|
Hi all,
in my company every message flow deployed to message broker has monitoring enabled in the SOAP Input node "transaction start" event and SOAP Reply node "terminal in" event.
In the test environments, message flow monitoring is active for every message flow deployed in message broker, generating an high volume of information in the tables (WMB_*) that support the record and replay
Those tables are stored in an oracle database.
The tables are:
WMB_MSG - This table holds information for every event emited
WMB_BINARY_DATA - This table holds the binary data for events that include the event payload. It relates to wmb_msg table by the column wmb_msgkey)
WMB_EVENT_FIELDS - ???
WMB_EVENT_TYPES - ???
Question 1: What information is supposed to be stored in the WMB_EVENT_FIELDS and WMB_EVENT_TYPES ? I have several ( > 200.000) events in the wmb_msg table but these two are empty 
Question 2: What is the best strategy to purge this tables (if we let this tables without maintenance it will grow up consuming a lot - some GB of space) ? I'd like to ear from your experience, what are the policy in place to clean this tables. Is it safe to simply truncate this tables in a regular basis (every month)
_________________
Best regards
Rui Madaleno |
|
| Back to top |
|
 |
| jsware |
| Posted: Wed May 21, 2014 11:44 pm Post subject: |
|
|
Chevalier
Joined: 17 May 2001
Posts: 455
|
We wrote a flow to delete messages older than a configured time.
You could set this to run every hour/day/month with a timer node depending on how long you want to keep data - i.e. don't run it every hour if you want to keep stuff for 1 month.
But if your volumes are high enough to be keeping stuff only for a few hours, (  ) then running every hour is probably required.
_________________
Regards
John
The pain of low quaility far outlasts the joy of low price. |
|
| Back to top |
|
|
| ruimadaleno |
| Posted: Thu May 22, 2014 2:20 am Post subject: |
|
|
Master
Joined: 08 May 2014
Posts: 274
|
| jsware wrote: |
We wrote a flow to delete messages older than a configured time.
You could set this to run every hour/day/month with a timer node depending on how long you want to keep data - i.e. don't run it every hour if you want to keep stuff for 1 month.
But if your volumes are high enough to be keeping stuff only for a few hours, ( ) then running every hour is probably required. |
Hi jsware, thank you for you reply.
the "maintenance flow" that you built deletes data from all WMB_* tables ?
_________________
Best regards
Rui Madaleno |
|
| Back to top |
|
|
| ruimadaleno |
| Posted: Mon May 26, 2014 1:52 am Post subject: |
|
|
Master
Joined: 08 May 2014
Posts: 274
|
for anyone who might be looking for a way to purge the WMB* tables in a automated fashion, i'm post the source code of the job that purges this information in my Staging and Develoment environments. This job is executed every seven days at a specific hour.
| Code: |
declare
pNumDays pls_integer := 30;
begin
/*
INFO: this jobs deletes info from wmb_* tables older than pNumDays days
wmb_* tables store information message flows event monitoring
(message broker record and replay)
*/
-- delete binarydata info from events older than xx days
delete mbrecord.wmb_binary_data a
where a.wmb_msgkey in (
select wmb_msgkey
from mbrecord.wmb_msgs
where to_date( substr(event_timestamp,1,19),'yyyy-mm-dd hh24:mi:ss') < trunc(sysdate) - pNumDays
);
-- delete events older than XX days
delete from mbrecord.wmb_msgs
where to_date( substr(event_timestamp,1,19) ,'yyyy-mm-dd hh24:mi:ss') < trunc(sysdate) - pNumDays ;
commit;
end;
|
My environemnt
Broker 8.0.0.3 running on windows
Record and Replay data stored in an Oracle Database 11.2 running on windows
_________________
Best regards
Rui Madaleno |
|
| 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
|
|
|
|
