File name: rprf_db2parameters.html![[AIX HP-UX Linux Solaris Windows]](../../dist.gif)

DB2 tuning parameters
Read this topic for parameters that you can configure for
better database performance.
For complete DB2® tuning information, refer to the DB2 UDB
Administration Guide: Performance document.
For more information about using AIX® with DB2 see
the topic Tuning AIX systems.
DB2 logging
DB2 has corresponding log files for each database
that provides services to administrators, including viewing database
access and the number of connections. For systems with multiple hard
disk drives, you can gain large performance improvements by setting
the log files for each database on a different hard drive from the
database files.
- How to view or set: At a DB2 command
prompt, issue the command: db2 update db cfg for [database_name]
using newlogpath [fully_qualified_path].
- Default value: Logs reside on the same disk as the database.
- Recommended value: Use a separate high-speed drive, preferably
performance enhanced through a redundant array of independent disk
(RAID) configuration.
DB2 configuration advisor
Located in the DB2 Control Center, this advisor
calculates and displays recommended values for the DB2 buffer
pool size, the database, and the database manager configuration parameters,
with the option of applying these values. See more information about
the advisor in the online help facility within the Control Center.
Number of connections to DB2 -
MaxAppls and MaxAgents
When configuring the data source settings for the databases,
confirm the DB2 MaxAppls setting is greater than the maximum
number of connections for the data source. If you are planning to
establish clones, set the MaxAppls value as the maximum number of
connections multiplied by the number of clones. The same relationship
applies to the session manager number of connections. The MaxAppls
setting must be equal to or greater than the number of connections.
If you are using the same database for session and data sources, set
the MaxAppls value as the sum of the number of connection settings
for the session manager and the data sources.
For example, MaxAppls = (number of connections set for the data
source + number of connections in the session manager) multiplied
by the number of clones.
After calculating the MaxAppls settings for the WebSphere® Application
Server database and each of the application databases, verify that
the MaxAgents setting for DB2 is equal to or greater than
the sum of all of the MaxAppls values. For example, MaxAgents = sum
of MaxAppls for all databases.
DB2 buffpage
Improves database system performance. Buffpage is a database
configuration parameter. A buffer pool is a memory storage area where
database pages containing table rows or index entries are temporarily
read and changed. Data is accessed much faster from memory than from
disk.
- How to view or set: To view the current value of buffpage
for database x, issue the DB2 command get
db cfg for x and look for the value BUFFPAGE. To
set BUFFPAGE to a value of n, issue the DB2 command update
db cfg for x using BUFFPAGE n and set NPAGES to
-1 as follows:
db2 <-- go to DB2 command mode, otherwise the following "select" does not work as is
connect to x <-- (where x is the particular DB2 database name)
select * from syscat.bufferpools
(and note the name of the default, perhaps: IBMDEFAULTBP)
(if NPAGES is already -1, there is no need to issue following command)
alter bufferpool IBMDEFAULTBP size -1
(re-issue the above "select" and NPAGES now equals -1)
You can collect a snapshot of the database while the application
is running and calculate the buffer pool hit ratio as follows:
- Collect the snapshot:
- Issue the update monitor switches using bufferpool on command.
- Make sure that bufferpool monitoring is on by issuing the get
monitor switches command.
- Clear the monitor counters with the reset monitor all command.
- Run the application.
- Issue the get snapshot for all databases command before
all applications disconnect from the database, otherwise statistics
are lost.
- Issue the update monitor switches using bufferpool off command.
- Calculate the hit ratio by looking at the following database snapshot
statistics:
- Buffer pool data logical reads
- Buffer pool data physical reads
- Buffer pool index logical reads
- Buffer pool index physical reads
- Default value: 250
- Recommended value: Continue increasing the value until
the snapshot shows a satisfactory hit rate.
The buffer pool hit ratio indicates the percentage of time that
the database manager did not need to load a page from disk to service
a page request. That is, the page is already in the buffer pool. The
greater the buffer pool hit ratio, the lower the frequency of disk
input and output. Calculate the buffer pool hit ratio as follows:
- P = buffer pool data physical reads + buffer pool index physical
reads
- L = buffer pool data logical reads + buffer pool index logical
reads
- Hit ratio = (1-(P/L)) * 100%
DB2 query optimization level
Sets the amount of work and resources that DB2 puts
into optimizing the access plan. When a database query runs in DB2,
various methods are used to calculate the most efficient access plan.
The range is from 0 to 9. An optimization level of 9 causes DB2 to
devote a lot of time and all of its available statistics to optimizing
the access plan.
DB2 reorgchk
Obtains the current statistics for data and rebinding.
Use this parameter because SQL statement performance can deteriorate
after many updates, deletes or inserts.
- How to view or set: Use the DB2 reorgchk
update statistics on table all command to perform the runstats operation
on all user and system tables for the database to which you are currently
connected. Rebind packages using the bind command. If statistics
are available, issue the db2 -v "select tbname, nleaf, nlevels,
stats_time from sysibm.sysindexes" command on DB2 CLP.
If no statistic updates exist, nleaf and nlevels are -1, and stats_time
has an empty entry (for example: "-"). If the runstats command was
previously run, the real-time stamp from completion of the runstats
operation also displays under stats_time. If you think the time shown
for the previous runstats operation is too old, run the runstats command
again.
- Default value: None
- Recommended value: None
DB2 locktimeout
Specifies the number of seconds that an application waits
to obtain a lock. Setting this property helps avoid global deadlocks
for applications.
- How to view or set: To view the current value of the lock
timeout property for database xxxxxx, issue the DB2 get
db cfg for xxxxxx command and look for the value, LOCKTIMEOUT.
To set LOCKTIMEOUT to a value of n, issue the DB2 update
db cfg for xxxxxx command using LOCKTIMEOUT n,
where xxxxxx is the name of the application database and n is
a value between 0 and 30 000 inclusive.
- Default value: -1, meaning lock timeout detection is turned
off. In this situation, an application waits for a lock if one is
not available at the time of the request, until either of the following
events occurs:
- The lock is granted
- A deadlock occurs
- Recommended value: If your database access pattern tends
toward a majority of writes, set this value so that it gives you early
warning when a timeout occurs. A setting of 30 seconds suits this
purpose. If your pattern tends toward a majority of reads, either
accept the default lock timeout value, or set the property to a value
greater than 30 seconds.
DB2 maxlocks
Specifies the percentage of the lock list that is reached
when the database manager performs escalation, from row to table,
for the locks held by the application. Although the escalation process
does not take much time, locking entire tables versus individual rows
decreases concurrency, and potentially decreases overall database
performance for subsequent attempts to access the affected tables.
- How to view or set: To view the current value of the maxlocks
property for database xxxxxx, issue the DB2 get
db cfg for xxxxxx command and look for the MAXLOCKS value.
To set MAXLOCKS to a value of n, issue the DB2 update
db cfg for xxxxxx command using MAXLOCKS n,
where xxxxxx is the name of the application database and n is
a value between 1 and 100 inclusive.
- Default value: Refer to the current database information
for property default values per operating system.
- Recommended value: If lock escalations are causing performance
concerns, you might need to increase the value of this parameter or
the locklist parameter, which is described in the following paragraph.
You can use the database system monitor to determine if lock escalations
are occurring.
DB2 locklist
Specifies the amount of storage that is allocated to the
lock list.
- How to view or set: To view the current value of the locklist
property for database xxxxxx, issue the DB2 get
db cfg for xxxxxx command and look for the LOCKLIST value
. To set LOCKLIST to a value of n, issue the DB2 update
db cfg for xxxxxx command using LOCKLIST n,
where xxxxxx is the name of the application database and n is
a value between 4 and 60 000 inclusive.
- Default value: Refer to the current database information
for property default values per operating system.
- Recommended value: If lock escalations are causing performance
concerns, you might need to increase the value of this parameter or
the maxlocks parameter, which is described in the previous paragraph.
You can use the database system monitor to determine if lock escalations
are occurring. Refer to the DB2 Administration
Guide: Performance document for more details.
|
