DB2 Access path Determination - Function of DB2 Optimiser
Function of DB2 Optimizer:
Ø The Optimizer is the method by which DB2 chooses to carry out the data manipulation requested in the SQL statements
Ø DB2 Optimizer on its own, chooses the access path determinations based on the statistics available from the Catalog tables
Ø DB2 Optimizer estimates both the Elapsed times and CPU times when choosing an access path
Ø Since Optimizer takes into account the type of CPU being used during the access path selection, it is important that whenever the tuning of SQL is done, it should be done with the Production DB2 subsystem (i.e., the EXPLAIN statement on the SQL should be run against the Production DB2 tables)
Ø Even if SQL and DB2 catalog statistics are identical for both Test and Production environments, the optimizer might be forced to choose a different access path when SQLs are run in each of these environments just because of the only reason of CPU difference.
Need to influence an Optimizer chosen Access path:
Ø Performance of a SQL and hence tuning is directly dependent on the Access path chosen for the statement by the DB2 Optimizer
Ø Often an Optimizer choses an inefficient access path than an efficient one due to the following reasons:
· Statistics being outdated, in other words RUNSTATS was never run or not run recently.
· Physical parameters like differences between physical storage devices (Model of DASD device, or faster devices) etc.,
Techniques to influence an Optimizer chosen Access path:
Ø Standard DB2-based methods
Ø Tweaking SQL statements
Ø Specifying the OPTIMIZE FOR n ROWS clause
Ø Updating the DB2 Catalog Statistics
Standard DB2-Based Methods:
Ø Keeping the Statistics of the tables upto date in the Catalog tables using RUNSTATS for better selection of Access paths.
Ø Reorganizing the tablespaces and indexes after mass deletions
Ø Ensuring that there are proper indexes by creating new indexes on mostly selected columns
Ø Dropping unnecessary and unused indexes.
- Reduces I/O incurred for DML (INSERT, UPDATE, DELETE) statements
- Reduces RUNSTATS resource requirements
- Speeds up the REORG and RECOVER processing