In which of the following situations would DB2 retain resources associated with a transaction at
COMMIT time?
A.
A cursor is defined as WITH HOLD.
B.
Another user executes the same transaction.
C.
The application program amends during COMMIT.
D.
The transaction terminates abnormally during COMMIT.
WITH HOLD
Maintains resources across multiple units of work. The effect of the WITH HOLD cursor attribute is as follows:
For units of work ending with COMMIT:
Open cursors defined WITH HOLD remain open. The cursor is positioned before the next logical row of the results table.
If a DISCONNECT statement is issued after a COMMIT statement for a connection with WITH HOLD cursors, the held cursors must be explicitly closed or the connection will be assumed to have performed work (simply by having open WITH HELD cursors even though no SQL statements were issued) and the DISCONNECT statement will fail.
All locks are released, except locks protecting the current cursor position of open WITH HOLD cursors. The locks held include the locks on the table, and for parallel environments, the locks on rows where the cursors are currently positioned. Locks on packages and dynamic SQL sections (if any) are held.
Valid operations on cursors defined WITH HOLD immediately following a COMMIT request are:
FETCH: Fetches the next row of the cursor.
CLOSE: Closes the cursor.
UPDATE and DELETE CURRENT OF CURSOR are valid only for rows that are fetched within the same unit of work.
LOB locators are freed.
The set of rows modified by:
A data change statement
Routines that modify SQL data embedded within open WITH HOLD cursors
is committed.
For units of work ending with ROLLBACK:
All open cursors are closed.
All locks acquired during the unit of work are released.
LOB locators are freed.
For special COMMIT case:
Packages can be recreated either explicitly, by binding the package, or implicitly, because the package has been invalidated and then dynamically recreated the first time it is referenced. All held cursors are closed during package rebind. This might result in errors during subsequent execution.
0
0