Troubleshooting
Problem
This document describes the iSeries Access for Windows ODBC driver support for the ODBC SQL_ATTR_QUERY_TIMEOUT property.
Resolving The Problem
The IBM i Access for Windows ODBC driver supports the ODBC SQL_ATTR_QUERY_TIMEOUT property via the IBM i DB2 Query processing time limit attribute.
Timeouts occur based on how long the DB2 UDB for i query optimizer estimates a query will run, not the actual execution time. The accuracy of this estimate is directly related to the information available to the optimizer. This information includes statistics on the data acquired through existing indexes. If the proper indexes are not in place, the estimate may be inaccurate and the query may not perform well.
Multiple methods exist for controlling the query processing time limit and conflicts can arise when more than one of these methods is used. The query processing time limit can be set through the following:
To disable query timeout, the string value "QueryTimeout" can alternatively be added to the data source registry key and set to 0. The cwbODBCreg tool can be used to set this option. The syntax is:
Applications That Use Query Timeout
Most Microsoft products (Access, DAO, RDO, and so on) set the SQL query timeout to a default of 60 seconds. The default for ADO is 30 seconds. Programmers can usually override this setting through some property exposed by the object model being used.
In ADO, you can set the CommandTimeout property to 0 to disable the SQL query timeout. ADO supports this property on both the connection and command object. The command object does not inherit the setting from the connection object (in ODBC, an ODBC statement handle does inherit the default value of the connection handle). The ADO 2.5 documentation states:
Contact Microsoft support for further information regarding ADO and the CommandTimeout property.
In Microsoft Access, the query timeout value can be changed by opening the query in Design View, right clicking anywhere outside of the field lists and design grid area, then selecting Properties. This brings up the query property sheet where you can then change the ODBCTimeout property to 0 (to disable it) or any desired value. Consult the Microsoft Access help for further information.
Timeouts occur based on how long the DB2 UDB for i query optimizer estimates a query will run, not the actual execution time. The accuracy of this estimate is directly related to the information available to the optimizer. This information includes statistics on the data acquired through existing indexes. If the proper indexes are not in place, the estimate may be inaccurate and the query may not perform well.
Multiple methods exist for controlling the query processing time limit and conflicts can arise when more than one of these methods is used. The query processing time limit can be set through the following:
| o | The Query processing time limit system value (QQRYTIMLMT) |
| o | The use of a QAQQINI options file. |
| o | An exit program or stored procedure that calls the CHGQRYA CL command. |
| o | An indirect execution of CHGQRYA by the application (through QCMDEXC). |
| o | An ODBC API call to SQLSetStmtAttr for SQL_ATTR_QUERY_TIMEOUT. |
The last value set takes precedence. This implies that the value set through the ODBC SQL_ATTR_QUERY_TIMEOUT, if nonzero, will override the value set through any other method.
Query Timeout Implementation
If required, support for the ODBC query timeout property can be disabled. The IBM i Access for Windows ODBC driver supports a "QueryTimeout" property that can be used to disable the ODBC SQL_ATTR_QUERY_TIMEOUT attribute. When the QueryTimeout property is set, the ODBC driver rejects attempts by the application to set the query timeout value by returning S1C00 "[IBM][iSeries Access ODBC Driver]Driver not capable". Note that this value has no affect on the current job setting.
The QueryTimeout property can be accessed via the Windows "ODBC Data Source Administrator". In the IBM i Access for Windows ODBC "Configure..." dialog, select the "Performance" tab and click the "Advanced" button. On the "Advanced performance options" dialog, uncheck "Allow query timeout":
Query Timeout Implementation
If required, support for the ODBC query timeout property can be disabled. The IBM i Access for Windows ODBC driver supports a "QueryTimeout" property that can be used to disable the ODBC SQL_ATTR_QUERY_TIMEOUT attribute. When the QueryTimeout property is set, the ODBC driver rejects attempts by the application to set the query timeout value by returning S1C00 "[IBM][iSeries Access ODBC Driver]Driver not capable". Note that this value has no affect on the current job setting.
The QueryTimeout property can be accessed via the Windows "ODBC Data Source Administrator". In the IBM i Access for Windows ODBC "Configure..." dialog, select the "Performance" tab and click the "Advanced" button. On the "Advanced performance options" dialog, uncheck "Allow query timeout":
To disable query timeout, the string value "QueryTimeout" can alternatively be added to the data source registry key and set to 0. The cwbODBCreg tool can be used to set this option. The syntax is:
cwbODBCreg myDSN QueryTimeout 0
...where myDSN is the ODBC datasource name.
Applications That Use Query Timeout
Most Microsoft products (Access, DAO, RDO, and so on) set the SQL query timeout to a default of 60 seconds. The default for ADO is 30 seconds. Programmers can usually override this setting through some property exposed by the object model being used.
In ADO, you can set the CommandTimeout property to 0 to disable the SQL query timeout. ADO supports this property on both the connection and command object. The command object does not inherit the setting from the connection object (in ODBC, an ODBC statement handle does inherit the default value of the connection handle). The ADO 2.5 documentation states:
| "Note: The CommandTimeout setting on a Connection object has no effect on the CommandTimeout setting on a Command object on the same Connection; that is, the Command object's CommandTimeout property does not inherit the value of the Connection object's CommandTimeout value." |
Contact Microsoft support for further information regarding ADO and the CommandTimeout property.
In Microsoft Access, the query timeout value can be changed by opening the query in Design View, right clicking anywhere outside of the field lists and design grid area, then selecting Properties. This brings up the query property sheet where you can then change the ODBCTimeout property to 0 (to disable it) or any desired value. Consult the Microsoft Access help for further information.
ADO Example
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
cn.Open "DSN=RCH730B"
'If the query is run on a connection, the connection command timeout must be set
cn.CommandTimeout = 0
Set rs = cn.Execute("select * from qiws.qcustcdt")
rs.Close
'If the query is run on a command object,
'the command timeout must be set. It does not inherit the connection
'property.
cmd.CommandTimeout = 0
cmd.CommandText = "Select * from qiws.qcustcdt"
Set cmd.ActiveConnection = cn
Set rs = cmd.Execute
rs.Close
'This uses a default of 30 because the connection timeout
'is not inherited by the new command object
Dim cmd2 As New ADODB.Command
Set cmd2.ActiveConnection = cn
cmd2.CommandText = "select * from qiws.qcustcdt"
Set rs = cmd2.Execute
'This will use the timeout of the connection object (MDAC 2.6)
rs.Open "select * from qiws.qcustcdt", cn
rs.Close
cn.Close
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing
Related Information
[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Data Access","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Historical Number
21130903
Was this topic helpful?
Document Information
Modified date:
24 June 2021
UID
nas8N1017615