Resolving The Problem

The IBM.Data.DB2.iSeries .Net database provider includes the iDB2Command Class Property "CommandTimeout" that can be used to specify the allowed time for SQL statements to run. The default value for this property is 30 seconds. When SQL statements are run, the database will run the statement only if the estimated run time is less than the value of the "CommandTimeout" property. If this happens, the application will receive the following message: SQL0666 - Estimated query processing time X exceeds limit Y.

Despite the wording of this method in the 'DB2 for i .NET Provider' technical reference, this message is issued before the statement is run. It makes use of the predictive query governor in DB2 on i rather than simply stopping the query after a preset number of seconds. Therefore, if the estimate is lower than the specified value but the statement runs longer, it will not stop processing after the command timeout has expired. To resolve SQL0666 error messages, change the "CommandTimeout" parameter to exceed the value in the SQL0666 error message. Alternatively, a value of 0 may specified to indicate that no timeout should be imposed.

Note that if the command.Prepare method is called, the CommandTimeout must be set BEFORE calling Prepare or it will have no effect. Calling Prepare causes the flow to the host that sets the query timeout value as well as the command text which is prepared.

CommandTimeout property examples

Visual Basic Example
Public Sub Example()

Dim cn As New iDB2Connection("DataSource=myiSeries;")
Dim cmd As New iDB2Command()
cmd.CommandText = "select * from mylib.mytable"
cmd.CommandType = CommandType.Text
cmd.CommandTimeout = 0
End Sub


C# Example
public void Example()
{
iDB2Connection cn = new iDB2Connection("DataSource=myiSeries;");
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "select * from mylib.mytable";
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 0;

}