DB2 Other Performance Anti-Patterns

How to investigate an anti-pattern

An anti-pattern is a warning, not proof of a defect. Db2 is a cost-based optimizer. A table space scan can beat an index when most rows qualify. Dynamic SQL can be the correct choice for optional search criteria. A rebind can adopt a better plan after statistics improve. Start with the business requirement, then collect EXPLAIN and runtime evidence. Useful measurements include statement frequency, rows returned, rows examined, getpages, synchronous reads, prefetch activity, CPU time, elapsed time, lock waits, sort work, and bytes returned to a distributed client.

Leading-wildcard LIKE searches

A normal index on LAST_NAME is ordered from the first character onward. The predicate LAST_NAME LIKE 'SMI%' provides a known prefix, so Db2 can potentially search a bounded part of the index. The predicate LAST_NAME LIKE '%SMI%' does not reveal where a matching key begins. Values containing SMI can appear throughout the index, so an ordinary matching index probe is usually unavailable. Db2 might scan the index, scan the table space, or use another selective predicate. “The index was used” is not enough; an index scan with no matching columns can still examine a large key range.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Difficult to delimit with an ordinary index on CUSTOMER_NAME
SELECT CUSTOMER_ID, CUSTOMER_NAME
  FROM CUSTOMER
 WHERE CUSTOMER_NAME LIKE '%SON';
 
-- Prefix search can provide an indexable range
SELECT CUSTOMER_ID, CUSTOMER_NAME
  FROM CUSTOMER
 WHERE CUSTOMER_NAME LIKE 'JOHN%';
 
-- Best when the requirement is an exact normalized key
SELECT CUSTOMER_ID, CUSTOMER_NAME
  FROM CUSTOMER
 WHERE CUSTOMER_SEARCH_NAME = ?;

Do not remove the first percent sign if that changes the requested result. Ask what the user actually needs. An exact lookup, a prefix search, a governed search column, or a specialized text-search capability may fit better than unrestricted contains-search. Also escape percent and underscore when they are literal data. Run EXPLAIN with representative values and read predicate information, ACCESSTYPE, ACCESSNAME, and MATCHCOLS before deciding that an index solved the problem.

Missing indexes and indexes in the wrong order

A missing index is not merely “a table with no index.” A table can have several indexes and still lack one whose leading keys match an important access pattern. Suppose an order inquiry uses equality on CUSTOMER_ID, a range on ORDER_TIMESTAMP, and descending display order. An index beginning with an unrelated status column may provide little matching value. A candidate beginning with CUSTOMER_ID and ORDER_TIMESTAMP is more aligned, subject to selectivity, data distribution, projection, and maintenance cost.

1
2
3
4
5
6
7
8
9
10
11
SELECT ORDER_ID, ORDER_TIMESTAMP, TOTAL_AMOUNT
  FROM SALES_ORDER
 WHERE CUSTOMER_ID = ?
   AND ORDER_TIMESTAMP >= ?
   AND ORDER_TIMESTAMP < ?
 ORDER BY ORDER_TIMESTAMP DESC
 FETCH FIRST 50 ROWS ONLY;
 
-- Candidate shape to evaluate, not a universal prescription:
-- (CUSTOMER_ID, ORDER_TIMESTAMP DESC, ORDER_ID)
-- Use EXPLAIN and workload measurements before creating it.

Every index has a price. INSERT, DELETE, and key-changing UPDATE operations maintain it. The index consumes index-space and buffer-pool storage, adds logging, and increases COPY, RECOVER, REORG, and RUNSTATS work. Do not create one index per slow query without considering overlap. Inventory existing keys, uniqueness, INCLUDE columns where supported and appropriate, partitioning, and index-only opportunities. Then compare the read benefit with the write and operational cost across the whole workload.

The wrong clustering index

A Db2 table can have one clustering index. Its key defines the preferred physical row sequence for clustering, and REORG can restore data toward that sequence. Good clustering lets related rows occupy nearby pages, which can improve range access, sequential prefetch, and ordered retrieval. A clustering choice based on an old report can hurt the current dominant workload. For example, clustering orders by STATUS may scatter one customer's recent order history across many pages, while clustering by CUSTOMER_ID and time could keep that access pattern together.

Declaring a clustering index does not continuously move every existing row into perfect order. Inserts, updates, page splits, free space, and growth can reduce clustering over time. Catalog statistics such as CLUSTERRATIOF help describe the current relationship, while real-time statistics and utility history help determine whether REORG is justified. Changing the clustering index alone is therefore incomplete: model all range workloads, evaluate insert hot spots and partitioning, define free-space policy, collect statistics, and plan the required reorganization.

Lock escalation and long-running transactions

Db2 and IRLM normally protect concurrent work with locks at documented resource levels. When one unit of work accumulates many page or row locks on a table space, lock escalation can replace those fine-grained locks with a coarser lock. This reduces lock storage but expands the protected resource, so unrelated transactions can become blocked. At the object level, LOCKMAX SYSTEM uses the subsystem NUMLKTS escalation threshold; a numeric LOCKMAX establishes an object-specific threshold; LOCKMAX 0 disables escalation for that table space. Disabling escalation does not remove the per-user NUMLKUS limit or IRLM capacity constraints.

Long-running transactions make the problem worse because locks survive until their documented duration ends, often COMMIT or ROLLBACK. A batch program that updates a million rows in one unit of work can retain resources, block online users, delay a utility drain, generate a large rollback obligation, and hold claims for a long time. “Commit every row” is not the answer either: excessive commits add overhead and can complicate restart. Choose a restartable commit interval based on business consistency, concurrency, log and recovery behavior, and measured cost.

1
2
3
4
5
6
7
8
9
10
When lock contention appears, capture:
1. Waiting and holding thread or correlation information
2. Locked object and lock mode
3. Age and size of the holding unit of work
4. SQL and number of rows touched
5. Object LOCKMAX and subsystem NUMLKTS / NUMLKUS context
6. Commit frequency, retry behavior, and utility drain impact
 
Raising a timeout only makes the waiter wait longer.
It does not shorten the transaction that owns the lock.

Cartesian products and incomplete join predicates

A Cartesian product combines every qualifying row from one input with every qualifying row from another. CROSS JOIN is valid when that result is intentional, such as creating a small matrix of dates and categories. The anti-pattern is an accidental product caused by a missing join condition or an incomplete composite relationship. Joining 10,000 orders to 5,000 customers without a customer-key predicate creates up to 50 million intermediate combinations before later filtering or aggregation.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Anti-pattern: no relationship between O and C
SELECT O.ORDER_ID, C.CUSTOMER_NAME
  FROM SALES_ORDER AS O,
       CUSTOMER AS C
 WHERE O.ORDER_STATUS = 'OPEN';
 
-- Intentional relationship
SELECT O.ORDER_ID, C.CUSTOMER_NAME
  FROM SALES_ORDER AS O
  JOIN CUSTOMER AS C
    ON C.CUSTOMER_ID = O.CUSTOMER_ID
 WHERE O.ORDER_STATUS = 'OPEN';
 
-- A composite business key must be complete when both parts define identity
-- ON D.ACCOUNT_ID = H.ACCOUNT_ID
-- AND D.BUSINESS_DATE = H.BUSINESS_DATE

Review join predicates separately from filters. Modern explicit JOIN syntax makes the relationship easier to see, but syntax alone cannot prove correctness. Compare expected cardinality with actual row counts, check join sequence and METHOD in EXPLAIN, and test duplicate business keys. DISTINCT is not a proper repair for an accidental product; it can add a large sort while concealing the missing relationship.

Excessive table space scans

A table space scan is an access path, not an error. Db2 can combine scanning with sequential prefetch and process many pages efficiently. If a monthly report needs most rows, random index probes could cost more. A scan becomes an anti-pattern when a high-frequency transaction needs a few rows but repeatedly reads a large object, when a join scans the same inner table many times, or when stale and missing statistics lead the optimizer to estimate selectivity poorly.

• Check whether predicates are indexable, matching, index-screening, stage 1, or stage 2 rather than judging SQL by appearance.
• Collect representative RUNSTATS, including distribution or column-group statistics when skew and correlation matter.
• Compare statement frequency, pages read, rows examined, rows returned, prefetch, CPU, and elapsed time. A cheap scan executed millions of times can become expensive.
• Consider partition pruning, index-only access, clustering, and query rewrite, but do not force index access merely to eliminate the word “scan.”

Bad pagination and inefficient OFFSET

Pagination needs deterministic ordering

FETCH FIRST without a business ORDER BY returns an arbitrary eligible subset. Even an order such as ORDER BY ORDER_TIMESTAMP can be unstable when several rows have the same timestamp. Add a unique tie-breaker, commonly the primary key, so every row has a fixed position. The supporting index should be evaluated against filters and that ordering. Stable ordering is a correctness requirement before it is a performance optimization.

