Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Command Center for Db2 13.2

Setting SQL tuning options

You can use the Options button to set optional behavior of the SQL tuning feature.

The following tabs are displayed:

  • The Tuning tab sets the options for defining SQL tuning settings for the current session.
  • The Execution tab sets the options for executing SELECT statements.
  • The Statistics Migration tab sets the options for statistics migration. For more details, see Setting-statistic-migration-options.

Tuning tab

The Tuning tab lets you set the following types of options: dynamic SQL, ad hoc SQL, and general.

Dynamic SQL options

Option

Description

Default values

Plan Table Owner

Specifies the name of the owner for the Db2 plan table used

Note

If you want the owner to use lowercase characters, verify that you have not enabled Convert filter values to upper case. See Setting-Command-Center-options.

BMC

Ad Hoc SQL options

Option

Description

Default values

Table Qualifier

Specifies a table qualifier to qualify Db2 objects that are not qualified in the SQL statement

No default value

Degree

Specifies whether to consider parallel processing during an Explain:

  • Any considers parallel processing.
  • 1 does not consider parallel processing.
  • Blank uses the value at bind time.

Default = Any


General options

Option

Description

Default values

Rule set

Defines the Explain expert rules set to use:

  • DEFAULT displays all rules that are related to performance issues and are primarily relevant to DBAs.
  • APPLDEV displays rules that are primarily relevant to application developers.

The rules files are available in the hlq.llqSAMP data set. The name of hlq.llqSAMP varies depending on your BMC AMI SQL Explorer for Db2 installation options and might be hlq.BMCSAMP. The name of the members that contain the rules files are as follows:

  • PSSDFLT contains the DEFAULT rules.
  • PSSAPPL contains the APPLDEV rules.

For more information, see Expert rules.

DEFAULT

Online Application

Specifies whether to trigger specific rules for table space scan, list and sequential prefetch, and multiple index access paths (MIAP) considerations:

  • Select to trigger the rules
  • Clear to not trigger the rules

Unchecked

Cost Translation Rate per Timeron

Specifies the rate used to translate the timeron cost into a monetary unit

1.0

Cost Translation Rate per Service Unit

Specifies the rate used to translate the service unit cost into a monetary unit

1.0

Execution tab

The Execution tab lets you set the select options.

Select options

Option

Description

Default values

Select Options

For SELECT statements, this option lets you set maximum values for the following items (with defaults shown in parentheses):

  • Maximum numeric length (10)
  • Maximum CHAR length (64)
  • Maximum VARCHAR length (64)
  • Maximum select rows (300), where valid values are 1 through 5000.

Note

The total amount of returned data in the result set cannot exceed 20 MB; the Select Options settings do not affect that limit. If the limit is reached, the product alerts you with a message.

N/A

Related topic

Tuning-SQL

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*