DB2 Component of CPExpert

Since IBM's 1983 introduction of DATABASE 2 (DB2), it has become one of the world's most popular relational data base management systems. Industry sources report that IBM has more than one million licensed copies of DB2 on all supported platforms.

With the announcement of DB2 Universal Database Server for OS/390 Version 6 in 1998, IBM celebrated its 15th anniversary of the product and the tenth release of DB2 since its introduction on the MVS platform.

Although DB2 has achieved tremendous acceptance in the marketplace and provides extraordinary features for its users, many users have found that the performance of DB2 can be frustrating and that DB2 can consume significant system resources without proper tuning.

Most sites have discovered that continual monitoring of performance constraints is required in order for DB2 to keep performing at a high level.

Unfortunately, many sites do not have individuals with DB2 performance tuning expertise, and users typically find that a significant effort is required to monitor DB2's performance continually.


The DB2 Component of CPExpert was developed to help sites monitor DB2 performance, to identify problems automatically, and to suggest solutions to the problems.

The DB2 Component evaluates the performance of DB2 for OS/390 and DB2 UDB for Z/OS. The DB2 Component automatically applies most of the DB2 analysis techniques documented in IBM's DB2 for OS/390 and DB2 UDB for z/OS Administration Guides, supplemented by techniques and guidance contained in IBM Redbooks, by papers presented at various technical conferences, and by IBM's suggestions in the DSNWMGS macros.

The DB2 Component processes the DB2 interval statistics data, DB2 accounting data, and selected DB2 trace data processed by MXG® or SAS/ITSV® and placed into SAS® data sets.

The DB2 Component analyzes DB2 performance in the following major areas:  

 

Buffer pools (including virtual pools, hiperpools, and group buffer pools)
EDM pool and RID pool
Parallel processing
Lock Manager
Distributed Data Facility (DDF)
Log Manager
Data sharing
ZPARMS specifications

After analyzing DB2 data, the DB2 Component reports problems or potential problems that restrict the performance of the DB2 subsystem. Sample findings for which solutions are suggested include:  
Threads were queued at CREATE
Buffer pools are too large for the available processor storage
DWQT and VDWQT might be too large
DWQT, VDWQT, or VPSEQT might be too small
Read accesses delayed because of unavailable resources
Parallel groups were reduced due to buffer shortage
Dynamic sequential prefetch was high
Prefetch I/O streams were denied, shortage of buffers
A low percent of data base descriptors were found in EDM pool
Waits were caused by unavailable output log buffer
The size of EDM pool could be reduced
Locks were escalated to shared mode
High accumulated wait time was caused by page latch contention
Conversation requests were queued by DDF waiting for allocation
Network problems might exist
Sequential prefetch was disabled because of buffer shortage
The prefetch quantity was reduced to one-quarter of the normal
Suspends occurred because of false lock contention
The parallelism coordinator had to bypass a DB2
Coupling facility read requests could not complete
Conversations were deallocated, ZPARM limit was reached
Database access thread was queued, ZPARM limit was reached
Pool expansion failed because of insufficient virtual storage
There was excessive Class 24 (EDM LRU) latch contention
Available virtual storage was dangerously low for DBM1

The analysis done by the DB2 Component can be controlled using guidance variables. Default values are provided for the guidance variables, but the defaults can be changed to suit management objectives or workload priorities. Additionally, guidance can be provided for individual buffer pools and individual group buffer pools.

The DB2 Component includes an extensive user manual. The DB2 Component User Manual (1) suggests the likely effect of each finding on performance of the DB2 subsystem being evaluated, (2) explains why the finding was made, (3) suggests alternatives to correct the problem, and (4) provides specific references in IBM documents where the problem is described.

Sample "rule" description from the DB2 Component User Manual.

Return to main page

Last updated by Don Deese on 11/07/06.