DB2 multiple-row FETCH

You can enhance the performance of your application programs by using multiple-row FETCH statements to request that DB2 send multiple rows of data, at one time from the database. Using these multiple-row statements in local applications results in fewer accesses of the database.  Using these multiple-row statements in distributed applications results in fewer network operations and a significant improvement in performance. Before Version 8, you needed to use a series of single-row fetches to return many rows of data. Now, in Version 8, you can use multiple fetches within a single SQL statement to accomplish the same task more efficiently.

The guide lines for when to use the construct are as follows:

1.  If it is known that five or more rows will be fetched, use rowset.

2.  If it is known that less than five rows will be fetched, use singleton.

3.  If it is not known how many rows will be fetched, use rowset. 

When we use ROWSET POSITIONING in the cursors, we are only making one call to DB2 to retrieve up to 100 rows vs executing 100 calls to DB2 to retrieve the same results without ROWSET.  This is why we can see significant CPU savings on the cursor operations.   

The cursor declaration contains the ROWSET POSITIONING construct establishing the setting for that cursor.  The FETCH on this cursor then determines the size of the ROWSET returned in one call.  The maximum we want to use is 100 ROWS since any rowset greater than 100 provides diminishing returns.  The setting for the ROWSET size should be determined by how many rows you expect the query to return.  As an example, if the query typically returns 30, set the FETCH to 30 rows.  If the query will be returning several hundred or more rows, we want to set the FETCH to 100 rows.  Remember the guide lines set out above when coding the cursors.   

A little more info on checking SQL codes, namely the SQLCODE and SQLERRD(3).

When you get a +100 while processing single rows, you are done since you are only expecting one row and returning one row.  When you get a +100 while processing a ROWSET, you need to check the SQLERRD(3) field to see how many rows were retrieved.  This coding method will tell you when you have completed processing the full result set for that cursor.

For example if there are 460 rows being fetched and the rowset size is 100:

Fetch 1 will get a zero SQLCODE and the SQLERRD(3) will be 100

Fetch 2 will get a zero SQLCODE and the SQLERRD(3) will be 100

Fetch 3 will get a zero SQLCODE and the SQLERRD(3) will be 100

Fetch 4 will get a zero SQLCODE and the SQLERRD(3) will be 100

Fetch 5 will get a +100 SQLCODE and the SQLERRD(3) will be 60

The basic steps in using a rowset cursor are below

Step 1: Declare the rowset cursor

To enable a cursor to fetch rowsets, use the WITH ROWSET POSITIONING clause in the DECLARE CURSOR statement. The following example shows how to declare a rowset cursor:

EXEC SQL

   DECLARE C1 CURSOR WITH ROWSET POSITIONING FOR

      SELECT EMPNO, LASTNAME, SALARY

        FROM TESTDB.EMP

END-EXEC.

Step 2: Open the rowset cursor

EXEC SQL

  OPEN C1

END-EXEC.

Step 3: Use multiple-row FETCH statement with host variable arrays

The following example shows a FETCH statement that retrieves 20 rows into host variable arrays that are declared in your program:

EXEC SQL

     FETCH NEXT ROWSET FROM C1

       FOR 20 ROWS

      INTO :HVA-EMPNO, :HVA-LASTNAME, :HVA-SALARY :INDA-SALARY

END-EXEC.

When your program executes a FETCH statement with the ROWSET keyword, the cursor is positioned on a rowset in the result table. That rowset is called the current rowset. The dimension of each of the host variable arrays must be greater than or equal to the number of rows to be retrieved.

The host variables should be defined as follows.

01 EMP-REC.

   05 HVA-EMPNO      PIC X(05) OCCURS 20 TIMES.

   05 HVA-LASTNAME   PIC X(10) OCCURS 20 TIMES.

   05 HVA-SALARY     PIC S9(5)V99 COMP-3 OCCURS 20 TIMES.

01 INDA-SALARY       PIC S9(4) COMP OCCURS 20 TIMES.

Step 4: Specify what to do at end-of-data for a rowset cursor

If SQL RC = +100 then the End of result table has been found, but rows could possibly exists within the host variable array to process. SQLERRD(3) contains the actual number of rows

returned.

Step 5: Close the rowset cursor

Endevor: 3 scenarios for SYNCHRONIZATION errors

This topic explains three common scenarios that generates SYNC errors.

When you add your program into Endevor, that version is assigned a BASE date that will be EQUAL to the CURRENT SOURCE Date of the module you took ( do a display element and then an M to see the two screen display, hit enter to see the SECOND page, (PANEL 2 OF 2)). the BASE DATE information  looks like this:

 -----------------------------------  BASE  ------------------------------------
 USERID:  TXXAKX9    DATE/TIME: 01JUL11 06:53                                         

This date is the CURRENT SOURCE DATE  of the element in the next highest stage, essentially. Display it and look at (PANEL 1 OF 2).  That relationship makes sense. The next highest up is your "starting point" or what your code is "based" on. From that point forward, Endevor will ALWAYS know that you code was 'based' on that code.

Here are three scenarios that WOULD cause out of sync or SYNCHRONIZATION ERRORS. All require that you 'do' something. A synchronization error ALWAYS requires that you do something.. 

1) the code 'above' yours wasn't quite right and needed a few more changes.... it wasn't perfect. That NEVER happens, right? Someone with the AUTHORITY to  make changes at that level in the lifecycle makes changes directly to it. This causes the CURRENT SOURCE DATE of that program to change. Your program was "based" on changes prior to that, WITHOUT these 'corrections' so your BASE DATE no longer is equal to the CURRENT SOURCE date of the program above it. If you try to promote that program, Endevor will tell you that the CURRENT SOURCE in the stage above is NOT what you based you program on.... Endevor is saying,  there have been changes to that upper version that you might not be aware of and may not have included in your version. If you tell Endevor to ignore that SYNC error and proceed and you have not examined and then included any subsequent changes from the upper version into your version (using PDM, for example to insure you have all the current code) , when you move your program your version will appear to be 'deleting' those "corrections" that occurred after you initially took you 'copy' of the program. You will reintroduce the reason for the correction if you do not alter you code to include that change. Once you have assured yourself that ALL corrections have been include you CAN safely ignore the SYNC error at that point. .   

2) Same setup. There is a program ahead of you, you take a copy, make your changes and add it back. Your BASE DATE matches the CURRENT DATE of the source ahead of you .Everything is fine. All of a sudden, out of the blue, the project ahead of yours is cancelled!! Pretty unlikely, huh? Well, the person who added that program DELETES it from Endevor. guess what? You program now is based on a program that doesn't even EXIST!  In the Endevor world, the simple rule that your BASE DATE does not match the CURRENT SOURCE date of the module above it is violated. A synchronization error will be issued. If you ignore it without doing a retrofit to your code, you will be 'installing' that cancelled project when you promote your module.

3) The OM team has an abend at night. They make a slight change and promote that module through the EMER path into PRODuction. In effect, they have changed the CURRENT SOURCE DATE for the highest module in the lifecycle. If anyone else was modifying that program on ANY of the paths, they will all be affected. See number 1) as the situation from here on, the resolution and actions required are the same and the danger is the same.

Examine these scenarios, try them yourself. These are then three most common situations but there are others. The main thing to remember is that relationship. Do a list element with 'search the map' .You will see that this relationship holds true always and if not, expect a SYNC error and that some 'action' will be required.

DB2 Isolation level

The Isolation Level in DB2 determines the extent of the locks to be held. In sequence from fewest locks to most locks, the values of Isolation Level are UR (Uncommitted Read), CS (Cursor Stability), RS (Read Stability) and RR (Repeatable Read). The isolation level of a program is set when the bind is done as an option to the bind. The default value for a bind of a PLAN is RR, while the default for a package is that of that plan to which is bound. A package can have a different isolation level than a plan that uses it. The isolation can used on select clause (WITH UR, or WITH CS). Uncommitted read cannot be specified on a statement that changes data (Insert, update or delete). Other options on a declare cursor have implications with regard to the number of rows or pages locked. Java language program use a different mechanism to set the isolation, with conflicting terminology (more on this later). 

When you use SPUFI, the isolation level is set in the Options panel. The default is RR. The first time that you run SPUFI, you are likely to think that you “can’t run SPUFI” because no one is authorized to run SPUFI with RR: you will need to change the default value to CS. RR is the safest for you to use, but it has the most impact on everyone else (it basically locks all the rows or pages that you might be using). The only recommended option for SPUFI is CS. 

The Current Data can be set to Yes or No at bind time (for a plan or package). Current data means that the data that is fetched cannot be changed while it has been fetched. If the server is local, there is a difference only for read-only ambiguous cursors (those that specify neither “for read only” nor “for update of …”). For those cursors, the data cannot be changed while it is fetched. Of course, if the cursor is fetching from a work file, the data cannot be changed anyway. If the server is remote from the client, current data yes will prevent the data from being fetched a block at a time (ambiguous cursors only –  read only and UR cursors are always block fetched). For best performance, it is strongly recommended that you always use a bind option of Current Data NO, and specify “for read only”  in your cursor declaration whenever possible. 

Uncommitted Read holds the fewest possible locks. With UR, you can be retrieving rows that someone else has updated or inserted, but not yet committed. If someone else insert 1000 rows, and then later rolls back their changes, you might still see those rows (even though they were never really in the database). UR should only be used for applications that don’t care about the consistency of the data. UR should never be used for queries or selects for financial transactions. 

Repeatable Read is the default and is the safest from the point of view of “if you don’t know, make the safest choice,” yet it has the most impact on other users of the data. RR locks all rows that MIGHT be part of the result set, in other words, all rows that retrieved to examine them for qualification against the where clause. RR is intended to be used if you open a cursor a second time with the same where clause values, it should retrieve exactly the same rows that it did the first time. If you have a program design that opens a cursor, counts the rows, then reopens the cursor and processes that number of rows, you need to use RR. You avoid this problem during the design phase, by designing the program to process the cursor only once. It is safest because the database doesn’t know what your program design is: can you tolerate getting different result sets? It is a problem because all rows that might be part of the result set will remain locked until you commit. 

