SQL Optimizer for DB2 ZOS 5.6 - User Guide

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

Intelligence Options

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

Intelligence Options

SQL Options

Optimization Options

Index Generation

Index Options

To set index options

1. Click .

2. Select Optimize SQL | Index Generation | Options.

3. Review the following for additional information:

   Option	Description
   Retrieve index selectivity by sampling a maximum number of records:	Enter the maximum number of records to retrieve for selectivity sampling.
   Maximum number of columns in a composite index:	Enter the maximum number of columns in a composite index. Range: 1 to 99
   Maximum number of indexes in an index set:	Enter the maximum number of indexes in an index set. Range: 1 to 99
   Evaluate columns in SELECT list:	Select to evaluate creating an index on columns in the SELECT list.
   Quota	Description
   Index Generation Quota:	Enter the maximum number of indexes to generate. Range: 10 to 9999
   Index Set Generation Quota:	Enter the maximum number of index sets to generate by combining two or more indexes. Range: 1 to 99,999
   Index Prefix	Description
   Default prefix of index name:	Enter the prefix placed on the index name when SQL Optimizer generates index candidates. Default: SQLOPT_IDX

Related Topics

Index Setting Options

Intelligence Options (Index Generation)