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

comments powered by Disqus