Handle Toolbox JDBC Long Running Queries

If the IBM i Db2 predictive query governor estimates the query takes longer than the specified time, the Toolbox for Java AS400JDBCStatement setQueryTimeout() method prevents a query from running. But there are cases occasionally when the query takes much longer to run than estimated.

To stop a currently running statement from within your client-side Java code, the following options are available:

Option 1: This option is by far the simplest case. Set the toolbox JDBC connection property, "query timeout mechanism=cancel" and use the setQueryTimeout() method to specify the timeout period. If the query takes longer that the specified limit, the JDBC driver creates a second connection to issue an SQLCancel() on the statement.

Option 2: This option is the next simplest, but relies on the query either completing or successfully being cancelled on the IBM i Db2 database server.

There is a Statement.cancel() method that can be used to end a running query. To leverage this method, a new thread must be created when a statement is run. Here are some code samples that illustrate how to use this method.

Before you run the query, start the timeout thread.

                // Create a thread to do the cancel if needed.  Start the thread.
                cancelThread_ = new QueryCancelThread(statement);
                cancelThread_.setDaemon(true);
                cancelThread_.start();


The code for the QueryCancelThread looks like the following.

class QueryCancelThread extends Thread {
    Statement statement_;

    public DB2QueryCancelThread(Statement s)
    {
        statement_ = s;
    }

    public void run()
    {
        try {
   if (statement_  != null) {
sleep(statement_.queryTimeout_ * 1000);
if ((statement_ != null) && (statement_.queryRunning_)) {
   statement_.cancel();
}
   }
        } catch (Exception e) {
               // Ignore exception (most likely from cancel)
        }
    }
}


After the query is done, the query cancel thread must be ended.

                // Detach the thread from the statement.
                cancelThread_.statement_ = null;
                // Interrupt the thread so that it wakes up and dies.
                cancelThread_.interrupt();



Option 3: A similar solution option would be to create a second thread to run the query and then cancel the statement from the current thread. The advantage of this approach is that the current thread does not hang when the cancel is unsuccessful.


Option 4: Another option is to use the QSYS2.SQL_CANCEL stored procedure to end the request in the job. To use it, you would need to establish another connection to the server. The parameters to the stored procedure are the following.

CALL QSYS2.CANCEL_SQL('483456/QUSER/QZDASOINIT');

You can get the job used by a connection by calling AS400Connection.getServerJobIdentifier().
This job name is returned in the form "QZDASOINITQUSER 364288", but is easily reformatted by using the following code.

jobName = jobName.substring(20).trim()+"/"+jobName.substring(10,20).trim() +"/"+jobName.substring(0,10).trim();