Chat now with support
Chat with Support

SQL Optimizer for DB2 ZOS 5.6.1 - User Guide

Welcome Optimize SQL Options Tutorial About us Third-party contributions Copyright

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

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

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

Intelligence Options (Index Generation)

Related Documents