Troubleshooting
Problem
This document discusses the cursor sensitivity of the IBM i Access Client Solutions ODBC driver and OLEDB provider and the IBM Toolbox for Java™ JDBC driver when connected to IBM i V5R2 or later. This information is also available in information APAR II13308.
Resolving The Problem
Background
The IBM i Access Client Solutions ODBC driver and OLEDB provider and the IBM Toolbox for Java JDBC driver provide functions for applications to specify a cursor type. One attribute of a cursor is its sensitivity to changes in membership, order, and values of the result set after the cursor is opened. Sensitivity can be further defined as applying to the current cursor (seeing only changes made with the current cursor) or applying to changes made outside the current cursor.
Following are the types of sensitivity:
Insensitive
The cursor never sees changes to the result set made by other cursors. This cursor type might or might not see its own changes (in the IBM System i products implementation it does not). Insensitive cursors are also referred to as static cursors or as "snapshots". Insensitive cursors are usually implemented by copying the result set data into a temporary table. It can be resource-intensive for large result sets.
Asensitive
The cursor might or might not reflect changes made to the result set. This type of cursor allows the database to use internal buffering of result data. It offers the best performance. The actual cursor returned might be sensitive, partially sensitive, or insensitive.
Sensitive
The cursor must reflect all changes made to the result set. It might prohibit the use certain query implementations (such as temp tables), which can impact performance. Some types of queries cannot be implemented by using a sensitive cursor.
How an application specifies sensitivity varies depending on the API used. The ODBC 2.0 and OLE DB specifications define a dynamic cursor type. A dynamic cursor is defined to be updatable, scrollable, and sensitive to all updates. A sensitive cursor is similar to the IBM i DYNAMIC SCROLL cursor. JDBC and ODBC 3.0 specifications allow users to directly specify a cursor sensitivity attribute. ODBC 3.0 allows all three sensitivity types with SQL_UNSPECIFIED equivalent to asensitive. JDBC 3.0 defines only insensitive and sensitive.
Before 5.2, scrollable cursors with read-only concurrency are implemented as asensitive cursors. A particular cursor's sensitivity depends on the type of query and the access plan used to implement the query. For most read-only queries, the cursor returned reflects all changes made to the result set. The types of queries that cannot reflect result set changes are documented in the SQL Reference under the DECLARE CURSOR section.
Starting in R520 of the operating system, there are many more types of read-only queries where the cursor returned does not reflect changes made to the result set. Even simple queries such as "select * from qiws.qucustcdt" might not always reflect changes made to the result set. Some applications might be affected by this change in sensitivity. Applications affected by this change have two options.
The first option is to change the application and specify pessimistic concurrency (FOR UPDATE). Updatable cursors are always sensitive. The disadvantage of this approach is that concurrency is reduced because an update record lock is used.
The second option is to use the new Cursor Sensitivity custom property. This property is supported by iSeries Access clients as well as IBM Toolbox for Java JDBC. The property allows a programmer to force certain types of read-only cursors to be implemented as sensitive. The disadvantage of this keyword is that all queries run on the connection are implemented as sensitive. Queries that cannot be implemented as sensitive return an error.
Client Sensitivity
JTOpen 4.2
INSENSITIVE cursors are now supported.
Note: JTOpen 4.0 and 4.1 used different cursor mappings.
Cursor sensitivity:
Specifies the cursor sensitivity to a request from the database. The behavior depends on the resultSetType:
The IBM i Access Client Solutions ODBC driver and OLEDB provider and the IBM Toolbox for Java JDBC driver provide functions for applications to specify a cursor type. One attribute of a cursor is its sensitivity to changes in membership, order, and values of the result set after the cursor is opened. Sensitivity can be further defined as applying to the current cursor (seeing only changes made with the current cursor) or applying to changes made outside the current cursor.
Following are the types of sensitivity:
Insensitive
The cursor never sees changes to the result set made by other cursors. This cursor type might or might not see its own changes (in the IBM System i products implementation it does not). Insensitive cursors are also referred to as static cursors or as "snapshots". Insensitive cursors are usually implemented by copying the result set data into a temporary table. It can be resource-intensive for large result sets.
Asensitive
The cursor might or might not reflect changes made to the result set. This type of cursor allows the database to use internal buffering of result data. It offers the best performance. The actual cursor returned might be sensitive, partially sensitive, or insensitive.
Sensitive
The cursor must reflect all changes made to the result set. It might prohibit the use certain query implementations (such as temp tables), which can impact performance. Some types of queries cannot be implemented by using a sensitive cursor.
How an application specifies sensitivity varies depending on the API used. The ODBC 2.0 and OLE DB specifications define a dynamic cursor type. A dynamic cursor is defined to be updatable, scrollable, and sensitive to all updates. A sensitive cursor is similar to the IBM i DYNAMIC SCROLL cursor. JDBC and ODBC 3.0 specifications allow users to directly specify a cursor sensitivity attribute. ODBC 3.0 allows all three sensitivity types with SQL_UNSPECIFIED equivalent to asensitive. JDBC 3.0 defines only insensitive and sensitive.
Before 5.2, scrollable cursors with read-only concurrency are implemented as asensitive cursors. A particular cursor's sensitivity depends on the type of query and the access plan used to implement the query. For most read-only queries, the cursor returned reflects all changes made to the result set. The types of queries that cannot reflect result set changes are documented in the SQL Reference under the DECLARE CURSOR section.
Starting in R520 of the operating system, there are many more types of read-only queries where the cursor returned does not reflect changes made to the result set. Even simple queries such as "select * from qiws.qucustcdt" might not always reflect changes made to the result set. Some applications might be affected by this change in sensitivity. Applications affected by this change have two options.
The first option is to change the application and specify pessimistic concurrency (FOR UPDATE). Updatable cursors are always sensitive. The disadvantage of this approach is that concurrency is reduced because an update record lock is used.
The second option is to use the new Cursor Sensitivity custom property. This property is supported by iSeries Access clients as well as IBM Toolbox for Java JDBC. The property allows a programmer to force certain types of read-only cursors to be implemented as sensitive. The disadvantage of this keyword is that all queries run on the connection are implemented as sensitive. Queries that cannot be implemented as sensitive return an error.
Client Sensitivity
JTOpen 4.2
INSENSITIVE cursors are now supported.
Note: JTOpen 4.0 and 4.1 used different cursor mappings.
Cursor sensitivity:
Specifies the cursor sensitivity to a request from the database. The behavior depends on the resultSetType:
- ResultSet.TYPE_SCROLL_SENSITIVE means that the value of this property controls what cursor sensitivity the Java program requests from the database.
- ResultSet.TYPE_FORWARD_ONLY and ResultSet.TYPE_SCROLL_INSENSITIVE causes this property to be ignored.
R520 IBM Toolbox for Java, JTOpen 3.3, and earlier
The default behavior of IBM Toolbox for Java JDBC is to return an asensitive cursor in place of a sensitive. Insensitive cursors are not supported. In JTOpen 3.1 and later, the cursor sensitivity behavior can be controlled by a new property cursor sensitivity.
Cursor sensitivity:
ASENSITIVE |
This value is the default. It applies only to statements created with a resultSetType of ResultSet.TYPE_FORWARD_ONLY or ResultSet.TYPE_SCROLL_SENSITIVE. |
INSENSITIVE |
This value is ignored. |
SENSITIVE |
This value applies to resultSetType of ResultSet.TYPE_FORWARD_ONLY and ResultSet.TYPE_SCROLL_SENSITIVE. |
iSeries Access OLE DB
OLE DB is usually used with ADO. The ADO Dynamic and forward-only cursors are mapped to asensitive by default. Cursor sensitivity can be controlled by a new custom connection property Cursor Sensitivity. Cursor Sensitivity applies only to SQL requests; it does not apply to RLA or DataQueues.
Cursor Sensitivity:
| 0 | ASENSITIVE. Applies to ForwardOnly and Dynamic cursors only. Static cursors are always Insensitive. |
| 1 | INSENSITIVE. Applies to ForwardOnly and Static cursors only. Dynamic cursors can never be Insensitive. |
| 2 | SENSITIVE. Applies only to Dynamic cursors. Forward Only and Static cursors can never be Sensitive. |
ADO applications written to the IBMDA400 OLE DB provider can specify Cursor Sensitivity by setting the Cursor Sensitivity property on the ADODB Connection object. This property is read/write before the connection is opened, and read-only after the connection is opened. Use one of the following methods:
- Method one: Setting it on the connection string:
cn.ConnectionString = "Provider=IBMDA400;Data Source=MyiSeries;Cursor Sensitivity=2" - Method two: As a custom property:
cn.Properties(“Cursor Sensitivity”) = 0 '// ASENSITIVE (applies to forwardOnly and Dynamic cursors)
'cn.Properties(“Cursor Sensitivity”) = 1 '// INSENSITIVE (applies to forwardOnly and Static cursors)
'cn.Properties(“Cursor Sensitivity”) = 2 '// SENSITIVE (applies to only Dynamic cursors)
cn.Open
IBM i Access Client Solutions ODBC (V5R2 and later)
IBM i Access Client Solutions ODBC uses the specified sensitivity when it is provided on the ODBC 3.0 SQLSetStatementAttr SQL_ATTR_CURSOR_SENSITIVITY API. If the applications use the older ODBC 1.0 SQLSetStmtOption SQL_CURSOR_TYPE API for SQL_CURSOR_FORWARD_ONLY or SQL_CURSOR_DYNAMIC, then the sensitivity is set according to the new connection property.
Connection Keyword: CURSORSENSITIVITY
| 0 | Asensitive. This value is the default option. This option applies to forward-only and dynamic cursors only. Static cursors are always insensitive. |
| 1 | Insensitive. |
| 2 | Sensitive. These options apply to forward-only and dynamic cursors only. Static cursors are always insensitive. |
This keyword is ignored when you connect to operating system R510 or earlier. The CursorSensitivity keyword can be set on the connection string or in the data source. The cwbODBCreg tool can be used to set the CURSORSENSITIVITY option for a particular data source. The syntax is:
cwbODBCreg myDSN CursorSensitivity 0
Change myDSN is the ODBC data source name. The cwbODBCreg tool is part of the iSeries Access for Windows product starting at V5R2. For older clients, it is also available at the following website: ftp.software.ibm.com/as400/products/clientaccess/win32/files/odbc_tool/cwbODBCreg.zip
[{"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Host Servers","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB68","label":"Power HW"}}]
Historical Number
26201216
Was this topic helpful?
Document Information
Modified date:
18 November 2024
UID
nas8N1017064