Read Stability is somewhat similar in that it locks all rows that ARE part of the result set. While RR locks all rows that are examined, RS, locks only those rows that qualify. With RS, if you reopen the cursor, you might see more rows the second time, but you will never see fewer rows. Once again, you can and should design to avoid the use of RS. No all servers support RS: those that don’t treat it as RR. 

Cursor Stability locks only the row or page that has been fetched. DB2 will lock the row and then fetch the row. When you issue the next fetch, DB2 will unlock the row, lock the next and then fetch the contents. If you are using Current Data NO and fetch data with CS concurrency, in most case DB2 can avoid taking locks entirely, so long as it is read only. For remote applications in these circumstances, DB2 will use block fetch and move many rows over the network simultaneously. Thus for best performance and maximum concurrency, you should always use CS in conjunction with current data NO. 

In order to achieve the maximum concurrency in the database, all applications should designed to use, and be using isolation level CS and Current Data NO. Cursors should be declared as FOR READ ONLY, or FOR UPDATE OF …. so that there are no ambiguous cursors. Commits should be frequently issued. Commits should not include less than one complete unit of work, nor should they be less frequent than 1000 changes to a single table. These specifications need to be considered in a project from early in the design phase and throughout a project. If they were not considered in the design, it is difficult to consider them later. But these principles are critical to getting good from a DB2 database.  

ISPF Edit/View Tips

1. Use HILITE command to set highlighting options.
2. Use TS line command to split the text at the cursor position.
3. Use TF line command to flow next record at the end of current record.
4. Use JEM edit macro to check JCL (use L .label command to locate any errors as indicated in the JEM report).
5. Use JSCAN and JSCAN1 edit macros for JCL checking. JSCAN1 will also resolve JOBTRAC variables.
6. Use FF, FN, FP, FL, FALL (Find First, Next, Previous, Last, All edit macros to find the field that your cursor is on.
7. Use X ALL to exclude all.  Use Fn and Ln line commands to un-exclude First and Last n excluded lines (where n is numeric).
8. Use X and NX parms on commands such as Find, Change and Delete to impact only excluded or non-excluded lines.
9. Use FLIP command to toggle between excluded lines and non-excluded lines.
10.COMPARE edit macro invokes Super-C from Edit/View mode. Type COMPARE on command line to get panel then hit PF1 for help. 
11. Use TE line command to edit and insert a block of lines after the current line.
12. Some commands useful in indenting code :
  )n - Shift characters right by 'n' places
  (n - Shift characters left by 'n' places
  ))n - Block Shift right by 'n' places
  ((n - Block Shift left by 'n' places

The below commands can be assigned to a PF Key or entered on the command line (do not prefix with TSO)

1. Use CRETRIEV command to combine the Cursor and Retrieve commands into one PF Key.
2. Use RETP to display a pop-up list of the last 25 commands you have entered. You can then select a command to re-invoke.
3. Use RETF to retrieve commands you have entered from oldest to most recent.
4. Use SPLIT NEW command to split screen and start a new logical session (upto 32 times). Use SWAP LIST to list logical sessions.


COMPARE SESSION command
Assume that you are editing a dataset or program and you went for a break. When you come back you don't remember what changes you have done. How will you find out that? 

Give COMPARE SESSION on the command line to see what changes you have done.

How MVS finds the program or procedure

To run a program or procedure, first MVS has to search and find that program or procedure. We have to use JOBLIB, STEPLIB, or JCLLIB statements to reduce search time.


Where MVS searches depends on what we specify in the JCL:

• For programs, MVS will automatically search standard system program libraries, such as SYS1.LINKLIB, which contains IBM-supplied programs. If the program we want to run resides in a private program library, we must specify either a JOBLIB DD statement or a STEPLIB DD statement for MVS to successfully locate the program.
  • When we submit the job, MVS will search any private libraries specified on the JOBLIB DD statement before searching system libraries. MVS repeats that search order for any programs called within the job.
    For a multi-step job, using the JOBLIB DD statement is most efficient when most of the programs reside in private libraries.
  • When we use a STEPLIB DD statement, we may place it anywhere within a job step but it typically appears after the EXEC statement. When we submit the job, MVS will search the private libraries specified on that STEPLIB DD statement, but only for the one step.
    For a multi-step job, using the STEPLIB DD statement is most efficient when most of the programs reside in system, rather than private, libraries.
• For procedures, MVS will automatically search standard system procedure libraries, such as SYS1.PROCLIB. If the procedure we want to run resides in a private library, we must specify the JCLLIB statement for MVS to successfully locate the procedure.
  
  
  If a job does not specify a procedure library, the system retrieves all cataloged procedures called by EXEC statements from the procedure libraries defined by the installation for the job's job class. To direct the system to search another procedure library, or to limit the procedure libraries it searches, code a JCLLIB statement that identifies one or more libraries. On a JCLLIB statement, we may list system libraries, installation-defined libraries, or private libraries. The system searches the libraries in the order in which they are specified on JCLLIB.