Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.3.1 - Installation Guide

Optimization (1) (Optimization Tab)

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


Temp table generation

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


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





Alternative SQL




          FROM DEPARTMENT)          



View to Nested Table Transformation Options


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




                    FROM EMPLOYEE)                    


Alternative SQL



          FROM EMPLOYEE) DERIVEDTABLE0,          



Query to Derived Table Transformation Option


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

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating