And finally, some actual detail about a DB2 for z/OS Version 8 feature.
My first detailed entry will introduce you to multi-row INSERT and FETCH.
This Version 8 SQL enhancement does exactly what you would expect.
It allows you for the first time, to insert multiple rows or fetch multiple rows with a single SQL statement.
Imagine the performance advantages, especially in a distributed environment, that could be realized with this enhancement.
This V8 SQL improvement could improve the performance of insert processing by as much as 20% and fetch processing by as much as 50%.
Some friends, a real customer, tested this feature out and averaged 76% improvement for FETCH processing and 20% improvement for INSERTs.
Of course, this is just one example. As they say, you need to do your own testing because your mileage (performance) may, and probably will, differ.

For years when we taught the DB2 Application Programming classes, we would stress how arrays could not be used in an SQL statement, with the exception of the indicator variable.
In fact, even today, you can find dozens of references in the DB2 Version 7 Application Programming and SQL Guide (SC26-9933) that states host variable cannot be an element of an array.
Well, that's all changed! DB2's multi-row processing allows multiple rows to be processed by a single FETCH or INSERT SQL statement.
Using an array defined in working storage, a FETCH moves multiple rows into the array or processes multiple rows from an array with an INSERT.
To accomplish this, V8 introduces a couple new keywords.

Let's start out with a definition of the working storage section that I will reference in my examples.
This is my attempt at a COBOL working storage section:

01        HOST-VARIABLES.	
    05    HV-ACT-NMBR-ARRAY PIC S9(04) USAGE COMP OCCURS 100 TIMES. 
    05    HV-ACT-CITY-ARRAY PIC X(40) OCCURS 100 TIMES. 
    05    HV-ACT-NAME-ARRAY OCCURS 100 TIMES. 
       49 HV-ACT-NMBR-ARRAY PIC S9(04) USGE COMP. 
       49 HV-ACT-NAME-ARRAY PIC X(40). 
    05    HV-IND-ARRAY      PIC S9(04) USAGE COMP OCCURS 100 TIMES. 

I hope my COBOL isn't too rusty. Arrays can also be used in PL/I, C, and C++.

I set up a couple of arrays in the above example.
The first, NBMR-ARRAY, holds 100 numeric values. The next, CITY-ARRAY, holds 100 character strings that I have identified as city.
In the third, NAME-ARRAY, I have set up an array for 100 variable length character strings.
The final line in the example is an indicator array for use with nulls.

If you decide you want to play around with multi-row FETCH, you will first have to set up a cursor.
The DECLARE CURSOR SQL statement has a new keyword added for just this purpose. WITH ROWSET POSITIONING tells DB2 at cursor open that this cursor allows multi-row processing.
Even if you define the cursor for multi-row processing, you still have the option to fetch one row at a time.
It would simply be a 1 row rowset. BTW, the default for a DECLARE CURSOR is WITHOUT ROWSET POSITIONING allowing a cursor to work exactly as it did in previous versions if no changes are made to the cursor.

EXEC SQL 
     DECLARE GETSET CURSOR 
     WITH ROWSET POSITIONING 
     FOR 
     SELECT ACT-NMBR-ARRAY, ACT-CITY-ARRAY, ACT-NAME-ARRAY 
     FROM   H_PRSN-ACT_TRNS_T 
END-EXEC. 

Now that the cursor is set up, we can move on to the fetch statement.
You notice I used the word rowset (I guess is not really a word though) near to the end of the previous paragraph. 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 and can have a maximum size of 32767.
Here's an example:

EXEC SQL 
     FETCH NEXT ROWSET FROM GETSET FOR 100 ROWS 
     INTO :HV-ACT-NMBR-ARRAY 
     ,    :HV-ACT-CITY-ARRAY:HV-IND-ARRAY 
     ,    :HV-ACT-NAME-ARRAY 
END-EXEC. 

The SQL OPEN CURSOR statement will build the result set that we will process the FETCH against.
In the above example, we are going to 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.
That is pretty basic COBOL programming and should not be a big deal.
DB2 will give you all the information you need, like row counts and SQLCODEs via the new V8 SQL statement GET DIAGNOSTICS, to aid in deciphering what was placed in the array by DB2.

In additional 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 is not specified on a cursor declared for multi-row FETCH, what ever value used for the last FETCH executed will be use.
However, I like simplicity and stuff that is easy to read and debug, so I would strongly suggest that the FOR nn ROWS eliminating any questions about the number of rows that will be returned.

Let's take a look a few more example to insure there is no confusion on how all of this stuff works.

EXEC SQL 
     FETCH FIRST ROWSET FROM GETSET FOR 20 ROWS 
     INTO :HV-ACT-NMBR-ARRAY 
     ,    :HV-ACT-CITY-ARRAY:HV-IND-ARRAY 
     ,    :HV-ACT-NAME-ARRAY 
END-EXEC. 

In this example, we are going to position the cursor at the beginning of the result and fetch 20 rows into the associated arrays on the INTO clause.
We have now accessed rows 1-20. If I were to issue a

EXEC SQL 
     FETCH NEXT ROWSET FROM GETSET FOR 20 ROWS 
     INTO :HV-ACT-NMBR-ARRAY 
     ,    :HV-ACT-CITY-ARRAY:HV-IND-ARRAY 
     ,    :HV-ACT-NAME-ARRAY 
END-EXEC. 

I would fetch the next 20 rows starting at the row AFTER the current set of rows fetched.
In my example, that would be starting on row 21 and fetching the next 20 rows (21-40).
Let's carry this out just a little further and examine what happens if you code what I suggested you should not earlier.
On this example, I am going to code a NEXT ROWSET without coding the FOR nn ROWS clause.

EXEC SQL 
     FETCH NEXT ROWSET FROM GETSET 
     INTO :HV-ACT-NMBR-ARRAY 
     ,    :HV-ACT-CITY-ARRAY:HV-IND-ARRAY 
     ,    :HV-ACT-NAME-ARRAY 
END-EXEC. 

What is also kind of cool is that the FETCH FIRST nn ROWS ONLY clause can be used with multi-row fetch.
The only catch here is that you will receive a +100 SQLCODE from the FETCH if the cursor tries to fetch multiple rows beyond the FETCH FIRST restriction.
You need to make sure you keep the two separate.
The FOR nn ROW 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.

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 would put the cursor past the end of the result set, a warning message is returned on the last row fetched.
This is just a little different than the FETCH FIRST nn ROWS example in the previous paragraph.
It all seems pretty straight forward, right?
You just have to remember that your cursor's position with multi-row FETCH is based on the rowset returned and not just a single row.

You also have to be a little cautious about end of result processing when using rowsets.
Unlike a simple single row FETCH, when a multi-row FETCH receives a +100, you cannot just sop processing because there could be some rows returned, a partial rowset, that remain to be processed.
Once again, the Version 8 SQL statement GET DIAGNOSTICS will assist determining 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 while 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 only want to retrieve a single row following the previous rowset just use the FOR 1 ROW clause on the FETCH NEXT ROWSET statement.

OK so far? If so, I have another new "term" to introduce to you: wide cursors.
A wide cursor is a cursor that covers more than one row.
Why do we care? Positioned updates and deletes are affected by how you handle a wide cursor.
Positioned refers to the use of WHERE CURRENT OF cursor.
If you code a WHERE CURRENT OF clause in the same fashion as 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 will need to use the new V8 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.

Have you had enough about fetching multiple rows yet?
I have! So it's time to move on the multi-row INSERT because this entry has gone on way too long.
I will continue with a discussion of multi-row INSERT in an entry that I will post tomorrow.

I did want to mention one last thing about fetching multiple rows in Version 8.
Multi-row fetch operations are delivered to you automatically for DRDA applications, and by DSNTEP4 (DSNTEP2 does not use multi-row), and DSNTIAUL.
Any distributed query will use multi-row fetch, even if you are still in compatibility mode (CM). Up to a 50% CPU reduction, as compared to V7, could be realized by a fetch form a remote client that invokes block fetch.

QMF can also take advantage of multi-row FETCH if APAR PQ99482 is applied.

That's it for now and check back tomorrow for the conclusion.

Thanks for stopping by Willie