Programming with Cursors

Programming with Cursors

Recall from Chapter 1, "The Magic Words," that an impedance mismatch occurs between SQL and the host language, such as COBOL. COBOL operates on data a row at a time; SQL operates on data a set at time. Without a proper vehicle for handling this impedance mismatch (such as arrays), using embedded SELECT statements would be impossible . IBM's solution is the structure known as a symbolic cursor , or simply cursor .

DB2 application programs use cursors to navigate through a set of rows returned by an embedded SQL SELECT statement. A cursor can be likened to a pointer. As the programmer, you declare a cursor and define an SQL statement for that cursor. After that, you can use the cursor in much the same manner as a sequential file. The cursor is opened, rows are fetched from the cursor one row at a time, and then the cursor is closed.

You can perform four distinct operations on cursors:

DECLARE	Defines the cursor, gives it a name unique to the program in which it is embedded, and assigns an SQL statement to the cursor name . The DECLARE statement does not execute the SQL statement; it merely defines the SQL statement.
OPEN	Readies the cursor for row retrieval. OPEN is an executable statement. It reads the SQL search fields, executes the SQL statement, and sometimes builds the results table. It does not assign values to host variables , though.
FETCH	Returns data from the results table one row at a time and assigns the values to specified host variables. If the results table is not built at cursor OPEN time, it is built FETCH by FETCH .
CLOSE	Releases all resources used by the cursor.

Whether the results table for the SQL statement is built at cursor OPEN time or as rows are fetched depends on the type of SQL statement and the access path . You will learn about access paths in Chapter 21, "The Optimizer."

When you're processing with cursors, an SQL statement can return zero, one, or many rows. The following list describes the cursor processing that occurs for the different number of retrieved rows:

One row	Use of the cursor is optional. A result set of one row occurs either because the SQL predicates provided specific qualifications to make the answer set distinct or because a unique index exists for a column or columns specified in the predicates of the WHERE clause.
Many rows	Cursor processing is mandatory. When multiple rows are returned by an SQL statement, a cursor must be coded. If multiple rows are returned by a SELECT statement not coded using a cursor, DB2 returns a -811 SQLCODE (the SQLSTATE value is 21000 ).
Zero rows	No rows exist for the specified conditions, or the specified conditions are improperly coded. When no rows are returned, the SQL return code is set to +100 .

When cursors are used to process multiple rows, a FETCH statement is typically coded in a loop that reads and processes each row in succession. When no more rows are available to be fetched, the FETCH statement returns an SQLCODE of +100 , indicating no more rows. For an example of cursor processing, consult Listing 11.2.

Listing 11.2. Cursor Processing

 WORKING-STORAGE SECTION.        .        .        .     EXEC SQL         DECLARE C1 CURSOR FOR             SELECT   DEPTNO, DEPTNAME, MGRNO             FROM     DEPT             WHERE    ADMRDEPT = :ADMRDEPT     END-EXEC.        .        .        . PROCEDURE DIVISION.        .        .        .     MOVE  'A00'      TO ADMRDEPT.     EXEC SQL         OPEN C1     END-EXEC. IF SQLCODE < 0         PERFORM 9999-ERROR-PARAGRAPH.     MOVE  'YES'     TO MORE-ROWS.     PERFORM 200-PROCESS-DEPTS         UNTIL MORE-ROWS = 'NO'.     EXEC SQL         CLOSE C1     END-EXEC.     GOBACK. 200-PROCESS-DEPTS.        .        .        .     EXEC SQL         FETCH C1         INTO :DEPTNO,              :DEPTNAME,              :MGRNO     END-EXEC.     IF SQLCODE < 0         PERFORM 9999-ERROR-PARAGRAPH.     IF SQLCODE = +100         MOVE  'NO'     TO MORE-ROWS     ELSE  perform required processing  . 

In Listing 11.2, a cursor is declared for an SQL SELECT statement in WORKING-STORAGE . Values are moved to the host variables, and the cursor is opened. A loop fetches and processes information until no more rows are available; then the cursor is closed.

Using a Cursor for Data Modification

Often an application program must read data and then, based on its values, either update or delete the data. You use the UPDATE and DELETE SQL statements to modify and delete rows from DB2 tables. These statements, like the SELECT statement, operate on data a set at a time. How can you then first read the data before modifying it?

You do so by using a cursor and a special clause of the UPDATE and DELETE statements that can be used only by embedded SQL: WHERE CURRENT OF . You declare the cursor with a special FOR UPDATE OF clause.

Refer to Listing 11.3, which declares a cursor named C1 specifying the FOR UPDATE OF clause. The cursor is opened and a row is fetched. After examining the contents of the retrieved data, the program updates or deletes the row using the WHERE CURRENT OF C1 clause.

