Chat now with support
Chat with Support

SQL Optimizer for DB2 ZOS 5.5.1 - User Guide

Welcome Optimize SQL Options Tutorial About us Copyright

Optimization Options

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To set Optimization options

  1. Click .
  2. Select Optimize SQL | SQL Rewrite | Optimization

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.

View to nested table transformation options

Description

Transform view to nested table

Specify whether to transform view to nested table.

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.

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.

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 source 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 using none 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 using Ansi-92 JOIN and none 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

Quota Options

SQL Options

SQL Options

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To set SQL Options

  1. Click .
  2. Select Optimize SQL | SQL Rewrite | SQL Options

  3. Review the following for additional information:

    Set Current Degree

    Specify whether to set the current degree of intra-partition parallelism. Note the following:

    • The value ANY lets the database optimizer choose and appropriate degree depending on the resources.
    • The value 1 prevents the database optimizer from running queries concurrently.
    Set Value for the "Optimize for" Option Specify the number of rows to optimize. If this option is not selected, then it is assumed that all rows will be retrieved.

 

Related Topics

Quota Options

Optimization Options

 

Quota Options

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To set quota options

  1. Click .
  2. Select Optimize SQL | SQL Rewrite | Quota

  3. Review the following for additional information:

    Quota Description

    Syntax Transformation Quota

    Specify the maximum number of SQL statements generated by applying SQL transformation rules. This quota affects how many SQL statements are created by changing the SQL syntax. The default quota of 100 is normally sufficient for most of the complicated SQL statements. However, the quota can be increased to tackle exceptionally complicated SQL statements with very high levels of table joins or multiple levels of nested sub-queries.

    SQL Options Quota

    Specify the maximum number of SQL statements to be generated by applying SQL options.

    Total Quota

    This is a read-only field indicating the maximum number of SQL statements generated during SQL Rewrite. This figure consists of: Syntax Transformation Quota + SQL Options Quota.

  

Related Topics

SQL Options

Optimization Options

 

Index Generation

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating