All About DB2 Optimizer

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.

  1. If statistics of Tables are out of Date, the DB2-Optimizer will update those.
  2. The optimizer analyzes SQL and determines the best access plan. The access path stores in EXPLAIN tables.
  3. 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.
  4. It uses the Bind option of REOPT, which tells if the access path to optimize at runtime, to arrive better access plan.
  5. DB2 Optimizer uses statistics created by automated RUNSTATS.

Sources

  1. Read other  functions and  Best examples

Keep Reading

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.