| Author |
Message
|
| wmb_wps_soa |
 Posted: Sun Oct 30, 2011 12:26 am Post subject: Expensive Database Extraction in Message Broker 7 |
 |
|
Acolyte
Joined: 19 Feb 2010
Posts: 65
Location: Detroit,Michigan,USA.
|
Hi,
I have a batch message flow that runs a day which extracts thousands of CLOB data (EDI X12 data) from DB2 database to a file. Since all the CLOB data to be written to a single file, there is a WHILE loop to accumulate all the extracted data from table to single character field. Since the data size is big, the database extraction and the looping operations are taking hours for extracting 5000 data from db. But in real time, we will get maximum 60,000 data in a day.
Since its taking lot of CPU memory and running for hours, we are trying to find a way to optimize the flow to improve the flow performance and processing time. We are thinking of calling a DB2 stored procedure which does the database extraction and looping. Is there any other way that can be used to improve the flow performance?
Thank you experts in advance. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Sun Oct 30, 2011 8:01 am Post subject: Re: Expensive Database Extraction in Message Broker 7 |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| wmb_wps_soa wrote: |
Hi,
I have a batch message flow that runs a day which extracts thousands of CLOB data (EDI X12 data) from DB2 database to a file. Since all the CLOB data to be written to a single file, there is a WHILE loop to accumulate all the extracted data from table to single character field. Since the data size is big, the database extraction and the looping operations are taking hours for extracting 5000 data from db. But in real time, we will get maximum 60,000 data in a day.
Since its taking lot of CPU memory and running for hours, we are trying to find a way to optimize the flow to improve the flow performance and processing time. We are thinking of calling a DB2 stored procedure which does the database extraction and looping. Is there any other way that can be used to improve the flow performance?
Thank you experts in advance. |
If you have a hammer, everything looks like a nail....
Why are you using WMB to do this and not a simple scheduled DB program or stored procedure? This should take secs for 5000 records and not hours... Depending on the size of the CLOB YMMV
Anyway the while loop is the wrong thing to do in WMB. Just propagage each record to the file output node and close the file when done... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Oct 30, 2011 9:54 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| And you might consider using the DatabaseInput node instead of an ESQL select. |
|
| Back to top |
|
|
| wmb_wps_soa |
| Posted: Sun Oct 30, 2011 1:56 pm Post subject: |
|
|
Acolyte
Joined: 19 Feb 2010
Posts: 65
Location: Detroit,Michigan,USA.
|
We had to go for WMB, as we are doing some routing and mapping using WTX map node in the message flow. Everything else works fine and fast except this database extracting and looping.
This is not event based database integration, thats why we did not go for DatabaseInsert node.
I am new DB2 storeprocedure, will try creating a stored procedure to replace the expensive database/looping operation in the flow.
Thank you. |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Mon Oct 31, 2011 6:23 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
| wmb_wps_soa wrote: |
We had to go for WMB, as we are doing some routing and mapping using WTX map node in the message flow. Everything else works fine and fast except this database extracting and looping.
This is not event based database integration, thats why we did not go for DatabaseInsert node.
I am new DB2 storeprocedure, will try creating a stored procedure to replace the expensive database/looping operation in the flow.
Thank you. |
why cant you do this extract in a separate WTx map flow, instead of doing it in MB
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| kimbert |
| Posted: Mon Oct 31, 2011 1:43 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| why cant you do this extract in a separate WTx map flow |
Perhaps because wmb_wps_soa doesn't use WTX for anything else? If I was wmb_wps_soa, I would think very hard indeed before introducing (yet) another technology into the mix. Especially if fjb_saper is correct, and the existing DB can do the job in seconds. |
|
| Back to top |
|
|
| wmb_wps_soa |
| Posted: Tue Nov 01, 2011 6:53 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2010
Posts: 65
Location: Detroit,Michigan,USA.
|
Actually our architecture to NOT use WTX maps for database extraction. So we dont use WTX maps to talk to databases directly.
I am trying to create a DB2 stored procedure to replace the expensive the database extraction and looping in the message flow. |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Tue Nov 01, 2011 7:36 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
| wmb_wps_soa wrote: |
Actually our architecture to NOT use WTX maps for database extraction. So we dont use WTX maps to talk to databases directly.
I am trying to create a DB2 stored procedure to replace the expensive the database extraction and looping in the message flow. |
very sad to know that you got a product and you are not utilizing it properly...... 
the second question: if that is the case why you bought it 
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| wmb_wps_soa |
| Posted: Tue Nov 01, 2011 8:50 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2010
Posts: 65
Location: Detroit,Michigan,USA.
|
Hi Gayathri,
We are using WTX maps for EDI X12 transformation.
Thanks |
|
| Back to top |
|
|
|
|
