DB2 anti-pattern: statistics and REORG neglect

The anti-pattern has two different failures

Statistics neglect and REORG neglect are related, but they are not the same problem. RUNSTATS changes metadata in the Db2 catalog; it does not rearrange application rows. REORG changes physical organization; it does not automatically guarantee every distribution statistic required by the optimizer. A mature maintenance process asks two separate questions: “Does Db2 understand the current data?” and “Is the current physical organization still efficient for this workload?”

• Stale or incomplete statistics can make the optimizer underestimate or overestimate rows, choose the wrong index, reverse a join order, select an unsuitable join method, allocate the wrong sort strategy, or misjudge parallelism.
• Physical disorganization can make a previously good access path perform more getpages and random reads because rows are poorly clustered, moved through indirect references, spread over more space, or affected by heavy churn.
• Maintenance without validation is a third failure. A utility can complete successfully while an important package retains an old path or a changed path performs worse for common parameter values.

How optimizer catalog statistics work

Db2 is a cost-based optimizer. Before executing SQL, it estimates the work required for available table-space scans, index accesses, join sequences, join methods, sorts, and other alternatives. It cannot read the whole production table during every prepare or bind. Instead, it relies on statistics stored in catalog tables such as SYSIBM.SYSTABLES, SYSIBM.SYSCOLUMNS, SYSIBM.SYSINDEXES, and partition-related catalog tables. Exact columns vary with object type and Db2 release.

The central estimate is cardinality: how many rows exist or how many rows are expected to survive a predicate. If a table grew from ten thousand rows to fifty million while catalog statistics still describe ten thousand, the estimated cost of repeated index probes or nested-loop work may be unrealistic. If a status column has one rare value and one extremely common value, a simple average based only on the number of distinct values can also be misleading.

RUNSTATS: start broad, add detail for a reason

A baseline RUNSTATS commonly collects table-space table and index statistics. The control statement below is an illustrative starting point. Utility syntax and supported options must be checked for the installed release, object type, and site procedure. SHRLEVEL CHANGE is useful when applications must continue accessing the object, but concurrency is not free: RUNSTATS still consumes CPU, I/O, catalog update work, and utility resources.

1
2
3
4
5
6
7
8
9
10
11
12
//SYSIN DD *
  RUNSTATS TABLESPACE APPDB.ORDERTS
    TABLE(ALL)
    INDEX(ALL)
    SHRLEVEL CHANGE
    UPDATE ALL
    REPORT YES
