#401 Attempt to cancel SQL execution fails on MSGW

[Pafsanias Ftakas]

We are calling a DB2 stored procedude through JDBC using the JT400 library. We have set the query timeout to 90 secs and we are using the QueryTimeoutMechanism flag set to "cencel", so that it honours the timeout even for non-select statements. If appears that the cancellation mechanism works great when the stored procedure executes normally.

However, when the stored procedure execution gets blocked by a MSGW in the IBM-i side the attempt to cancel the stored procedure execution apparently fails and the original Java thread that made the call remains blocked waiting for the IBM-i side to answer. When the MSGW blocked job gets cancelled from the IBM-i side, we get an SQLException in the Java thread, which is to be expected I supposed.

The questions we have are the following: is the fact that cancelling the job blocked with MSGW not working a normal situtation, or is there a problem in the jt400<->IBM-i communication? Is there a way to force the job that is blocked in MSGW to "honour" the cancel operation that is being attempted by the driver? Or is there no way to do this?

I attach a log trace from the Toolbox for the situation I am describing:
a stored procedure is called @ line 11 in the attached file,
the query timeout is set to 90 secs @ line 21,
as expected (90 secs later) @ line 351 the QueryCancelThread wakes up and attempts to cancel the statement,
the cancel attempt finishes with an abrupt socket disconnection @ line 581.

[John Eberhard]

You will need to work with IBM service to determine why the cancel does not stop the current SQL operation when the job is stuck on a MSGW condition. It could be a limitation of the SQL cancel support that a job in a MSGW condition cannot be cancelled.

Another option to try is to prevent the MSGW condition on the server. One way to do that is to change the system reply list to respond to the MSGW and have the server job use the system reply list.