DB2 SQL, lock, timeout, and performance parameters

How to read subsystem parameters safely

A subsystem parameter is a default or boundary, not a complete performance plan. Object DDL, bind options, special registers, workload manager policy, available processors, and the access path can all narrow what happens. Some values can be changed online; others require a Db2 or IRLM restart. Names, ranges, defaults, and installation-panel locations can also change by Db2 function level or release. Therefore, read the active settings, consult the manual for that exact release, and test a change rather than copying a value from another subsystem.

1
2
3
4
5
6
7
8
9
A useful troubleshooting chain
------------------------------
Symptom
  -> identify the waiting or failing resource
  -> find the controlling object option / special register
  -> find the subsystem ceiling or default
  -> measure workload and IRLM impact
  -> test one controlled change
  -> verify messages, accounting, statistics, and EXPLAIN

IRLM resource and deadlock parameters

The Internal Resource Lock Manager (IRLM) coordinates Db2 locks. Two clocks are easy to confuse. A deadlock detection interval determines how often IRLM searches for a cycle such as thread A waiting for B while B waits for A. No amount of waiting can solve that circle, so IRLM selects a victim. IRLMRWT, whose common default is 30 seconds, limits an ordinary resource wait. That wait might have no cycle at all: one long transaction may simply hold a lock needed by another.

The local deadlock-cycle value is configured for IRLM, traditionally through installation panel DSNTIPJ and the generated startup configuration. Keep deadlock detection substantially shorter than the resource timeout so true cycles are broken promptly rather than surviving until IRLMRWT. A shorter cycle can detect problems faster but performs detection more often; an excessively long cycle makes deadlocked applications appear frozen.

PC, MAXCSA, and SCOPE awareness

IRLM also has systems-level startup values. PC=YES historically selected cross-address-space program-call operation, and MAXCSAhistorically limited common storage. Modern IRLM keeps PC and MAXCSA for compatibility but no longer uses their values; applicable procedures still specify PC=YES and MAXCSA=0. SCOPE remains meaningful: LOCAL is for a non-data-sharing IRLM, while GLOBAL is required for an IRLM participating in a data sharing group. These are systems-programmer settings, not application tuning knobs. Lock capacity now depends on IRLM private storage and MEMLIMIT-related planning.

Security and authorization parameters

Security parameters decide who performs authorization processing, which privileged identities exist, and how often Db2 can reuse authorization results. They deserve change-control treatment because a performance-looking cache adjustment can alter when changed privileges become visible, while an exit-module mistake can interrupt attachments.

SECADM1 and SECADM2 establish the two installation security-administrator identities. Their companion type choices distinguish the identity form as AUTHID or ROLE. Separate named administrators support accountability and recovery if one identity is unavailable. Do not use these values as general application IDs.

AUTH is the use-protection switch: YES tells Db2 to perform authorization checking. AUTHEXIT_CHECK chooses PRIMARY or DB2 identity behavior for the documented plan/package authorization checks.IDAUTH_MODULE names the default connection authorization exit module.AUTH_COMPATIBILITY preserves selected compatibility behavior for documented authorization checks. Its allowed keywords have evolved, so this is an awareness item: inventory why a compatibility option exists before removing it, but do not retain weaker legacy behavior without a business requirement.AUTHCACH controls the plan authorization cache. The old CACHEPAC and CACHERAC size controls were removed from maintained Db2 12 by APAR PH28280, so Db2 13 manages those package and routine authorization caches internally.

Thread capacity and idle-thread control

Db2 separates local and distributed execution. CTHREAD limits allied threads created by local attachments such as batch, CICS, IMS, TSO, CAF, and RRSAF.MAXDBAT limits database access threads for DDF clients. Although the controls are separate, their sum must remain within the supported combined limit, approximately 20,000 on current releases. Verify the exact maximum for your installed Db2 level.

Raising a ceiling does not create capacity. More simultaneous threads can consume more virtual storage, locks, EDM resources, buffer-pool pages, CPU, and downstream connections. A MAXDBAT shortage can queue distributed work, while CTHREAD pressure can block local attachment demand. Diagnose which population is constrained before changing either number.

