Intermittent issue when calling an oracle stored procedure

,
1

Hi,

I’m relatively new to webMethods and I’m executing an Oracle Stored Procedure to update a table from the IS and the Oracle database is running slow - Intermittently I encounter a “IO Error: Socket read timed out”.
I have configured a LOCAL_TRANSACTION JDBC connection as follows:

Enable Connection Pooling is set to true
Minimum Pool Size is 0
Maximum Pool Size is 20
Pool Increment Size 1
Block Timeout (msec) 15000
Expire Timeout (msec) 60000
Startup Retry Count is 0
Startup Backoff Timeout (sec) is 10

Our webMethods release is 9.7, JDBC adapter is at 6.5 and we’re using odbc7.jar JAVA 1.7.

The issue I have is that the “consumer” of the service is experiencing a “timeout” as it’s not received a response within 30 seconds and resends the data which adds duplicate data into the database.
How can I make the call to the backend database/stored procedure execution fail and rollback if taking longer than 20 seconds to run?
This would tell the “consumer” of the service that a temporary error has occurred and they should “retry”.

Any advice?

2

Make sue you have the latest JDBC adapter fix levels installed on IS.

For this error its a good practice to co-ordinate with DBA and if required with network team to troubleshoot the issue together.

Not sure if making Minimum Pool Size=1 will help to this case but give a try. But from my experience its not a good practice because one stale connection will be open.

3

How can I make the call to the backend database/stored procedure execution fail and rollback if taking longer than 20 seconds to run?

For this if you can make use of “Query Time Out” to see if this helps. For more details refer JDBC adapter guide.

4

Setting the query time out does not work with stored procedures - certainly not on the baseline version of the JDBC Adapter 6.5.
When I changed this from -1 (default value) to 20 I encountered the following error:

Error = '[ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service {serviceName}.
[ADA.1.316] Cannot execute the SQL statement “{? = call (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }”. "
(65000/6550) ORA-06550: line 1, column 13:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
ORA-06550: line 1, column 13:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
', Type = ‘com.wm.pkg.art.error.DetailedServiceException’,

5

did you able to successfully execute the storedProc from DB ? From error could not able to figure out the exact one.

Thanks,