3 DB2 Optimizer Interview Questions

I want to share some interview questions that were asked in big companies like Dell, HCL, Infosys, CTS, etc.

1. What is optimizer?

A) The DB2 optimizer is the brain of DB2. The optimizer is a sophisticated and complex set of algorithms whose main objective is to calculate the fastest way to retrieve your data based on your database statistics.

2. What are the functions of optimizer?

A. Parse the query. The optimizer’s first task is to analyze the SQL query to validate the syntax. If it detects any syntax errors, the optimizer stops processing, and the appropriate SQL error is returned to the application attempting to compile the SQL statement. When parsing is complete, an internal representation of the query is created.

Check the query semantics. The second task of the optimizer is to further validate the SQL statement by checking to ensure that the parts of the statement make sense given the other parts, for example, ensuring that the data types of the columns input into scalar functions are correct for those functions.

Also during this stage, the optimizer adds the behavioral semantics to the query graph model, such as the effects of referential constraints, table check constraints, triggers, and views.

Rewrite the query. The optimizer uses global semantics provided in the query graph model to transform the query into a form that can be optimized more easily.

For example, the compiler might move a predicate, altering the level at which it is applied, in an attempt to improve query performance.

This particular process is called a general predicate pushdown. Any changes made to the query are rewritten back to the query graph model.

Optimize the access plan. The SQL optimizer portion of the optimizer uses the query graph model as input and generates many alternative execution plans for satisfying the user’s request.

It estimates the execution cost of each alternative plan using the statistics for tables, indexes, columns, and functions, and chooses the plan with the smallest estimated execution cost.
The optimizer uses the query graph model to analyze the query semantics and to obtain information about a wide variety of factors, including indexes, base tables, derived tables, subqueries, correlation, and recursion.
The output from this step of the optimizer is an access plan, which provides the basis for the information captured in the explain tables. The information used to generate the access plan can be captured with an explain snapshot.

Generate the executable code. The optimizer’s final step uses the access plan and the query graph model to create an executable access plan, or section, for the query.

This code generation step uses information from the query graph model to avoid repetitive execution of expressions that only need to be computed once for a query.
Examples for which this optimization is possible to include code page conversions and the use of host variables.

3. Where optimizer get data?

A. If the RUNSTATS command is not run, or the optimizer determines that RUNSTATS was run on empty or near-empty tables, the optimizer may either use defaults or attempt to derive certain statistics based upon the number of file pages used to store the table on disk.