If you try to fetch data from a delete hole
or an update hole, Db2 issues
an SQL warning. If you try to update or to delete a delete hole or
delete an update hole, Db2 issues
an SQL error.
About this task
You can remove a delete hole only by opening the scrollable
cursor, setting a savepoint, executing a positioned DELETE statement
with the scrollable cursor, and rolling back to the savepoint.
You
can convert an update hole back to a result table row by updating
the row in the base table, as shown in the following figure. You can
update the base table with a searched UPDATE statement in the same
application process, or a searched or positioned UPDATE statement
in another application process. After you update the base table, if
the row qualifies for the result table, the update hole disappears.
Figure 1. Removing an update hole
A hole becomes visible to a cursor when a cursor operation
returns a non-zero SQLCODE. The point at which a hole becomes visible
depends on the following factors:
Whether the scrollable cursor creates the hole
Whether the FETCH statement is FETCH SENSITIVE or FETCH INSENSITIVE
If the scrollable cursor creates the hole, the hole is
visible when you execute a FETCH statement for the row that contains
the hole. The FETCH statement can be FETCH INSENSITIVE or FETCH SENSITIVE.
If
an update or delete operation outside the scrollable cursor creates
the hole, the hole is visible at the following times:
If you execute a FETCH SENSITIVE statement for the row that contains
the hole, the hole is visible when you execute the FETCH statement.
If you execute a FETCH INSENSITIVE statement, the hole is not
visible when you execute the FETCH statement. Db2 returns the row as it was before the update
or delete operation occurred. However, if you follow the FETCH INSENSITIVE
statement with a positioned UPDATE or DELETE statement, the hole becomes
visible.
