54 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI
2.5 Key performance knobs
Every organization’s configuration, workload, and business application priorities
tend to be unique, which correspondingly requires a custom approach to
configuring, monitoring, and tuning their DB2 environment.
DB2 provides a number of tuning knobs for customizing an organization’s DB2
environment in order to deliver good performance. This subsection lists and
briefly describes key performance knobs available to the DBA to manage their
environment. These performance knobs are:
򐂰 Database manager configuration parameters
򐂰 Database configuration parameters
򐂰 DB2 registry and environment variables
DB2 UDB version 8 provides a Configuration Advisor Wizard and an
autoconfigure command described in “Configuration Advisor and
AUTOCONFIGURE” on page 54 to help the DBA define optimal database
manager and database configuration parameters for their environment.
The Configuration Advisor sets database manager configuration parameters in
addition to database configuration parameters, and should only be run against a
database that is the only database associated with that DB2 instance. The
Configuration Advisor only recommends values for a little over 30 of hundreds of
database manager and database configuration parameters and DB2 registry and
environment variables available for configuring by the DBA.
For detailed information on database manager and database configuration
parameters, and DB2 registry and environment variables, refer to DB2 UDB
Administration Guide: Performance, SC09-4821.
2.5.1 Configuration Advisor and AUTOCONFIGURE
The Configuration Advisor Wizard can be invoked from the Control Center using
Tools -> Wizards -> Configuration Advisor. The wizard then presents a series
of screens with questions regarding your environment and application workload,
Note: Recommendations and monitoring guidelines for these parameters are
discussed in Chapter 3, “Application design and system performance
considerations” on page 107.
Important: We strongly recommend the use of the Configuration Advisor or
AUTOCONFIGURE command to tune your system.
Chapter 2. DB2 UDB architecture overview 55
and generates the screen of suggested parameter settings shown in shown in
Figure 2-10 on page 56.
Example 2-1 on page 57 shows the script generated for the sample database by
the Configuration Advisor using most (but not all) of the default settings.
Note: Configuration Advisor actually executes on the target DB2 system, and
performs auto discovery of the target DB2 environment’s available resources
before coming up with its recommendations for the various configuration
parameters.
56 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI
Figure 2-10 Configuration Advisor wizard
Chapter 2. DB2 UDB architecture overview 57
Example 2-1 Database manager and database configuration parameters set
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING APP_CTL_HEAP_SZ 128;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING BUFFPAGE 118915;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING CATALOGCACHE_SZ 343;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING CHNGPGS_THRESH 60;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING DBHEAP 600;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING LOCKLIST 50;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING LOGBUFSZ 65;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING LOGFILSIZ 1024;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING LOGPRIMARY 3;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING LOGSECOND 0;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING MAXAPPLS 40;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING MAXLOCKS 60;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING MINCOMMIT 1;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING NUM_IOCLEANERS 1;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING NUM_IOSERVERS 4;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING PCKCACHESZ 859;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING SOFTMAX 120;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING SORTHEAP 192;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING STMTHEAP 2048;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING DFT_DEGREE 1;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING DFT_PREFETCH_SZ 32;
UPDATE DATABASE CONFIGURATION FOR SAMPLE USING UTIL_HEAP_SZ 39638;
UPDATE DATABASE MANAGER CONFIGURATION USING SHEAPTHRES 1140;
UPDATE DATABASE MANAGER CONFIGURATION USING INTRA_PARALLEL OFF;
UPDATE DATABASE MANAGER CONFIGURATION USING MAX_QUERYDEGREE 1;
UPDATE DATABASE MANAGER CONFIGURATION USING MAXAGENTS 400;
UPDATE DATABASE MANAGER CONFIGURATION USING NUM_POOLAGENTS 400;
UPDATE DATABASE MANAGER CONFIGURATION USING NUM_INITAGENTS 0;
UPDATE DATABASE MANAGER CONFIGURATION USING FCM_NUM_BUFFERS 4096;
UPDATE DATABASE MANAGER CONFIGURATION USING FCM_NUM_RQB 0;
CONNECT TO SAMPLE;
ALTER BUFFERPOOL IBMDEFAULTBP SIZE 118915;
COMMIT;
CONNECT
RESET;
The autoconfigure command delivers the same results as the Configuration
Advisor wizard, by accepting the same input via options listed and described in
Table 2-10 on page 58. autoconfigure calculates and displays the optimum
values for the buffer pool size, database configuration, and database manager
configuration parameters, with the option of applying these recommended values
immediately.
The AUTOCONFIGURE option may also be used with the CREATE DATABASE
command to configure databases as soon as they are created.
For more details on the autoconfigure command, refer to DB2 UDB Command
Reference, SC09-4828.
Note: These recommendations should be validated through actual
measurements in regression test environments before committing the
changes in the production environment.

Get DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.