Appendix A. Tips for optimizing very large static workloads 401
Tablename.columnname
Grouping by the column expressions and ordering by descending count provides a prioritized
list of the column expressions that are reasonable tuning targets. To remediate:
򐂰 Rewrite individual SQL to a Stage 1 alternative, where possible, replace YEAR(date) =
year with date BETWEEN first-date-in-year AND last-date-in-year.
򐂰 Create an index directly over a heavily-referenced column expression.
A.2 Automated RUNSTATS column lists
Compare with the commonly employed RUNSTATS specification:
RUNSTATS TABLESPACE dbname.tsname
TABLE(ALL) INDEX(ALL)
This collects COLUMN statistics for all columns. Our procedure targets COLUMN statistics
only for those columns used in the predicates in the static SQL population. Statistics for the
other columns have no optimization benefit and just add to the RUNSTATS cost.
This does not collect FREQVAL for ANY column (other than the leading column in each
index). This procedure targets FREQVAL statistics for columns used in predicates with literal
values, whose frequencies (potentially skewed) can help optimizer costs.
These tips employ a batch alternative to the Workload Statistics Advisor, to obtain a set of
reasonable first-cut recommendations where the scale of the workloads (thousands or tens of
thousands of SQL statements) prevents convenient processing by the Optimization Expert.
After these are run, run the Statistics Advisor to refine the estimates further. See Chapter 9,
“Statistics Advisor” on page 223.
A.2.1 Mass-explain procedure
Use the same procedure as described in Appendix A.1.1, “Mass-explain procedure” on
page 398.
In fact, run this procedure only once to provide information for both analyses in this appendix.
A.2.2 Columns in predicates (need COLUMN statistics)
The following SQL (Example A-4) returns all columns that appear:
򐂰 On the left-hand-side of predicates.
򐂰 As join columns (left-hand-side or right-hand-side of joins).
They appear along with reference counts for the package population processed according to
Appendix A.1.1, “Mass-explain procedure” on page 398.
Example: A-4 Columns used in predicates - Target COLUMN statistics
SELECT
TBCREATOR
,TBNAME
,COLNAME
,COUNT(*) AS REFERENCE_COUNT
402 DB2 9 for z/OS: New Tools for Query Optimization
FROM
(
SELECT
PL.CREATOR AS TBCREATOR
,PL.TNAME AS TBNAME
,CHAR(PR.LEFT_HAND_SIDE,30) AS COLNAME
FROM DSN_PREDICAT_TABLE AS PR
JOIN PLAN_TABLE AS PL
ON (PR.PROGNAME = PL.PROGNAME
AND PR.EXPLAIN_TIME = PL.BIND_TIME
AND PR.QUERYNO = PL.QUERYNO
AND PR.LHS_QBNO = PL.QBLOCKNO
AND PR.LHS_TABNO = PL.TABNO
AND PR.LHS_TABNO > 0
AND PL.MIXOPSEQ = 0)
UNION ALL
SELECT
PL.CREATOR AS TBCREATOR
,PL.TNAME AS TBNAME
,CHAR(PR.RIGHT_HAND_SIDE,30) AS COLNAME
FROM DSN_PREDICAT_TABLE AS PR
JOIN PLAN_TABLE AS PL
ON (PR.PROGNAME = PL.PROGNAME
AND PR.EXPLAIN_TIME = PL.BIND_TIME
AND PR.QUERYNO = PL.QUERYNO
AND PR.RHS_QBNO = PL.QBLOCKNO
AND PR.RHS_TABNO = PL.TABNO
AND PR.LHS_TABNO > 0
AND PR.RHS_TABNO > 0)
) AS T
GROUP BY
TBCREATOR
,TBNAME
,COLNAME
ORDER BY
TBCREATOR
,TBNAME
;
A program can easily process the result set, breaking by table name, to generate the
appropriate COLUMN list:
RUNSTATS TABLESPACE dbname.tsname
TABLE(tbcreator.tbname) INDEX(ALL)
COLUMN(column1,column2,...)

Get IBM DB2 9 for z/OS: New Tools for Query Optimization now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.