Why deep OFFSET gets slower

OFFSET is convenient for page numbers, but Db2 still has to locate and skip the earlier rows. Page 2 may discard 25 rows; page 20,000 may discard nearly half a million. Repeating deep requests performs that prefix work again. Concurrent inserts and deletes can also shift positions between requests, producing duplicates or omissions from a user's browsing session.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Simple, but cost grows with the skipped prefix
SELECT ORDER_ID, ORDER_TIMESTAMP, TOTAL_AMOUNT
  FROM SALES_ORDER
 WHERE CUSTOMER_ID = ?
 ORDER BY ORDER_TIMESTAMP DESC, ORDER_ID DESC
 OFFSET 500000 ROWS
 FETCH NEXT 25 ROWS ONLY;
 
-- Keyset pagination: values come from the final row of the previous page
SELECT ORDER_ID, ORDER_TIMESTAMP, TOTAL_AMOUNT
  FROM SALES_ORDER
 WHERE CUSTOMER_ID = ?
   AND (ORDER_TIMESTAMP < ?
        OR (ORDER_TIMESTAMP = ? AND ORDER_ID < ?))
 ORDER BY ORDER_TIMESTAMP DESC, ORDER_ID DESC
 FETCH FIRST 25 ROWS ONLY;

Keyset pagination is excellent for next and previous browsing because it resumes at an ordering boundary. It does not naturally jump to arbitrary page 8,432, and changing filters requires a new cursor boundary. OFFSET can remain reasonable for shallow result sets or true page-number navigation. Choose according to the interface, then test with late pages rather than timing only page one.

Excessive LOB retrieval

BLOB, CLOB, and DBCLOB values can be much larger than ordinary columns. A document list may need DOCUMENT_ID, TITLE, MIME_TYPE, and UPDATED_TIMESTAMP, but not a 20 MB document body. Selecting the LOB for every row can increase result construction, query-block and network traffic, character conversion, driver work, memory pressure, and response time. A row limit helps only partly if every returned row still carries a large unused value.

1
2
3
4
5
6
7
8
9
10
11
12
-- List request: retrieve metadata only
SELECT DOCUMENT_ID, TITLE, MIME_TYPE, UPDATED_TIMESTAMP
  FROM DOCUMENT_STORE
 WHERE OWNER_ID = ?
 ORDER BY UPDATED_TIMESTAMP DESC, DOCUMENT_ID DESC
 FETCH FIRST 25 ROWS ONLY;
 
-- Detail request: retrieve one requested LOB
SELECT DOCUMENT_BODY
  FROM DOCUMENT_STORE
 WHERE DOCUMENT_ID = ?
   AND OWNER_ID = ?;

Retrieve a LOB only when the consumer needs it. Supported locators and streaming APIs can reduce eager materialization, but their behavior depends on the host language, driver, cursor, and commit scope. A locator is a reference, not a permanent copy of the value. Follow the application programming documentation for the installed Db2 and driver level, and test cancellation and error handling for large transfers.

Dynamic SQL misuse

Dynamic SQL is appropriate when statement structure genuinely varies, including optional search predicates, administrative tools, and metadata-driven applications. Misuse appears when an application concatenates every value into SQL text. Two statements that differ only in a customer number are different texts and can consume separate preparation work and dynamic statement cache entries. Concatenation also creates SQL injection risk when untrusted data reaches the statement.

1
2
3
4
5
6
7
8
-- Literal-heavy variants reduce reusable statement identity
SELECT CUSTOMER_NAME FROM CUSTOMER WHERE CUSTOMER_ID = 1001;
SELECT CUSTOMER_NAME FROM CUSTOMER WHERE CUSTOMER_ID = 1002;
 
-- Stable statement text with a parameter marker
SELECT CUSTOMER_NAME
  FROM CUSTOMER
 WHERE CUSTOMER_ID = ?;

Parameter markers improve reuse but are not a blind cure. Highly skewed data can make one access path unsuitable for every value, and optional predicates generated as a huge OR expression can create poor estimates. Group statements into a small set of meaningful shapes, bind parameters with correct Db2 data types, and monitor full prepares, cache hits, statement concentration policy, and runtime by statement. Static SQL remains valuable for stable high-volume work because packages provide controlled bind options and access-path management; dynamic SQL remains valuable when variability is real.

Package invalidation

A static SQL package records dependencies on referenced Db2 objects and routines. Certain DDL and dependency changes can make a package invalid or otherwise require regeneration according to documented rules. The next execution might trigger automatic processing, wait for competing work, or fail if the package cannot be made executable. A production change that “only alters a table” can therefore create a response-time spike across many application threads.

