db2fopt - Specify query optimizer parameters command
The db2fopt command specifies parameters for use by the query optimizer. This command can be used when setting up a test system which has less physical resources than the production system.
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
- Required connection
- None
Command syntax
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.