The DB2 UDB for AS/400 commitment control support provides a means to process a group of database changes (updates, inserts, DDL operations, or deletes) as a single unit of work (transaction). A commit operation guarantees that the group of operations is completed. A rollback operation guarantees that the group of operations is backed out. A commit operation can be issued through several different interfaces. For example,
A rollback operation can be issued through several different interfaces. For example,
The only SQL statements that cannot be committed or rolled back are:
If commitment control was not already started when either an SQL statement is executed with an isolation level other than COMMIT(*NONE) or a RELEASE statement is executed, then DB2 UDB for AS/400 sets up the commitment control environment by implicitly calling the CL command Start Commitment Control (STRCMTCTL). DB2 UDB for AS/400 specifies NFYOBJ(*NONE) and CMTSCOPE(*ACTGRP) parameters along with LCKLVL on the STRCMTCTL command. The LCKLVL specified is the lock level on the COMMIT parameter on the CRTSQLxxx, STRSQL, or RUNSQLSTM commands. In REXX, the LCKLVL specified is the lock level on the SET OPTION statement. 8 You may use the STRCMTCTL command to specify a different CMTSCOPE, NFYOBJ, or LCKLVL. If you specify CMTSCOPE(*JOB) to start the job level commitment definition, DB2 UDB for AS/400 uses the job level commitment definition for programs in that activation group.
| Note: | When using commitment control, the tables referred to in the application program by Data Manipulation Language statements must be journaled. |
For cursors that use column functions, GROUP BY, or HAVING, and are running under commitment control, a ROLLBACK HOLD has no effect on the cursor's position. In addition, the following occurs under commitment control:
For cursors where either COMMIT(*ALL), COMMIT(*RR), or COMMIT(*CS) with the KEEP LOCKS clause is specified and either catalog files are used or a temporary result table is required, DB2 UDB for AS/400 will lock all referenced tables in shared mode (*SHRNUP). This will prevent concurrent processes from executing anything but read-only operations on the table(s). A message (either SQL7902 or CPI430A) is sent that says COMMIT(*ALL) is requested but not allowed. Message SQL0595 may also be sent.
If ALWBLK(*ALLREAD) and COMMIT(*CHG) were specified, when the program was precompiled, all read only cursors will allow blocking of rows and a ROLLBACK HOLD will not roll the cursor position back.
If COMMIT(*RR) is requested, the tables will be locked until the query is closed. If the cursor is read only, the table will be locked (*SHRNUP). If the cursor is in update mode, the table will be locked (*EXCLRD). Since other users will be locked out of the table, running with repeatable read will prevent concurrent access of the table.
If an isolation level other then COMMIT(*NONE) was specified and the application issues a ROLLBACK or the activation group ends normally (and the commitment definition is not *JOB), all updates, inserts, deletes, and DDL operations made within the unit of work are backed out. If the application issues a COMMIT or the activation group ends normally, all updates, inserts, deletes, and DDL operations made within the unit of work are committed.
DB2 UDB for AS/400 uses locks on rows to keep other jobs from accessing changed data before a unit of work completes. If COMMIT(*ALL) is specified, read locks on rows fetched are also used to prevent other jobs from changing data that was read before a unit of work completes. This will not prevent other jobs from reading the unchanged records. This ensures that, if the same unit of work rereads a record, it gets the same result. Read locks do not prevent other jobs from fetching the same rows.
Commitment control handles up to 4 million distinct row changes in a unit of work. If COMMIT(*ALL) or COMMIT(*RR) is specified, all rows read are also included in the limit. (If a row is changed or read more than once in a unit of work, it is only counted once toward the limit.) Holding a large number of locks adversely affects system performance and does not allow concurrent users to access rows locked in the unit of work until the end of the unit of work. It is in your best interest to keep the number of rows processed in a unit of work small.
Commitment control will allow up to 512 files for each journal to be open under commitment control or closed with pending changes in a unit of work.
COMMIT HOLD and ROLLBACK HOLD allows you to keep the cursor open and start
another unit of work without issuing an OPEN again. The HOLD value is
not available when you are connected to a remote database that is not on an
AS/400 system. However, the WITH HOLD option on DECLARE CURSOR may be
used to keep the cursor open after a COMMIT. This type of cursor is
supported when you are connected to a remote database that is not on an AS/400
system. Such a cursor is closed on a rollback.
Table 25. Record Lock Duration
| SQL Statement | COMMIT Parameter (See note 6) | Duration of Record Locks | Lock Type | ||
|---|---|---|---|---|---|
|
SELECT INTO SET variable VALUES INTO |
*NONE *CHG *CS (See note 8) *ALL (See note 2) |
No locks No locks Row locked when read and released From read until ROLLBACK or COMMIT |
READ READ | ||
| FETCH (read-only cursor) |
*NONE *CHG *CS (See note 8) *ALL (See note 2) |
No locks No locks From read until the next FETCH From read until ROLLBACK or COMMIT |
READ READ | ||
| FETCH (update or delete capable cursor) (See note 1) |
*NONE *CHG *CS *ALL |
When record not updated or deleted from read until next FETCH When record is updated or deleted from read until UPDATE or DELETE When record not updated or deleted from read until next FETCH When record is updated or deleted from read until COMMIT or ROLLBACK When record not updated or deleted from read until next FETCH When record is updated or deleted from read until COMMIT or ROLLBACK From read until ROLLBACK or COMMIT |
UPDATE UPDATE UPDATE UPDATE3 | ||
| INSERT (target table) |
*NONE *CHG *CS *ALL |
No locks From insert until ROLLBACK or COMMIT From insert until ROLLBACK or COMMIT From insert until ROLLBACK or COMMIT |
UPDATE UPDATE UPDATE4 | ||
| INSERT (tables in subselect) |
*NONE *CHG *CS *ALL |
No locks No locks Each record locked while being read From read until ROLLBACK or COMMIT |
READ READ | ||
| UPDATE (non-cursor) |
*NONE *CHG *CS *ALL |
Each record locked while being updated From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT |
UPDATE UPDATE UPDATE UPDATE | ||
| DELETE (non-cursor) |
*NONE *CHG *CS *ALL |
Each record locked while being deleted From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT |
UPDATE UPDATE UPDATE UPDATE | ||
| UPDATE (with cursor) |
*NONE *CHG *CS *ALL |
Lock released when record updated From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT |
UPDATE UPDATE UPDATE UPDATE | ||
| DELETE (with cursor) |
*NONE *CHG *CS *ALL |
Lock released when record deleted From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT |
UPDATE UPDATE UPDATE UPDATE | ||
| Subqueries (update or delete capable cursor or UPDATE or DELETE non-cursor) |
*NONE *CHG *CS *ALL (see note 2) |
From read until next FETCH From read until next FETCH From read until next FETCH From read until ROLLBACK or COMMIT |
READ READ READ READ | ||
| Subqueries (read-only cursor or SELECT INTO) |
*NONE *CHG *CS *ALL |
No locks No locks Each record locked while being read From read until ROLLBACK or COMMIT |
READ READ | ||
| |||||