Static Package Regression After Statistics Refresh
An online banking query joins ACCT and TXN on ACCT_ID, filters TXN.STATUS = :hv and TXN.POST_DATE between :start and :end, and orders by POST_DATE. It is static SQL in a CICS package bound with REOPT(NONE). A new index TXN_IX3 on (STATUS, POST_DATE, ACCT_ID) was created. After RUNSTATS with default options, the transaction's CPU rose 45% and elapsed time exceeded the 2-second SLA. EXPLAIN before the statistics refresh showed a matching index scan on TXN_IX3 with no sort; after the refresh it shows a tablespace scan and a 40,000-row sort. STATUS has 2 distinct values, and 96% of rows are 'POSTED'. The DBA ran RUNSTATS with KEYCARD and no FREQVAL. The package has 12 statements; only this cursor regressed. The application cannot change SQL for 6 weeks. You have a change window in 48 hours.
1. What is the first thing you would verify, and what evidence would you collect to explain the access path change?
Read the worked answer
The first check is whether a REBIND actually occurred. With REOPT(NONE), RUNSTATS alone cannot change a static package's access path; something must have rebound or activated a new package. If the plan truly changed, the useful evidence is the before-and-after PLAN_TABLE rows: ACCESS TYPE, INDEX ONLY, SORTCARD, sort indicators, and filter factors. Also query SYSIBM.SYSCOLDIST for STATUS and SYSIBM.SYSINDEXSTAT for the new index. The absence of a FREQVAL row for STATUS matters because the optimizer may not know that 'POSTED' is 96% of the table; a leading low-cardinality column can make the index look unattractive for a host-variable predicate. Check whether the package was rebound after the RUNSTATS and whether the statement uses host variables whose values are unknown at bind time.
2. What immediate remedy would you apply in the 48-hour window without changing application SQL?
Read the worked answer
Prefer a remedy that restores the known-good access path and is reversible. First collect distribution statistics with FREQVAL on STATUS and rebind or rebind with REOPT(ALWAYS) in a test subsystem to see whether the optimizer again chooses the matching index scan. REOPT(ALWAYS) can help with host-variable skew, but it affects all 12 statements in the package and adds prepare cost, so measure it rather than assuming it is free. If statistics alone do not restore the plan, capture the old access path in an optimizer profile and use OPTHINT to pin it for this statement. A profile is narrower than a package-wide REOPT change, but it is sensitive to exact statement text and must be tested. Do not add another index in the 48-hour window without proving that the existing index and statistics cannot solve the problem.
3. What longer-term changes would you make to indexing and statistics policy?
Read the worked answer
The index design deserves review because STATUS is the leading column and 96% of rows have one value. For a query that filters a date range and joins on ACCT_ID, an index led by POST_DATE or ACCT_ID may be more useful, depending on the join order and the selectivity of the account predicate. Use EXPLAIN and actual cardinalities to compare candidates rather than guessing. Keep FREQVAL collection for skewed columns and use KEYCARD where appropriate. Store statistics history so a future refresh can be compared with the known-good profile. Finally, decide deliberately whether this package needs REOPT(ALWAYS), REOPT(AUTO), or a profile; a blanket REOPT setting across a 12-statement CICS package is a performance trade-off, not a default fix.