Synchronous JDBC Timeout

[markbernabe]

Hi Experts, I have a synchronous proxy-jdbc with SQL_QUERY (Select) as action. When the query is executed directly in the DB, it's only taking 3-4 minutes to get the results. But when it's run thru PI, we always encounter synchronous timeout exceeded error (20 minutes). Since the query is getting results on time in the DB, I'm thinking the problem could be in PI side but I'm running out of options to check. Has anyone faced this problem before? Any inputs will be greatly appreciated. Thanks!

[markbernabe]

Some additional information: When we try to query for a few rows (1-5), it's working. We're getting the response in seconds. However, if we run the normal query which is supposed to get 300k records with 12 fields, we always get timeout. What I would like to understand is the reason why we're able to fetch all the results in the DB in less than 5 minutes but if we send it thru PI, 20 minutes (current timeout) is not enough. Can JDBC handle 300k+ records? Configuration is ICO. I've also added the parameters sqlquerytimeout in the JDBC channel and XI.Timeout in SOAP sender to no avail. What I haven't done yet (due to missing authorization) is to change the value of xiadapter.inbound.timeout.default. But would that help?

[markangelo_dihiansan]

Hi Mark,

Can you try checking "Disconnect from database after each message processing" in the advanced mode? Also try using Native SQL instead of XML SQL. What does RWB communication channel monitoring says?

Regards,

Mark

[markbernabe]

Hi Mark,

Same error after checking 'Disconnect from Database..'. Here's the log from RWB.

[markangelo_dihiansan]

Hi Mark,

How many records before it gets into error? How about trying native sql in ID? Did it help?

Regards,

Mark

[markbernabe]

Hi Mark, Now we're trying to query by range. So far 10k records is fine. I haven't tried Native SQL yet. Do I need any specific XML format for that? Or will it work as long as the SQL statement exists in any field?

[markangelo_dihiansan]

Hi Mark,

Yes, it is better to use query by range. I have not used it too but based on this help Defining XML Documents for Message Protocol Native SQL Format - Configuring the JDBC Adapter in the ...

the output is in non-xml format (also used for testing purposes only).

Regards,

Mark

[markangelo_dihiansan]

Hi Mark,

It looks like a network problem. Can you ping the database hostname in your PI system (create ping command in SM69)? Also take a look at this note: Note 1085539 - XI JDBC/FTP connection Test and XML Validation Application

Hope this helps,

Mark

[markbernabe]

Hi Mark, Unfortunately, I don't have SM69 authorization. I will check with Basis. Thanks!