How Db2 allocates threads

1. Thread creation

The following list shows the main steps in thread creation.

1. Db2 checks whether the maximum number of active threads has been exceeded. The thresholds are specified by the following subsystem parameters:
   • CTHREAD for allied threads. See MAX USERS field (CTHREAD subsystem parameter).
   • MAXDBAT for database access threads. See MAX REMOTE ACTIVE field (MAXDBAT subsystem parameter).

   If the applicable limit has been exceeded, the request waits. The wait for threads is not traced, but the number of requests queued is provided in the performance trace record with IFCID 73.

2. Db2 checks the authorization ID for an application plan in the SYSIBM.SYSPLANAUTH catalog table (IFCID 15). If this check fails, the table SYSUSERAUTH is checked for the SYSADM special privilege.

3. For application plans, Db2 loads the control structures associated with the plan. The control block for an application plan is divided into sections. The header and directory contain control information; SQL sections contain SQL statements from the application. A copy of the control structure for the plan is made for each thread executing the plan. Only the header and directory are loaded when the thread is created.
4. Db2 loads the descriptors necessary to process the plan. Some of the control structures describe the Db2 table spaces, tables, and indexes used by the application.

2. Resource allocation

Some of the structures necessary to process the statement are stored in 4 KB pages. If they are not already present, those structures are read into database buffer pool BP0 and copied from there into the EDM pool.

To load the control structures necessary to process the SQL section, Db2 uses the following process:

1. If it is not already in the EDM pool, Db2 loads the section of the control structure that corresponds to the SQL statement.
2. Db2 loads structures that are referred to by the SQL statement that are not already in the EDM pool.
3. Allocate and open data sets. When the control structure is loaded, Db2 locks the resources used.

3. SQL statement execution

If the statement resides in a package, the directory and header of the control structure for the package are loaded at the time of the first execution of a statement in the package.

The control structure for the package is allocated at statement execution time. The header of the control structure for the plan is allocated at thread creation time.

When the package is allocated, Db2 uses the package authorization cache or the SYSPACKAUTH catalog table checks authorization . Db2 checks to see that the plan owner has execute authority on the package. On the first execution, the information is not in the cache; therefore, the catalog is used. After the first execution, the cache is used.

For dynamic bind, authorization checking also occurs at statement execution time.

A summary record, produced at the end of the statement (IFCID 58), contains information about each scan that is performed.

From a system performance perspective, the most important factor in the performance of SQL statement execution is the size of the database buffer pool. If the buffer pool is large enough, some index and data pages can remain there and can be accessed again without an additional I/O operation.

4. Commit and thread termination

Commit processing can occur many times while a thread is active. For example, an application program running under the control structure of the thread could issue an explicit COMMIT or SYNCPOINT several times during its execution. When the application program or the thread terminates, an implicit COMMIT or SYNCPOINT is issued.

When a COMMIT or SYNCPOINT is issued from an IMS application running with Db2, the two-phase commit process begins if Db2 resources have been changed since the last commit point. In a CICS® or RRSAF application, the two-phase commit process begins only if Db2 resources have changed and a resource outside of Db2 has changed within the same commit scope.

The significant events that show up in a performance trace of a commit and thread termination operation occur in the following sequence:

1. In commit phase 1 (IFCID 84), Db2 writes an end of phase 1 record to the log (IFCIDs 0032 and 0033). The trace shows two I/O operations, one to each active log data set (IFCIDs 0038 and 0039).
2. In commit phase 2 (IFCID 70), Db2 writes a beginning of phase 2 record to the log. Again, the trace shows two I/O operations. Page and row locks , held to a commit point, are released. An unlock (IFCID 21) with a requested token of zeros frees any lock for the specified duration. A summary lock record (IFCID 20) is produced, which gives the maximum number of page locks held and the number of lock escalations. Db2 writes an end of phase 2 record to the log.
   If RELEASE(COMMIT) is used, the following events also occur:

   • Table space locks are released.
   • All the storage used by the thread is freed, including storage for skeleton cursor tables (SKCT), skeleton package tables (SKPT), and working areas.
   • The use counts of the DBDs are decreased by one. If space is needed in the EDM DBD cache, a DBD can be freed when its use count reaches zero.
   • Those table spaces and index spaces with no claimers are made candidates for deferred close.
3. The thread is terminated, and the accounting record is written. The accounting record does not report transaction activity that takes place before the thread is created.

   If RELEASE(DEALLOCATE) is used to release table space locks, the DBD use count is decreased, and the thread storage is released.