Tuning of SQL queries in DB2 when RUNSTAT is not feasible
Updating the DB2 Catalog Statistics:
Ø This method of tuning is chosen whenever standard methods (RUNSTATS) of influencing the DB2’s access path selection are not satisfactory.
Ø It can be used in situations, where the tables are huge and running RUNSTATS on the table is not a feasible solution because of CPU, Execution time and memory constraints.
Ø Here the trick is to update modify the DB2 Catalog table statistics by manuallyfiring SQL queries (UPDATE, DELETE, INSERT) statements against the catalog tables.
Ø Note the above updates can be performed on the catalog tables only by users having SYSADM access.
DB2 catalog statistics used during Optimization:
The table below lists out some of the important columns in DB2 catalog tables that affects the access path selection.
DB2 Catalog TableColumn Description
SYSCOLSTATSHIGH2KEY Second highest value stored in the column
LOW2KEY Second lowest value stored in the column
COLCARD Number of distinct values for the column
SYSCOLUMNSCOLCARDF Estimated no of distinct values for the column.
SYSINDEXSTATSCLUSTERRATIO % of rows in clustered order
FIRSTKEYCARD Number of distinct values for the first column of the index key
FULLKEYCARD Number of distinct values for the full column index key
SYSTABSTATSCARD Number of rows for the partition