Introduction

IBM DB2 Driver for JDBC and SQLJ has a datasource property called useRowsetCursor. The default value for this property is true, which means the driver will always try to use multi-row fetch (MRF) for scrollable cursors if the server supports it. This property allows the application to be able to turn off MRF if desired. There is no MRF support for forward only cursors. In addition, T2zos (DB2 V9 for z/OS) does not support MRF yet.

Currently, there are three public interfaces for MRF support:

• public void setUseRowsetCursor (boolean useRowsetCursor);
• public boolean getUseRowsetCursor ();
• public boolean getUseRowsetCursor (java.util.Properties properties);

However, this is not a common solution for T2zos. This is because T2zos requires the rowset support to have a default of false or unset. This requirement causes the need for a new property to override the current useRowsetCursor property (this property is described further in the New connection property section below). In addition, the new property is used for both forward only and scrollable cursors for T2zos. The added support for MRF for T2zos (DB2 V9 for z/OS) is enabled in IBM DB2 Driver for JDBC and SQLJ versions 3.7.xx, 3.51.xx, 4.1.xx, and later.

What is multi-row fetch?

Multi-row fetch allows you to FETCH zero or more rows of the result table. Instead of fetching a single row, you can fetch a set of rows referred to as a rowset.

As illustrated in Figure 1, by fetching multiple rows, your applications reduce the number of SQL calls and can fetch a rowset using a single FETCH statement. This results in less crossing of the SQL Application Programming Interface (API) and reduced CPU usage for this function of the application.

Back to top

New connection property

The new connection property related to MRF is named enableRowsetSupport. Possible values for the property are:

• NOT_SET (the default value)
• YES
• NO

When the enableRowsetSupport connection property is set to YES or NO, it overrides the current setting for useRowsetCursor.

For T2zos, if enableRowsetSupport has a value of NOT_SET, then it means there is no MRF support. For T4 and T2u, NOT_SET results in the current value of the useRowsetCursor property being used, which has a default value of true. This means it will use MRF for scrollable cursors only if the server supports it for T4 and T2u. T4 and T2u users can turn this behavior off by setting useRowsetCursor to false or enableRowsetSupport to NO.

To enable MRF, set enableRowsetSupport to YES. T2zos will use MRF for both scrollable and forward only cursors if the server supports it. T4 and T2u will use MRF for scrollable cursors if the server supports it. T4 and T2u may elect to use MRF for forward only cursors in the future.

To disable MRF, set enableRowsetSupport to NO. This means that MRF is not enabled for all types of users (T2zos, T4, and T2u).

Following are the new connection properties to override the current setting of the useRowsetCursor property:

• public void setEnableRowsetSupport(int enableRowsetSupport);
• public int getEnableRowsetSupport();
• public int getEnableRowsetSupport(java.util.Properties properties);

Back to top

Multi-row positioned UPDATE or DELETE

The IBM Data Server Driver for JDBC and SQLJ supports a technique for performing positioned UPDATE or DELETE operations that follow the JDBC 1 standard. For rowset cursors, the JDBC 1 positioned update syntax needs be aware of the rowset. The syntax needs to be in the following format:

update table set.... where current of cursor for row N of rowset

If an application wants to use JDBC 1 positioned UPDATE operations, then it is responsible for building the correctly positioned UPDATE/DELETE statement.

However, current t2zos applications still use the JDBC 1 positioned UPDATE statements and use the form:

update table set....where current of cursor

Therefore, the behavior is different with rowset support. The UPDATE affects the whole rowset, not just a single row. That is why the default setting for t2zos rowsets support is false (see the New connection property section).

This technique involves using the ResultSet.getCursorName method to obtain the name of the cursor for the ResultSet, and defining a positioned UPDATE or positioned DELETE statement in the following format:

UPDATE table SET col1=value1…coln=valueN WHERE CURRENT OF cursorname

DELETE FROM table WHERE CURRENT OF cursorname

If you use the JDBC 1 technique to UPDATE or DELETE data on a data source that supports multi-row FETCH, the positioned UPDATE or DELETE statement might UPDATE or DELETE multiple rows, when you expect it to only UPDATE or DELETE a single row.

To avoid unexpected UPDATEs or DELETE operations, you can take one of the following actions:

• Use an updatable ResultSet to retrieve and UPDATE one row at a time.
• Use the FOR ROW n OF ROWSET clause in your UPDATE or DELETE statements, to identify the specific row to MODIFY or DELETE.

Back to top

Sample Java program

Listing 1 is a sample Java program that shows how the new enableRowsetSupport connection property can be used to override the current useRowsetCursor property.

This program sets enableRowsetSupport to YES, meaning it will enable MRF.

import java.sql.*;

