| Author |
Message
|
| touchofcrypticthunder |
 Posted: Wed Apr 18, 2012 5:25 am Post subject: Integration with JDE iSeries DB2 timing out in UI |
 |
|
Apprentice
Joined: 08 Jul 2009
Posts: 30
|
I am working on a solution where Message broker (V7) is integrated with front end UI and backed SAP services and JDE DB2 for customer search.
Integration summary is as follows:
-----------------------------------------
> UI sends data to Message Broker through SOAP protocol
> Message broker calls backed SAP services
> Message broker enrich the data received from SAP with DB2 records from JDE iSeries
> Message broker finally sends the reply to UI
Issue description:
---------------------
Some searches are timing out in front end UI and timeout defined in UI is 120 sec. After doing lot of testing, it was found that call to JDE DB2 is causing timeout in UI as it is taking more time. If we disconnect JDE part in the integration then response is received within few sec.
But if the SQL query is executed in iSeries command console, the result is received within less than a second and result contains 3000+ records. When Message Broker receives this result set and process the records, UI is timing out. We have tried left join and right join on DB2 which did not help improve the performance.
Please provide your inputs to resolve this performance issue. |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Apr 18, 2012 5:38 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
When you turn on User Trace, what does the log show? When you turn on ODBC trace what does that log show? When you put Trace nodes in your flows, before and after each Compute node, which node takes the longest? What version of WMB are you using? V7 - what? WMB version numbers have four digits.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Apr 18, 2012 5:46 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
the most likely cause is that your flow is using ESQL and you are using numbered indexes instead of references to loop over the result set and populate the output tree.
always use references. |
|
| Back to top |
|
|
| touchofcrypticthunder |
| Posted: Wed Apr 18, 2012 5:50 am Post subject: |
|
|
Apprentice
Joined: 08 Jul 2009
Posts: 30
|
| Quote: |
| When you turn on User Trace, what does the log show? When you turn on ODBC trace what does that log show? When you put Trace nodes in your flows, before and after each Compute node, which node takes the longest? What version of WMB are you using? V7 - what? WMB version numbers have four digits |
WMB Version is V7.0.0.3. The node which is taking time is Compute node where JDE call is made. |
|
| Back to top |
|
|
| touchofcrypticthunder |
| Posted: Wed Apr 18, 2012 5:53 am Post subject: |
|
|
Apprentice
Joined: 08 Jul 2009
Posts: 30
|
| mqjeff wrote: |
the most likely cause is that your flow is using ESQL and you are using numbered indexes instead of references to loop over the result set and populate the output tree.
always use references. |
You are right. Coding is done using ESQL. I have tried to convert the indexed loops with ESQL references. But some loops are still using indexes. I will check if converting this to references solves the issue. |
|
| Back to top |
|
|
| touchofcrypticthunder |
| Posted: Wed Apr 18, 2012 6:38 am Post subject: |
|
|
Apprentice
Joined: 08 Jul 2009
Posts: 30
|
| touchofcrypticthunder wrote: |
| mqjeff wrote: |
the most likely cause is that your flow is using ESQL and you are using numbered indexes instead of references to loop over the result set and populate the output tree.
always use references. |
You are right. Coding is done using ESQL. I have tried to convert the indexed loops with ESQL references. But some loops are still using indexes. I will check if converting this to references solves the issue. |
After doing some more tests with and without JDE call, got to know that the execution of the below SQL statement is taking more time and causing timeout in JDE.
SET Environment.Variables.JDEAvailableInventory.row=PASSTHRU(sqlstatment);
In most of the cases FOR loop with ref is being used to loop through the items.
FOR jdeRef AS ev.JDEAvailableInventory.row[] DO
.
.
.
END FOR;
I hope this is another way of using references to loop thru which is equivalent to WHILE loop with REF and MOVE statement.
Please provide your inputs. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Apr 18, 2012 6:43 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Inside that FOR loop, are you doing something like
| Code: |
| Set OutputRoot.XMLNSC.Body.Result[i]=jdeRef; |
?
It's not enough to use references to match the input row, you also need to use references to build the output row.
And you might look at a user trace of the flow. This will help you determine where the most time is actually being taken.
It is possible that it's being taken in
| Code: |
| SET Environment.Variables.JDEAvailableInventory.row=PASSTHRU(sqlstatment); |
If that's the case, you need to look at ODBC trace and how you're building sqlsatement and etc. etc. etc. |
|
| Back to top |
|
|
| touchofcrypticthunder |
| Posted: Wed Apr 18, 2012 7:08 am Post subject: |
|
|
Apprentice
Joined: 08 Jul 2009
Posts: 30
|
| Quote: |
| It's not enough to use references to match the input row, you also need to use references to build the output row |
References are used not only to match the input row but also to build the output tree.
Just by commenting the sql statement excution, response was received well under 5 seconds. This helped me to conclude that cause for timeout is SQL statement execution.
I will check further on user trace and ODBC trace and share you the result.
Thanks for your inputs. |
|
| Back to top |
|
|
| touchofcrypticthunder |
| Posted: Wed May 09, 2012 9:12 am Post subject: |
|
|
Apprentice
Joined: 08 Jul 2009
Posts: 30
|
| Continuation with Performance of JDE, we are currently executing SQL statement as PASSTHRU. Is it going to improve the performance, if we replace this with STORED PROCEDURE? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed May 09, 2012 7:58 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| touchofcrypticthunder wrote: |
| Continuation with Performance of JDE, we are currently executing SQL statement as PASSTHRU. Is it going to improve the performance, if we replace this with STORED PROCEDURE? |
Possibly, but quite unlikely. What really needs to be tuned is the way the SQL statement is being executed. Just moving it into a stored procedure won't do the trick. At the same time talk to your DB folks, maybe all that is missing is an index?
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
