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 message rules: DEFAULT displays all rules that are related to performance issues and are primarily relevant to DBAs. APPDEV displays rules that are primarily relevant to application developers.	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 0 returns all rows in the result set.

Note

The total amount of returned data in the result set cannot exceed 50 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