DB2 tuning parameters
DB2 has many parameters that you can configure to optimize database performance.
For complete DB2 tuning information, refer to the DB2 UDB Administration Guide:
Performance.
DB2 logging
- Description: 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 RAID.
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.
Use TCP/IP sockets for DB2 on Linux
- Description: On Linux platforms, whether the DB2 server resides
on a local machine with WebSphere Application Server or on a remote machine,
configure the DB2 application databases to use TCP/IP sockets for communications
with the database.
- How to view or set: Locate the directions for configuring DB2 on
Linux in Installing WebSphere Application Server products. This document specifies setting
DB2COMM for TCP/IP and corresponding changes required in the etc/service file.
- Default value: Shared memory for local databases
- Recommended value: On Linux, change the specification for the DB2
application databases and any session databases from shared memory to TCP/IP
sockets.
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 = (# of connections set for data source + # of connections
in session manager) x # of clones.
After calculating the MaxAppls settings for the WebSphere 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
- Description: 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 was 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
DB2 reorgchk
- Description: 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 issue runstats on all user and system tables for
the database to which you are currently connected. Rebind packages using
the bind command. If runstats exists, issue the db2 -v "select
tbname, nleaf, nlevels, stats_time from sysibm.sysindexes" command on
DB2 CLP. If no runstats exist, nleaf and nlevels are -1, and stats_time
has an empty entry , for example "-". If runstats was previously done, the
real-time stamp from completion of the runstats also displays under stats_time.
If you think the time shown for the previous runstats is too old, execute
runstats again.
- Default value: None
- Recommended value: None
DB2 MinCommit
- Description: Delays the writing of log records to a disk until
a minimum number of commits is performed, reducing the database manager overhead
associated with writing log records. For example, if MinCommit is set to 2,
a second commit causes output to the transaction log for the first and second
commits. The exception occurs when a one-second timeout forces the first commit
to be output if a second commit does not ocurr within one second.
In test
applications, up to 90% of the disk input and output was related to the DB2
transaction log. Changing MinCommit from 1 to 2 reduced the results to 45%.
Adjust
this parameter if the disk input and output wait is more than 5% and there
is DB2 transaction log activity from multiple sources. When a lot of activity
occurs from multiple sources, it is less likely that a single commit has to
wait for another commit, or the one second timeout. Do not adjust this parameter
if you have an application with a single thread performing a series of commits
because each commit can hit the one second delay.
- How to view or set:
- Issue the DB2 get db cfg for xxxxxxcommand, where xxxxxx is
the name of the application database, to list database configuration parameters.
- Look for "Group commit count (MINCOMMIT)".
- Set a new value by issuing the DB2 update db cfg for xxxxxx using
mincommit n command, where xxxxxx is the name of the application
database and n is a value between 1 and 25 inclusive.
The new setting takes effect immediately.
The following are
several metrics that are related to DB2 MinCommit:
- The disk input and output wait can be observed on AIX with the command vmstat
5. This shows statistics every 5 seconds. Look for the wa column
under the CPU area.
- The percentage of time a disk is active can be observed on AIX with the
command iostat 5. This shows statistics every 5 seconds. Look for the %tm_act column.
- The DB2 get snapshot for db on xxxxxx command , where xxxxxx is
the name of the application database, shows counters for log pages read and
log pages written.
- Default value: 1.
- Recommended value: 1 or 2, if the circumstance permits.
DB2 Deadlock Event Monitor
.
Searchable topic ID:
rprf_db2parameters
Last updated: Jun 21, 2007 8:07:48 PM CDT
WebSphere Business Integration Server Foundation, Version 5.0.2
http://publib.boulder.ibm.com/infocenter/wasinfo/index.jsp?topic=/com.ibm.wasee.doc/info/ee/ae/rprf_db2parameters.html