Db2 13 for z/OS Index Efficiency: 8 Proven Troubleshooting Techniques

Most index efficiency problems on Db2 13 for z/OS are not I/O problems. They are CPU problems that look like I/O problems from a distance. Index efficiency is measured in getpages, and a four-level index costs four getpages per probe whether or not the pages are already in the buffer pool, and on a subsystem doing tens of thousands of probes a second that CPU shows up in class 2 time, in the MLC bill, and eventually in a ticket that says "the batch window is slipping".

This post is the sequence I follow when that ticket lands, pinned to Db2 13 function levels through FL 509, with the catalog queries, commands and utility control statements I use at each step.

Two things before the detail. First, everything here is Db2 for z/OS. None of it transfers to Db2 LUW, where the catalog, the monitoring functions and the optimizer behave differently. Second, I have kept the sample outputs illustrative and labelled them as such. Real numbers belong to real subsystems, and the point of the method is that you produce your own.

Why index efficiency on z/OS is a CPU question first

Index efficiency starts with the mechanics. An index probe on Db2 for z/OS walks the B-tree from the root page through the non-leaf levels to a leaf page, then follows the RID to the data page. Every page touched is a getpage, and a getpage is a buffer manager call with a latch, a hash lookup and accounting, even when the page is resident. Index efficiency, in the sense that matters for the CPU bill, is the number of getpages a statement needs to satisfy its predicates divided by the number of rows it returns.

That framing is useful because it separates the four things that make an index inefficient, and each of them has a different evidence source and a different fix. The predicate may not be able to use the index, or may only match some of its columns. The optimizer's estimate of what the index will return may be wrong because statistics are stale or the data is skewed.

The index itself may be physically disorganised, so a range scan touches far more leaf pages than the key range warrants. Or the index may be perfectly designed and perfectly organised and still cost more than it should, because a mechanism that would have removed getpages from the path, such as fast index traversal, is not engaged.

Db2 13 changed the fourth index efficiency category more than any release since Db2 12 introduced it, which is why I treat FTB as a first-class troubleshooting topic rather than a footnote.

Figure 1. Index efficiency in getpages: the same four-level index probed classically and through a fast traverse block.

Start from the accounting record, never from the index

The mistake I see most often is starting an index efficiency investigation at the index. Somebody notices REORGLEAFFAR is high on a big index, runs a REORG, and nothing changes because the statement that was hurting never touched that index. Start from the application side instead.

The SMF 101 accounting record, formatted by whichever monitor you run, gives you class 1 elapsed (application), class 2 elapsed and CPU (inside Db2), and class 3 suspensions broken down by wait type. If class 2 CPU per commit has gone up and class 3 synchronous I/O wait has not, you are looking at getpage volume, which is an index efficiency problem or a stage 2 predicate problem. If synchronous read wait dominates, you are looking at pages that are not in the pool, which is buffer pool sizing, prefetch behaviour or a range scan across a disorganised index.

For index efficiency work, the accounting record tells you which plan and package. The dynamic statement cache tells you which statement. On Db2 13 I run EXPLAIN STMTCACHE ALL under an authorization ID that owns a full set of EXPLAIN tables, then read DSN_STATEMENT_CACHE_TABLE with the statistics columns that matter for index efficiency: getpages, synchronous reads, index scans and rows examined against rows processed.

-- Run EXPLAIN STMTCACHE ALL first under the same authid that owns these tables
EXPLAIN STMTCACHE ALL;

SELECT   STMT_ID,
         STAT_EXEC                                   AS executions,
         STAT_GPAG                                   AS getpages,
         STAT_SYNR                                   AS sync_reads,
         STAT_INDX                                   AS index_scans,
         STAT_RSCN                                   AS ts_scans,
         STAT_EROW                                   AS rows_examined,
         STAT_PROW                                   AS rows_processed,
         DEC(STAT_GPAG, 18, 2) / NULLIF(STAT_PROW, 0) AS getpages_per_row,
         DEC(STAT_CPU, 18, 6)  / NULLIF(STAT_EXEC, 0) AS cpu_per_exec,
         SUBSTR(STMT_TEXT, 1, 120)                   AS stmt_text
