Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.1 - User Guide

SQL Optimizer for IBM® DB2® LUW
UsageStatistics Getting Started Options SQL Scanner SQL Optimizer SQL Formatter SQL Inspector Database Explorer SQL Repository Index Impact Analyzer Index Usage Analyzer User-Defined Temp Table Editor Functions SQL Functions SQL Information and Functions Activity Log
Tutorials About us Copyright

Intelligence

ClosedView SQL Rewrite tab – Intelligence options

The Intelligence page under the SQL Rewrite tab on the Options window allows users to select the optimization level for rewriting the SQL. You can customize the optimization level or select a predefined level.

Intelligence Level

Option

Description

Custom

Enables you to customize the settings on the SQL Options, SQL Rewrite (1), SQL Rewrite (2), and Quota pages.

Predefine

Uses the predefined optimization level. The SQL Options, SQL Rewrite (1), and Quota pages are automatically updated according to the level selected. The higher the level, the more likely SQL Rewrite finds a better SQL alternative.

Note: The settings on the SQL Rewrite(2) and the General pages are adjusted independent of the optimization-level settings.

 

Related Topics

SQL Rewrite (1)

ClosedView SQL Rewrite tab – SQL Rewrite (1) options

The SQL Rewrite (1) page under the SQL Rewrite tab on the Options window allows you to enable or disable certain specific SQL optimization techniques.

Temp Table

Temp Table Options

Description

Temp table generation

Specify whether to allow temp table generation as SQL is being rewritten.

L

Specify whether to apply the selected SQL options to the generated temp table SQL.

User temporary table space

Specify the temporary table space to use when declaring the temp table. If no user temporary table space is specified then the database default user temporary table space will be used.

On commit

Specify whether to Delete rows or Preserve rows in the temp table after commit.

Note: To create or modify temporary tables in SQL Rewrite, the logon user needs the following privileges:

  • Connection to DB2 LUW 7 or above

  • USE privilege on the USER TEMPORARY table space or SYSADM or DBADM authority.

View to Nested Table Transformation

This transformation is only applicable if the SQL statement is using a view to access information from the database. When a SQL statement is using a view, the SQL Rewrite optimizes the view's SQL statement along with the original SQL statement. SQL Rewrite inserts the view's SQL into the original SQL statement in every place the view is referenced. Therefore the view's SQL is going to be rewritten along with the original SQL. This is very useful when you want to optimize a SQL statement that is using a poor performing view but you cannot change the view's SQL.

 

Original SQL

SELECT *

  FROM V_DEPT

 WHERE DPT_MANAGER = 'SMITH' 

 

Alternative SQL

SELECT *

  FROM (SELECT DPT_ID,  

               DPT_MANAGER

          FROM DEPARTMENT)          

 WHERE DPT_MANAGER = 'SMITH' 

 

View to Nested Table Transformation Options

Description

Transform view to nested table

Specify whether to transform view to nested table - a subquery using as a table in the FROM clause.

Transformation level

Specify the recursive level to transform views inside the subquery of a nested table. You can control whether the view's SQL is rewritten with the original SQL statement with the Transform view to nested table option. SQL Rewrite can also transform a view that is being used by another view. You control how many views will be included when the original SQL is rewritten by specifying the transformation levels that you would like to perform during the optimization.  

Query to Derived Table Transformation

This transformation takes a original SQL statement with an IN or EXISTS clause and rewrites it as a derived table. 

 

Original SQL

SELECT *

  FROM DEPARTMENT  

 WHERE DPT_ID IN (SELECT EMP_DEPT 

                    FROM EMPLOYEE)                    

 

Alternative SQL

SELECT DEPARTMENT.*

  FROM (SELECT DISTINCT EMP_DEPT AS COL1  

          FROM EMPLOYEE) DERIVEDTABLE0,          

       DEPARTMENT        

 WHERE DPT_ID = DERIVEDTABLE0.COL1 

Query to Derived Table Transformation Option

Description

Transform query to derived table

Specify whether to transform the query to a derived table – a subquery used as a table in a FROM clause.

 

Related Topics

SQL Rewrite (2)

ClosedView SQL Rewrite tab – SQL Rewrite (2) options

The SQL Rewrite (2) page under the SQL Rewrite tab on the Options window allows you to enable or disable certain specific optimization techniques and to define how duplicated access plans are eliminated.

Eliminate SQL alternative with

After SQL Rewrite has parsed the original SQL statement, it creates all the semantically equivalent SQL statements. It then eliminates alternative SQL statements based on one of two criteria: same access plans or same costs. Eliminating the SQL based on like access plans is more accurate, but can take longer because SQL Rewrite compares every operation in the access plans, instead of comparing only cost.

Eliminate SQL alternative with options

Description

