Db2 for z/OS and its ecosystem

Starting in Db2 13 at function level 500, application developers and DBAs have new controls over lock contention situations, with the CURRENT LOCK TIMEOUT special register and DEADLOCK_RESOLUTION_PRIORITY built-in global variable.

Before the availability of these new controls, application developers and DBAs seldom had much control over resource contention with regard to DB2 locking when conflicting works are executed concurrently.

Most of the lock wait times, either from an application DML SQL request, a DDL process, a utility job, or a command, is controlled by the single subsystem parameter IRLMRWT value. This broad value might not fit a particular application design and characteristics, making it difficult to achieve a desired result. Similarly, when processes are involved in a deadlock cycle, there are not many external mechanisms for influencing which process should be denied to break the cycle. It is up to the internal logic of DB2 logic to choose the deadlock victim.

With the new application-level locking control in Db2 13, an application developer can express to Db2 the maximum waiting interval for its lock requests in case of contention or whether no waiting is needed via setting the CURRENT LOCK TIMEOUT special register to a value between -1 and 32,767 seconds. The SET CURRENT LOCK TIMEOUT SQL statement is introduced in Db2 13 for this purpose. A conditional lock or no waiting is indicated by a zero value or the NO WAIT keyword. An indefinite wait time is the -1 value or the WAIT keyword, and this option should be used carefully due to the process could have collected other resources and locks already and affect other processes. 

A DBA who issues DDL statements such as ALTER or DROP of an object can also set the CURRENT LOCK TIMEOUT special register to express their own desired value other than the default IRLMRWT value. The CURRENT LOCK TIMEOUT special register is also applicable to claims, drains and other waiting periods like locks such as when a DDL process needs to quiesce cached dynamic SQL statements. Conversely, this special register does not control the timeout interval for other locks such as the P-lock owned by the Db2 subsystem or the plan and package allocation locks.

The  CURRENT LOCK TIMEOUT special register support in Db2 13 for z/OS is compatible to the Db2 support on Linux, UNIX, and Windows, with an additional useful factor. Since a special register setting doesn’t require any permission or authorization, any application can specify a high value and inadvertently affects other applications. Therefore, Db2 13 for z/OS also introduces a new subsystem parameter named SPREG_LOCKTIMEOUT_MAX, and administrators can control the upper bound on the value that an application can specify for the CURRENT LOCK TIMEOUT special register on the SET statement. The trace record IFCID 437 can also be monitored to discover the specific applications and authorization IDs that use this special register.

Example 1:

SELECT C1 FROM T1 INTO :hv             ! Lock timeout interval = IRLMRWT

SET CURRENT LOCK TIMEOUT = 5

INSERT INTO T2 VALUES (‘12345’)        ! Lock timeout interval = 5 seconds

SELECT * FROM T1                       ! Lock timeout interval = 5 seconds

CALL SP1 (:hv)                         ! Lock timeout interval in SP1 is

                                       ! 5 seconds if SP1 has the

                                       ! INHERIT SPECIAL REGISTER option

Along the same theme, deadlock resolution control at the application level in Db2 13 is more possible with the system built-in global variable SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY. Prior to Db2 13, when resolving a deadlock cycle, Db2 uses a few criteria, to determine the relative importance of processes involved in the deadlock, such as whether a lock is in a critical recovery process, the thread references a NO LOG table, the number of update activities in the application so far, etc. There are certain subsystem parameters and an input parameter on the CREATE THREAD API to express a higher worth of an application process, but they are possible for certain IMS or RRSAF types of applications only.  With Db2 13, any application process can use the SET SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY to specify a value between 0 and 255. The higher the value, the less likely that locks requested by this application process are denied to resolve a deadlock cycle. Although this global variable is not a guarantee because other factors such as certain recovery processes always have higher precedence, it is a simple way to control many deadlock situations. Since this is a global variable, a WRITE privilege on this built-in global variable is required for the SET SQL statement to work.

Example 2:

SET CURRENT LOCK TIMEOUT = 10

SET SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY = 200

ALTER INDEX IX5 ADD COLUMN (C5)

With Db2 13, you can also avoid the costly application change process to add the SET SQL statements by taking advantage of the profile tables. Db2 13 supports the SET CURRENT LOCK TIMEOUT and SET SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY statements in the DSN_PROFILE_ATTRIBUTES table for both remote and local applications. This approach allows the DBA a way to enable a profile (via the -START PROFILE command) which sets and unsets these special register and global variable for qualified application packages when they are loaded for execution. The DBA can specify the qualified local applications based on the following attributes:

• Authorization ID
• Role
• Both authorization ID and role
• Package name
• Collection ID
• Both collection ID and package name
• Client user ID
• Client application name
• Client workstation name
• Combination of client user ID, client application name and client workstation name

When the SET statement is written in an application, its package needs to be bound with a required application compatibility (APPLCOMPAT) value. When the SET statement exists in the profile table, the Db2 current function level needs to be at a certain level for the rows to be accepted on the -START PROFILE command. The following table describes the required APPLCOMPAT or function level for the appropriate SET statements when specified in an application or the profile table:

For more information about using profile tables for these settings, see:

• Setting special registers by using profile tables
• Setting built-in global variables by using profile tables

Tammie Dang is a Senior Software Engineer in Db2 for z/OS development

#Db2forz/OS
#Db2Znews
#db2z13