Intelligence
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. |
SQL Rewrite (1)
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. |
SQL Rewrite (2)
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
Join Tables
SQL 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.