SQL Optimizer for DB2 ZOS 5.0

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.

Test Run

Test Run Options

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

To set test run settings options

Click .
Select Optimize SQL | Test Run | Test Run Settings
Review the following for additional information:

Test Run Settings

Description

Custom

Enables you to customize the settings.

Predefined

Uses the predefined set of options.

Execution Method Options

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

To set execution method options

Click .
Select Optimize SQL | Test Run | Execution Method

Review the following for additional information:

Execution Method	Description
Maximum rows to be retrieved (records)	Select this checkbox and then enter the maximum number of rows to retrieve for a test run of all records.
Run Time Retrieval Method	Description
Run SQL options	Select one of the following options: Run all SQL twice if original SQL runs faster than (seconds)—Combines the Original SQL twice and all others once and the All SQL twice options into one option and allows you to determine (by the number of seconds a SQL statement runs) which option to use. The original SQL statement always runs twice. The SQL alternatives run twice if the original SQL statement runs in less time than the value specified. Otherwise, the SQL alternatives all run once. Original SQL twice and all others once—Caches data from a table into memory the first time you access it. The next time you access that data, it is already in memory so the following SQL statements run faster. To provide an accurate comparison, the first SQL statement runs twice but only the time from the second run is compared to the times for the other statements. All SQL twice—Executes all SQL statements twice to eliminate factors that can affect the accuracy of the results. If you recently executed a SQL statement, the information for that statement may be cached and the statement may execute faster. This option eliminates time variation caused by caching since it runs all SQL statements twice but only uses the second run time for comparison. All SQL once—Executes all SQL statements once. For long running SQL, you do not need to run any statement twice since the effect from caching diminishes over time.

Test Run Settings	Description
Custom	Enables you to customize the settings.
Predefined	Uses the predefined set of options.