DB2 UDB for iSeries cursor sensitivity enhancements

Kent Milligan explains what cursor sensitivity is and how it's programmatically controlled on DB2 UDB for iSeries. In addition, the article covers the performance impacts of using the different cursor sensitivity settings.

Kent Milligan (kmill@us.ibm.com), DB2 UDB Technology Specialist, IBM

Kent Milligan is a DB2 UDB for iSeries Technology Specialist in IBM eServer Solutions Enablement. Kent spent the first eight years of his IBM career as a member of the DB2 development group in Rochester. He speaks and writes regularly on various iSeries and AS/400e relational database topics.



11 March 2004

Sensitivity defined

SQL cursors have several different attributes available to control different aspects of a cursor’s behavior as the result set is constructed and returned to the application. Scrolling and sensitivity are examples of two such cursor attributes that can be controlled by the application developer.

Cursor sensitivity refers to a cursor setting that defines whether or not the result set returned by a cursor will include recent changes to the underlying table. For example, a sensitive cursor declared as “SELECT * FROM orders” would return all the current orders as well as any new orders inserted into the database after the cursor was opened. For example, if there were there six orders when a sensitive cursor was opened and two new orders inserted after the cursor was opened by another application, then a sensitive cursor will try to include all eight orders in its result set (see Table 1). The result set contents returned by an insensitive cursor in the same application scenario are displayed in Table 2..

Table 1. Sensitive cursor result set
IDNumberItem
A1225Hammer
A123500Screw - 1/4
A12510Saw
A126100Carriage Bolt
A1276Hammer
A13022Electric Drill
A1311Table Saw
A132200Clamp
Table 2. Insensitive cursor result set
IDNumberItem
A1225Hammer
A123500Screw - 1/4
A12510Saw
A126100Carriage Bolt
A1276Hammer
A13022Electric Drill

The cursor sensitivity setting obviously needs to have an impact on the access plan created by the query optimizer. The access plan for a sensitive cursor cannot include algorithms or data access techniques that make a copy of the table data. For instance, hash tables are often used in the implementation of join and grouping queries, but since they contain a copy of the table data, they cannot be used in the implementation of sensitive queries.

This direct correlation between cursor sensitivity and the query optimizer is why the IBM® Universal Database (UDB)™ for iSeries™ cursor sensitivity support was recently enhanced in V5R2. V5R2 included query optimization enhancements that resulted in the optimizer using more algorithms that make a copy of table data – the end result was fewer cursors returning sensitive result sets than in previous releases. To solve this problem, DB2 UDB for iSeries programming interfaces were enhanced to give application developers greater control of the cursor sensitivity setting.

Sensitivity settings

With the latest set of enhancements, iSeries application developers, whether they’re using JDBC™, ODBC, or OLE DB, can now specify any of the three cursor sensitivity settings defined below. These sensitivity enhancements can be obtained by just applying the latest V5R2 fixes (available online at ibm.com/iseries/support) for each middleware offering.

ASENSITIVE
This is the default setting on all cursor definitions. The database manager can implement the cursor as either SENSITIVE or INSENSITIVE depending on optimization of the SQL request. Updateable cursors do not default to ASENSITIVE, instead they are always implemented as SENSITIVE cursors.

SENSITIVE
The cursor has some level of sensitivity to any inserts, updates, or deletes made to the associated tables after the cursor has been opened. If the database manager cannot make changes visible to the cursor, then an error is returned on the open request.

INSENSITIVE
Once the cursor is opened, it does not have sensitivity to inserts, updates, or deletes performed by this or any other connection or job. If INSENSITIVE is specified, the cursor is read-only and a temporary result is created when the cursor is opened. In addition, the SELECT statement cannot contain a FOR UPDATE clause and the precompiler ALWCPYDTA (Allow Copy Data) setting cannot be *NO.

In prior releases, application developers could only specify the INSENSITIVE cursor setting or just use the default sensitivity setting of ASENSITIVE. There was no easy way to force the usage of a Sensitive cursor. The only choices a developer had were to make the cursor updateable or specify *NO for the ALWCPYDTA precompiler option. Both of these options, however, had unwanted side effects. Updateable cursors are typically slower because of the additional row locks that have to be obtained when processing a table. And ALWCPYDTA(*NO) can cause the open of a cursor to fail, if the only way to implement the cursor definition is to make a copy of the data.


Performance and query optimization impacts

The default setting of ASENSITIVE is the best performing option because it allows the query optimizer to use its complete set of algorithms when deciding on the best method for implementing a query. Utilization of an algorithm that makes a copy of the data (e.g., hashing) can drastically improve performance and other queries where performance is better when copies of the data are avoided. The ASENSITIVE setting gives the query optimizer the freedom to choose the best performing method. The SENSITIVE and INSENSITVE cursor settings can force the optimizer into a plan that is sub-optimal from a performance point of view. The SENSITIVE setting eliminates the usage of temporary data copies by the optimizer; this also prevents parallel processing since the DB2 Symmetric MultiProcessing (DB2 SMP) feature makes copies of the table data. The INSENSITIVE setting forces a copy of the table data whether it it is good for performance or not. Thus, the INSENSITIVE and SENSITIVE cursor settings should be used only when the associated cursor behavior is absolutely required by the application.


Sensitivity programming interfaces

The following sections contain the programming details on how to control the cursor sensitivity setting with the iSeries integrated middleware. Please be aware that when controlling the cursor sensitivity at a connection level that sensitivity is applied to all cursors opened within that connection. That means that if SENSITIVE is the connection level setting, the database manager must be able to implement all of the cursors in that connection as sensitive cursors. Otherwise, an error will be returned.