public class connectionInfo_MRF 
{
 public static void main(String[] args) throws Exception
 {
  	System.out.println("\nTest case begins !!!\n ");
          
  	javax.sql.DataSource ds = new com.ibm.db2.jcc.DB2SimpleDataSource();
          
  	((com.ibm.db2.jcc.DB2BaseDataSource) ds).setServerName("ServerName"); 
  	((com.ibm.db2.jcc.DB2BaseDataSource) ds).setPortNumber(portNumber);
  	((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDatabaseName("databaseName");
  	((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDriverType(2); 
          
  	// Enable MRF support. To disable MRF, set setEnableRowsetSupport 
  	// to com.ibm.db2.jcc.DB2BaseDataSource.YES
  
   	int setValue = com.ibm.db2.jcc.DB2BaseDataSource.YES;
  	((com.ibm.db2.jcc.DB2BaseDataSource)ds).setEnableRowsetSupport(setValue);
  
  	((com.ibm.db2.jcc.DB2BaseDataSource) ds).setTraceFile("jccTrace.txt");
  
  	System.out.println(((com.ibm.db2.jcc.DB2BaseDataSource) ds).getJccVersion());
  
  	java.sql.Connection con = ds.getConnection("userName", "passWord");
  
  	// Get  Rowset support value. When MRF is enabled this should return value 1.
  	int RowSet = ((com.ibm.db2.jcc.DB2BaseDataSource)ds).getEnableRowsetSupport();
  
  	System.out.println("\nRow Set Support value :" +RowSet);
  	System.out.println("\n");	
  
  	// Set the cursor type to scrollable.
  	//Modify the below statement if you want the cursor type to be  TYPE_FORWARD_ONLY
  	java.sql.Statement s = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
    	                                           ResultSet.CONCUR_READ_ONLY);
  	// Create table
  	s.executeUpdate ("create table TestQBatch (col1 int)");
  
  	// Populate tables with data.
  	for (int i =1; i less than 1000; i++)
  	{
		  s.executeUpdate ("insert into TestQBatch values (" +i+ ")");
  	}
  
  	java.sql.ResultSet rs = s.executeQuery("Select * from TestQBatch");
  
  	while (rs.next()) 
  	{
  			System.out.print (rs.getInt (1) + "  ");
  	}
  
  	// When MRF is enabled, ResultSet.getFetchSize() should alway return value > 1.
  	int ActualResult = rs.getFetchSize();
  
  	System.out.print ("\n\nFetch Size : " +ActualResult);
  
  	// Drop the table created.
 	s.executeUpdate("DROP TABLE TestQBatch");
  
  	System.out.println("\n\nTest case Ends !!! ");
  
  	con.commit();
  
 }
}

Back to top

How to verify whether a fetch uses MRF or not

Run the sample Java program shown in Listing 1 on a T2zos (DB2 V9 for z/OS) machine with IBM DB2 Driver for JDBC and SQLJ at version 3.7.xx, 3.51.xx, 4.1.xx, or later. Locate the JCC trace file named jccTrace.txt. As mentioned earlier, on a T2zos server that supports MRF, if you set enableRowsetSupport to YES, T2zos prepares with WITH ROWSET POSITIONING for the cursors.

If the jccTrace.txt trace file contains the string WITH ROWSET POSITIONING, this means that MRF is enabled. If MRF is not enabled, FETCH statements are prepared without rowset positioning. So in this case, you will not find the string WITH ROWSET POSITIONING in the trace file.

You can also determine if MRF is active or not for a given ResultSet by calling ResultSet.getFetchSize(). If the returned value for fetchSize is greater than one, then MRF was used. Note that this is a call on the ResultSet, not the PreparedStatement.

Back to top

Limitations

• Due to a DB2 limitation, rowset cursor (MRF) is not compatible with progressive streaming in T2zos (Fetch Continue).
• Under T2zos, if you set enableRowsetSupport to YES, T2zos will prepare with WITH ROWSET POSITIONING for the cursors if the server supports MRF. However, if lobs or xml are present in the cursor and progressive stream is defaulted or set to on, MRF will be turned OFF for the fetch statement. The fetch statement will be re-prepared without rowset positioning.
• T2zos does not know if the cursor returned from a stored procedure is rowset or not, thus the t2zos driver allows only single row fetch for a stored procedure.
• T2zos will not support lob or xml rowset cursors returned from a stored procedure that are progressive streaming. A -225 sqlcode maybe thrown in this case during the fetch continue (T2zosCursor.getMoreData_) call.
• A T2zos java stored procedure will turn off rowset support if lob or xml is presented in the cursor.

Back to top

Conclusion

Using Multi-row FETCH (MRF), you can get better performance than retrieving one row with each FETCH statement. This article should help you understand what MRF is and how it can be used. It also illustrated with a sample Java program how you can set MRF in an application and explained how you can verify whether or not a fetch uses MRF.

Resources

• Participate in the discussion forum.
  
  
• In the DB2 for z/OS area on developerWorks, get the resources you need to advance your skills in DB2 for z/OS.
  
  
• Check out developerWorks blogs and get involved in the developerWorks community.
  
  
• Download IBM product evaluation versions or explore the online trials in the IBM SOA Sandbox and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  

About the author

Praveen Sogalad currently works on the Common Application Development for DB2 Universal JDBC Driver (JCC) quality assurance team in the IBM India Software Labs, Bangalore, India. Prior to this role, he was an application developer for the DB2 Samples Development team in the IBM India Software Labs.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• What is multi-row fetch?
• New connection property
• Multi-row positioned UPDATE or DELETE
• Sample Java program
• How to verify whether a fetch uses MRF or not
• Limitations
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers