---
title: "All About DB2 Optimizer"
description: "The optimizer in DB2 is a main task that saves time and money. How DB2 optimizer works you need to know for DB2 SQL."
url: https://srinimf.com/2017/01/11/the-optimizer-in-db2-5-popular-functions-in-a-nutshell/
date: 2017-01-11
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2017/01/pexels-photo-3770179.jpeg?fit=1832%2C1300&ssl=1
categories: ["DB2"]
tags: ["DB2 Optimizer"]
type: post
lang: en
---

# All About DB2 Optimizer

DB2 engine has [DB2 Optimizer](https://srinimf.com/2015/05/08/db2-optimizer-interview-questions/), which reads statistics (created by RUNSTATS) and improves SQL query performance by generating an optimized access-plan. The [SQL](http://www.indeed.com/jobs?indpubnum=6634974704162507&chnl=srinimf) 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](http://tinyurl.com/jluqmtk)

## 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](https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/perf/src/tpc/db2z_createexplaintables.html).
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](http://www.ibm.com/developerworks/data/library/techarticle/dm-1025db2accessplan/)

Keep Reading

- [DB2 Optimizer Vs RUNSTATS: Top Differences](https://srinimf.com/2020/11/15/db2-optimizer-versas-runstats/)DateNovember 15, 2020
- [3 DB2 Optimizer Interview Questions](https://srinimf.com/2015/05/08/db2-optimizer-interview-questions/)DateMay 8, 2015
- [20 Neat DB2 Interview Questions and Answers](https://srinimf.com/2023/08/14/20-exclusive-db2-interview-questions-to-read-now/)DateAugust 14, 2023

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2017%2F01%2F11%2Fthe-optimizer-in-db2-5-popular-functions-in-a-nutshell%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2017%2F01%2Fpexels-photo-3770179.jpeg%3Ffit%3D1200%252C852%26ssl%3D1&description=All%20About%20DB2%20Optimizer)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2017/01/11/the-optimizer-in-db2-5-popular-functions-in-a-nutshell/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2017/01/11/the-optimizer-in-db2-5-popular-functions-in-a-nutshell/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2017/01/11/the-optimizer-in-db2-5-popular-functions-in-a-nutshell/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2017/01/11/the-optimizer-in-db2-5-popular-functions-in-a-nutshell/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20All%20About%20DB2%20Optimizer&body=https%3A%2F%2Fsrinimf.com%2F2017%2F01%2F11%2Fthe-optimizer-in-db2-5-popular-functions-in-a-nutshell%2F&share=email)
-

### Like this:

Like Loading…