Concurrency and compatibility of utilities

Two utilities are considered compatible if they do not need access to the same object at the same time in incompatible modes. The concurrent operation of two utilities is not typically controlled by either drain locks or transaction locks, but merely by a set of compatibility rules.

Begin program-specific programming interface information.
Before a utility starts, it is checked against all other utilities running on the same target object. The utility starts only if all the others are compatible.

The check for compatibility obeys the following rules:

  • The check is made for each target object, but only for target objects. Typical utilities access one or more table spaces or indexes, but if two utility jobs use none of the same target objects, the jobs are always compatible.

    An exception is a case in which one utility must update a catalog or directory table space that is not the direct target of the utility. For example, the LOAD utility on a user table space updates DSNDB06.SYSCOPY. Therefore, other utilities that have DSNDB06.SYSCOPY as a target might not be compatible.

  • Individual data and index partitions are treated as distinct target objects. Utilities operating on different partitions in the same table or index space are compatible.
  • When two utilities access the same target object, their most restrictive access modes determine whether they are compatible. For example, if utility job 1 reads a table space during one phase and writes during the next, it is considered a writer. It cannot start concurrently with utility 2, which allows only readers on the table space. (Without this restriction, utility 1 might start and run concurrently with utility 2 for one phase; but then it would fail in the second phase, because it could not become a writer concurrently with utility 2.)

The following figure illustrates how SQL applications and DB2® utilities can operate concurrently on separate partitions of the same table space.

Figure 1. SQL and utility concurrency
. Two LOAD jobs execute concurrently on two partitions of a table space
Begin figure description. This figure is described in the surrounding text. End figure description.
Time
Event
t1
An SQL application obtains a transaction lock on every partition in the table space. The duration of the locks extends until the table space is deallocated.
t2
The SQL application makes a write claim on data partition 1 and index partition 1.
t3
The LOAD jobs begin draining all claim classes on data partitions 1 and 2 and index partitions 1 and 2. LOAD on partition 2 operates concurrently with the SQL application on partition 1. LOAD on partition 1 waits.
t4
The SQL application commits, releasing its write claims on partition 1. LOAD on partition 1 can begin.
t6
LOAD on partition 2 completes.
t7
LOAD on partition 1 completes, releasing its drain locks. The SQL application (if it has not timed out) makes another write claim on data partition 1.
t10
The SQL application deallocates the table space and releases its transaction locks.
End program-specific programming interface information.