| Author |
Message
|
| deepak_paul |
 Posted: Tue Dec 22, 2009 9:13 pm Post subject: WMB Database hit - Performance issue |
 |
|
Centurion
Joined: 04 Oct 2008
Posts: 147
Location: US
|
All
We have an WMB requirement that we need to look up to DB2 database and get data(around 15 select queries) from the database. We are allowed to have less than 3 seconds to complete the whole transaction of retrieving data from database and formatting them(COBOL to XML) then sending to downstream system. As it is a code look up and maintenance of the database is not done quite often(probably no updates/inserts once loaded), we plan to save some mill seconds by avoiding the hit of database for each transaction, instead, possibly store the required look up data from database in to some local area or some other way in order to get retrieved quickly.
[This should be accessible by all message flows/instances in an execution group]
There are possible ways to approach this.
1. Call Java programs which can first time connect to the database and retrieve and dump the data into java hash maps then second time onwards no database hit but data is taken java hash maps.
2. Using MQ get node. Store the database data into a Q.
3. Using Shared Variables
4. Using ROW or LIST data type
Could you please advise me which would be best way to go ahead with. And also if there are a lot better other ways to do this, please let me know. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Tue Dec 22, 2009 9:25 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
May be this will give you some ideas:
We use WII to detect changes to the DB and build in the DB a "cache" for a specific query. We then retrieve the specific row of the cache via a request/reply type query using MQ and CICS triggered transaction.
Turn around for the request/reply message is around 125 ms avg including Cobol to XML transformation.
This would of course depend on your network and the size of the messages.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| deepak_paul |
| Posted: Wed Dec 23, 2009 1:36 am Post subject: |
|
|
Centurion
Joined: 04 Oct 2008
Posts: 147
Location: US
|
Thanks for your reply, fjb_saper.
Could you please elaborate your ideas so as to make that as a possible solution for my issue?
1. First off, What is WII? How it detects changes in teh DB[maybe the answer to the first question can avoid second question]
2. You meant building a cache in the DB itself. If, how we do build so? While retrieving data from DB cache, will this make a greater difference when the DB is comparively small.
Please elaborate your answer. |
|
| Back to top |
|
|
| sirsi |
| Posted: Wed Dec 23, 2009 6:40 am Post subject: |
|
|
Disciple
Joined: 11 Mar 2005
Posts: 177
|
| Quote: |
| retrieve the specific row of the cache via a request/reply type query using MQ and CICS triggered transaction. |
if the query size is less probably using shared variables is the best option.
but u need to have some logic to retrive updated data from db... |
|
| Back to top |
|
|
| deepeshk79 |
| Posted: Fri Dec 25, 2009 11:54 am Post subject: |
|
|
Apprentice
Joined: 25 Mar 2007
Posts: 45
Location: Los Angeles
|
If you have WMB 6.0 and above then one of the good options here seems to be defining ROW Shared Variables
DECLARE rowsharedvariable[] ROW SHARED ROW[Select R.a1, R.a2 from Table1 As R]
The rowsharedvariable[] can be either at node level (within module) or at a flow level (within schema)
-- Deepesh |
|
| Back to top |
|
|
|
|
