The disk space and memory allocated by the database manager
based on default values of the parameters might be sufficient to meet
your needs. In some situations, however, you might not be able to
achieve maximum performance by using these default values.
About this task
Since the default values are oriented towards machines
that have relatively small memory resources and are dedicated as database
servers, you might need to modify these values if your environment
has:
- Large databases
- Large numbers of connections
- High performance requirements for a specific application
- Unique query or transaction loads or types
Each transaction processing environment is unique in one
or more aspects. These differences can have a profound impact on the
performance of the database manager when using the default configuration.
For this reason, you are strongly advised to tune your configuration
for your environment.
A good starting point for tuning your
configuration is to use the Configuration Advisor or the AUTOCONFIGURE command. These tools generate values for
parameters based on your responses to questions about workload characteristics.
Some configuration parameters can be set to AUTOMATIC, allowing the database manager to automatically manage these parameters
to reflect the current resource requirements. To turn off the AUTOMATIC setting of a configuration parameter while maintaining
the current internal setting, use the MANUAL keyword
with the UPDATE DATABASE CONFIGURATION command.
If the database manager updates the value of these parameters, the GET DB CFG SHOW DETAIL and GET DBM CFG SHOW DETAIL commands will show the new value.
Parameters for an individual
database are stored in a configuration file named SQLDBCONF. This file is stored along with other control files for the database
in the SQLnnnnn directory,
where nnnnn is a number assigned when the database
was created. Each database has its own configuration file, and most
of the parameters in the file specify the amount of resources allocated
to that database. The file also contains descriptive information,
as well as flags that indicate the status of the database.
Attention: If you edit db2systm, SQLDBCON, or SQLDBCONF by using a
method other than those provided by the database manager, you might
make the database unusable. Do not change these files by using methods
other than those documented and supported by the database manager.
In a partitioned database environment, a separate SQLDBCONF file exists for each database partition. The values in the SQLDBCONF file might be the same or different at each
database partition, but the recommendation is that in a homogeneous
environment, the configuration parameter values should be the same
on all database partitions. Typically, there could be a catalog node
needing different database configuration parameters setting, while
the other data partitions have different values again, depending on
their machine types, and other information.
- Update configuration parameters.
- View updated configuration values.
For some
database manager configuration parameters, the database manager must
be stopped (db2stop) and then restarted (db2start) for the new parameter values to take effect.
For some database parameters, changes take effect only when the
database is reactivated, or switched from offline to online. In these
cases, all applications must first disconnect from the database.
(If the database was activated, or switched from offline to online,
then it must be deactivated and reactivated.) Then, at the first new
connect to the database, the changes take effect.
If you change
the setting of a configurable online database manager configuration
parameter while you are attached to an instance, the default behavior
of the UPDATE DBM CFG command is to apply the change
immediately. If you do not want the change applied immediately, use
the DEFERRED option on the UPDATE DBM
CFG command.
To change a database manager configuration
parameter online:
db2 attach to instance-name
db2 update dbm cfg using parameter-name value
db2 detach
For clients, changes to the database
manager configuration parameters take effect the next time the client
connects to a server.
If you change a configurable online database
configuration parameter while connected, the default behavior is
to apply the change online, wherever possible. Note that some parameter
changes might take a noticeable amount of time to take effect due
to the additional processing time associated with allocating space. To change configuration
parameters online from the command line processor, a connection to
the database is required. To change a database configuration parameter
online:
db2 connect to dbname
db2 update db cfg using parameter-name parameter-value
db2 connect reset
Each configurable online configuration
parameter has a
propagation class associated with it.
The propagation class indicates when you can expect a change to the
configuration parameter to take effect. There are four propagation
classes:
- Immediate: Parameters that change immediately upon command
or API invocation. For example, diaglevel has
a propagation class of immediate.
- Statement boundary: Parameters that change on statement
and statement-like boundaries. For example, if you change the value
of sortheap, all new requests use the new value.
- Transaction boundary: Parameters that change on transaction
boundaries. For example, a new value for dl_expint is updated after a COMMIT statement.
- Connection:
Parameters that change on new connection to the database. For example,
a new value for dft_degree takes effect for new
applications connecting to the database.
While new parameter values might not be immediately effective,
viewing the parameter settings (by using the GET DATABASE
MANAGER CONFIGURATION or GET DATABASE CONFIGURATION command) always shows the latest updates. Viewing the parameter
settings by using the SHOW DETAIL clause on these
commands shows both the latest updates and the values in memory.
- Rebind applications after updating database configuration
parameters.
Changing some database configuration parameters
can influence the access plan chosen by the SQL and XQuery optimizer.
After changing any of these parameters, consider rebinding your applications
to ensure that the best access plan is being used for your SQL and
XQuery statements. Any parameters that were modified online (for example,
by using the UPDATE DATABASE CONFIGURATION IMMEDIATE command) cause the SQL and XQuery optimizer to choose new access
plans for new query statements. However, the query statement cache
is not purged of existing entries. To clear the contents of the query
cache, use the FLUSH PACKAGE CACHE statement.
Note: A number of configuration parameters (for
example, health_mon) are
described as having acceptable values of either Yes or No, or On or Off in the help and other DB2 documentation.
To clarify, Yes should be considered equivalent to On and No should be considered equivalent
to Off.