In this section: |
While you are changing data in a table, the table is in an unstable state. The changes may eventually become permanent, or they may be backed out. In order for reports to contain meaningful results, users should not see changes until they are permanent.
To protect data integrity, the RDBMS must guarantee to the person updating a table that no other user will change the selected values before the update made by the first user is submitted.
The RDBMS provides a locking system for concurrency management. With native SQL, you can specify the length of time to hold a lock. This section discusses the duration of the lock, called the isolation level.
During installation, every DB2 application plan is bound with a default isolation level. The installation procedures for the adapter specify that the default isolation level is Cursor Stability. (Static MODIFY procedures are an exception. See Static SQL (DB2) for information about isolation levels in static procedures.)
With the Cursor Stability setting, shared (read) locks on a data row or page are released as your cursor moves off that location. For example, if a report reads many data pages, the shared lock acquired on each data page is released as the shared lock on the next data page is acquired.
Use Cursor Stability for read-only applications such as TABLE requests or read-only MODIFY or Maintain procedures that browse data without updating. You may not use Cursor Stability with a MODIFY or Maintain procedure that changes values in the database. Doing so would leave the data susceptible to change by other tasks in the interim between the initial selection (MATCH) and the update or inclusion.
The isolation level setting named Repeatable Read provides the highest level of protection. With Repeatable Read, any lock acquired is held until the transaction boundary (COMMIT WORK or ROLLBACK WORK). Therefore, any data the MODIFY or Maintain procedure displays on the screen remains unchanged until the procedure submits the update and executes a COMMIT WORK command.
This higher level of protection is provided at the expense of concurrency, since all locks, including shared locks, remain in effect until the end of a logical unit of work. Therefore, you should commit transactions on a regular basis throughout the MODIFY or Maintain procedure. Periodic transaction termination is especially important for NEXT processing. All rows retrieved by NEXT remain locked even if they are not all updated. These retrieved rows are not available for update by another user until your procedure releases them (using SQL COMMIT WORK or SQL ROLLBACK WORK in MODIFY, or using COMMIT or ROLLBACK in Maintain).
Because of these concurrency considerations, all read/write MODIFY and Maintain procedures require the Repeatable Read isolation level, including those MODIFY procedures that invoke the Change Verify Protocol described in Change Verify Protocol: AUTOCOMMIT ON CRTFORM.
In DB2, there are two additional isolation levels for read-only access. Uncommitted Read (UR) provides read-only access to records even if they are locked. However, these records may not yet be committed to the database. The other isolation level is called Read Stability (RS). For more information, see the DB2 Command and Utility Reference.
Note: The adapter AUTOCOMMIT ON CRTFORM feature is designed to eliminate some concurrency problems. (See Change Verify Protocol: AUTOCOMMIT ON CRTFORM for more information.) However, it also requires an isolation level of Repeatable Read.
The following sections describe how to change the isolation setting.
How to: |
The SET ISOLATION command allows you to dynamically change the isolation level for DB2.
For DB2 on z/OS, you can also switch to a plan with a different isolation level. See Changing the DB2 Isolation Level by Switching to Another Plan.
You can change the isolation level by issuing the SET ISOLATION command in a MODIFY procedure or at the FOCUS command level. (For Maintain, you must issue the SET ISOLATION command at the command level prior to invoking the Maintain procedure.) The setting remains in effect for the FOCUS session or until you reset it.
From the FOCUS command level, issue
{ENGINE|SQL} [DB2] SET ISOLATION level
where:
Indicates the isolation level. Valid values are:
CS Cursor Stability, the default. Releases shared locks as the cursor moves on in the table. Use for read-only requests.
RR Repeatable Read. Use for MODIFY and Maintain read/write routines. Locks the retrieved data until it is released by an SQL COMMIT WORK or SQL ROLLBACK WORK.
UR Uncommitted Read. Provides read-only access to records even if they are locked. However, these records may not yet be committed to the database. Use for read-only requests.
RS Read Stability. Use for read-only requests. For more information, see the DB2 Command and Utility Reference.
blank A blank value resets the level to the adapter default.
Note:
For more information about adapter commands, consult Adapter Commands.
How to: |
The Adapter for DB2 SET PLAN command allows you, within a FOCUS session, to switch to a plan bound with a different isolation level. The SET PLAN command is only available if the Adapter for DB2 was installed using the Call Attachment Facility (CAF).
The systems group responsible for adapter installation must generate two DB2 application plans for the adapter, one bound with the Cursor Stability isolation level and the other with Repeatable Read. You can then use the adapter SET PLAN command to switch between the plans for the desired isolation level. After you issue the SET PLAN command, all FOCUS TABLE, Maintain, or MODIFY procedures take advantage of the new isolation level.
DB2 has two additional isolation levels appropriate for read-only access. Uncommitted Read (UR) provides read-only access to records even if they are locked. However, these records may not yet be committed to the database. The other isolation level is Read Stability (RS). For more information, see the DB2 Command and Utility Reference.
Issue the SET PLAN command from the FOCUS command level
{ENGINE|SQL} [DB2] SET PLAN planname
where:
Is the name of your application plan. The default is DSQL unless your site changed the default at installation time.
Note:
How to: |
Use Cursor Stability or Transient Read for read-only applications such as TABLE requests or read-only MODIFY procedures that browse data without updating. You may not use Transient Read with a MODIFY procedure that changes values in the database. Doing so would leave the data susceptible to change by other tasks in the interim between the initial selection (MATCH) and the update or inclusion.
Cursor Stability (the adapter default) provides the highest level of protection. With Cursor Stability, any lock acquired is held until the transaction boundary (COMMIT WORK or ROLLBACK WORK) or until an updateable cursor is closed. Therefore, any data the MODIFY procedure displays on the screen remains unchanged until the procedure submits the update and executes a COMMIT WORK command.
This higher level of protection is provided at the expense of concurrency, since all locks, including shared locks, remain in effect until the end of a Logical Unit of Work. Therefore, you should COMMIT transactions on a regular basis throughout the MODIFY procedure. Periodic transaction termination is especially important for NEXT processing. All rows retrieved by NEXT remain locked even if they are not all updated, since the cursor used is a retrieval cursor. These retrieved rows are not available for update by another user until your procedure releases them (using SQL COMMIT WORK, SQL ROLLBACK WORK, or another IDMS SQL transaction or session ending command such as RELEASE in MODIFY).
Because of these concurrency considerations, all read/write MODIFY procedures require the Cursor Stability Isolation Level, including those MODIFY procedures that invoke the Change Verify Protocol described in Change Verify Protocol: AUTOCOMMIT ON CRTFORM.
Note: The adapter AUTOCOMMIT ON CRTFORM feature is designed to eliminate some concurrency problems. However, it also requires an Isolation Level of Cursor Stability.
The isolation level must be Cursor Stability for all read/write MODIFY procedures. You must issue the IDMS/SQL SET TRANSACTION command at the FOCUS command level prior to invoking the MODIFY procedure. The setting remains in effect for the FOCUS session or until you reset it.
From the FOCUS command level, issue
SQL [SQLIDMS] SET TRANSACTION level
where:
Can be one of the following:
CURSOR STABILITY is Cursor Stability, the default. Provides the maximum amount of concurrency while guaranteeing the integrity of the data selected.
TRANSIENT READ is Transient Read. Allows the reading of records locked by other users (allows dirty reads). Recommended for TABLE only. Transient Read prevents the SQL transaction from performing updates. Use this only when you do not need the data retrieved to be absolutely consistent and accurate. If you specify Transient Read, IDMS assumes Read Only.
READ ONLY allows data to be retrieved, but does not allow the database to be updated.
READ WRITE allows data to be retrieved, and allows the data source to be updated.
Note: Omit the SQLIDMS target RDBMS qualifier from the command when issuing it in a MODIFY procedure or if you previously issued the SET SQLENGINE command for SQLIDMS.
The method of concurrency control used by Oracle is implemented by locking the shared data. Locks may be of the following types:
| Information Builders |