Holes in the result table of a scrollable cursor

A hole in the result table means that the result table does not shrink to fill the space of deleted rows. It also does not shrink to fill the space of rows that have been updated and no longer satisfy the search condition. You cannot access a delete or update hole. However, you can remove holes in specific situations.

In some situations, you might not be able to fetch a row from the result table of a scrollable cursor, depending on how the cursor is declared:
  • Scrollable cursors that are declared as INSENSITIVE or SENSITIVE STATIC follow a static model, which means that Db2 determines the size of the result table and the order of the rows when you open the cursor.

    Deleting or updating rows after a static cursor is open can result in holes in the result table. See Removing a delete hole or update hole.

  • Scrollable cursors that are declared as SENSITIVE DYNAMIC follow a dynamic model, which means that the size and contents of the result table, and the order of the rows, can change after you open the cursor.

    A dynamic cursor scrolls directly on the base table. If the current row of the cursor is deleted or if it is updated so that it no longer satisfies the search condition, and the next cursor operation is FETCH CURRENT, then Db2 issues an SQL warning.

The following examples demonstrate how delete and update holes can occur when you use a SENSITIVE STATIC scrollable cursor.

Creating a delete hole with a static scrollable cursor:

Suppose that table A consists of one integer column, COL1, which has the values shown in the following figure.
Figure 1. Values for COL1 of table A
Begin figure description. A box contains the numbers 1 through 5 in ascending order. The box represents a one-column table.End figure description.
Now suppose that you declare the following SENSITIVE STATIC scrollable cursor, which you use to delete rows from A: 
EXEC SQL DECLARE C3 SENSITIVE STATIC SCROLL CURSOR FOR
  SELECT COL1
  FROM A
  FOR UPDATE OF COL1;
Now you execute the following SQL statements: 
EXEC SQL OPEN C3;
EXEC SQL FETCH ABSOLUTE +3 C3 INTO :HVCOL1;
EXEC SQL DELETE FROM A WHERE CURRENT OF C3;
The positioned delete statement creates a delete hole, as shown in the following figure.
Figure 2. Delete hole
Begin figure summary.A column is shown before a query and after a query. The later column has a delete hole.Detailed description available.

After you execute the positioned delete statement, the third row is deleted from the result table, but the result table does not shrink to fill the space that the deleted row creates.

Creating an update hole with a static scrollable cursor

Suppose that you declare the following SENSITIVE STATIC scrollable cursor, which you use to update rows in A: 
EXEC SQL DECLARE C4 SENSITIVE STATIC SCROLL CURSOR FOR
  SELECT COL1
  FROM A
  WHERE COL1<6;
Now you execute the following SQL statements: 
EXEC SQL OPEN C4;
UPDATE A SET COL1=COL1+1;
The searched UPDATE statement creates an update hole, as shown in the following figure.
Figure 3. Update hole
Begin figure summary. A column is shown before a query and after a query. The later column has an update hole. Detailed description available.

After you execute the searched UPDATE statement, the last row no longer qualifies for the result table, but the result table does not shrink to fill the space that the disqualified row creates.