170 DB2 for z/OS Application Programming Topics
In Example 12-9 we show how the same cursor defined in Example 12-8 is valid if it is
INSENSITIVE. Since the AVG function is only processed at OPEN CURSOR time, the data
does not change and thus the values for the AVG does not change.
Example 12-9 Aggregate function in an INSENSITIVE cursor
DECLARE C1 INSENSITIVE SCROLL CURSOR
WITH HOLD FOR
SELECT NORDERKEY, AVG(TAX)
FROM SC246300.TBLINEITEM
GROUP BY NORDERKEY
In Example 12-10 we show the scalar function SUBSTR. If the cursor is SENSITIVE, the
function is evaluated at FETCH time. If the cursor is INSENSITIVE, the function is evaluated
at OPEN CURSOR time.
Example 12-10 Scalar functions in a cursor
DECLARE C1 SENSITIVE STATIC SCROLL CURSOR
WITH HOLD FOR
SELECT CUSTKEY, LASTNAME, SUBSTR(COMMENT,1,20)
FROM SC246300.TBCUSTOMER
In Example 12-11 shows that you can also use an expression in a scrollable cursor. In this
example we use a sensitive scrollable cursor. Therefore, the expression will be re-evaluated
against the base table at each FETCH operation to make sure the rows still qualifies.
Example 12-11 Expression in a sensitive scrollable cursor
DECLARE TELETEST SENSITIVE STATIC SCROLL
CURSOR FOR
SELECT EMPNO
,FIRSTNME
,SALARY
,COMM
,BONUS
,COMM + BONUS AS ADDITIONAL_MONEY
FROM SC246300.TBEMPLOYEE
WHERE COMM + BONUS > 200
FOR UPDATE OF BONUS
12.6 Update and delete holes
Update and delete holes are only created for SENSITIVE STATIC scrollable cursors. An
update hole occurs when the corresponding row of the underlying table has been updated
such that the updated row no longer satisfies the search condition specified in the SELECT
statement of the cursor. A delete hole occurs when the corresponding row of the underlying
table has been deleted.
Chapter 12. Scrollable cursors 171
12.6.1 Delete hole
Delete holes can be created in three ways:
򐂰 When a row has been deleted from the base table by another agent.
򐂰 When the cursor itself has deleted a row that was part of the result set returned at OPEN
CURSOR time.
򐂰 When the current process deletes a row outside of the scrollable cursor, and the row is
part of the cursors result set.
An example of the occurrence of a delete hole is:
DECLARE C1 SENSITIVE STATIC SCROLL CURSOR
FOR SELECT ACCOUNT
,ACCOUNT_NAME
FROM TBACCOUNT
WHERE TYPE = 'P'
FOR UPDATE OF ACCOUNT_NAME;
The OPEN CURSOR is executed and the DB2 temporary table is built with two rows. See
Example 12-12 for the results of the OPEN CURSOR.
Another user executes the statement:
DELETE FROM TBACCOUNT
WHERE TYPE = ‘P’
AND ACCOUNT = ‘MNP230’ ;
COMMIT ;
The row is deleted from the base table.
The process executes its first FETCH:
FETCH SENSITIVE FROM C1 INTO :hv_account, hv_account_name ;
DB2 attempts to fetch the row from the base table but the row is not found, DB2 marks the
row in the result table as a delete hole.
DB2 returns the SQLCODE +222 to highlight the fact that the current cursor position is
over a hole.
+222: HOLE DETECTED USING cursor-name
At this stage, the host variables are empty; however, it is important for your application
program to recognize the hole, as DB2 does not reset the host variables if a hole is
encountered.
If the FETCH is executed again, the cursor is positioned on the next row, which in the
example is for account ULP231. The host variables now contain ULP231 and MS S
FLYNN.
It is important to note that if an INSENSITIVE fetch is used, then only update and delete holes
created under the current open cursor are recognized. Updates and deletes made by other
processes or outside the cursor are not recognized by the INSENSITIVE fetch.
Note: An application program is not able to distinguish between a delete hole and an
update hole, only that there is a hole.

Get DB2 for z/OS Application Programming Topics now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.