| Author |
Message
|
| satya2481 |
 Posted: Thu Sep 25, 2014 3:15 pm Post subject: Message Broker Flow for DB2 HouseKeeping |
 |
|
Disciple
Joined: 26 Apr 2007
Posts: 170
Location: Bengaluru
|
Hi All,
We are planning to use Message flow for Housekeeping DB2 database tables. In our case Housekeeping means deletion of the records from 4 tables at regular intervals. So would like to know is it a good way of coming up a flow to do DB2 house keeping. We are thinking to design the flow like below.
Timer Node -> Compute Node -> Database Node
Timer Node
- Triggers the flow at regular intervals
Compute Node
- Read the configuration details from a table. Like tables to be considered. How often the tables needs to be cleared etc
Database Node
- Executing the DELETE queries based on the configured parameters
Thank You
Satya
_________________
IBM Certified Solution Developer WebSphere Message Broker V6.0
IBM Certified System Administrator WebSphere MQ V6.0 |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Fri Sep 26, 2014 12:02 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I've done this sort of thing before and while it does work it can lead to a lot of issues when the Tables get large (> 1M rows)
The real solution is to use the facilities within the DB to do it. At the very minimum, a stored procedure for each of the tables.
Broker really isn't the right tool for this. There are better soluitions out there.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Sep 26, 2014 5:04 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Yeah, when I've done DB maintenance like this, I've used database triggers.
For example - every time an insert happens, remove all rows older than X days.
As smdavies99 says, you do have to consider the performance of large maintenance efforts. |
|
| Back to top |
|
|
| ruimadaleno |
| Posted: Fri Sep 26, 2014 5:58 am Post subject: |
|
|
Master
Joined: 08 May 2014
Posts: 274
|
Just use the tools to perform the functions they were designed for ... need db maintenance ? use db resources 
Using the wrong tool could get you a kick and shinny result but you will end up with a broken tool and a cloudy/sluggish environment.. 
WMB is just about integration and mediation, not db maintenance ...
_________________
Best regards
Rui Madaleno |
|
| Back to top |
|
|
| satya2481 |
| Posted: Sun Sep 28, 2014 1:36 pm Post subject: |
|
|
Disciple
Joined: 26 Apr 2007
Posts: 170
Location: Bengaluru
|
Hi All,
Thank you for your quick responses. I will rethink about this design. But anyone know which is best solution for this
- Stored Procedure which will be triggered at a regular intervals?
- Schedule a DB2 script to delete the records?
- Manually deleting the records with direct SQL queries at a regular intervals
Any other options ?
Two of the tables needs deleting every hour so that the WMB Main functionality flow will be faster. So we are thinking to come up with a message flow which will be in our control and can be configured with multiple tables and multiple conditions to delete the records. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Sep 29, 2014 4:49 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You keep thinking of "at regular intervals".
Does your database not support event-based triggering?
I did this with SQLServer more than 15 years ago. I'd be *shocked* if whatever DBMS you're using doesn't support triggering!
Again, I wrote a trigger that ran every time someone did an INSERT. This was a stored procedure that removed all rows older than X time. I never needed to worry about "did it run?".
The only "downside" to this is that you are keeping rows based on time, rather than based on the actual size of the table. but this is kind of a good thing in my mind. You know that you have *all* events for a given time period, not just "those events that still fit". |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Sep 30, 2014 2:52 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
| You keep thinking of "at regular intervals". |
How did you come to "an hour"? What happens if you get a lot of traffic and the table fills past X (where X is the maximum size for the database to be before things slow down) inside 30 minutes? Tell your users they're SOL for a while?
Also what you're describing is 4 database tables where the data stored in them has a limited useful life. That sounds to me a lot like a cache, and you should consider using something like that, which comes with the kind of old record deletion you're describing built in.
If you can't afford a cache, there's one built into IIBv9.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
