SQL Options (Optimization Tab)
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.