Listing 11.3. Updating with a Cursor

 WORKING-STORAGE SECTION.     EXEC SQL         DECLARE C1 CURSOR FOR             SELECT  DEPTNO, DEPTNAME, MGRNO             FROM    DEPT             WHERE   ADMRDEPT = :ADMRDEPT             FOR UPDATE OF MGRNO     END-EXEC. PROCEDURE DIVISION.            .            .            .         MOVE  'A00'      TO ADMRDEPT.         EXEC SQL             OPEN C1         END-EXEC.         MOVE  'YES'     TO MORE-ROWS.         PERFORM 200-MODIFY-DEPT-INFO             UNTIL MORE-ROWS = 'NO'.         EXEC SQL             CLOSE C1         END-EXEC.         GOBACK.     200-MODIFY-DEPT-INFO.            .            .            .         EXEC SQL             FETCH C1             INTO  :DEPTNO,                   :DEPTNAME,                   :MGRNO         END-EXEC.         IF SQLCODE < 0             PERFORM 9999-ERROR-PARAGRAPH.         IF SQLCODE = +100             MOVE  'NO'     TO MORE-ROWS         ELSE             EXEC SQL                 UPDATE DEPT                 SET MGRNO = '000060'                 WHERE CURRENT OF C1             END-EXEC. 

These features enable you to perform row-by-row operations on DB2 tables, effectively mimicking sequential file processing.

Scrollable Cursors

One of the more significant new application development features of DB2 V7 is the scrollable cursor. Scrollable cursors provide the ability to scroll forward and backward through data in an application program. Contrast this with a "normal" cursor, in which data can only be accessed one row at a time in a forward direction using the FETCH statement.

With scrollable cursors, no additional host language code (COBOL, C, etc.) is required to move forward and backward through the results set of a SELECT statement. A scrollable cursor makes navigating through SQL result sets much easier. The SCROLL keyword is used to specify a cursor as a scrollable cursor.

DB2 V7 introduced two types of scrollable cursors:

• SENSITIVE : ” The results are updateable; the program can access data that is changed by the user or other users.

• INSENSITIVE : ” Not updateable; the cursor results will not show any changes that are subsequently made to the data.

More details on cursor sensitivity are provided later in this section. Scrollable cursors allow developers to move through the results of a query in multiple ways. The FETCH statement is still used, but it is combined with keywords to move the fetching in a specific direction. The following keywords are supported when fetching data from a scrollable cursor:

• NEXT ” Will FETCH the next row, the same way that the pre-V7 FETCH statement functioned.

• PRIOR ” Will FETCH the previous row.

• FIRST ” Will FETCH the first row in the results set.

• LAST ” Will FETCH the last row in the results set.

• CURRENT ” Will re- FETCH the current row from the result set.

• BEFORE ” Positions the cursor before the first row of the results set.

• AFTER ” Positions the cursor after the last row of the results set.

• ABSOLUTE n ” Will FETCH the row that is n rows away from the first row in the results set.

• RELATIVE n ” Will FETCH the row that is n rows away from the last row fetched.

For both ABSOLUTE and RELATIVE , the number n must be an integer. It can be either a positive or a negative number, and it can be represented as a numeric constant or as a host variable.

All of the FETCH options for scrollable cursors also reposition the cursor before fetching the data. For example, consider the following cursor logic:

 DECLARE csr1 SENSITIVE STATIC SCROLL CURSOR FOR SELECT   FIRSTNAME, LASTNME    FROM     DSN8710.EMP    ORDER BY LASTNME; OPEN csr1; FETCH LAST csr1 INTO :FN, :LN; 

Issuing these SQL statements will declare a scrollable cursor named csr1 , open that cursor, and then FETCH the last row from the cursor's results set. The FETCH LAST statement will reposition the cursor to the last row of the results set, and then FETCH the results into the host variables as specified. Scrollable cursors reduce the amount of time and effort required to move backward and forward through the results of SQL queries.

Sensitive Versus Insensitive

An INSENSITIVE cursor causes the results to be materialized and changes to the underlying data will never be reflected in the results set.

A SENSITIVE cursor, on the other hand, causes updates and deletes to be reflected in the answer set. Whether or not you can actually see the modifications depends on whether the cursor is defined as STATIC or DYNAMIC (which is covered in the next section).

Any UPDATE or DELETE that is processed will cause a SENSITIVE STATIC cursor to show the change as an update or delete hole; any INSERT s, however, will not be reflected in the answer set. A SENSTIVE DYNAMIC cursor will show the actual changed data ”including all data affected by UPDATE , DELETE , and INSERT operations.

Update and delete holes pose a coding challenge for programmers. You will need to write additional code in your program to test the SQLCODE returned. Pay attention to the following SQLCODE s:

-222	An UPDATE or DELETE operation was attempted against a hole using the following cursor name ( SQLSTATE: 24510 )
-223	An UPDATE or DELETE operation was attempted against an update hole using the following cursor name ( SQLSTATE: 24511 )

ASENSITIVE Scrollable Cursors

Flexibility is offered for declaring cursor sensitivity with the ASENSITIVE option for scrollable cursors. An ASENSITIVE cursor that is read-only behaves as an INSENSITIVE cursor. If the ASENSITIVE cursor is not read-only, it will behave as a SENSITIVE DYNAMIC cursor.

The default for cursor sensitivity is ASENSITIVE . Such a cursor is helpful for distributed applications where you do not know for sure whether the server supports sensitivity or scrollability.

Dynamic Versus Static

When using a SENSITIVE scrollable cursor, an additional parameter is required to specify whether the cursor is to be DYNAMIC or STATIC . For DB2 V7, STATIC is the only option, but as of DB2 V8, scrollable cursors can be DYNAMIC .

To use scrollable cursors in DB2 V7 you must use declared temporary tables, another new feature of DB2 Version 7. Declared temporary tables are discussed in-depth in Chapter 5, "Data Definition Guidelines." DB2 uses a declared temporary table to hold and maintain the data returned by the scrollable cursor. When a static scrollable cursor is opened, qualifying rows are copied to a declared temporary table that is created automatically by DB2. DB2 drops the result table when the cursor is closed. So, before you begin to use scrollable cursors in V7, be sure to define a temporary database.

CAUTION

Be careful when using scrollable cursors with DB2 V7. Every new scrollable cursor creates a physical table in the temporary database.

With DB2 V8 IBM now supports dynamic scrollable cursors. So, as of V8, a SENSITIVE cursor can now be specified as DYNAMIC , too. A dynamic scrollable cursor does not use a temporary table to store the results set. Instead, it simply accesses the data right out of the base table. Some SQL statements cannot be used in a dynamic scrollable cursor; for example, a read-only cursor cannot be DYNAMIC .

Dynamic scrolling is possible because DB2 V8 allows us to backward scan through an index for the first time. This allows DB2 to move backward and forward through the scrollable cursor using an index.

Use a dynamic scrollable cursor when your application must be able to see all modified rows.

CAUTION

If a result table has to be materialized at the time cursor is opened, dynamic scrolling is not possible.

Scrollable Cursor Guidelines

The following guidelines are offered to help you implement effective scrollable cursors in your DB2 application programs.

Use Scrollable Cursors with Caution

As helpful as scrollable cursors are, do not make every cursor a scrollable cursor. Scrollable cursors require substantially more overhead than a traditional, non-scrollable cursor. Analyze the requirements of your applications and deploy scrollable cursors only where it makes sense to do so.

No Scrollable Cursors in Pseudo-Conversational CICS

Pseudo-conversational CICS programs cannot use scrollable cursors. Do not attempt to code a scrollable cursor in a pseudo-conversational CICS program.

Use Dynamic Cursors to See Changes

If you need to see the changes made to data by concurrent processes, be sure to use a dynamic scrollable cursor. This is the only type of scrollable cursor that has visibility to underlying changes.

CAUTION

Concurrent INSERT activity will not be visible to any scrollable cursor ”only concurrent UPDATE and DELETE activity can be made visible to the cursor.

Know Your Scrollable Cursor Types

The resources required and the flexibility of processing are greatly affected by the proper sensitivity and dynamism settings for your scrollable cursors. Consult Table 11.7 for a short synopsis of the features and requirements for each type of scrollable cursor at your disposal. The visibility column refers to whether the cursor has visibility to changes made to the data; the updatability column refers to whether data can be updated using the cursor.

Table 11.7. Types of Scrollable Cursors

Dynamism	Sensitivity	Visibility	Updatability
N/A	INSENSITIVE	No	No
STATIC	SENSITIVE	[1]	Yes
DYNAMIC	SENSITIVE	Yes	Yes

^[1] Inserts are not visible; updates and deletes are visible as holes.

Be sure to choose the appropriate cursor for the type of processing you require. Consider the following precautions as you decide on your cursor type:

• If you do not need to move forward and backward through your results set, do not use a scrollable cursor.

• If the query will cause DB2 to materialize a results set, do not use a DYNAMIC scrollable cursor ”it will not work.

• Use common sense; if no concurrent modifications are possible, do not choose a DYNAMIC scrollable cursor.

• If you want to see updates and deletes but do not want to see newly inserted data, choose a STATIC SENSITIVE scrollable cursor.

• If you do not want to see any modifications, choose an INSENSITIVE scrollable cursor.

Rowset Positioning Cursors (Multiple Row FETCH )

