db2fopt - Specify query optimizer parameters command

For example, if the production system is running with sortheap=20000 and the test system can only run with sortheap=5000, you can use db2fopt on the test system to set the opt_sortheap optimizer parameter to 20000. This will direct the optimizer to use 20000 as the sort heap value when evaluating access plans while the sortheap database configuration parameter is set to 5000.

Scope

This command only affects the database partition on which it is executed.

Authorization

To query parameters using the get option: none

To update parameters, one of the following authorities is required:

• SYSADM
• SYSCTRL
• SYSMAINT

Required connection

None

Command syntax

>>-db2fopt--DBname---------------------------------------------->

             .----------------------------.     
             V                            |     
>--+-update----optimizer-parameter--value-+-+------------------><
   |      .---------------------.           |   
   |      V                     |           |   
   '-get----optimizer-parameter-+-----------'   

Command parameters

DBname

Alias name of the database.

update optimizer-parameter value

Use this command to update optimizer parameters.

• opt_buffpage
• opt_sortheap
• opt_locklist
• opt_maxlocks

get optimizer-parameter

Use this command to query optimizer parameter values.

• opt_buffpage
• opt_sortheap
• opt_locklist
• opt_maxlocks

Usage notes

This tool is sometimes used in partitioned database environments that consist of heterogeneous database partition configurations. In this case, statement compilation occurs on a coordinator database partition which can have different database configuration settings from that of the database partitions in the instance on which query processing takes place.

If an optimizer parameter has a value of 0, then no optimizer value has been specified. Statement compilation will use the value from the database configuration.

Updating an optimizer parameter to a value of 0 will reset a previously updated value.

Specifying a non-numeric or a negative value on an update action will set the value to 0.

For an update to take effect, all connections must be terminated on the database partition, and the database partition must be deactivated if previously activated.

The optimizer parameters are only used for statement compilation. In partitioned database environments, they must be set on coordinator database partitions.

To determine the actual values to specify on the test system, you can obtain an explain output from the production system by using the db2exfmt tool, and review the Database context section.