DB2 CLI can run a subset of functions asynchronously; the DB2 CLI driver returns control to the application after calling the function, but before that function has finished executing. The function returns SQL_STILL_EXECUTING each time it is called until it is finished running, at which point it returns a different value (SQL_SUCCESS for example).
Asynchronous execution is only beneficial on single-threaded operating systems. Applications that run on multithreaded operating systems should execute functions on separate threads instead.
Asynchronous execution is possible for those functions that normally send a request to the server and then wait for a response. Rather than waiting, a function executing asynchronously returns control to the application. The application can then perform other tasks, or return control to the operating system, and use an interrupt to repeatedly poll the function until a return code other than SQL_STILL_EXECUTING is returned.
Each application that will will run functions asynchronously must complete the following steps in addition to the normal CLI steps, in the following order:
To ensure that functions can be called asynchronously, the application should call SQLGetInfo() with an option of SQL_ASYNC_MODE.
/* See what type of Asynchronous support is available. */
rc = SQLGetInfo( hdbc, /* Connection handle */
SQL_ASYNC_MODE, /* Query the support available */
&ubuffer, /* Store the result in this variable */
4,
&outlen);
The call to SQLGetInfo() will return one of the following values:
Statement level asynchronous execution is set using the statement attribute SQL_ATTR_ASYNC_ENABLE. An application can have at most 1 active function running in asynchronous mode on any one connection. It should be set to SQL_ASYNC_ENABLE_ON using SQLSetStmtAttr().
/* Set statement level asynchronous execution on */
rc = SQLSetStmtAttr( hstmt, /* Statement handle */
SQL_ATTR_ASYNC_ENABLE,
(SQLPOINTER) SQL_ASYNC_ENABLE_ON,
0);
Connection level asynchronous execution is set using the connection attribute SQL_ATTR_ASYNC_ENABLE. It should be set to SQL_ASYNC_ENABLE_ON using SQLSetConnectAttr().
All statements already allocated, as well as future statement handles allocated on this connection will be enabled for asynchronous execution.
In either case the functions will be executed synchronously. If the application does call SQLSetStmtAttr() or SQLSetConnectAttr() to turn on asynchronous execution, the call will return an SQLSTATE of 01S02 (option value changed).
When the application calls a function that can be run asynchronously one of two things can take place.
In this case the application runs as it would if the asynchronous mode had not been enabled.
See the SQL_ATTR_ASYNC_ENABLE statement attribute in the SQLSetStmtAttr() function for a list of functions that can be executed asynchronously.
The following example demonstrates a common while loop that takes both possible outcomes into account:
while ( (rc = SQLExecDirect(hstmt, sqlstmt, SQL_NTS) ) == SQL_STILL_EXECUTING)
{
/* Other processing can be performed here, between each call to
* see if SQLExecDirect() has finished running asynchronously.
* This section will never run if CLI runs the function
* synchronously.
*/
}
/* The application continues at this point when SQLExecDirect() */
/* has finished running. */
The application determines whether the function has completed by calling it repeatedly with the same arguments it used to call the function the first time. A return code of SQL_STILL_EXECUTING indicates it is not yet finished, any other value indicates it has completed. The value other than SQL_STILL_EXECUTING is the same return code it would have returned if it had executed synchronously.
Functions that can be called during Asynchronous execution
The following functions can be called while a function is being executed asynchronously. Any other function will return an SQLSTATE of HY010 (Function sequence error).
The following values are returned when SQLGetDiagField() is called on a statement handle that has an asynchronous function executing:
SQLGetDiagRec() always returns SQL_NO_DATA when it is called on a statement handle that has an asynchronous function executing.
The application can issue a request to cancel any function that is running asynchronously by calling SQLCancel(). There are cases, however, where this request will not be carried out (if the function has already finished, for example).
The return code from the SQLCancel() call indicates whether the cancel request was received, not whether the execution of the asynchronous function was stopped.
The only way to tell if the function was canceled is to call it again, using the original arguments.
The following CLI sample, async.c, demonstrates a simple application that runs SQLExecDirect() asynchronously. It is based on the CLI sample program fetch.c.
/* ... */
/* Make the result from SQLGetInfo() more meaningful by mapping */
/* the returned value to the string. */
static char ASYNCMODE[][19] = { "SQL_AM_NONE",
"SQL_AM_CONNECTION",
"SQL_AM_STATEMENT" };
/* ... */
* See what type of Asynchronous support is available,
* and whether or not the CLI/ODBC configuration keyword ASYNCENABLE
* is set on or off.
*/
rc = SQLGetInfo( hdbc, /* Connection handle */
SQL_ASYNC_MODE, /* Query the support available */
&ubuffer, /* Store the result in this variable */
4,
&outlen);
CHECK_STMT(hstmt, rc);
printf("SQL_ASYNC_MODE value from SQLGetInfo() is %s.\n\n",
ASYNCMODE[ubuffer]);
if (ubuffer == SQL_AM_NONE ) { /* Async not supported */
printf("Asynchronous execution is not supported by this datasource\n");
printf("or has been turned off by the CLI/ODBC configuration keyword\n");
printf("ASYNCENABLE. The application will continue, but\n");
printf("SQLExecDirect() will not be run asynchronously.\n\n");
/* There is no need to set the SQLSetStmtAttr() option */
} else {
/* Set statement level asynchronous execution on */
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_ASYNC_ENABLE,
(SQLPOINTER) SQL_ASYNC_ENABLE_ON,
0);
CHECK_STMT(hstmt, rc);
}
/* The while loop is new for the asynchronous sample, the */
/* SQLExecDirect() call remains the same. */
while ((rc = SQLExecDirect(hstmt, sqlstmt, SQL_NTS) )
== SQL_STILL_EXECUTING) {
printf(" ...SQLExecDirect() still executing asynchronously...\n");
/* Other processing can be performed here, between each call
* to see if SQLExecDirect() has finished running asynchronously.
* This section will never run if CLI runs the function
* synchronously.
*/
}
CHECK_STMT(hstmt, rc);
rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER) deptname.s, 15,
&deptname.ind);
CHECK_STMT(hstmt, rc);
rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) location.s, 15,
&location.ind);
CHECK_STMT(hstmt, rc);
printf("Departments in Eastern division:\n");
printf("DEPTNAME Location\n");
printf("-------------- -------------\n");
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
printf("%-14.14s %-14.14s \n", deptname.s, location.s);
}
if (rc != SQL_NO_DATA_FOUND)
check_error(henv, hdbc, hstmt, rc, __LINE__, __FILE__);
rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
CHECK_STMT(hstmt, rc);
rc = SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT);
CHECK_DBC(hdbc, rc);
printf("Disconnecting .....\n");
rc = SQLDisconnect(hdbc);
CHECK_DBC(hdbc, rc);
rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
CHECK_DBC(hdbc, rc);
rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);
if (rc != SQL_SUCCESS)
return (terminate(henv, rc));
} /* end main */