cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC Lookup in PI 7.1 - SELECT ? FROM DUAL and Connection timed out

former_member181959
Contributor
0 Kudos

Hi,

We have a scenarios (Idoc to JMS) with JDBC lookup. We have used graphical JDBC lookup functionality.

We are reading country names for a given country code from SAP in an external database table. The query is so simple. That should not take much time

Now the actual issues is,

When we are executing the scenario, its taking quite a long time. Almost 6 minutes to excute a mapping. Which causing high performance issue in the Porduction.

We started the inviestigation about found some interesting stuff. Here we have used Willy Introscope for the investigation.

1. First few messages are taking quite a long time. LIke 6 minutes per messages. As i can see in the log i am getting below error in Willy,

I dont know why PI is executing below queury apart from real secelt query to fetch the country name. I am getting below error: Error Message: Backends|ABCD2 mydatabase01-1526 (Oracle DB)|SQL|Dynamic|Query|SELECT ? FROM DUAL: java.sql.SQLException: Io exception: Connection timed out

2. After couple of messages, interface works very normal. I mean rest of the messages works pretty fine.

Please let me know if you have any idea about this error. What could be the problem for the issue.

Thank you in advnace.

Best Regards,

Prasad.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Did you check how many SQL requests were executed per one message ? Do you have a log of these SQL requests ?

I assume that the country table is quite small, so that lookup should not be an issue.

About this:

>Message: Backends|ABCD2 mydatabase01-1526 (Oracle DB)|SQL|Dynamic|Query|SELECT ? FROM DUAL: >java.sql.SQLException: Io exception: Connection timed out

1. I only know SELECT * FROM DUAL, not SELECT ? FROM DUAL. Better use the former

2. the exception means that the database server can not be reached => check your network configuration

So I assume that there is a network (performance) problem between PI and this Oracle server. Or the Oracle Server is so overloaded that it has (sometimes) problems in processing new requests.

CSY

Edited by: Christian Sy on Mar 9, 2010 10:17 AM

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Prasad,

Earlier to 7.1 each record will make a call to the database where it will take lot of time.

Instead in 7.1 you can do multiple records or all records within one call to the database. Here you can probably minimize your time. Please check this weblog for this. He has given for rfc lookup but you can do the same for jdbc also.

/people/shabarish.vijayakumar/blog/2010/01/28/graphical-rfc-lookup-and-how-you-can-design-lookups-for-multiple-values-to-be-retrieved-in-a-single-call

Regards,

---Satish