FROM     DSN_STATEMENT_CACHE_TABLE
WHERE    STAT_EXEC > 100
ORDER BY getpages_per_row DESC
FETCH FIRST 25 ROWS ONLY;

Rows examined against rows processed is the single most useful index efficiency column in that result. A statement that examines 40,000 rows to return 12 is telling you that either the index is not being matched on enough columns or a stage 2 predicate is doing the real filtering after the fetch. Static SQL needs a different route: package-level accounting (class 7 and 8) to find the package, then PLAN_TABLE rows from the last BIND or REBIND with EXPLAIN(YES).

Reading the access path without flattering it

PLAN_TABLE is where the index efficiency argument is settled, but only if you read the right columns together. ACCESSTYPE tells you whether an index is used at all. MATCHCOLS tells you how many leading key columns actually narrow the probe. ACCESSNAME names the index. INDEXONLY tells you whether the data page was avoided. PREFETCH tells you whether Db2 expects to read sequentially, through a RID list, or dynamically. The sort columns tell you whether the index order was wasted.

SELECT   QUERYNO, QBLOCKNO, PLANNO, METHOD,
         TNAME, ACCESSTYPE, MATCHCOLS, ACCESSNAME, INDEXONLY,
         PREFETCH, PAGE_RANGE,
         SORTN_ORDERBY, SORTC_ORDERBY, SORTC_GROUPBY, SORTC_UNIQ,
         TIMESTAMP
FROM     PLAN_TABLE
WHERE    COLLID   = 'BATCHCOLL'
AND      PROGNAME = 'PAYPOST1'
AND      BIND_TIME = (SELECT MAX(BIND_TIME)
                      FROM   PLAN_TABLE
                      WHERE  COLLID   = 'BATCHCOLL'
                      AND    PROGNAME = 'PAYPOST1')
ORDER BY QUERYNO, QBLOCKNO, PLANNO;

What I am looking for in an index efficiency review, in order. ACCESSTYPE of R on a large table in an OLTP package is a scan and needs no further debate. ACCESSTYPE of I with MATCHCOLS lower than the number of predicates on that index's key columns means at least one predicate has been demoted, or the key column order does not match the predicate pattern.

ACCESSTYPE of N means an IN-list probe, which is fine until the list is long. MX, MI and MU rows mean multiple index access with RID list processing, which depends on the RID pool and can fall back to a scan under pressure. INDEXONLY of N on a statement that only selects key columns is a missed INCLUDE opportunity.

Compare MATCHCOLS with the index definition, not with your memory of it; index efficiency arguments fail on that detail more often than on anything else. SYSIBM.SYSKEYS gives you the column order, and SYSIBM.SYSINDEXES gives you the cardinality figures the optimizer saw.

SELECT   I.NAME              AS index_name,
         I.UNIQUERULE,
         I.CLUSTERING,
         I.PADDED,
         I.COMPRESS,
         I.NLEVELS,
         I.NLEAF,
         I.FIRSTKEYCARDF,
         I.FULLKEYCARDF,
         I.CLUSTERRATIOF,
         I.DATAREPEATFACTORF,
         I.STATSTIME,
         K.COLSEQ,
         K.COLNAME,
         K.ORDERING
FROM     SYSIBM.SYSINDEXES I
JOIN     SYSIBM.SYSKEYS    K
      ON K.IXCREATOR = I.CREATOR
     AND K.IXNAME    = I.NAME
WHERE    I.TBCREATOR = 'PAYROLL'
AND      I.TBNAME    = 'POSTING'
ORDER BY I.NAME, K.COLSEQ;

If STATSTIME is months old on an index whose table churns daily, stop and collect statistics before you conclude anything about the access path. The optimizer is not wrong when it costs a plan from stale numbers; it is doing arithmetic on the inputs it was given.

Predicate stage discipline decides index efficiency before the optimizer runs