When you need to retrieve multiple rows, consider using a cursor defined with rowset positioning. A rowset is a group of rows that are operated on as a set. Such a cursor enables your program to retrieve more than one row using a single FETCH statement. This capability is new as of DB2 Version 8. By fetching multiple rows at once, your request might become more efficient, especially for distributed requests .

To use this feature, you must DECLARE your cursor as using the WITH ROWSET POSITIONING parameter. For example

 EXEC SQL   DECLARE CURSOR SAMPCURS  WITH ROWSET POSITIONING  FOR   SELECT DEPTNO   FROM   DSN8810.DEPT END-EXEC. 

Furthermore, to use a multi-row fetch you must have defined the appropriate structures to receive multi-row data. This means you must have defined an array of host variables into which the fetched rows can be placed. Each column fetched requires its own host variable array into which its values will be placed. Be sure to match the array size to the rowset size . With the appropriate setup coded, FETCH statements can be written to retrieve more than a single row from the result set. For example

 FETCH ROWSET FROM SAMPCURS   FOR 5 ROWS   INTO HOSTVAR-ARRAY; 

As you can see, the multiple-row fetch block is identical to the existing single-row-fetch block in DB2 V7, except that there are two additional clauses ” ROWSET and FOR n ROWS . The ROWSET clause specifies that the orientation of this cursor is rowset positioning (instead of single row). The FOR n ROWS clause specifies the size of the rowset to be returned. The maximum rowset size is 32,767.

Rowset cursors are very useful when you need to retrieve many rows or large amounts of data in distributed systems. By retrieving multiple rows with a single FETCH , multiple trips between the application and the database can be eliminated, thereby improving network performance.

Rowset positioning can be combined with scrollable cursors to deliver very flexible and efficient data access. To FETCH from a scrollable cursor with rowset positioning, you will need to deploy similar keywords as are used with simple scrollable cursors, namely

• NEXT ROWSET

• PRIOR ROWSET

• FIRST ROWSET

• LAST ROWSET

• CURRENT ROWSET

• ROWSET STARTING AT ABSOLUTE n

• ROWSET STARTING AT RELATIVE n

Each of these provides similar functionality as described in the previous section on scrollable cursors, but with a rowset orientation ”that is, they operate on multiple rows.

CAUTION

Multiple-row fetch is not supported in FORTRAN, Java, REXX, or SQL procedures.

An application program can combine single-row and multiple-row fetches from a rowset positioning cursor. This is done simply by specifying the ROWSET orientation clause for multiple-row fetches and removing it for single-row fetches. If FOR n ROWS is not specified for a rowset positioning cursor, then the size of the rowset will be the same as the previous multiple row FETCH .

Modifying Data Using Rowset Positioning Cursors

Data that has been fetched using a rowset positioning cursor can be subsequently modified (using positioned UPDATE s and DELETE s). You can either modify all of the rows in the rowset or just one specific row in the rowset. If you specify WHERE CURRENT OF cursor , all of the rows in the rowset will be affected (that is, either deleted of updated). For example, the following SQL statements will impact the entire rowset ( assuming , of course, that csr1 and csr2 in the following statements are rowset positioning cursors):

 UPDATE EMP SET SALARY = 50000.00 WHERE CURRENT OF CSR1; DELETE DEPT WHERE CURRENT OF CSR2; 

New syntax is offered to modify just a single row within the rowset. A new clause, FOR CURSOR csr FOR ROW n OF ROWSET , allows you to direct DB2 to UPDATE or DELETE just a single row. For example

 UPDATE EMP SET SALARY = 50000.00 FOR CURSOR CSR1 FOR ROW :HV OF ROWSET; DELETE DEPT FOR CURSOR CSR2 FOR ROW 3 OF ROWSET; 

Inserting Multiple Rows

Similar to FETCH processing, INSERT processing can be used in conjunction with the FOR n ROWS clause as of DB2 Version 8. Consider using this feature to INSERT multiple rows with a single statement when your application program needs to do bulk data insertion.

By coding your program to fill up an array with data to be inserted and then using INSERT...FOR n ROWS , the INSERT process can be made more efficient. For example

 INSERT INTO SAMPLE_TABLE FOR :N ROWS VALUES(:HOSTVAR-ARRAY1, : HOSTVAR-ARRAY2, ...) ATOMIC; 

The [NOT] ATOMIC clause is used to specify whether the INSERT should fail or succeed as a complete unit. By specifying ATOMIC , if one row fails, every row fails. On the other hand, specifying NOT ATOMIC allows each row to fail or succeed on its own accord. Before deciding to use specify ATOMIC , take into consideration the number of rows and row size. If one row out of thousands fails, performance can suffer as DB2 rolls back previous changes and logs information.

NOTE

To handle nulls, you will need to supply a null indicator array. This array is required for nullable columns and must be specified following the host variable array.