/*
 
-- Review the utility messages and report.
-- Confirm which table, column, index, partition, and distribution
-- statistics were actually updated before planning REBIND.

TABLE(ALL) and INDEX(ALL) communicate broad scope. UPDATE ALL requests catalog updates for the selected statistics, while REPORT YES produces information useful for review. Scope should still be deliberate. On a large partitioned object, collecting everything after every small load can waste a maintenance window. Partition-level change patterns, inline statistics from utilities, and statistics profiles can support more targeted collection.

Cardinality, frequency, histogram, and column groups

Basic cardinality tells Db2 the table size and number of distinct values, but it does not fully describe distribution. Frequency statistics record selected common or uncommon values and their frequencies. They are valuable for skewed equality predicates such as STATUS = 'FAILED' when FAILED is rare but COMPLETE is common. Histogram statistics divide data into ranges or quantiles and can improve estimates for uneven range predicates.

Column-group statistics matter when columns are correlated. Suppose COUNTRY = 'US' and STATE = 'CA'. Estimating each predicate as independent can produce a poor combined estimate because state codes depend strongly on country. A COLGROUP can describe the combined distinctness or distribution more accurately. RUNSTATS options such as FREQVAL, HISTOGRAM, COUNT, MOST, BOTH, and NUMQUANTILES control distribution detail in supported contexts. Do not copy a maximal template blindly; choose columns, groups, and counts from important SQL predicates and statistics recommendations, then verify the exact syntax for your Db2 level.

• Collect distributions for columns whose skew materially changes an important access path, not merely because the column exists.
• Collect column groups for correlated predicates and joins where independent selectivity assumptions are inaccurate.
• Use a statistics profile when a tested RUNSTATS recipe should be saved and reused consistently for the object.
• Review whether LOAD, REORG, or another utility already collected suitable inline statistics before scheduling duplicate work.

What REORG repairs

REORG TABLESPACE unloads or reads rows and rebuilds their physical arrangement according to the utility's rules and object design. It can restore clustering sequence, remove indirect references, consolidate space, reestablish free-space characteristics, and materialize certain pending definition changes. REORG INDEX addresses index organization. The correct utility depends on the observed condition; reorganizing an index does not physically recluster table rows.

Clustering is especially important for range access. If an index promises rows in customer-and-date order but many new rows land elsewhere, following the index can bounce among data pages. The optimizer might still choose the same sensible index, yet runtime getpages and reads rise because the physical locality has degraded. Updates can also make a row no longer fit in its original location. Db2 may leave an indirect reference that points to the moved row, adding another navigation step.

1
2
3
4
5
6
7
8
9
10
11
12
//SYSIN DD *
  REORG TABLESPACE APPDB.ORDERTS
    SHRLEVEL CHANGE
    LOG YES
/*
 
Operational questions before submission:
  1. Which partitions or objects actually need work?
  2. What drains, claims, logging, sort, and temporary space are expected?
  3. Are image-copy or recovery actions required by site policy?
  4. Will statistics be collected inline or by a planned RUNSTATS?
  5. How will clustering, space, and workload metrics be validated?

This short control statement intentionally omits site-specific choices. SHRLEVEL CHANGE allows substantial concurrent access, but online REORG still has phases that interact with claims and drains. LOG YES supports logged processing but can create significant log volume. Partition selection, DISCARD processing, mapping tables, inline COPY, statistics, flash-copy support, and deadline or switch behavior require an operational design, not a generic copy-and-paste job.

Use real-time statistics as signals, not automatic verdicts

Db2 real-time statistics record activity and conditions as objects change. For table spaces, administrators commonly inspect SYSIBM.SYSTABLESPACESTATS. Index and LOB objects have related real-time statistics. The counters support trend analysis and policy; they are not identical to optimizer catalog statistics and do not replace RUNSTATS.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Illustrative review of table-space real-time statistics.
-- Confirm columns available at your Db2 release and function level.
SELECT DBNAME,
       NAME,
       PARTITION,
       REORGINSERTS,
       REORGDELETES,
       REORGUPDATES,
       REORGUNCLUSTINS,
       REORGNEARINDREF,
       REORGFARINDREF,
       REORGMASSDELETE
FROM SYSIBM.SYSTABLESPACESTATS
WHERE DBNAME = 'APPDB'
  AND NAME = 'ORDERTS'
ORDER BY PARTITION;

Interpret counters relative to object size, access pattern, elapsed time, and baseline. Ten thousand unclustered inserts can be severe for a small, latency-sensitive table and irrelevant for a huge append-oriented partition. Near and far indirect-reference indicators deserve attention when updates move variable-length rows, but impact must be confirmed with getpages and response time. IBM-supplied procedures such as DSNACCOX can apply formulas to recommend maintenance; sites should review and calibrate those recommendations rather than treating them as commands.

Space and clustering evidence beyond one counter

REORG decisions should combine real-time statistics with catalog and runtime evidence. Look at object space, extents, partition imbalance, inserted and deleted activity, clustering measures, page splits for relevant indexes, indirect references, and free-space behavior. Then connect those indicators to the SQL that matters. A batch scan may benefit from restored clustering, while a point lookup that touches one row might barely notice.

A high space number does not itself prove waste: business data may simply have grown. Deleted rows do not always mean immediate space reclamation is economically useful. Conversely, an object can need REORG before it is “full” because poor clustering causes expensive random access. The maintenance objective should be explicit: reclaim disk, improve locality, remove indirect references, materialize pending changes, or meet a recovery and utility standard.

REBIND after statistics: make new knowledge usable

Updating catalog statistics gives the optimizer better input the next time it optimizes a statement. Dynamic SQL can receive a new access path when it is prepared again, subject to dynamic statement caching and invalidation behavior. Static SQL in a package normally continues to use its currently bound access path. If the purpose of RUNSTATS was to correct an important static access path, a controlled REBIND PACKAGE is usually part of the plan.

1
2
3
4
5
6
7
8
9
10
//SYSIN DD *
  REBIND PACKAGE(APP_COLL.ORDERPKG)
    EXPLAIN(YES)
    PLANMGMT(EXTENDED)
/*
 
-- Illustrative only: retain the package's required bind options.
-- Review access-path changes and fallback capability before production.
-- Access-path reuse or comparison options can deliberately limit change;
-- choose them according to the site's package-management policy.

REBIND is not a harmless ceremonial step. New statistics can improve one statement and regress another, especially when host-variable values vary widely. Preserve required bind options, use access-path management facilities such as PLANMGMT according to site policy, compare paths, test representative values, and keep a fallback procedure. Avoid rebinding every package after every small statistics update merely to “make it current.”

Validate access paths with EXPLAIN

Capture comparable EXPLAIN evidence before maintenance and after the intended optimization event. Use the same SQL, APPLCOMPAT and optimization environment, and representative parameter assumptions. PLAN_TABLE describes the selected access method, but related explain tables can provide predicate, cost, and structure detail. A changed plan is not automatically an improved plan, and an unchanged plan can still run faster after REORG because physical locality improved.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
EXPLAIN PLAN SET QUERYNO = 9340 FOR
SELECT ORDER_ID, ORDER_TS, STATUS
FROM APP.SALES_ORDER
WHERE CUSTOMER_ID = 4711
  AND ORDER_TS >= TIMESTAMP('2026-08-01-00.00.00')
  AND ORDER_TS <  TIMESTAMP('2026-09-01-00.00.00');
 
SELECT QUERYNO,
       PLANNO,
       METHOD,
       ACCESSTYPE,
       MATCHCOLS,
       ACCESSCREATOR,
       ACCESSNAME,
       INDEXONLY
FROM PLAN_TABLE
WHERE QUERYNO = 9340
ORDER BY PLANNO;

• Compare estimated cardinality at each step, not only the final returned row count.
• Check ACCESSTYPE, ACCESSNAME, MATCHCOLS, index-only access, join sequence, join method, sorts, prefetch, parallelism, and partition access.
• After execution, compare CPU, elapsed time, getpages, synchronous reads, rows processed, sort activity, and statement frequency.
• Test skew-sensitive statements with common and rare values. One favorable literal does not represent every execution.

Build an evidence-driven utility schedule

“Run everything every Sunday” is easy to document but often inefficient. A quiet reference table may need statistics only after a controlled refresh. A volatile partition can cross a meaningful change threshold within hours. An append-only current partition might need targeted statistics frequently but no immediate full table-space REORG. A history partition that becomes read-only can receive final RUNSTATS, REORG, COPY, and then little recurring work.

• Define triggers based on percentage and absolute change, distribution change, partition lifecycle, clustering, indirect references, and observed service impact.
• Schedule utilities around claims, drains, batch deadlines, replication, logging capacity, sort work, image-copy policy, and recovery objectives.
• Use saved RUNSTATS profiles where consistent distribution collection matters, and review profiles when SQL or data shape changes.
• Coordinate RUNSTATS, REORG, LOAD, COPY, and REBIND so one job does not duplicate or accidentally undo the purpose of another.
• Record the reason for maintenance, expected improvement, validation query, owner, completion evidence, and next review date.

The opposite anti-pattern: over-REORG

Neglect does not justify reorganizing every object continuously. REORG can consume substantial CPU, I/O, sort capacity, temporary storage, and logs. Online processing can still contend with production and may need a successful switch or drain. Utility failures can leave follow-up states or operational work. Extra image copies and retained data sets can increase storage demand, and unnecessary REBIND after every utility can introduce access-path churn.

REORG also resets or changes the interpretation of certain maintenance counters, so a calendar loop can erase useful trend context without proving benefit. Establish a minimum expected gain and compare post-REORG measurements. If clustering, getpages, elapsed time, space, and indirect-reference behavior do not improve materially, adjust the policy. Sometimes better PCTFREE planning, partition rotation, a different clustering index, application update behavior, or targeted partition maintenance is the more durable fix.

A safe maintenance workflow

• Inventory important objects, SQL, static packages, partition lifecycles, and current RUNSTATS profiles or utility templates.
• Capture catalog statistics, real-time statistics, EXPLAIN output, and runtime measurements before changing anything.
• Identify whether the problem is optimizer knowledge, physical organization, or both.
• Run the narrowest suitable utility with reviewed concurrency, logging, space, recovery, and fallback requirements.
• Re-optimize affected static SQL through controlled REBIND when new statistics are intended to influence its access path.
• Compare EXPLAIN and runtime evidence, document the result, and tune future thresholds from the measured outcome.

Explain It Like I'm Five

Imagine Db2 is a librarian. The catalog statistics are the librarian's map: it says how many books are in each room, which subjects are common, and where popular books are likely to be. If the map says there are ten books but the room now holds a million, the librarian may choose a terrible route. RUNSTATS redraws the map. REORG is different: it tidies the actual shelves so books that belong together are close together and empty shelf space is useful again. REBIND lets the librarian make a new route for instructions that were written using the old map. EXPLAIN shows the planned route, and runtime measurements tell you whether the walk really became shorter.

Exercises

• A table grows from 100,000 to 40 million rows after a monthly load. List three optimizer decisions that stale cardinality could distort and propose a RUNSTATS and REBIND validation sequence.
• A STATUS column is 98 percent COMPLETE, 1.9 percent FAILED, and 0.1 percent HELD. Explain why simple distinct-value cardinality can be misleading and which distribution statistic family you would evaluate.
• COUNTRY and REGION appear together in important predicates. Explain when column-group statistics are better than treating their selectivities independently.
• Review REORGUNCLUSTINS, REORGNEARINDREF, and REORGFARINDREF for one partition. State what additional object-size and runtime evidence you need before recommending REORG.
• Compare PLAN_TABLE rows before and after a controlled REBIND. Record access type, index, matching columns, join sequence, estimated cardinality, and any sort change.
• Design a utility calendar for active, closed, and archive partitions. Avoid running identical RUNSTATS and REORG jobs for all three lifecycle stages.
• Write a rollback plan for an access-path regression introduced after statistics collection and REBIND, including package management and runtime verification.