< Day Day Up > |
Programming with CursorsRecall 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:
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:
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 ModificationOften 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 CursorWORKING-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
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:
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:
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 InsensitiveAn 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:
ASENSITIVE Scrollable CursorsFlexibility 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 StaticWhen 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 GuidelinesThe following guidelines are offered to help you implement effective scrollable cursors in your DB2 application programs. Use Scrollable Cursors with CautionAs 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 CICSPseudo-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 ChangesIf 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 TypesThe 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
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:
Rowset Positioning Cursors (Multiple Row FETCH )
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
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
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
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. |
< Day Day Up > |