Note that updateable cursors are always implemented as SENSITIVE cursors, so the sensitivity settings described below are ignored for updateable cursors.

iSeries access ODBC driver

With the ODBC driver, sensitivity can be controlled at an individual cursor level with the SQL_ATTR_CURSOR_SENSITIVITY attribute on the SQLSetStmtAttr API. It should be noted that ODBC specifications do not support a value of Asensitive; instead, Unspecified is the value used on the SQLSetStmtAttr API to specify an Asensitive cursor sensitivity behavior.

The CURSORSENSITIVTY connection keyword can be specified to control the cursor sensitivity setting at a connection level with one of the following values.

  • 0 - Asensitive
  • 1 - Insensitive
  • 2 - Sensitive

The keyword applies only to apps that use the ODBC 2.0 cursor type statement attribute (which associates sensitivity and scrollability). The connection keyword is ignored in applications that use ODBC 3.0 attributes for sensitivity.

Static cursors are always Insensitive. The sensitivity settings only apply to dynamic and forward only cursors.

The following ODBC code snippet shows how to control the cursor sensitivity at a connection level with the CURSORSENSITIVITY connection keyword and at a statement level with the SQLSetStmtAttr API. When using the SQLSetStmtAttr API to change attributes, changing some cursors attributes such as cursor type can also cause the sensitivity setting to be changed. These indirect changes of the sensitivity setting are documented in the ODBC specifications.

ODBC code example
connectStr = “DRIVER={iSeries Access ODBC Driver};SYSTEM=myiSvr;UID=myid;PWD=mypwd;” +
             “CURSORSENSITIVITY=2;” 
rc = SQLDriverConnect(hdbc, NULL, (SQLCHAR *)connectStr, SQL_NTS, NULL, 0, NULL, 
     SQL_DRIVER_NOPROMPT); 
... 

SQLAllocHandle(SQL_HANDLE_STMT, hdbc,&hstmt1); 
SQLSetStmtAttr(hstmt1, SQL_ATTR_CONCURRENCY, SQL_CONCUR_VALUES, 0);
SQLSetStmtAttr(hstmt1, SQL_ATTR_CURSOR_SENSITIVITY, SQL_SENSITIVE, 0); 
SQLExecDirect(hstmt1, "SELECT orderid, status FROM orders", SQL_NTS); 
...

iSeries OLE DB provider

The OLE DB provider only allows Cursor Sensitivity to be controlled at the connection level with the connection property, “Cursor Sensitivity.” The connection property values are the same as the ODBC connection keyword. This new connection property cannot be changed after a connection has been opened.

Here is a coding example showing how to set the new connection property either on the connection string or with the Properties method.

OLE DB code example
cn.ConnectionString = "Provider=IBMDA400;Data Source=MyiSeries;Cursor Sensitivity=2" 

cn.Properties(“Cursor Sensitivity”) = 0 '// ASENSITIVE 
cn.Open ...

Like the ODBC driver, static cursors are always implemented with the Insensitive attribute. Forward only cursors can never be sensitive with the OLE DB provider. The next release will allow forward only cursors to be defined as sensitive.

Toolbox JDBC Driver

The Toolbox JDBC driver also has a "Cursor sensitivity" connection property that supports the property values of: asensitive, sensitive and insensitive.

The sensitive connection property value is only honored for cursors declared with a resultSetType of TYPE_SCROLL_SENSITIVE. Cursors declared with the resultSetType of TYPE_SCROLL_SENSITIVE are implemented as ASENSITIVE by default. The connection property of “sensitive” must be specified in order for cursors with resultSetType, TYPE_SCROLL_SENSITIVE, to be implemented as SENSITIVE cursors.

Insensitive cursors are defined by specifying TYPE_SCROLL_INSENSITIVE for the resultSetType. The only time that the insensitive connection property value is recognized is for cursors declared with a resultSetType of TYPE_FORWARD_ONLY. TYPE_FORWARD_ONLY cursors are always implemented as ASENSITIVE unless the insensitive connection property value is specified.

The following JDBC coding example shows how to specify a sensitive cursor setting with the new connection property.

JDBC code example
// Load the IBM Toolbox for Java JDBC driver. 

DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver()); 
Properties props = new Properties(); 

props.setProperty("user", "myid"); 
props.setProperty("password", “mypass"); 
props.setProperty( "cursor sensitivity", "sensitive" ); 

Connection connection = DriverManager. getConnection(" jdbc: as400://myiSvr", props);
 ... 
s = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
   ResultSet.CONCUR_READ_ONLY); 
ResultSet rs = s.executeQuery("SELECT ordid, status FROM orders”); 
...

Embedded SQL

The next version of DB2 UDB for iSeries will include the ability to specify the three cursor sensitivity settings on the DECLARE CURSOR statement. The current V5R2 support only allows the default setting of ASENSITIVE or choosing an INSENSITIVE cursor definition. If you have a strong need for this support on V5R2, please send an email to rchudb@us.ibm.com to check if a fix is available.


Summary

I hope this article gives you a clear understanding of how the cursor sensitivity settings affects the result set returned to the application and how to programmatically control the sensitivity setting. And, more importantly, how the cursor sensitivity setting can both positively and negatively affect query optimization and application performance.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=14484
ArticleTitle=DB2 UDB for iSeries cursor sensitivity enhancements
publish-date=03112004