DB2 engine has DB2 Optimizer, which reads statistics (created by RUNSTATS) and improves SQL query performance by generating an optimized access-plan. The SQL compiler is a program that converts static and dynamic SQL into code that is executable by the DB2 engine (the Query is then inputted to Optimizer).
Also Read | DB2 DBA role and certification
Five Functions of DB2 Optimizer.
- If statistics of Tables are out of Date, the DB2-Optimizer will update those.
- The optimizer analyzes SQL and determines the best access plan. The access path stores in EXPLAIN tables.
- The access plan consists of methods and procedures that the DB2 optimizer has determined are necessary to provide the answer set to the requesting application.
- It uses the Bind option of REOPT, which tells if the access path to optimize at runtime, to arrive better access plan.
- DB2 Optimizer uses statistics created by automated RUNSTATS.
- Read other functions and Best examples