|
|
|
|
New multirow processing capabilities let FETCH and INSERT use arrays.
One of the coolest features in DB2 for z/OS version 8 turns an old programming rule on its head. For years, we taught DB2 application programmers not to use arrays (with the exception of the indicator variable). DB2 version 8 changes that with multirow FETCH and INSERT.
This version 8 SQL enhancement does exactly what you would expect; it lets you insert or fetch multiple rows with a single SQL statement. Imagine the performance advantages, especially in a distributed environment. This enhancement can boost the performance of INSERT processing by as much as 20 percent and FETCH processing by as much as 50 percent. One customer I know tested this feature and averaged 76 percent improvement for FETCH processing and 20 percent improvement for INSERTs. You'll need to do your own testing, of course, because your performance will probably differ.
Here's how multirow processing works: Using an array defined in working storage, a FETCH moves multiple rows into the array; an INSERT processes multiple rows from an array. You'll also need to learn some new keywords. I'll explain the new keywords for multirow FETCH in this column; you can read about the new multirow INSERT keywords in my blog (see Resources).
Getting Started
First, let's define the working storage section I'll reference in my examples. Listing 1 shows my attempt at a working storage section and sets up a couple of arrays. (I used Cobol, but you can also use arrays in PL/I, C, and C++.) The first, NBMR-ARRAY, holds 100 numeric values. The next, CITY-ARRAY, holds 100 character strings that I've identified as CITY. The third, NAME-ARRAY, holds 100 variable length character strings. The final line is an indicator array for use with nulls.
To get started with multirow FETCH, you first have to set up a cursor with the new WITH ROWSET POSITIONING keyword in the DECLARE CURSOR SQL statement (see Listing 2). WITH ROWSET POSITIONING tells DB2 at cursor open that this cursor allows multirow processing. You still have the option to FETCH one row at a time. The default for a DECLARE CURSOR is WITHOUT ROWSET POSITIONING, which allows a cursor to work exactly as it did in previous versions.
You've probably noticed the term "ROWSET". A ROWSET is the group of rows returned by the FETCH. To process multiple rows, you need to add one of the ROWSET keywords and FOR nn ROWS keyword to the FETCH statement — nn can be either a host variable or integer constant with a maximum size of 32,767.
The SQL OPEN CURSOR statement builds the result set that we'll process the FETCH against. In Listing 3, we select the next 100 rows from the cursor's current position and return them to the host variable arrays specified on the INTO clause. Your application will still be responsible for taking the rows out of the array for use in the program and for checking the indicator array. DB2 gives you all the information you need to decipher what was placed in the array, including row counts and SQLCODEs, via the new version 8 SQL statement GET DIAGNOSTICS.
In addition to NEXT ROWSET processing, you can also fetch the PRIOR ROWSET, FIRST ROWSET, LAST ROWSET, CURRENT ROWSET, and a ROWSET STARTING AT ABSOLUTE or RELATIVE position.
If the FOR nn ROWS keyword isn't specified on a cursor declared for multirow FETCH, the value used for the last FETCH executed will be used. Specifying FOR nn ROWS eliminates any question about the number of rows that will be returned.
Coding Carefully
You can also use the FETCH FIRST nn ROWS ONLY clause with multirow fetch. However, you'll get a +100 SQLCODE from the FETCH if the cursor tries to fetch rows beyond the FETCH FIRST restriction. The FOR nn ROW clause controls the number of rows returned by a single FETCH, and the FETCH FIRST nn ROWS ONLY affects the number of rows returned by the SELECT statement coded on the cursor; make sure to keep these clauses separate.
If I were to specify FETCH PRIOR ROWSET with a FOR nn ROWS that puts the cursor before the beginning of the result set, or a FETCH NEXT ROWSET with a FOR nn ROWS value that puts the cursor past the end of the result set, a warning message is returned on the last row fetched. It's all pretty straightforward. Just remember that your cursor's position with multirow FETCH is based on the ROWSET returned, not just a single row.
Be a little cautious about end-of-result processing when using ROWSETS. When a multirow FETCH receives a +100, you can't stop processing (unlike a single-row FETCH) because there could be a partial ROWSET that remains to be processed. The new GET DIAGNOSTICS command will help you determine the number of rows that need to be processed.
One last how-to-code warning: If you start mixing FETCH NEXT ROW and FETCH NEXT ROWSET SQL statements in the same program, remember how they work. Your next FETCH NEXT ROWSET positions the fetch from the next row following the ROWSET returned, and FETCH NEXT (without the ROWSET keyword) will fetch the next from the cursor's position at the beginning of the ROWSET. For example, the rowset FETCH returns rows 1-10 and the following row fetch would return row 2. If you really want to retrieve a single row following the previous ROWSET, just use the FOR 1 ROWS clause on the FETCH NEXT ROWSET statement.
Wide Cursors
The new term "wide cursors" refers to a cursor that covers more than one row. Positioned updates and deletes are affected by how you handle a wide cursor. "Positioned" refers to the use of the WHERE CURRENT OF cursor. If you code a WHERE CURRENT OF clause the way you would have in DB2 version 7 for a cursor defined to use multiple rows, all rows within the rowset will be updated or deleted. If you want to update or delete a specific row or set of rows within the rowset, you'll need to use the new version 8 clause FOR ROW xyz OF ROWSET added to the UPDATE or DELETE WHERE CURRENT OF cursor SQL statement; xyz can be an integer value or a host variable.
Final Considerations
Multirow fetch operations are delivered to you automatically for Distributed Relational Database Architecture (DRDA) applications and by DSNTEP4 and DSNTIAUL. Any distributed query will use multirow fetch, even if you're still in compatibility mode. You can realize up to a 50 percent CPU reduction (compared to version 7) via a FETCH from a remote client that invokes block FETCH. Remember, for uses other than DRDA, you need to get to new function mode to use multirow FETCH.
Although I didn't have space to cover it here, multirow INSERT processing can also result in significant performance improvements whether performed locally or remotely. I cover the new keywords and considerations you'll need for multirow INSERT in one of my blog entries.
There are a few APARs that will affect your implementation of multirow INSERT and, to a lesser degree, multirow FETCH. Many of these are old by DB2 version 8 terms, but you need to make sure they're all applied.
Make sure you verify that you have the most current version of the DB2 manuals before you decide to use multirow FETCH. The most current versions are always available for download from IBM's Web site. My blog entries on this topic and the materials listed in Resources should help you get started with this performance boosting SQL feature.
Willie Favero is a senior certified IT software specialist and DB2 specialty sales rep. An IBM Certified Solutions Expert for DB2 for z/OS Version 8, Willie is a popular speaker, author, and blogger.
Resources
