DB2 troubleshooting for performance symptoms

Begin with an evidence-first diagnostic workflow

Before tuning, define exactly what changed. Record the first known bad time, the last known good time, affected applications, Db2 subsystem or data-sharing member, SQL identity, package and collection, connection type, and business volume. “The query is slow” is too vague. “This statement rose from 200 milliseconds to 8 seconds after 14:00, while input volume stayed stable” gives you a comparison that can be tested.

• Confirm the symptom from both the user's view and Db2's view. Separate network, application, queue, lock-wait, and database execution time.
• Capture a baseline from a comparable healthy interval. Match day, workload, input values, commit pattern, subsystem member, and data volume as closely as possible.
• Use Db2 accounting traces for thread or package execution, statistics traces for subsystem and buffer-pool trends, and performance traces only when the extra detail is justified. Relevant IFCIDs feed these records; your site monitor usually presents them without requiring beginners to decode raw trace records.
• Inspect current threads, DDF activity, utilities, locks, and object status with the appropriate Db2 displays or monitoring product. Save timestamps and complete messages so separate observations can be correlated.
• Form one hypothesis, make the smallest safe test, and check the same metrics again. Keep a rollback plan. Do not change several knobs and then guess which one helped.

1
2
3
4
5
6
7
8
Symptom -> scope -> baseline -> Db2 evidence -> hypothesis -> controlled test
 
For every incident, ask:
1. What became worse, and when?
2. Which SQL, object, thread, connection, or utility is involved?
3. What differs from the last healthy period?
4. Is time spent on CPU, I/O, locks, Db2 queues, or outside Db2?
5. Which single observation would prove or disprove the leading theory?

Query suddenly slow

A statement that was fast yesterday can be slow today even when its SQL text looks unchanged. The access path may have changed after RUNSTATS, REORG, index DDL, a bind, package invalidation, maintenance, or an application deployment. The path may be unchanged while input values now select far more rows. Contention, storage latency, CPU pressure, or a different member can also increase elapsed time without changing the optimizer's decision.

Start with accounting data for the affected execution. Compare elapsed time, general processor CPU, zIIP-eligible or consumed time where applicable, getpages, synchronous reads, prefetch activity, rows processed, sorts, lock/latch waits, and number of executions. Normalize totals per execution or per business transaction. A daily total can rise simply because the application did more work.

Compare old and new access paths with EXPLAIN

EXPLAIN records the optimizer's proposed access path in PLAN_TABLE and related tables. Use the same SQL, schema, special registers, parameter markers or representative literals, optimization environment, and catalog statistics when recreating a path. For static SQL, also inspect the package's captured access-path information and bind history available at your site. Do not assume a fresh ad hoc EXPLAIN exactly represents the package that ran during the incident.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Simplified dynamic EXPLAIN example.
-- Use the same qualifiers and relevant special-register context as the application.
EXPLAIN PLAN SET QUERYNO = 4101 FOR
SELECT O.ORDER_ID, O.ORDER_DATE, O.TOTAL_AMOUNT
  FROM APP.ORDERS O
 WHERE O.CUSTOMER_ID = ?
   AND O.ORDER_DATE >= ?
 ORDER BY O.ORDER_DATE DESC;
 
-- Read your site's PLAN_TABLE with the columns supported at its Db2 level.
SELECT QUERYNO, QBLOCKNO, PLANNO, METHOD, ACCESSTYPE,
       MATCHCOLS, ACCESSNAME, INDEXONLY, PREFETCH
  FROM PLAN_TABLE
 WHERE QUERYNO = 4101
 ORDER BY QBLOCKNO, PLANNO;

It used an index before, but now it scans

A table-space scan is not automatically wrong. If a query returns a large percentage of a table, sequential access with prefetch can cost less than jumping between an index and many data pages. The diagnostic question is not “How do I force the index?” but “What changed in Db2's cost estimate or in the available choices?”

• Check whether the index still exists, is available, has the same key order, and can support the predicates. A function or expression on an indexed column can make a predicate less usable unless a suitable expression-based index exists.
• Compare table, column, distribution, frequency, correlation, and index statistics. Stale, missing, default, or unrepresentative statistics can distort cardinality.
• Check predicate changes, implicit casts, data-type mismatches, parameter markers, host-variable behavior, and range size. Similar-looking SQL can estimate very different row counts.
• Review binds, APPLCOMPAT and optimizer-level context, package invalidation, access path reuse, and relevant subsystem changes. Record facts before considering a hint or plan-management action.

If the scan is genuinely harmful, correct the cause: collect suitable statistics, rewrite a non-indexable predicate, restore or improve an index, or safely rebind after testing. Forcing yesterday's path can preserve a plan that no longer fits today's data.

CPU and getpages increased

CPU measures processor work. Getpages measure logical requests for Db2 pages. They are related but not identical to physical I/O. A page already resident in a buffer pool can satisfy many getpages without a disk read. Compare getpages per execution, rows examined versus rows returned, synchronous reads, prefetch reads, and CPU per execution.

Rising getpages with stable result rows often points to less selective access, fewer matching index columns, loss of index-only access, a changed join order, repeated probes, or poor clustering. Rising CPU without a similar getpage increase may suggest more sorting, expression evaluation, data conversion, compression work, or application calls. Both may rise when a query scans and filters far more rows. Confirm with EXPLAIN and accounting evidence rather than assigning every CPU increase to one SQL statement.

Buffer-pool hit ratio dropped

Hit ratio is the share of logical page requests satisfied without a new read, but an aggregate percentage is not a verdict. A deliberate sequential scan can lower a pool ratio while finishing efficiently. A high ratio can coexist with poor performance if the application repeatedly touches too many cached pages. Always correlate the ratio with synchronous I/O, read latency, getpages, writes, threshold events, and response time.

Use Db2 statistics monitoring to find the affected buffer pool, then move toward object-level evidence. Ask whether a new scan displaced frequently reused pages, whether one object grew, whether pool assignment changed, whether page size or sequential thresholds fit the workload, and whether storage became slower. Resize or reassign a pool only after proving capacity or isolation is the constraint. Otherwise, SQL that reads fewer pages is often the stronger fix.

Sort explosion and work-file pressure

A sort explosion means more sorts, larger sorts, or more concurrent sort work. ORDER BY, GROUP BY, DISTINCT, merge scan joins, duplicate removal, and some subqueries can require sorting. A useful index may previously have supplied the desired order. A join order or cardinality change may now create a huge intermediate result before sorting.

Compare accounting sort counts and records processed, statistics for the work-file database, and EXPLAIN sort indicators. Check SQL changes, access-path changes, data growth, inaccurate statistics, and concurrency. Work-file space or sort-memory tuning may relieve a proven resource bottleneck, but it does not repair a query that sorts millions of unnecessary rows.

Lock contention, deadlocks, and timeouts

Lock contention begins when a thread needs an incompatible lock held by another unit of work. A timeout means the wait exceeded its allowed duration. A deadlock means a cycle exists—for example, thread A holds row 1 and wants row 2 while thread B holds row 2 and wants row 1. Waiting longer cannot solve that cycle, so IRLM chooses a victim.

Capture the waiter, holder, object, partition, lock mode, SQL, authorization or correlation identity, connection type, and unit-of-work age. Look for infrequent commits, user think time inside a transaction, large updates, inconsistent object access order, lock escalation, and queries reading more rows than expected. The durable fix is usually shorter transactions, consistent access order, selective SQL, and intentional isolation—not a blanket timeout increase.

Thread buildup and DDF connection buildup

Thread buildup is a queueing symptom. Local allied threads and distributed DBATs are different populations, so identify the attachment type first. Inspect thread states: are they executing, waiting on locks, waiting for Db2 resources, inactive, or stalled in long units of work? A surge can come from slower SQL, a blocked transaction, application retry storms, a downstream outage, or traffic growth. Raising CTHREAD or MAXDBAT without finding the bottleneck may admit more work and make CPU, storage, locks, or another service collapse faster.

DDF also distinguishes network connections from active DBATs. Connection pooling and high-performance DBAT behavior can make counts differ from a simple one-client, one-thread model. Use DDF displays and your monitor to compare remote location, authorization ID, application name, connection count, active DBAT count, queued work, idle age, and transaction state. Coordinate with application owners to check pool maximums, leaked connections, retry policy, commit behavior, and whether requests are actually completing.

CHECK INDEX failure

CHECK INDEX verifies consistency between index entries and table data. A nonzero result is not merely a performance statistic. Preserve the complete utility output, return code, messages, object identity, scope, and resulting restrictive state. Check preceding hardware, I/O, recovery, LOAD, REORG, or utility events. The corrective path depends on exactly what Db2 found and on the installed release.

Do not jump directly to REPAIR. First protect recoverability, understand whether the data or index is authoritative, and follow documented procedures. A rebuild may be appropriate for an index inconsistency, but the DBA must account for availability, image copies, related objects, pending states, and the original cause.

REBUILD INDEX failure

Read utility output from the first relevant error, not only the final failure code. Determine the phase and whether the problem is an unavailable input object, insufficient sort or work space, full target data set, authorization issue, I/O failure, duplicate key, damaged data, concurrent utility, or drain problem. Confirm the index and table space states before retrying.

Correct the named cause and use the documented restart or rerun behavior. Repeatedly submitting identical JCL can consume the window while preserving the same failure. After success, verify object state and consistency, and capture why the failure occurred so space estimates, utility controls, or scheduling can be corrected.

Utility drain timeout

A utility requests a drain when it needs existing claims to leave and may need to prevent new incompatible claims. A drain timeout means an application claim remained too long. Find the claim holder, application owner, SQL, transaction age, and whether work is active or idle. A long cursor, uncommitted batch update, forgotten connection, or continuous application traffic can all block the utility.

Coordinate before canceling production work. The safest action may be a normal commit, ending an idle session, pausing traffic, or rescheduling the utility. Increasing UTIMOUT only allows a longer wait; it does not make the claim disappear. Also inspect retry behavior and the utility's options so repeated drain attempts do not create an avoidable application impact.

Explain It Like I'm Five

Imagine Db2 is a supermarket. A slow query is a shopper taking too long, but you should not immediately build a new checkout lane. First watch what happened. Did the shopper lose the aisle map and walk past every shelf? That is like losing an index and scanning. Did too many shoppers reach for the same freezer door? That is lock contention. Did carts fill every aisle? That resembles thread buildup. Did the cleaning crew wait because one shopper would not leave? That is a utility drain wait. EXPLAIN is the planned route through the store, while accounting and statistics show what the trip actually cost. Good troubleshooting compares the old trip with the new one before moving shelves.

Exercises

• Create a baseline worksheet for a query regression. Include elapsed time, CPU, getpages, reads, rows returned, sorts, lock waits, package, member, and input values.
• Explain three valid reasons why Db2 might prefer a scan over an index, then list the EXPLAIN and RUNSTATS evidence you would use to test each reason.
• A statement's getpages doubled but physical reads stayed flat. Describe what this proves, what it does not prove, and which metric you would inspect next.
• Draw a two-thread deadlock. Label the held and requested locks, then propose an application change that prevents the cycle.
• A utility timed out waiting for a drain. Write a safe five-step response that identifies the claim holder before any cancellation decision.
• Compare CHECK INDEX and REBUILD INDEX. Explain what each utility does and why their failure messages must be preserved before choosing corrective action.