5 SQL tuning Strategies - DB2
Ø Tuning Strategy 1:
ü Adjust the COLCARD, FIRSTKEYCARD and FULLKEYCARD columns to an artificially high value.
ü In order to influence DB2 to use and index
ü As cardinality increases, filter factor decreases. As filter factor decreases, the chance that DB2 will use an available index becomes greater.
Ø Tuning Strategy 2:
ü Adjust the CLUSTERRATIO to a high value between 96 and 100, preferably 100.
ü To enhance the probability of DB2 to use an unclustered index.
ü As CLUSTERRATIO increases, DB2’s reluctance to use the unclustered indices from access path selection is removed.
Ø Tuning Strategy 3:
ü Adjust the cardinality CARD to an artificially high value
ü To influence DB2 to change the order in which tables are joined.
ü DB2 uses the table having more no of rows as the Outer table in a join.
Ø Tuning Strategy 4:
ü Decrease the Column Cardinality value (COLCARD or COLCARDF)
ü Favours DB2 to use nested loop join which is efficient over merge scan join
Ø Tuning Strategy 5:
ü Altering the HIGH2KEY and LOW2KEY values to more accurately reflect the overall range of values stored in the column.
ü To influence the access path selection for data with skewed distribution