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
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 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|
|Distributed Data Facility (DDF)|
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.
Last updated by Don Deese on 11/07/06.