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.
DB2 10.5 for Linux, UNIX, and Windows