Db2 for z/OS classifies every predicate as indexable, stage 1 non-indexable, or stage 2, and the classification is decided by the form of the predicate, not by the optimizer's mood. Indexable predicates can advance MATCHCOLS. Stage 1 predicates are evaluated by the data manager, which can apply them as index screening on the leaf page before the data page is touched. Stage 2 predicates are evaluated by the relational data system after the row has been fetched, so every row they reject was fetched for nothing.

Figure 2. Predicate stages on Db2 for z/OS and their index efficiency cost. Index efficiency is lost before the optimizer runs when a predicate lands in the right-hand column.

The index efficiency demotions that cost the most are the quiet ones. A host variable declared as CHAR(12) compared with a CHAR(10) column, a DECIMAL host variable against an INTEGER column, a SUBSTR wrapped around an indexed column because somebody wanted a prefix match that LIKE 'ABC%' would have handled as stage 1, or an arithmetic expression on the column side of the comparison. None of these produce an error. They produce a plan that still says ACCESSTYPE I, with MATCHCOLS one lower than it should be, and a getpage count that nobody questions because "it is using the index".

Since Db2 10 the EXPLAIN tables record the classification directly, so you do not need to reason it out from the predicate text. DSN_PREDICAT_TABLE carries each predicate, and DSN_FILTER_TABLE carries the stage at which Db2 applies it.

SELECT   P.QUERYNO,
         P.PREDNO,
         F.STAGE,
         F.ORDERNO,
         P.TYPE,
         P.LEFT_HAND_SIDE,
         P.RIGHT_HAND_SIDE,
         P.FILTER_FACTOR,
         P.BOOLEAN_TERM,
         P.SEARCHARG,
         SUBSTR(P.TEXT, 1, 100) AS predicate_text
FROM     DSN_PREDICAT_TABLE P
JOIN     DSN_FILTER_TABLE   F
      ON F.QUERYNO   = P.QUERYNO
     AND F.PREDNO    = P.PREDNO
     AND F.EXPLAIN_TIME = P.EXPLAIN_TIME
WHERE    P.QUERYNO = 4410
AND      P.EXPLAIN_TIME = (SELECT MAX(EXPLAIN_TIME)
                           FROM   DSN_PREDICAT_TABLE
                           WHERE  QUERYNO = 4410)
ORDER BY F.STAGE, F.ORDERNO;

STAGE comes back as MATCHING, SCREENING, STAGE1 or STAGE2, which is the index efficiency verdict in one column. A predicate that should be MATCHING and shows as STAGE2 is a rewrite, not a tuning parameter. The rewrite is usually trivial. Getting the application team to accept that a working, tested COBOL paragraph needs to change because of a host-variable declaration is the harder part, and PLAN_TABLE before and after is the evidence that gets it accepted.

What Db2 13 changed for index efficiency, by function level

Db2 13 has been shipping function levels roughly every six months since GA, and several of them touch index efficiency directly. I pin each item to the level that introduced it, because "we are on Db2 13" tells me nothing about whether a given column exists in the catalog or whether a given default applies. Check your own level with -DISPLAY GROUP before trusting any of this on a client subsystem.

The index efficiency change I would call out for anyone migrating is the FTB_NON_UNIQUE_INDEX default. On Db2 12 non-unique indexes were excluded from fast index traversal unless you turned them on. On Db2 13 they are candidates by default, which is right in most cases but means FTB memory demand can rise after migration on a subsystem whose non-unique indexes are hot. The IBM documentation on Db2 13 function levels is the reference for the level table above, and the FL 509 announcement covers NSYNCREADIO.

Fast index traversal: the index efficiency mechanism most sites run blind

Fast index traversal keeps a copy of an index's root and non-leaf levels in a memory structure called a fast traverse block, so a probe resolves to the leaf page without a getpage per level. The FTB daemon evaluates candidate indexes on a cycle, scoring random traversals up and sequential access, index look-aside hits and leaf page splits down, and builds or frees FTBs within the memory budget set by INDEX_MEMORY_CONTROL. Robert Catterall's post on reconsidering FTB where it was disabled is worth reading if your site turned it off during the early Db2 12 maintenance and never revisited that decision.

Troubleshooting FTB is mostly about answering three questions: is the feature on, is the index eligible, and is it being chosen. The commands answer the third question and part of the second.

-DISPLAY STATS(INDEXMEMORYUSAGE)
-DISPLAY STATS(INDEXTRAVERSECOUNT) DBNAME(PAYDB) SPACENAM(POSTIX*)

-- Illustrative DSNT830I layout after PH69673 (values are not from a real subsystem):
-- DBID  PSID  DBNAME    IX-SPACE  LVL  PART  TRAV.COUNT  FTB FACTOR  ACTION
-- 0301  0007  PAYDB     POSTIX01    4     0     18422190           1  A
-- 0301  0009  PAYDB     POSTIX03    3     0       902114           1
-- 0301  0011  PAYDB     POSTIX07    4     0     22910044          -1  D

INDEXMEMORYUSAGE returns DSNT783I with the list of indexes that currently have an FTB and the storage in use. INDEXTRAVERSECOUNT returns DSNT830I ordered by traversal count, and with PH69673 applied it carries an ACTION column and a meaningful FTB FACTOR even when INDEX_MEMORY_CONTROL is in SELECTED mode. An index near the top of that list without an FTB is the index efficiency case to investigate. Either it is ineligible, or the memory budget is exhausted by indexes above it, or a SYSINDEXCONTROL row is excluding it.

Eligibility on Db2 13 means: unique key up to 128 bytes, or non-unique key up to 120 bytes with FTB_NON_UNIQUE_INDEX at its default of YES; no index versioning in effect; no TIMESTAMP WITH TIME ZONE column in the key. INCLUDE columns do not count toward the limit. The catalog gives you the key length and the versioning state in one query.

SELECT   I.NAME,
         I.UNIQUERULE,
         I.OLDEST_VERSION,
         I.CURRENT_VERSION,
         SUM(CASE WHEN K.COLSEQ > 0 THEN C.LENGTH + CASE WHEN C.NULLS = 'Y' THEN 1 ELSE 0 END ELSE 0 END) AS key_bytes_approx,
         CASE
           WHEN I.UNIQUERULE IN ('U','P','C','R') THEN 128
           ELSE 120
         END AS ftb_limit_bytes
FROM     SYSIBM.SYSINDEXES I
JOIN     SYSIBM.SYSKEYS    K ON K.IXCREATOR = I.CREATOR AND K.IXNAME = I.NAME
JOIN     SYSIBM.SYSCOLUMNS C ON C.TBCREATOR = I.TBCREATOR AND C.TBNAME = I.TBNAME AND C.NAME = K.COLNAME
WHERE    I.TBCREATOR = 'PAYROLL'
AND      I.TBNAME    = 'POSTING'
GROUP BY I.NAME, I.UNIQUERULE, I.OLDEST_VERSION, I.CURRENT_VERSION;

The length arithmetic in that index efficiency screen is approximate on purpose: varying-length and non-padded columns add length bytes, and I would rather have a DBA check the two or three indexes that land near the limit than trust a query to rule on them. OLDEST_VERSION differing from CURRENT_VERSION means versioning is in effect, and a REORG that materialises the pending versions clears it.

When an eligible, hot index still has no FTB, the parameter and the control table are the next stop. INDEX_MEMORY_CONTROL accepts AUTO, DISABLE, a size in megabytes from 10 to 200,000, or SELECTED with either AUTO or a size, in which case only indexes with an enabling row in SYSIBM.SYSINDEXCONTROL are considered.

AUTO sets the FTB budget to 20 percent of the total buffer pool configuration or 10 MB, whichever is larger. That is real storage, not buffer pool storage, and an index efficiency gain is not free; and on an LPAR already tight on real memory it can turn a CPU improvement into a paging problem. Measure headroom before you enable it, and enable it on one member of the data sharing group first. The rollback is the previous parameter value, applied the same way, and the FTB daemon frees the structures on its next cycle.

SELECTED mode exists for sites that want to name the indexes rather than let the daemon choose. The control rows also let you disable FTB for a specific index during a window, which is the cleaner answer when a single index is being built and freed repeatedly because its traversal pattern hovers around the threshold.

-- Enable FTB for POSTIX01 on member DB2A at any time
INSERT INTO SYSIBM.SYSINDEXCONTROL
       (SSID, PARTITION, IXNAME, IXCREATOR, TYPE, ACTION,
        MONTH_WEEK, MONTH, DAY, FROM_TIME, TO_TIME)
VALUES ('DB2A', 0, 'POSTIX01', 'PAYROLL', 'F', 'A',
        'W', NULL, NULL, NULL, NULL);

-- Disable FTB for POSTIX07 during the batch reorganisation window, every day
INSERT INTO SYSIBM.SYSINDEXCONTROL
       (SSID, PARTITION, IXNAME, IXCREATOR, TYPE, ACTION,
        MONTH_WEEK, MONTH, DAY, FROM_TIME, TO_TIME)
VALUES ('DB2A', 0, 'POSTIX07', 'PAYROLL', 'F', 'D',
        'W', NULL, NULL, '22:00:00', '03:00:00');

Two traces close the index efficiency loop when the console is not enough. IFCID 389, in statistics trace class 8, records every index that currently uses fast traversal at each statistics interval, so a monitor can trend it. IFCID 477, in performance trace class 4, records each FTB allocation and deallocation, which is how you catch an index thrashing in and out of memory.

Real-time statistics: the index efficiency evidence Db2 writes for you

SYSIBM.SYSINDEXSPACESTATS is updated by Db2 itself as the index is used and modified. No trace, no monitor licence, no sampling. It is the index efficiency table I read before any REORG INDEX decision and, since FL 509, before any argument about whether an index is suffering synchronous I/O.

Figure 3. Real-time statistics as the index efficiency trigger. The columns on the left justify the actions on the right, not the calendar.

SELECT   S.DBNAME,
         S.INDEXSPACE,
         S.PARTITION,
         S.NLEVELS,
         S.NLEAF,
         S.NACTIVE,
         S.TOTALENTRIES,
         S.REORGPSEUDODELETES,
         DEC(S.REORGPSEUDODELETES, 18, 4) / NULLIF(S.TOTALENTRIES, 0)        AS pseudo_del_ratio,
         S.REORGLEAFNEAR,
         S.REORGLEAFFAR,
         DEC(S.REORGLEAFFAR, 18, 4) / NULLIF(S.NACTIVE, 0)                   AS leaf_far_ratio,
         S.REORGNUMLEVELS,
         S.REORGAPPENDINSERT,
         S.REORGTOTALSPLITS,                    -- FL 501
         S.REORGEXCSPLITS,                      -- FL 501
         DEC(S.REORGSPLITTIME, 18, 3) / NULLIF(S.REORGTOTALSPLITS, 0)        AS avg_split_time,
         S.GETPAGES,
         S.NSYNCREADIO,                         -- FL 509
         DEC(S.NSYNCREADIO, 18, 4) / NULLIF(S.GETPAGES, 0)                   AS sync_io_per_getpage,
         S.LASTUSED,
         S.REORGLASTTIME,
         S.STATSLASTTIME
FROM     SYSIBM.SYSINDEXSPACESTATS S
JOIN     SYSIBM.SYSINDEXES I
      ON I.DBNAME     = S.DBNAME
     AND I.INDEXSPACE = S.INDEXSPACE
WHERE    I.TBCREATOR = 'PAYROLL'
ORDER BY leaf_far_ratio DESC, pseudo_del_ratio DESC;

How I read it for index efficiency. REORGLEAFFAR against NACTIVE is the disorganisation ratio; when a meaningful share of leaf pages are more than sixteen pages away from where a sequential scan would want them, range scans and index-driven prefetch degrade and that index is a REORG INDEX candidate. Pseudo-deleted entries against TOTALENTRIES tell you how much of the index is dead weight that every probe still has to step over; the INDEX_CLEANUP_THREADS daemon should be clearing these, and a persistently high ratio means it cannot keep up or is disabled.

REORGNUMLEVELS moving up is a level added since the last REORG, which is a permanent extra getpage on every probe until the index is rebuilt. REORGEXCSPLITS climbing with a rising average split time is a split storm, and the answer is PCTFREE and key design, not a bigger buffer pool.

The FL 509 ratio, NSYNCREADIO over GETPAGES, is the index efficiency number I have wanted for years. A high ratio on a hot index means the pages are not staying in the pool; that is a buffer pool assignment or sizing question, and no amount of index redesign fixes it. A low ratio with a high getpage count means the pages are resident and the cost is CPU, which is exactly the case fast index traversal and predicate discipline address.

LASTUSED closes the unused-index side of index efficiency. An index with an old LASTUSED, near-zero GETPAGES since the last REORG, and no static package dependency in SYSIBM.SYSPACKDEP is costing you on every insert, update and delete for nothing. I still wait a full business cycle, including year-end, before dropping one, because the query that uses it once a year is real.

Statistics the optimizer can use for index efficiency

An index efficiency investigation that finds the access path wrong should look at statistics before it looks at hints. RUNSTATS on Db2 13 collects key cardinality by default, so the old KEYCARD keyword is redundant. What it does not collect unless asked, and what index efficiency depends on, are the frequency values and histograms that describe skew, and skew is where the optimizer's filter factor for an equality predicate goes wrong by an order of magnitude.

//RUNSTATS EXEC DSNUPROC,SYSTEM=DB2A,UID='PAYRUNST'
//SYSIN    DD *
  RUNSTATS TABLESPACE PAYDB.POSTTS
    TABLE(PAYROLL.POSTING)
    COLGROUP(PAY_PERIOD) FREQVAL COUNT 20
    COLGROUP(POST_DATE)  HISTOGRAM NUMQUANTILES 50
    INDEX(PAYROLL.POSTIX01, PAYROLL.POSTIX03)
    SHRLEVEL CHANGE
    REPORT YES
    SET PROFILE