IDTHTOIN handles an allocated, eligible thread that is doing no work. It protects Db2 from clients that disappear or pause while retaining resources. It is not IRLMRWT, because there may be no lock wait; it is not UTIMOUT, because no utility is draining an object; and it is not the timer used to trim an unused pooled DBAT.

Utility wait and resource parameters

Online utilities cooperate with applications through claims, drains, and object states. UTIMOUT limits how long a utility waits for the resource or drain it needs. A low value makes a utility fail quickly during contention. A very high value can leave an operations schedule stalled behind one long transaction. Fixing the transaction pattern is often better than repeatedly increasing UTIMOUT.

UTILS_BLOCK_FOR_CDC is important where change data capture or replication depends on utility behavior. Treat it as a cross-team setting: a utility window that looks faster can disrupt a downstream capture contract.PARAMDEG_UTIL and related sort or parallel controls influence eligible utility work. More parallelism may reduce elapsed time, but concurrent sort tasks need memory, work data sets, CPU, and I/O bandwidth. Utility messages and reports show what Db2 actually used; the parameter is a limit, not proof of the achieved degree.

Optimizer and performance options

Optimizer parameters usually enable a possibility or provide a compatibility boundary. They do not repair stale RUNSTATS, poor indexing, non-stage-1 predicates, or an undersized buffer pool. For example, enabling STAR JOIN lets Db2 consider access paths designed for a large fact table joined to smaller dimensions. Db2 still rejects that choice when statistics and cost estimates favor a different plan.

OPTIMIZATION options are a family of release-dependent optimizer controls rather than permission to toggle every new feature at once. Compare PLAN_TABLE output, estimated costs, join sequence, matching columns, parallel groups, and runtime accounting before and after a change. Historically,CACHEPAC and CACHERAC sized caches for package and routine authorization decisions. They were removed as ZPARMs by Db2 12 maintenance; current Db2 manages the storage internally. The names remain useful when reading old subsystem listings and tuning advice.

RELCURHL=YES, the current default, releases at COMMIT the page or row lock on which a WITH HOLD cursor is positioned; the cursor position itself remains. NO retains the lock for legacy compatibility and can reduce concurrency.NPGTHRSH tells the optimizer to favor the index with the most matching columns when a table or partition has fewer NPAGES than the threshold. The current best-practice default of 1 mainly protects empty or default-statistics cases. Large thresholds can force matching-index access when a scan would cost less, so tune only with RUNSTATS and EXPLAIN evidence.

Parallelism degree controls

Query parallelism divides eligible work among multiple execution units. It can reduce one query's elapsed time while consuming resources faster, so Db2 layers several controls. PARAMDEG is the subsystem ceiling. TheCURRENT DEGREE special register influences dynamic SQL, and the package DEGREE bind option applies to eligible static SQL.CDSSRDEF supplies default CURRENT DEGREE behavior for dynamic SQL applications that do not explicitly establish it.

1
2
3
4
5
6
7
8
9
10
-- Conceptual dynamic SQL session controls
SET CURRENT DEGREE = 'ANY';
 
SELECT REGION, SUM(SALES_AMOUNT)
FROM SALES_FACT
GROUP BY REGION;
 
-- Requesting ANY permits consideration of parallelism.
-- PARAMDEG and runtime conditions still limit the actual degree.
SET CURRENT DEGREE = '1';

Parallelism can also be reduced by the access path, storage constraints, workload manager goals, processor availability, or a small amount of qualifying work. Read EXPLAIN and runtime monitors rather than assuming PARAMDEG=8 means eight-way execution for every statement. Utility parallelism has separate considerations and is not governed solely by query PARAMDEG.

NUMLKUS, NUMLKTS, and LOCKMAX

Lock limits protect two different things. NUMLKUS protects the subsystem from one thread accumulating too many locks. When the thread reaches that maximum, it fails. NUMLKTS is an escalation threshold: Db2 may trade many row or page locks on a table space for a coarse lock. That reduces lock storage but can block more users.