Identical access plan (Default)

Eliminate optimized SQL statements with identical access plans.

Identical DB2 LUW cost

Eliminate optimized SQL statements that incur the same DB2 LUW cost amounts.

Advanced SQL transformation

Advanced SQL transformation option

Description

Enable transformation that adds COALESCE (Default = enabled)

Specify to apply the SQL syntax transformation rule that adds COALESCE to a column. When the data is retrieved, the COALESCE function, which in this case is not actually doing anything to change the value of the column, causes a full table scan or the database to pick another index to use. For example:

SELECT *

  FROM EMPLOYEE,

       DEPARTMENT

 WHERE COALESCE(DPT_ID, DPT_ID) = EMP_DEPT

Join Tables

Join Tables options

Description

Rewrite SQL using the same JOIN syntax as the original SQL (Default)

Specify that the alternative SQL statements join the tables in the FROM clause using the same SQL syntax that is used in the original SQL statement. If the original SQL statement contains both syntax types, the optimization process rewrites the syntax using the Ansi-92 JOIN syntax. The outer join is not included in this conversion.

Rewrite SQL using the Ansi-92 JOIN syntax

Specify to use the JOIN clause from the Ansi-92 SQL standard when generating the SQL alternatives. During the optimization, the SQL statement is converted to the Ansi-92 SQL standard and then SQL syntax transformation rules are applied to rewrite the converted SQL statement. Next, the DB2 SQL Options are applied to the original SQL and the transformed SQL. So you can see SQL alternatives that use the JOIN syntax from the original SQL, but these SQL alternatives are simply the original SQL with a SQL Option applied.

The outer join is not including in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the outer join using the (+) operator. So to avoid producing the wrong result set, the conversion of the outer join syntax cannot be applied.

For example:

SELECT DPT_ID

  FROM EMPLOYEE

       INNER JOIN DEPARTMENT

          ON EMP_DEPT = DPT_ID

Rewrite SQL without using the Ansi-92 JOIN syntax

Specify to join tables in the FROM clause without the Ansi-92 JOIN syntax or using comma. The join analysis occurs in the WHERE clause which specifies the column in one table that is compared to a column in another table. During the optimization, the SQL statement is converted from the Ansi-92 SQL standard and then SQL syntax transformation rules are applied to rewrite the converted SQL. Next, the DB2 SQL Options are applied to the original SQL and the transformed SQL. So you may see SQL alternatives that use the JOIN syntax from the original SQL, but these SQL alternatives are simply the original SQL with a SQL Option applied.

The outer join is not including in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the outer join using the (+) operator. So to avoid producing the wrong result set, the conversion of the outer join syntax cannot be applied.

For example:

SELECT DPT_ID

  FROM EMPLOYEE,

       DEPARTMENT

 WHERE DPT_ID = EMP_DEPT

Rewrite SQL with and without using the Ansi-92 JOIN syntax

Specify to use the both types of SQL syntax for joining the tables. Each type of join syntax will result in a different alternative.

 

Related Topics

SQL Options

ClosedView SQL Rewrite tab – SQL Options options  

The SQL Options page under the SQL Rewrite tab on the Options window allows the user to define parameters for rewriting the SQL, as each database and optimizing objective is unique. By adding SQL options, you can force the SQL Rewrite process to choose a particular access plan. SQL options are added to the rewritten SQL statements and shown on the SQL Optimizer window.

When unsure which optimization SQL options to apply, use the defaults or choose a particular optimization level. The default settings should deal with most SQL rewriting cases.

set current query optimization

Specify whether to set the current DB2 query optimization class to 0, 1, 2, 3, 5, 7, 9 classes. The current query optimization controls the class the query optimization performs. This value affects the number of techniques the database optimizer is going to use to rewrite your SQL statement, in order to derive an adequate access plan. A higher value will allow more techniques used but may lengthen the compilation time of your SQL. Each class that you select will be applied to the rewritten SQL statements.

set current degree

Specify whether to set the current degree of intra-partition parallelism. A maximum 12 values can be selected at one time ranging from ANY, 1 to 32,767. The value ANY lets the database optimizer choose and appropriate degree depending on the resources. The fixed number values specify the number of parts the query is going to break down and execute concurrently. The higher the number, the more system resources are needed to execute the query.

Optimize for (for select statement only)

Specify whether to optimize the select statement for a particular number of rows. If this option is not selected then it is assumed that all rows will be retrieved. A maximum of 11 values can be selected at one time.

for read only (for SELECT statement only)

FOR READ ONLY

Specify whether to indicate that the result table is read-only. Specifying this clause informs the database optimizer that only the Share locks are required to execute the query. In certain situation, this piece of information helps the database optimizer derive a different access path.

 

Related Topics

Related Documents