Do not invent a universal invalidation list from memory because behavior varies by operation, object type, Db2 release, and function level. Before DDL, query the catalog and administrative tooling for dependent packages, identify critical plans and collections, and determine the supported bind or rebind action. Schedule package work deliberately, preserve required bind options and ownership, and test authorization. Afterward, verify package validity and execute representative transactions before releasing the workload broadly.

Rebind problems and access-path regression

REBIND PACKAGE does not rebuild a program from a DBRM; it regenerates an existing package using its stored statements and applicable options. Rebind may be required for maintenance, invalid packages, release migration, or adoption of optimizer improvements. Because the optimizer evaluates current statistics, subsystem environment, function level, and bind options, a rebind can change access paths. That is useful when old plans are stale, but it also creates regression risk for important statements.

• Capture current package information, access paths, elapsed time, CPU, getpages, and statement frequency before the change.
• Ensure RUNSTATS represents production data. A rebind based on default, stale, or unrepresentative statistics makes optimizer comparison unreliable.
• Use PLANMGMT according to site policy so managed previous or original package copies can support fallback. Understand the difference among BASIC, EXTENDED, and OFF for the installed release.
• Evaluate APREUSE when preserving previous access paths is important. WARN and ERROR have different outcomes when an old path cannot be reused; neither replaces testing.
• Rebind in controlled groups, watch errors and runtime metrics, and keep a documented recovery action rather than rebinding an entire application estate at once.

A successful command does not prove a successful performance result. Compare EXPLAIN output and actual accounting after representative executions. If one statement regresses, determine whether the cause is cardinality estimation, changed join order, index choice, sort, parallelism, or a missing physical design feature. Use supported package-copy switching or another planned recovery method when appropriate instead of repeatedly rebinding and hoping for a different answer.

A practical prevention checklist

• Make search semantics explicit: exact, prefix, range, or contains. Do not make every request a leading-wildcard search.
• Design indexes from the measured workload, including leading-key order, projection, clustering, writes, utilities, and storage.
• Keep units of work restartable and bounded. Monitor lock counts, holder age, escalation, timeouts, and utility drain interference.
• Require complete join relationships and compare expected cardinality with observed row counts before adding DISTINCT.
• Accept a scan when it is cheapest; investigate frequent selective scans with EXPLAIN, RUNSTATS, and runtime evidence.
• Use deterministic pagination, test deep pages, and prefer keyset navigation where the product experience allows it.
• Keep LOB retrieval on demand and keep dynamic statement text stable where statement structure does not need to change.
• Treat DDL, invalidation, bind, and rebind as application changes with dependency analysis, baselines, staged rollout, and recovery.

Explain It Like I'm Five

Imagine Db2 is a huge library. An index is the alphabetic card catalog. Asking for names that start with JOHN lets the librarian open the catalog near J; asking for names that contain OH anywhere may require checking nearly every card. A clustering index decides which books should sit near each other on the shelves. A long transaction is a child who keeps many books and room keys until the end of the day, so nobody else can use them. OFFSET tells the librarian to count and throw away the first 500,000 matching cards every time, while keyset pagination says, “Continue after this exact card.” Retrieving every LOB is like delivering the full encyclopedia when the visitor only asked for its title. Package and rebind management is the library's route map: update it carefully, because a new route can be better—or send every visitor around the long way.

Exercises

• Rewrite a product search from LIKE '%ABC%' to an exact or prefix search, then explain what business results changed and why that change needs approval.
• For a query filtered by ACCOUNT_ID and a posting-date range, propose an index key order. List the read, write, utility, and storage measurements needed before creation.
• A batch job updates 200,000 rows before COMMIT and blocks an online transaction. Design a restartable commit strategy and identify the lock evidence you would capture.
• Find an intentional CROSS JOIN use case and an accidental Cartesian product. Predict the row count of each input and the combined result.
• Compare page 1 and a page with OFFSET 100000 using EXPLAIN and runtime metrics. Then implement the equivalent keyset boundary with a unique tie-breaker.
• Split a document query into metadata and LOB detail requests. Explain how ownership filtering remains enforced in both paths.
• Convert two literal dynamic statements into one parameterized statement. Verify the parameter data type and describe how you would test skewed values.
• Draft a package-maintenance plan containing dependency inventory, validity checks, access-path baselines, PLANMGMT policy, staged rebind, verification, and fallback.