/*
//* Subsequent runs: RUNSTATS TABLESPACE PAYDB.POSTTS TABLE(PAYROLL.POSTING) USE PROFILE

SET PROFILE stores the option set in SYSIBM.SYSTABLES_PROFILES, and USE PROFILE reuses it, which is how you stop the Tuesday RUNSTATS quietly collecting less than the one that fixed the problem. On Db2 13, USE PROFILE also deletes statistics that the profile no longer asks for, so a histogram that was collected once by hand and never again does not linger in the catalog misleading the optimizer. Collect, then REBIND with APCOMPARE(WARN) so the index efficiency change is compared rather than assumed, and PLANMGMT(EXTENDED), then compare PLAN_TABLE. If MATCHCOLS or ACCESSTYPE moved the wrong way, REBIND with SWITCH(PREVIOUS) puts the old package back without a bind from source.

Index efficiency design corrections that hold up

When the predicate is stage 1, the statistics are current and the index is organised, and index efficiency is still wrong, the index is wrong. The corrections I reach for are few, and each has an EXPLAIN in front of it.

-- Equality columns first, range column next, then the columns the SELECT list needs
CREATE UNIQUE INDEX PAYROLL.POSTIX01
  ON PAYROLL.POSTING (EMP_ID ASC, PAY_PERIOD ASC, POST_SEQ ASC)
  INCLUDE (POST_AMT, GL_ACCT)
  NOT PADDED
  COMPRESS NO
  BUFFERPOOL BP8
  CLOSE NO
  PIECESIZE 2G;

-- Data-partitioned secondary index for the partition-scoped batch,
-- accepting that queries without the partitioning column will probe every part
CREATE INDEX PAYROLL.POSTIX03
  ON PAYROLL.POSTING (POST_DATE ASC, GL_ACCT ASC)
  PARTITIONED
  NOT PADDED
  BUFFERPOOL BP8;

INCLUDE turns a unique index into an index-only path for the statements that select only key and included columns, without widening the uniqueness rule, and on Db2 13 the included columns do not count toward the FTB key limit. Key column order follows predicate form: equality columns first, the single range column next, ordering columns after that.

NOT PADDED shortens the key for VARCHAR columns, which shrinks leaf pages, raises the chance of FTB eligibility, and costs a little CPU on comparison. The DPSI versus NPSI choice is about access pattern, not preference; a DPSI serves partition-scoped batch and partition-level utilities well and punishes queries that lack the partitioning predicate with a probe per partition. Compression on indexes trades CPU for I/O and rarely helps a CPU-bound index efficiency case. I put each of these in front of EXPLAIN before CREATE, because a new index also costs every INSERT, UPDATE and DELETE on the table for the rest of its life.

The index efficiency maintenance cycle, with rollback at each step

Figure 4. The index efficiency maintenance cycle: evidence before utility, verification after. The verification numbers become the next baseline.

//REPORT   EXEC DSNUPROC,SYSTEM=DB2A,UID='PAYRPT'
//SYSIN    DD *
  REORG INDEX PAYROLL.POSTIX03 PART 7
    LEAFDISTLIMIT 200 REPORTONLY
/*
//REORG    EXEC DSNUPROC,SYSTEM=DB2A,UID='PAYREOR'
//SYSIN    DD *
  REORG INDEX PAYROLL.POSTIX03 PART 7
    SHRLEVEL CHANGE
    MAXRO 30 DRAIN WRITERS LONGLOG CONTINUE DELAY 1200
    TIMEOUT TERM
    STATISTICS REPORT YES UPDATE ALL
/*

REPORTONLY with LEAFDISTLIMIT tells you whether the utility itself thinks the index needs reorganising, using the LEAFDIST measure from the catalog, and it costs nothing. The SHRLEVEL CHANGE run keeps the index available; the switch phase needs the drain, and MAXRO, DELAY and LONGLOG CONTINUE keep a long-running batch from holding the switch hostage indefinitely.

STATISTICS inline saves a separate RUNSTATS. REORG INDEX is non-destructive, and if the switch cannot complete the utility terminates with the original index intact, which is the rollback. After the REORG, the same RTS query and the same accounting comparison tell you whether index efficiency moved. Sometimes it does not, because the index efficiency problem was never physical, and that result is worth recording too.

The index efficiency troubleshooting sequence in one picture

Figure 5. The index efficiency sequence: four evidence steps, five causes, one fix per cause, and verification against the telemetry you started with.

Written out, the index efficiency sequence is short. Read the accounting record and decide whether the cost is class 2 CPU or class 3 wait. Find the statement through the statement cache or package accounting. Read PLAN_TABLE and DSN_FILTER_TABLE and decide whether the predicate, the statistics or the index is at fault. Read SYSINDEXSPACESTATS and the FTB displays and decide whether the object is disorganised or the traversal mechanism is missing. Apply exactly one change that matches that evidence, keep the previous package copy, and verify with the same numbers. Then repeat, because the second-worst statement is now the worst.

Things I have learned to expect from index efficiency work. A REORG on an index that was not the problem changes nothing and costs a night. Enabling FTB on a subsystem where the hot indexes were already look-aside friendly shows a smaller gain than the traversal counts suggested. A RUNSTATS with histograms can make one statement better and another worse, which is what PLANMGMT is for. None of these are reasons not to do the work; they are reasons to measure before and after every single step.

Where MinervaDB fits

MinervaDB runs Db2 for z/OS consulting, 24x7 consultative support and managed services as a database-layer practice that works alongside your systems programmers. Index efficiency work is a large share of it: performance health checks built on accounting data, RTS and EXPLAIN tables rather than opinion, Db2 12 to Db2 13 migration reviews that re-check FTB eligibility and index look-aside behaviour after the level change, and the FL 511 object modernisation inventory that every Db2 13 estate now needs. We complement IBM support rather than replace it; product defects go to IBM with our evidence package attached.

Test every change described here on a non-production subsystem with a representative workload before applying it to production, keep image copies and package copies current so that every step has a rollback, and maintain your disaster recovery posture throughout. The index efficiency techniques are sound. Your workload is the only thing that can tell you which of them pays.