The table-space LOCKMAX option determines whether NUMLKTS governs that object. LOCKMAX SYSTEM uses NUMLKTS; a numeric LOCKMAX supplies an object-specific threshold; LOCKMAX 0 disables escalation for that object. Disabling escalation does not grant unlimited locks, because NUMLKUS and available IRLM storage still apply. Before increasing thresholds, ask why a unit of work touches so many rows. Smaller commit intervals may improve concurrency and recovery while reducing lock demand.

1
2
3
4
5
6
7
8
9
10
-- Conceptual object-level choices
ALTER TABLESPACE APPDB.ORDERTS LOCKMAX SYSTEM;
-- Uses the subsystem NUMLKTS escalation threshold.
 
ALTER TABLESPACE APPDB.ORDERTS LOCKMAX 5000;
-- Uses an object-specific threshold.
 
ALTER TABLESPACE APPDB.ORDERTS LOCKMAX 0;
-- Disables escalation for this table space,
-- but NUMLKUS and IRLM capacity still protect Db2.

Four timeout controls that solve different problems

Db2 13 adds awareness of the CURRENT LOCK TIMEOUT special register, which lets supported applications express a lock-wait policy more precisely than one subsystem-wide resource timeout. That is useful when an interactive request should fail quickly but a controlled batch transaction can wait longer. Validate supported values and driver behavior at the active function level. It does not control a utility drain or disconnect an idle thread.

1
2
3
4
5
6
7
8
9
10
11
-- Db2 13 conceptual workload-level lock-wait policy
SET CURRENT LOCK TIMEOUT = 5;
 
UPDATE CUSTOMER
   SET STATUS = 'REVIEW'
 WHERE CUSTOMER_ID = 1042;
 
COMMIT;
 
-- The special register governs this application's lock-wait policy
-- where supported; IRLM and subsystem safeguards still exist.

Timeout failures are symptoms, not random events. Record the waiter, holder, object, lock mode, unit-of-work age, and SQL. Increasing IRLMRWT can hide a slow commit pattern and make response time worse. Decreasing it can cause needless retries during healthy short bursts. Likewise, increasing UTIMOUT does not make an undrainable object drain, and lowering IDTHTOIN can terminate legitimate conversational work.

A practical tuning workflow

1. Capture active subsystem and IRLM values, including whether a value is dynamic or restart-only.
2. Classify the symptom: deadlock, ordinary lock timeout, utility drain wait, idle thread, thread-capacity queue, escalation, or slow access path.
3. Identify the narrowest control first: SQL design, commit frequency, table-space LOCKMAX, package DEGREE, or application special register.
4. Estimate side effects on IRLM storage, CPU, I/O, thread storage, authorization refresh, and competing workloads.
5. Test under representative concurrency, then compare statistics, accounting, utility reports, messages, EXPLAIN output, and business response time.
6. Document the reason, owner, expected signal, rollback value, and review date.

Explain It Like I'm Five

Imagine Db2 is a busy library. IRLM is the librarian who controls keys to locked rooms. The deadlock checker notices when two children each hold one toy and wait for the other; IRLMRWT is how long any child may wait at a locked door. CTHREAD and MAXDBAT say how many local and telephone visitors can have helpers. NUMLKUS stops one visitor from carrying every key, while NUMLKTS lets the librarian replace a huge ring of tiny keys with one big room key. PARAMDEG says how many helpers one research job may use. UTIMOUT tells a cleaning crew how long to wait for everyone to leave, and IDTHTOIN sends home a visitor who has a helper but is doing nothing. Every limit keeps one person from consuming the whole library.

Exercises

1. Draw a two-thread deadlock and explain why a deadlock cycle should usually be detected before IRLMRWT expires.
2. A subsystem has CTHREAD 4,000 and MAXDBAT 14,000. Calculate the combined setting and list three resources to validate before increasing either value.
3. Compare NUMLKUS, NUMLKTS, LOCKMAX SYSTEM, and LOCKMAX 0 in four sentences.
4. Choose the right timer for each symptom: utility cannot drain, JDBC request waits on a row, and an allocated thread has been idle.
5. Explain why PARAMDEG 8 does not guarantee eight-way parallelism and name two other controls that influence query parallelism.
6. Draft a change plan for AUTHCACH or CACHEPAC that includes security validation, storage measurement, rollback, and privilege-change testing.