DB2 for z/OS Application Programming Topics

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 cursor’s 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.