DB2 Optimizer Versas RUNSTATS

DB2 Optimizer looks after your query performance(efficient way of execution). The optimizer takes informed decisions before the SQL query executes. The statistics of your tables; the number of rows, indexes, index cardinality, the number of pages consumed, stored in catalog tables. The optimizer uses this data.

DB2 Optimizer Vs RUNSTATS

In this post explained the Optimizer details before it executes SQL Query in three steps:

  1. DB2 Optimizer Functionality flowchart
  2. Automated RUNSTATS
  3. Manual RUNSTATS

1. DB2 Optmizer Functionality Flowchart.

DB2 Optimizer
Source: DB2help

2. Automated RUNSTATS.

In the above flow, it is clearly stated that Optimizer has access to Statistics, Bind Options, and SQL Query.

The Optimizer prepares an access-plan to the SQL query. From the snapshot, the Access plan goes to the Visual Explain, which is a tool, you can view the execution in visual mode.

The collection of statistics is an automated process. So from the developer side, it is nothing to do.

3. Manual RUNSTATS.

The developer can use the RUNSTATS command on the Table and indexes to update catalog tables (This you need during building SQL query for the first time).

The purpose is to get insights about the Queries (This is useful to issue explain plan command after executing RUNSTATS). So, the developer can fix the query if optimization is needed.

Example:

RUNSTATS ON TABLE employee AND DETAILED INDEXES ALL;

RUNSTATS collects a wealth of information about; Table, and the indexes on the table, such as index “height,” number-of-pages at the leaf level, and more. Gathering this index-related information is controlled using the indexes clause.

Author: Srini

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

Start Discussion

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.