PART II - THE OPTIMIZER - IBM Mainframe

The Optimizer is the most important part of DB2. It is the brain of DB2. It analyses the SQL statements and determines the most efficient access path available for satisfying the statement. In the SQL statements, we only say what we want, the Optimizer decides how to get the things that we have asked for. It accomplishes this by parsing the SQL statements to determine which tables and columns must be accessed. It then queries statistics stored in DB2 catalog to determine the best method of accomplishing the tasks necessary to satisfy the SQL statement.

Working of the Optimizer

Working of the Optimizer

Physical Data Independence

The concept of optimizing data access in the DBMS is one of the most powerful capabilities of DB2. Access to DB2 data is achieved by telling DB2 what you want and not how to get it. DB2's Optimizer accomplishes this task thus giving physical data independence. Regardless of how data is physically stored and manipulated, DB2 and SQL can still access data. This separation of access criteria from physical storage characteristics is called physical data independence.

If indexes are removed DB2 can still access data (only thing is that the performance will reduce). If a column is added to the table being accessed, the data can still be manipulated by DB2 without changing the program code. This is all possible because the physical access paths to DB2 data are not coded by programmers but generated by DB2.

Working of the Optimizer

The Optimizer performs complex calculations based on a host of information. To simplify the functionality of the Optimizer we can picture it the following four steps:

  1. Receive and verify the SQL statement.
  2. Analyze the environment and optimize the method of satisfying the SQL statement.
  3. Create machine-readable instructions to execute the optimized SQL.
  4. Execute the instructions or store them./or future execution.

The Optimizer has many types of strategies for optimizing the SQL. How does it choose the best strategy? The details and logic used by the Optimizer is not disclosed by IBM, but the strategy is a c6st-based one. This means that the Optimizer will always attempt to find an access path that will reduce the overall cost. To accomplish this, the DB2 Optimizer evaluates 4 factors for each potential access paths. Those factors are CPU cost, I/O cost, the DB2 catalog statistics and the SQL statement.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics