JDBC Receiver adapter not reaching timeout

[Former Member]

Hi Everyone,

The adapter is hooked up with the Oracle DB version 10g 10.2.0.5. We are trying to run a stored procedure to clear the tables before the INSERT.

Stored Procedure:

BEGIN

Truncate AD.SAP_DIR table */

EXECUTE IMMEDIATE 'TRUNCATE TABLE AD.SAP_DIR';

ResponseOut := 'COMPLETE';

END;

No matter whatever we try the message are not leaving the status "Delivering" in message monitoring. We have tried to use the timeout parameters as mentioned below, but no luck and the DB also holds the thread after executing the TRUNCATE command.

poolWaitingTime 6000

driver:oracle.jdbc.ReadTimeout 5000

driver:oracle.net.CONNECT_TIMEOUT 5000

sqlquerytimeout 20

It would be highly appretiated if you can throw some light on this.

Thanks,

AJ

[rajasekhar_reddy14]

Try to restart JDBC adapter service in NWA.

[baskar_gopalakrishnan2]

>No matter whatever we try the message are not leaving the status "Delivering" in message monitoring. We have tried to use the timeout parameters as mentioned below, but no luck and the DB also holds the thread after executing the TRUNCATE

Check whether any issue with firewall ports between PI server and database. Also add logSQLStatment as parameter in the advanced tab of receiver jdbc adapter and set the value true. This might provide some more information about error message. Also check whether db side any issue with thread locks or so. Contact DBA to analyze this.

[Former Member]

Thanks for the reply Basker.

logSQLStatment is already added. There is no firewall issue. We have tested this interface with an INSERT statement and it updates the database properly.

The trace stops at the point after calling the Stored procedure. DBA says the PROC gets executes successfully and thread waits for something else. In this case not sure why the thread is not reaching the timeout even after configuring the parameters.

Any thoughts.

Thanks,

AJ

[baskar_gopalakrishnan2]

I suspect there could be some issue with stored procedure call or Are you using help document recommended message structure for stored procedure? If feasible test it outside PI and see what's going on.