Introduction

                                                                            
                                                                            
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001              
                Analyzed by Version 11.2.0 of CPExpert(tm)                  
          (C) Copyright 2001, Computer Management Sciences, Inc.            
                               PAGE:    1                                   
                                                                            
                              INTRODUCTION                                  
                                                                            
This report consists of three parts:                                        
                                                                            
 - This Introduction and a page of general run statistics.                  
                                                                            
 - A listing of any rules that will not be produced, either because         
   CPExpert determined that required information was not present, or        
   because the rules were turned off in USOURCE(DB2GUIDE).                  
                                                                            
 - Findings resulting from the analysis of DB2 statistics.                  
                                                                            
   - Findings in the DB2-100(series) relate to general DB2 areas.           
                                                                            
   - Findings in the DB2-200(series) relate to local buffer pools.          
                                                                            
   - Findings in the DB2-300(series) relate to Lock Manager areas.          
                                                                            
   - Findings in the DB2-400(series) relate to DDF areas.                   
                                                                            
   - Findings in the DB2-500(series) relate to Log Manager areas.           
                                                                            
   - Findings in the DB2-600(series) relate to data sharing areas.          
                                                                            
   It is important to realize that these findings normally identify         
   a POTENTIAL problem.  Your systems programming staff must decide         
   whether the findings (and their associated recommendations) make         
   sense in your environment.  For example, your systems programming        
   staff might have deliberately selected certain parameter values.         
   The values might be appropriate for your installation and your           
   management objectives, even though CPExpert might produce a rule         
   indicating that there is a potential problem with the parameter.         
                                                                            
                                                                            
When findings are produced by the DB2 Component, please refer to the        
detailed description of the rule contained in the DB2 Component User        
Manual.  The User Manual will describe why the finding was made, give       
an assessment of the overall performance impact of the finding, and         
discuss alternatives that may be implemented to solve the problem.          
                                                                            
Please call Computer Management Sciences at (703) 922-7027 or e-mail        
to Don_Deese@cpexpert.com if you have any questions or if you have          
suggestions for product improvement.                                        

 


 
Rules that will be suppressed

                                                                            
                                                                            
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001              
                Analyzed by Version 11.2.0 of CPExpert(tm)                  
          (C) Copyright 2001, Computer Management Sciences, Inc.            
                               PAGE:    2                                   
                                                                            
  The following rules will be suppressed.  A rule is suppressed             
  when (1) the required data variables do not exist in your                 
  performance data base, (2) you are not using the DB2 feature              
  being evaluated, (3) the rule does not apply to the version               
  of DB2 being analyzed, or (4) you have 'turned off' the rule in           
  USOURCE(DB2GUIDE).                                                        
                                                                            
                                                                            
    RULE: DB2-644   QWPAASST=0                                              
    RULE: DB2-651   R744SCN=0  R744STRC=0 R744SNAM=0                        
    RULE: DB2-652   R744SCN=0  R744STRC=0 R744SFCN=0 R744SNAM=0             
    RULE: DB2-660   R744SSTM=0 R744SSRC=0 R744SNAM=0                        
    RULE: DB2-661   R744SATM=0 R744SARC=0 R744SNAM=0                        
    RULE: DB2-665   R744CDER=0 R744CXDR=0 R744SNAM=0                        
    RULE: DB2-671   R744CDER=0 R744CXDR=0 R744SNAM=0                        
                                                                            
    RULE: DB2-310   TURNED OFF                                              

 


 
Guidance variables

                                                                            
                                                                            
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001              
                SYSTEM: *ALL           DB2 SUBSYSTEM: *ALL                  
                Analyzed by Version 11.2.0 of CPExpert(tm)                  
          (C) Copyright 2001, Computer Management Sciences, Inc.            
                               PAGE:    3                                   
                                                                            
THE FOLLOWING GUIDANCE WAS PROVIDED TO THE DB2                              
COMPONENT DURING THIS EXECUTION OF CPEXPERT.                                
                                                                            
DB2DATES = 01FEB1991       * START DATE FOR DATA ANALYSIS                   
DB2TIMES = 08:00:00        * START TIME FOR DATA ANALYSIS                   
DB2DATEE = 31DEC2199       * END DATE FOR DATA ANALYSIS                     
DB2TIMEE = 17:59:59        * END TIME FOR DATA ANALYSIS                     
DB2DAT2S = 0               * DEFAULT SECOND SELECTION DATE - START          
DB2TIM2S = 0               * DEFAULT SECOND SELECTION TIME - START          
DB2DAT2E = 0               * DEFAULT SECOND SELECTION DATE - END            
DB2TIM2E = 0               * DEFAULT SECOND SELECTION TIME - END            
SHIFT    = N               * START AND END TIMES DO NOT APPLY TO A SHIFT    
SYSTEM   = *ALL            * SPECIFY SYSTEM TO PROCESS (*ALL = ALL)         
DB2SID   = *ALL            * SPECIFY DB2 SID TO PROCESS (*ALL = ALL)        
HTML=Y;  = Y=Y;            * USE SAS ODS FEATURES                           
HTMLLINK = Y               * LINK TO CPEXPERT DOCUMENTATION                 
FRAME    = DB2FRAME.HTM    * USE GENERIC ODS FRAME NAME                     
CONTENTS = DB2CONTENTS.HTM * USE GENERIC ODS CONTENTS NAME                  
BODY     = DB2BODY.HTM     * USE GENERIC ODS BODY NAME                      
PATH     =                 * DO NOT SPECIFY PATH FOR ODS OUTPUT             
PRODTEST = PROD            * PRODUCTION OR TEST SUBSYSTEM                   
DB2ACCTX = N               * DB2 ACCOUNTING DATA AVAILABILITY               
T102S106 = N               * DB2 ZPARMS DATA AVAILABILITY                   
T102S172 = N               * DB2 DEADLOCK TRACE RECORDS AVAILABILITY        
T102S196 = N               * DB2 TIMEOUT TRACE RECORDS AVAILABILITY         
BPGUIDE  = N               * GUIDANCE PROVIDED FOR INDIVIDUAL BUFFER        
GBPGUIDE = N               * GUIDANCE PROVIDED FOR GLOBAL BUFFERS           
DB2LIB   = PDBLIB          * SAS LIBRARY CONTAINING DB2 DATA                
DB2STATS = PDBLIB.DB2STATS * SPECIFY DB2STATS SAS LIB.FILE                  
DB2STATB = PDBLIB.DB2STATB * SPECIFY DB2STATB SAS LIB.FILE                  
DB2STAT2 = PDBLIB.DB2STAT2 * SPECIFY DB2STAT2 SAS LIB.FILE                  
DB2GBPAT = PDBLIB.DB2GBPAT * SPECIFY DB2GBPAT SAS LIB.FILE                  
DB2GBPST = PDBLIB.DB2GBPST * SPECIFY DB2GBPST SAS LIB.FILE                  
DB2STATR = PDBLIB.DB2STATR * SPECIFY DB2STATR SAS LIB.FILE                  
DB2ACCT  = PDBLIB.DB2ACCT  * SPECIFY DB2ACCT SAS LIB.FILE                   
DB2Rnnn  = OFF             * EXAMPLE: TURN OFF DB2-nnn RULE                 
DB2R310  = 0               * EXAMPLE: TURN OFF DB2-nnn RULE                 
BUFFPGRT = 100             * ACCEPTABLE PAGE-IN RATE, ALL DB2 BUFFER POOLS  
PAGERATE = 10                                                               
* ACCEPTABLE PAGE-IN RATE, INDIVIDUAL BUFFER POOL                           
PCTCBA   = 0.500           * PERCENT BUFFERS AVAILABLE                      
PCTCTL   = 0.950           * PERCENT CURSOR TABLE SECTIONS FOUND            
PCTDBDL  = 0.990           * PERCENT DATA BASE DESCRIPTOR SECTIONS FOUND    
PCTDPF   = 0.00            * PERCENT DYNAMIC SEQUENTIAL PREFETCH            
PCTEDM   = 0.250           * PERCENT FREE PAGES IN EDM POOL                 
PCTESC   = 0.00            * PERCENT LOCK ESCALATION WAS NOT EFFECTIVE      
PCTFLAM  = 0.00            * PERCENT FAILED LOOK-AHEAD TAPE MOUNTS          
PCTPTL   = 0.950           * PERCENT PACKAGE TABLE SECTIONS FOUND           
PCTPWS   = 0.500           * PERCENT PAGES WRITTEN TO DASD/PAGES UPDATED    
PCTRAND  = 0.950           * PERCENT GETPAGE FROM RANDOM REQUESTORS         
PCTRSAT  = 0.00            * PERCENT RECONNECTION ATTEMPTS SUCCEEDED        
PCTSEQ   = 0.950           * PERCENT GETPAGE FROM SEQUENTIAL REQUESTORS     
PCTSLOCK = 0.5             * PERCENT LOCK REQUESTS THAT WERE SUSPENDED      
Q3STCTHW = 0               * THREAD REQUESTS WERE QUEUED AT CREATE          
Q3STMEOM = 0               * END-OF-MEMORY (NON-DB2 TASK MVS DELETED)       
Q3STMEOT = 0               * END-OF-TASK (NON-DB2 TASK ABENDS)              
QBGLAD   = 0               * CF READS PREFETCH DATA RETURNED                
QBGLAN   = 0               * CF PREFETCH READS, NO DATA, NO DIRECTORY       
QBGLAR   = 0               * CF PREFETCH READS, NO DATA, DIRECTORY CREATED  
QBGLCN   = 0               * TIMES WHEN CASTOUT ENGINE NOT AVAILABLE        
QBGLRF   = 0               * CF READS FAILED DUE LACK OF CF STORAGE         
                                                                            
                                                                            
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001              
                SYSTEM: *ALL           DB2 SUBSYSTEM: *ALL                  
                Analyzed by Version 11.2.0 of CPExpert(tm)                  
          (C) Copyright 2001, Computer Management Sciences, Inc.            
                               PAGE:    4                                   
                                                                            
QBGLWF   = 0               * CF WRITES FAILED DUE LACK OF CF STORAGE        
QBGLSU   = 0               * TIMES WHEN CF WRITE ENGINE WAS AVAILABLE       
QBSTARF  = 0               * UNSUCCESSFUL HP TO VP READS, USING ASYNC ADMF  
QBSTAWF  = 0               * UNSUCCESSFUL HP TO VP WRITES, USING ASYNC ADMF 
QBSTCBA  = 50              * PERCENT ACTIVE BUFFERS AT INTERVAL END         
QBSTDMC  = 0               * DATA MANAGER THRESHOLD REACHED                 
QBSTHRF  = 0               * UNSUCCESSFUL HP TO VP MVPG READS               
QBSTHWF  = 0               * UNSUCCESSFUL VP TO HP PAGE WRITES              
QBSTIMW  = 0               * NUMBER OF IMMEDIATE WRITES                     
QBSTJIS  = 0               * PREFETCH I/O STREAMS DENIED                    
QBSTMIG  = 0               * TIMES MIGRATED DATA SETS WERE ENCOUNTERED      
QBSTNGT  = 0               * UNSUCCESSFUL GETPAGE REQUESTS                  
QBSTPL1  = 0               * PREFETCH REDUCED TO  OF NORMAL                
QBSTPL2  = 0               * PREFETCH REDUCED TO 1/4 OF NORMAL              
QBSTPQF  = 0               * TIMES I/O PARALLELISM WAS DOWNGRADED           
QBSTREE  = 0               * PREFETCH DISABLED NO READ ENGINE               
QBSTRTO  = 0               * TIMES RECALL TIMEOUTS OCCURRED                 
QBSTSPD  = 0               * SEQ PREFEETCH REQ DISABLED UNAVAIL BUFFER      
QBSTWEE  = 0               * WRITE ENGINE NOT AVAILABLE FOR ASYNC I/O       
QBSTWFD  = 0               * WORKFILES DENIED DURING SORT                   
QBSTWFF  = 0               * SORT INEFFICIENT (BUFFER SHORTAGE)             
QBSTWFL  = 0               * NUMBER OF MERGE PASSES (CONTROLS DB2-226)      
QBSTWFR  = 0               * NUMBER OF MERGE PASSES (CONTROLS DB2-226)      
QBSTWIO  = 16              * ASYNC WRITE I/O (CONTROLS DB2-225)             
QBSTWKPD = 0               * PREFETCH ABORT - ZERO*PREFETCH QTY             
QDSTQCRT = 0               * CONVERSATIONS DEALLOC DUE TO ZPARM LIMIT       
QDSTQDBT = 0               * DB ACCESS QUEUED DUE TO MAX REMOTE THREADS     
QISEFAIL = 0               * FAILURES DUE TO POOL FULL                      
QISTCOLS = 0               * COLS BYPASSED INVALIDATED DB2 SERVICE          
QISTRLLM = 0               * RID TERMINATED, ENTRIES GT CALCULATED LIMIT    
QISTRMAX = 0               * RID TERMINATED, MAXIMUM STORAGE                
QISTRPLM = 0               * RID TERMINATED, ENTRIES GT PHYSICAL LIMIT      
QISTRSTG = 0               * RID TERMINATED, INSUFFICIENT STORAGE           
QJSTALR  = 0               * ARCHIVE LOG READ ALLOCATIONS                   
QJSTALW  = 2               * ARCHIVE LOG WRITE ALLOCATIONS                  
QJSTRACT = 0               * READS SATISFIED FROM ACTIVE LOG DATA SET       
QJSTRARH = 0               * READS SATISFIED FROM ARCHIVE LOG DATA SET      
QJSTTVC  = 0               * READ ACCESSES DELAYED, TAPE VOLUME CONTENTION  
QJSTWTB  = 0               * WAITS DUE TO UNAVAILABLE WRITE BUFFER          
QJSTWUR  = 0               * READ ACCESSES DELAYED, UNAVAILABLE RESOURCES   
QLSTCNVQ = 0               * CONV REQS QUEUED BY DDF, WAITING FOR ALLOC     
QLSTINDT = 0               * THREADS INDOUBT WITH REMOTE AS COORDINATOR     
QTXADEA  = 0               * LOCK DEADLOCK COUNT                            
QTXALES  = 0               * LOCKS ESCALATED TO SHARED MODE                 
QTXALEX  = 0               * LOCKS ESCALATED TO EXCLUSIVE MODE              
QTXASLOC = 0               * SUSPEND COUNT,  LOCK CONFLICT                  
QTXATIM  = 0               * LOCK TIMEOUT COUNT                             
QWACAWTP = 0               * WAIT TIME DUE TO PAGE LATCH CONTENTION         
QWSBSBUF = 0               * BUFFER ERRORS, SMF DESTINATION                 
QXCALLAB = 0               * STORED PROCEDURE ABENDS                        
QXCALLRJ = 0               * SQL CALLS REJECTED                             
QXCALLTO = 0               * SQL CALLS TIMED OUT WAITING SCHEDULE           
QXCOORNO = 0               * PARALLEL GROUPS EXECUTED ON A SINGLE DB2       
QXDEGBUF = 0               * PARALLEL GROUPS FELL BACK BUFFERS              
QXDEGENC = 0               * PARALLEL GROUPS EXECUTED IN SEQUENTIAL         
QXISORR  = 0               * GROUPS EXECUTED ON SINGLE DB2, REPEAT READ     
QXREDGRP = 0               * PARALLEL GROUPS REDUCED DUE TO BUFFERS         
QXSTDEXP = 0               * DB2 DISCARDED PREPARED STATEMENT               
                                                                            
                                                                            
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001              
                SYSTEM: *ALL           DB2 SUBSYSTEM: *ALL                  
                Analyzed by Version 11.2.0 of CPExpert(tm)                  
          (C) Copyright 2001, Computer Management Sciences, Inc.            
                               PAGE:    5                                   
                                                                            
QXXCSKIP = 0               * BYPASSED DB2, NOT ENOUGH BUFFERS               

 


 
Summary of analysis performed

                                                                            
                                                                            
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001              
                Analyzed by Version 11.2.0 of CPExpert(tm)                  
          (C) Copyright 2001, Computer Management Sciences, Inc.            
                               PAGE:    6                                   
                                                                            
                                                                            
       SUMMARY OF DB2 COMPONENT OPERATION, EXECUTED ON 15JUN2001            
                                                                            
                                                                            
                                                                            
                                                                            
DB2 STATISTICS INTERVALS PROCESSED FROM MXG DB2STATS INPUT FILE:        21  
                                                                            
INTERVALS SELECTED BASED UPON SYSTEM/START/END CRITERIA:                21  
                                                                            
TOTAL RULE RECORDS WRITTEN BY THE DB2 COMPONENT:                       233  

 


 
System: SYS1DB1P, Overview of rule results

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS1           DB2 SUBSYSTEM: DB1P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:    7                                                           
                                                                                                    
                                                                                                    
                        OVERVIEW OF RULE RESULTS                                                    
                                                                                                    
                                                                                                    
                     DB2-120  DB2-181  DB2-216  DB2-224  DB2-226  DB2-235  DB2-311  DB2-512         
                     ------------------------------------------------------------------------       
 20MAR2001:13:13:44                                                                                 
 20MAR2001:13:14:00     X        X                 X                 X                              
 20MAR2001:13:28:41                                                                                 
 20MAR2001:13:29:00     X        X                 X        X        X                              
 20MAR2001:13:43:37                                                                                 
 20MAR2001:13:44:00     X        X        X        X        X        X                              
 20MAR2001:13:58:34                                                                                 
 20MAR2001:13:59:00     X        X        X        X                 X        X                     
 20MAR2001:14:13:31                                                                                 
 20MAR2001:14:14:00     X        X        X        X        X        X                              
 20MAR2001:14:28:00     X        X        X        X                 X        X        X            
 20MAR2001:14:28:27                                                                                 
 20MAR2001:14:43:00     X        X                 X                 X                              
 20MAR2001:14:43:24                                                                                 

 


 
System: SYS2DB2P, Overview of rule results

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS2           DB2 SUBSYSTEM: DB2P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:    8                                                           
                                                                                                    
                                                                                                    
                        OVERVIEW OF RULE RESULTS                                                    
                                                                                                    
                                                                                                    
                     D       D       D       D       D       D       D       D       D              
                     B       B       B       B       B       B       B       B       B              
                     2       2       2       2       2       2       2       2       2              
                     -       -       -       -       -       -       -       -       -              
                     1       1       2       2       2       2       3       5       5              
                     2       8       1       2       2       3       1       0       1              
                     0       1       6       4       6       5       1       2       2              
                     ------------------------------------------------------------------------       
 20MAR2001:13:04:00  X       X               X       X       X               X                      
 20MAR2001:13:04:19                                                                                 
 20MAR2001:13:19:00  X       X               X       X       X                       X              
 20MAR2001:13:19:16                                                                                 
 20MAR2001:13:34:00  X       X               X       X       X                                      
 20MAR2001:13:34:12                                                                                 
 20MAR2001:13:49:00  X       X               X       X       X                                      
 20MAR2001:13:49:09                                                                                 
 20MAR2001:14:04:00  X       X               X       X       X                                      
 20MAR2001:14:04:06                                                                                 
 20MAR2001:14:19:00  X       X       X       X       X       X                                      
 20MAR2001:14:19:02                                                                                 
 20MAR2001:14:33:58                                                                                 
 20MAR2001:14:34:00  X       X               X       X       X       X                              

 


 
System: SYS3DB3P, Overview of rule results

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS3           DB2 SUBSYSTEM: DB3P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:    9                                                           
                                                                                                    
                                                                                                    
                        OVERVIEW OF RULE RESULTS                                                    
                                                                                                    
                                                                                                    
                     D       D       D       D       D       D       D       D       D              
                     B       B       B       B       B       B       B       B       B              
                     2       2       2       2       2       2       2       2       2              
                     -       -       -       -       -       -       -       -       -              
                     1       1       2       2       2       3       3       3       5              
                     2       8       2       2       3       0       0       1       1              
                     0       1       4       6       5       3       5       1       2              
                     ------------------------------------------------------------------------       
 20MAR2001:13:02:49                                                                                 
 20MAR2001:13:03:00  X       X       X       X       X                                              
 20MAR2001:13:17:45                                                                                 
 20MAR2001:13:18:00  X       X       X       X       X                                              
 20MAR2001:13:32:43                                                                                 
 20MAR2001:13:33:00  X       X       X       X       X                                              
 20MAR2001:13:47:39                                                                                 
 20MAR2001:13:48:00  X       X       X       X       X                               X              
 20MAR2001:14:02:36                                                                                 
 20MAR2001:14:03:00  X       X       X       X       X                       X                      
 20MAR2001:14:17:32                                                                                 
 20MAR2001:14:18:00  X       X       X       X       X                                              
 20MAR2001:14:32:00  X       X       X       X       X       X       X       X                      
 20MAR2001:14:32:28                                                                                 

 


 
DB2 Subystem DB1P, General findings

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS1           DB2 SUBSYSTEM: DB1P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   10                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-120: INVALID SELECT PROCEDURE WAS ENCOUNTERED                                           .
                                                                            
   A select procedure is made invalid by applying DB2 maintenance that                              
   directly affects the select procedure.  Invalid select procedures are                            
   bypassed by DB2, and this can cause some degradation in performance.                             
   Plans and packages with invalid select procedures continue to function                           
   correctly but incur some performance degradation. You should consider                            
   running REBIND for any plan or package that has encountered invalid                              
   select procedures to eliminate this degradation.  An invalid select                              
   procedure was encountered during the intervals shown below:                                      
                                                                                                    
                                          TIMES INVALID SELECT                                      
   MEASUREMENT INTERVAL                PROCEDURES WERE ENCOUNTERED                                  
   13:14-13:29, 20MAR2001                            96                                             
   13:29-13:44, 20MAR2001                           528                                             
   13:44-13:59, 20MAR2001                         1,238                                             
   13:59-14:14, 20MAR2001                           445                                             
   14:14-14:28, 20MAR2001                         1,545                                             
   14:28-14:43, 20MAR2001                           220                                             
   14:43-14:58, 20MAR2001                         1,123                                             
                                                                                                    
                                                                                                    
RULE DB2-181: RID LIST PROCESSING TERMINATED                                                     .
                                                                            
   RID list processing was terminated because the number of RID entries                             
   was greater than the limit of MAX(25% of table size, number of RIDs                              
   that can fit into the guaranteed number of RID blocks).  This finding                            
   occurred during the intervals shown below:                                                       
                                                                                                    
                                   TIMES RID LIST PROCESSING                                        
   MEASUREMENT INTERVAL                 WAS TERMINATED                                              
   13:14-13:29, 20MAR2001                       17                                                  
   13:29-13:44, 20MAR2001                        8                                                  
   13:44-13:59, 20MAR2001                       31                                                  
   13:59-14:14, 20MAR2001                       45                                                  
   14:14-14:28, 20MAR2001                       79                                                  
   14:28-14:43, 20MAR2001                       56                                                  
   14:43-14:58, 20MAR2001                       84                                                  

 


 
DB2 Subsystem DB1P, Local Buffer Pool Findings

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS1           DB2 SUBSYSTEM: DB1P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   11                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE                                                    .
                                                                            
   Buffer Pool 0: CPExpert believes that the Sequential Steal Threshold                             
   (VPSEQT) value might be too large for the intervals shown below.  Most                           
   of the GETPAGE requests for this buffer pool were issued by random                               
   requestors.  However, the VPSEQT threshold was not specified to favor                            
   random access significantly over sequential access.  Please ignore this                          
   finding (or "turn off" this rule for this buffer pool) unless the                                
   finding is consistently produced during intervals when critical work is                          
   being processed.                                                                                 
                                                                                                    
                                BUFFERS      GETPAGE     PERCENT   VPSEQT                           
   MEASUREMENT INTERVAL        ALLOCATED    REQUESTS      RANDOM    VALUE                           
   13:14-13:29, 20MAR2001       10,000       193,184       100        80                            
   13:29-13:44, 20MAR2001       10,000       287,792       100        80                            
   13:44-13:59, 20MAR2001       10,000       180,320       100        80                            
   14:14-14:28, 20MAR2001       10,000        81,088        96        80                            
   14:28-14:43, 20MAR2001       10,000        33,824        98        80                            
                                                                                                    
                                                                                                    
RULE DB2-216: PAGE FAULT RATE WAS HIGH FOR INDIVIDUAL BUFFER POOL                                .
                                                                            
   Buffer Pool 1: The page fault rate for read and write I/O was larger                             
   than 10 pages per second. This situation occurred for Buffer Pool 1                              
   during the intervals shown below:                                                                
                                                                                                    
                             BUFFERS     PAGE-IN FOR   PAGE-IN FOR  PAGE                            
   MEASUREMENT INTERVAL     ALLOCATED      READ I/O     WRITE I/O   RATE                            
   13:44-13:59, 20MAR2001    80,000         11,090          39      12.4                            
   13:59-14:14, 20MAR2001    80,000         11,478         190      13.0                            
   14:14-14:28, 20MAR2001    80,000         15,633          35      17.5                            
   14:28-14:43, 20MAR2001    80,000         19,754         216      22.3                            
                                                                                                    
                                                                                                    
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE                                                    .
                                                                            
   Buffer Pool 1: CPExpert believes that the Sequential Steal Threshold                             
   (VPSEQT) value might be too large for the intervals shown below.  Most                           
   of the GETPAGE requests for this buffer pool were issued by random                               
   requestors.  However, the VPSEQT threshold was not specified to favor                            
   random access significantly over sequential access.  Please ignore this                          
   finding (or "turn off" this rule for this buffer pool) unless the                                
   finding is consistently produced during intervals when critical work is                          
   being processed.                                                                                 
                                                                                                    
                                BUFFERS      GETPAGE     PERCENT   VPSEQT                           
   MEASUREMENT INTERVAL        ALLOCATED    REQUESTS      RANDOM    VALUE                           
   13:14-13:29, 20MAR2001       80,000     4,029,440        98        80                            
   13:29-13:44, 20MAR2001       80,000     3,317,760        96        80                            
   13:44-13:59, 20MAR2001       80,000     3,363,840        96        80                            
   13:59-14:14, 20MAR2001       80,000     5,806,080        99        80                            
   14:14-14:28, 20MAR2001       80,000     5,949,440        98        80                            
   14:28-14:43, 20MAR2001       80,000     4,342,784        96        80                            
   14:43-14:58, 20MAR2001       80,000     5,330,944        97        80                            
                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS1           DB2 SUBSYSTEM: DB1P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   12                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 1: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 1 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:14-13:29, 20MAR2001     3,228         55,888       59,116     94.5                            
   13:29-13:44, 20MAR2001     4,464         28,992       33,456     86.7                            
   13:44-13:59, 20MAR2001     4,630         38,752       43,382     89.3                            
   13:59-14:14, 20MAR2001     2,370         23,376       25,746     90.8                            
   14:14-14:28, 20MAR2001     4,492         47,152       51,644     91.3                            
   14:28-14:43, 20MAR2001     5,844         33,664       39,508     85.2                            
   14:43-14:58, 20MAR2001     4,576         46,384       50,960     91.0                            
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 2: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 2 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:14-13:29, 20MAR2001     7,688         96,800      104,488     92.6                            
   13:29-13:44, 20MAR2001    10,936         81,184       92,120     88.1                            
   13:44-13:59, 20MAR2001    28,056        134,608      162,664     82.8                            
   13:59-14:14, 20MAR2001    15,488         50,016       65,504     76.4                            
   14:14-14:28, 20MAR2001    10,376         64,336       74,712     86.1                            
   14:28-14:43, 20MAR2001    11,136         61,728       72,864     84.7                            
   14:43-14:58, 20MAR2001    13,576         50,896       64,472     78.9                            
                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS1           DB2 SUBSYSTEM: DB1P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   13                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE                                                    .
                                                                            
   Buffer Pool 3: CPExpert believes that the Sequential Steal Threshold                             
   (VPSEQT) value might be too large for the intervals shown below.  Most                           
   of the GETPAGE requests for this buffer pool were issued by random                               
   requestors.  However, the VPSEQT threshold was not specified to favor                            
   random access significantly over sequential access.  Please ignore this                          
   finding (or "turn off" this rule for this buffer pool) unless the                                
   finding is consistently produced during intervals when critical work is                          
   being processed.                                                                                 
                                                                                                    
                                BUFFERS      GETPAGE     PERCENT   VPSEQT                           
   MEASUREMENT INTERVAL        ALLOCATED    REQUESTS      RANDOM    VALUE                           
   13:14-13:29, 20MAR2001       40,000        98,816        95        80                            
   13:29-13:44, 20MAR2001       40,000       648,704        97        80                            
   13:44-13:59, 20MAR2001       40,000        99,584        98        80                            
   14:28-14:43, 20MAR2001       40,000       449,792        99        80                            
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 3: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 3 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:29-13:44, 20MAR2001       381          1,355        1,736     78.1                            
   13:59-14:14, 20MAR2001       390          1,058        1,448     73.1                            
   14:14-14:28, 20MAR2001       341          3,796        4,137     91.8                            
   14:28-14:43, 20MAR2001       150         16,281       16,431     99.1                            
   14:43-14:58, 20MAR2001       250          1,476        1,726     85.5                            
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 4: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 4 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:14-13:29, 20MAR2001       240          3,976        4,216     94.3                            
   13:29-13:44, 20MAR2001     2,330         16,528       18,858     87.6                            
                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS1           DB2 SUBSYSTEM: DB1P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   14                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:44-13:59, 20MAR2001       433         12,792       13,225     96.7                            
   13:59-14:14, 20MAR2001     1,032         13,336       14,368     92.8                            
   14:14-14:28, 20MAR2001       778         13,640       14,418     94.6                            
   14:28-14:43, 20MAR2001     2,014         34,288       36,302     94.5                            
   14:43-14:58, 20MAR2001     3,285         11,152       14,437     77.2                            
                                                                                                    
                                                                                                    
RULE DB2-226: DWQT, VDWQT, OR VPSEQT MIGHT BE TOO SMALL                                          .
                                                                            
   Buffer Pool 7:  CPExpert noticed that this buffer pool is used for                               
   sort activity.  The DWQT threshold was specified as 50, the VDWQT                                
   threshold was specified as 10, and the VPSEQT threshold was specified                            
   as 95.  While it is normally suggested that the DWQT and VDWQT values                            
   be relatively low to avoid "spikes" in page write activity, this advice                          
   does not apply to buffer pools heavily used by sort processing.                                  
   Instead, the advice is reversed for these buffer pools, to allow sort                            
   processing to process sorted data in central storage. This situation                             
   occurred for Buffer Pool 7 during the intervals shown below:                                     
                                                                                                    
                             BUFFERS   TOTAL MERGE   THRESHOLDS REACHED                             
   MEASUREMENT INTERVAL     ALLOCATED     PASSES     DWQT         VDWQT                             
   13:29-13:44, 20MAR2001     7,000          646        0             5                             
   13:44-13:59, 20MAR2001     7,000          779        0             9                             
   14:14-14:28, 20MAR2001     7,000          728        0            10                             

 


 
DB2 Subsystem DB1P, Lock Manager Findings

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS1           DB2 SUBSYSTEM: DB1P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   15                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-311: DEADLOCKS WERE DETECTED                                                            .
                                                                            
   A deadlock occurs when two or more application processes each hold                               
   locks on resources that the others need and without which they cannot                            
   proceed.  DB2 scans for deadlocked processes at regular intervals,                               
   with the scan interval set by the DEADLOCK TIME on Installation Panel                            
   DSNTIPJ.  Upon detecting a deadlock, DB2 can roll back the current unit                          
   of work for one of the processes or request a process to terminate.                              
   Deadlocks can significantly affect performance and should be avoided.                            
   This situation occurred during the intervals shown below:                                        
                                                                                                    
                                   LOCK       LOCK       LOCK                                       
   MEASUREMENT INTERVAL          REQUESTS  SUSPENSION  TIMEOUTS  DEADLOCKS                          
   13:59-14:14, 20MAR2001       7,220,224       41          0        1                              
   14:28-14:43, 20MAR2001       3,582,976       33          1        1                              
                                                                                                    
                                                                                                    
RULE DB2-512: LOG READS WERE SATISFIED FROM ACTIVE LOG DATA SET                                  .
                                                                            
   The DB2 Log Manager statistics revealed that more than 25% of the                                
   log reads were satisfied from the active log data set. It is                                     
   preferable that the data be in the output buffer, but this is not                                
   always possible with an active DB2 environment.  However, if a large                             
   percent of reads are satisfied from the active log, you should ensure                            
   that the output buffer is as large as possible. This finding occurred                            
   during the intervals shown below:                                                                
                                                                                                    
                             TOTAL LOG       LOG READS FROM                                         
   MEASUREMENT INTERVAL        READS      ACTIVE LOG DATA SET   PERCENT                             
   14:28-14:43, 20MAR2001        898                512           57.0                              

 


 
DB2 Subystem DB2P, General findings

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS2           DB2 SUBSYSTEM: DB2P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   16                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-120: INVALID SELECT PROCEDURE WAS ENCOUNTERED                                           .
                                                                            
   A select procedure is made invalid by applying DB2 maintenance that                              
   directly affects the select procedure.  Invalid select procedures are                            
   bypassed by DB2, and this can cause some degradation in performance.                             
   Plans and packages with invalid select procedures continue to function                           
   correctly but incur some performance degradation. You should consider                            
   running REBIND for any plan or package that has encountered invalid                              
   select procedures to eliminate this degradation.  An invalid select                              
   procedure was encountered during the intervals shown below:                                      
                                                                                                    
                                          TIMES INVALID SELECT                                      
   MEASUREMENT INTERVAL                PROCEDURES WERE ENCOUNTERED                                  
   13:04-13:19, 20MAR2001                         2,452                                             
   13:19-13:34, 20MAR2001                         6,550                                             
   13:34-13:49, 20MAR2001                         4,972                                             
   13:49-14:04, 20MAR2001                         4,888                                             
   14:04-14:19, 20MAR2001                         3,552                                             
   14:19-14:34, 20MAR2001                         3,946                                             
   14:34-14:49, 20MAR2001                         3,626                                             
                                                                                                    
                                                                                                    
RULE DB2-181: RID LIST PROCESSING TERMINATED                                                     .
                                                                            
   RID list processing was terminated because the number of RID entries                             
   was greater than the limit of MAX(25% of table size, number of RIDs                              
   that can fit into the guaranteed number of RID blocks).  This finding                            
   occurred during the intervals shown below:                                                       
                                                                                                    
                                   TIMES RID LIST PROCESSING                                        
   MEASUREMENT INTERVAL                 WAS TERMINATED                                              
   13:04-13:19, 20MAR2001                      508                                                  
   13:19-13:34, 20MAR2001                       23                                                  
   13:34-13:49, 20MAR2001                       41                                                  
   13:49-14:04, 20MAR2001                       60                                                  
   14:04-14:19, 20MAR2001                      108                                                  
   14:19-14:34, 20MAR2001                       31                                                  
   14:34-14:49, 20MAR2001                       35                                                  

 


 
DB2 Subsystem DB2P, Local Buffer Pool Findings

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS2           DB2 SUBSYSTEM: DB2P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   17                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE                                                    .
                                                                            
   Buffer Pool 0: CPExpert believes that the Sequential Steal Threshold                             
   (VPSEQT) value might be too large for the intervals shown below.  Most                           
   of the GETPAGE requests for this buffer pool were issued by random                               
   requestors.  However, the VPSEQT threshold was not specified to favor                            
   random access significantly over sequential access.  Please ignore this                          
   finding (or "turn off" this rule for this buffer pool) unless the                                
   finding is consistently produced during intervals when critical work is                          
   being processed.                                                                                 
                                                                                                    
                                BUFFERS      GETPAGE     PERCENT   VPSEQT                           
   MEASUREMENT INTERVAL        ALLOCATED    REQUESTS      RANDOM    VALUE                           
   13:04-13:19, 20MAR2001       10,000       202,560        98        80                            
   13:19-13:34, 20MAR2001       10,000       426,880       100        80                            
   13:34-13:49, 20MAR2001       10,000       695,040       100        80                            
   13:49-14:04, 20MAR2001       10,000       547,648       100        80                            
   14:04-14:19, 20MAR2001       10,000       508,032        99        80                            
   14:19-14:34, 20MAR2001       10,000       536,256       100        80                            
   14:34-14:49, 20MAR2001       10,000       412,992       100        80                            
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 1: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 1 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:04-13:19, 20MAR2001    38,696         25,136       63,832     39.4                            
   13:19-13:34, 20MAR2001    16,680         38,040       54,720     69.5                            
   13:34-13:49, 20MAR2001     8,736         30,928       39,664     78.0                            
   13:49-14:04, 20MAR2001    47,460         12,648       60,108     21.0                            
   14:04-14:19, 20MAR2001    33,172         28,288       61,460     46.0                            
   14:19-14:34, 20MAR2001   109,116         29,000      138,116     21.0                            
   14:34-14:49, 20MAR2001     6,328         17,104       23,432     73.0                            
                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS2           DB2 SUBSYSTEM: DB2P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   18                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 2: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 2 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:04-13:19, 20MAR2001    42,528         83,120      125,648     66.2                            
   13:19-13:34, 20MAR2001    38,288         73,552      111,840     65.8                            
   13:34-13:49, 20MAR2001    54,576         99,232      153,808     64.5                            
   13:49-14:04, 20MAR2001    50,048         87,776      137,824     63.7                            
   14:04-14:19, 20MAR2001    57,280         86,096      143,376     60.0                            
   14:19-14:34, 20MAR2001    50,800         69,488      120,288     57.8                            
   14:34-14:49, 20MAR2001    39,216         49,664       88,880     55.9                            
                                                                                                    
                                                                                                    
RULE DB2-216: PAGE FAULT RATE WAS HIGH FOR INDIVIDUAL BUFFER POOL                                .
                                                                            
   Buffer Pool 3: The page fault rate for read and write I/O was larger                             
   than 10 pages per second. This situation occurred for Buffer Pool 3                              
   during the intervals shown below:                                                                
                                                                                                    
                             BUFFERS     PAGE-IN FOR   PAGE-IN FOR  PAGE                            
   MEASUREMENT INTERVAL     ALLOCATED      READ I/O     WRITE I/O   RATE                            
   14:19-14:34, 20MAR2001    40,000         16,542           0      18.5                            
                                                                                                    
                                                                                                    
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE                                                    .
                                                                            
   Buffer Pool 3: CPExpert believes that the Sequential Steal Threshold                             
   (VPSEQT) value might be too large for the intervals shown below.  Most                           
   of the GETPAGE requests for this buffer pool were issued by random                               
   requestors.  However, the VPSEQT threshold was not specified to favor                            
   random access significantly over sequential access.  Please ignore this                          
   finding (or "turn off" this rule for this buffer pool) unless the                                
   finding is consistently produced during intervals when critical work is                          
   being processed.                                                                                 
                                                                                                    
                                BUFFERS      GETPAGE     PERCENT   VPSEQT                           
   MEASUREMENT INTERVAL        ALLOCATED    REQUESTS      RANDOM    VALUE                           
   13:04-13:19, 20MAR2001       40,000     1,115,136        96        80                            
   13:19-13:34, 20MAR2001       40,000     4,393,472        99        80                            
   13:34-13:49, 20MAR2001       40,000     2,833,408        98        80                            
                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS2           DB2 SUBSYSTEM: DB2P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   19                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 3: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 3 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:04-13:19, 20MAR2001     1,127          3,474        4,601     75.5                            
   13:19-13:34, 20MAR2001       962          7,792        8,754     89.0                            
   13:34-13:49, 20MAR2001     1,720          4,212        5,932     71.0                            
   13:49-14:04, 20MAR2001     3,553          8,986       12,539     71.7                            
   14:04-14:19, 20MAR2001     3,568          2,446        6,014     40.7                            
   14:19-14:34, 20MAR2001     7,276          2,308        9,584     24.1                            
   14:34-14:49, 20MAR2001     6,821         11,150       17,971     62.0                            
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 4: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 4 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:04-13:19, 20MAR2001    10,396         30,704       41,100     74.7                            
   13:19-13:34, 20MAR2001     2,464         50,416       52,880     95.3                            
   13:34-13:49, 20MAR2001    10,448         84,496       94,944     89.0                            
   13:49-14:04, 20MAR2001    17,020        100,976      117,996     85.6                            
   14:04-14:19, 20MAR2001    11,788         77,552       89,340     86.8                            
   14:19-14:34, 20MAR2001    20,128         60,560       80,688     75.1                            
   14:34-14:49, 20MAR2001     7,064         35,040       42,104     83.2                            
                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS2           DB2 SUBSYSTEM: DB2P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   20                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-226: DWQT, VDWQT, OR VPSEQT MIGHT BE TOO SMALL                                          .
                                                                            
   Buffer Pool 7:  CPExpert noticed that this buffer pool is used for                               
   sort activity.  The DWQT threshold was specified as 50, the VDWQT                                
   threshold was specified as 10, and the VPSEQT threshold was specified                            
   as 95.  While it is normally suggested that the DWQT and VDWQT values                            
   be relatively low to avoid "spikes" in page write activity, this advice                          
   does not apply to buffer pools heavily used by sort processing.                                  
   Instead, the advice is reversed for these buffer pools, to allow sort                            
   processing to process sorted data in central storage. This situation                             
   occurred for Buffer Pool 7 during the intervals shown below:                                     
                                                                                                    
                             BUFFERS   TOTAL MERGE   THRESHOLDS REACHED                             
   MEASUREMENT INTERVAL     ALLOCATED     PASSES     DWQT         VDWQT                             
   13:04-13:19, 20MAR2001     7,000          149        0           175                             
   13:19-13:34, 20MAR2001     7,000           74        0           254                             
   13:34-13:49, 20MAR2001     7,000           65        0           241                             
   13:49-14:04, 20MAR2001     7,000          140        0           298                             
   14:04-14:19, 20MAR2001     7,000          213        0           119                             
   14:19-14:34, 20MAR2001     7,000           84        0         1,573                             
   14:34-14:49, 20MAR2001     7,000          135        0           599                             

 


 
DB2 Subsystem DB2P, Lock Manager Findings

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS2           DB2 SUBSYSTEM: DB2P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   21                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-311: DEADLOCKS WERE DETECTED                                                            .
                                                                            
   A deadlock occurs when two or more application processes each hold                               
   locks on resources that the others need and without which they cannot                            
   proceed.  DB2 scans for deadlocked processes at regular intervals,                               
   with the scan interval set by the DEADLOCK TIME on Installation Panel                            
   DSNTIPJ.  Upon detecting a deadlock, DB2 can roll back the current unit                          
   of work for one of the processes or request a process to terminate.                              
   Deadlocks can significantly affect performance and should be avoided.                            
   This situation occurred during the intervals shown below:                                        
                                                                                                    
                                   LOCK       LOCK       LOCK                                       
   MEASUREMENT INTERVAL          REQUESTS  SUSPENSION  TIMEOUTS  DEADLOCKS                          
   14:34-14:49, 20MAR2001         832,000       84          0        5                              
                                                                                                    
                                                                                                    
RULE DB2-502: ARCHIVE LOG WRITE ALLOCATIONS EXCEEDED GUIDANCE                                    .
                                                                            
   The archive log open and close activity for write allocations was higher                         
   than the guidance provided to CPExpert.  This finding might indicate a                           
   need for more or larger active log data sets.  Archive log write open                            
   and close activity was high during the intervals shown below:                                    
                                                                                                    
                                           ARCHIVE LOG OPEN                                         
   MEASUREMENT INTERVAL                AND CLOSE WRITE ACTIVITY                                     
   13:04-13:19, 20MAR2001                           6                                               
                                                                                                    
                                                                                                    
RULE DB2-512: LOG READS WERE SATISFIED FROM ACTIVE LOG DATA SET                                  .
                                                                            
   The DB2 Log Manager statistics revealed that more than 25% of the                                
   log reads were satisfied from the active log data set. It is                                     
   preferable that the data be in the output buffer, but this is not                                
   always possible with an active DB2 environment.  However, if a large                             
   percent of reads are satisfied from the active log, you should ensure                            
   that the output buffer is as large as possible. This finding occurred                            
   during the intervals shown below:                                                                
                                                                                                    
                             TOTAL LOG       LOG READS FROM                                         
   MEASUREMENT INTERVAL        READS      ACTIVE LOG DATA SET   PERCENT                             
   13:19-13:34, 20MAR2001      4,605              1,229           26.7                              

 


 
DB2 Subystem DB3P, General findings

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS3           DB2 SUBSYSTEM: DB3P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   22                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-120: INVALID SELECT PROCEDURE WAS ENCOUNTERED                                           .
                                                                            
   A select procedure is made invalid by applying DB2 maintenance that                              
   directly affects the select procedure.  Invalid select procedures are                            
   bypassed by DB2, and this can cause some degradation in performance.                             
   Plans and packages with invalid select procedures continue to function                           
   correctly but incur some performance degradation. You should consider                            
   running REBIND for any plan or package that has encountered invalid                              
   select procedures to eliminate this degradation.  An invalid select                              
   procedure was encountered during the intervals shown below:                                      
                                                                                                    
                                          TIMES INVALID SELECT                                      
   MEASUREMENT INTERVAL                PROCEDURES WERE ENCOUNTERED                                  
   13:03-13:18, 20MAR2001                           887                                             
   13:18-13:33, 20MAR2001                         1,252                                             
   13:33-13:48, 20MAR2001                           354                                             
   13:48-14:03, 20MAR2001                         3,451                                             
   14:03-14:18, 20MAR2001                         1,550                                             
   14:18-14:32, 20MAR2001                         1,067                                             
   14:32-14:47, 20MAR2001                         2,072                                             
                                                                                                    
                                                                                                    
RULE DB2-181: RID LIST PROCESSING TERMINATED                                                     .
                                                                            
   RID list processing was terminated because the number of RID entries                             
   was greater than the limit of MAX(25% of table size, number of RIDs                              
   that can fit into the guaranteed number of RID blocks).  This finding                            
   occurred during the intervals shown below:                                                       
                                                                                                    
                                   TIMES RID LIST PROCESSING                                        
   MEASUREMENT INTERVAL                 WAS TERMINATED                                              
   13:03-13:18, 20MAR2001                       36                                                  
   13:18-13:33, 20MAR2001                       15                                                  
   13:33-13:48, 20MAR2001                       22                                                  
   13:48-14:03, 20MAR2001                       14                                                  
   14:03-14:18, 20MAR2001                       15                                                  
   14:18-14:32, 20MAR2001                       40                                                  
   14:32-14:47, 20MAR2001                       16                                                  

 


 
DB2 Subsystem DB3P, Local Buffer Pool Findings

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS3           DB2 SUBSYSTEM: DB3P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   23                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE                                                    .
                                                                            
   Buffer Pool 0: CPExpert believes that the Sequential Steal Threshold                             
   (VPSEQT) value might be too large for the intervals shown below.  Most                           
   of the GETPAGE requests for this buffer pool were issued by random                               
   requestors.  However, the VPSEQT threshold was not specified to favor                            
   random access significantly over sequential access.  Please ignore this                          
   finding (or "turn off" this rule for this buffer pool) unless the                                
   finding is consistently produced during intervals when critical work is                          
   being processed.                                                                                 
                                                                                                    
                                BUFFERS      GETPAGE     PERCENT   VPSEQT                           
   MEASUREMENT INTERVAL        ALLOCATED    REQUESTS      RANDOM    VALUE                           
   13:03-13:18, 20MAR2001       10,000       298,688       100        80                            
   13:18-13:33, 20MAR2001       10,000       999,712       100        80                            
   13:33-13:48, 20MAR2001       10,000       623,072       100        80                            
   13:48-14:03, 20MAR2001       10,000       336,256       100        80                            
   14:03-14:18, 20MAR2001       10,000       191,488        98        80                            
   14:18-14:32, 20MAR2001       10,000       452,800       100        80                            
   14:32-14:47, 20MAR2001       10,000        91,808        99        80                            
                                                                                                    
                                                                                                    
RULE DB2-224: VPSEQT VALUE MIGHT BE TOO LARGE                                                    .
                                                                            
   Buffer Pool 1: CPExpert believes that the Sequential Steal Threshold                             
   (VPSEQT) value might be too large for the intervals shown below.  Most                           
   of the GETPAGE requests for this buffer pool were issued by random                               
   requestors.  However, the VPSEQT threshold was not specified to favor                            
   random access significantly over sequential access.  Please ignore this                          
   finding (or "turn off" this rule for this buffer pool) unless the                                
   finding is consistently produced during intervals when critical work is                          
   being processed.                                                                                 
                                                                                                    
                                BUFFERS      GETPAGE     PERCENT   VPSEQT                           
   MEASUREMENT INTERVAL        ALLOCATED    REQUESTS      RANDOM    VALUE                           
   13:03-13:18, 20MAR2001       80,000     1,820,672        98        80                            
   13:18-13:33, 20MAR2001       80,000     2,525,184        99        80                            
   13:48-14:03, 20MAR2001       80,000     2,428,928        98        80                            
   14:03-14:18, 20MAR2001       80,000     2,160,640        97        80                            
   14:18-14:32, 20MAR2001       80,000     2,502,656        98        80                            
   14:32-14:47, 20MAR2001       80,000     4,134,912        98        80                            
                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS3           DB2 SUBSYSTEM: DB3P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   24                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 1: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 1 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:03-13:18, 20MAR2001     1,152         24,352       25,504     95.5                            
   13:18-13:33, 20MAR2001     1,376         48,064       49,440     97.2                            
   13:33-13:48, 20MAR2001    17,464         96,352      113,816     84.7                            
   13:48-14:03, 20MAR2001     1,656         49,776       51,432     96.8                            
   14:03-14:18, 20MAR2001     2,296         53,376       55,672     95.9                            
   14:18-14:32, 20MAR2001     1,840         55,696       57,536     96.8                            
   14:32-14:47, 20MAR2001     2,592        134,472      137,064     98.1                            
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 2: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 2 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:03-13:18, 20MAR2001     2,544         41,440       43,984     94.2                            
   13:18-13:33, 20MAR2001     2,576         84,416       86,992     97.0                            
   13:33-13:48, 20MAR2001     7,552        141,472      149,024     94.9                            
   13:48-14:03, 20MAR2001    20,032         56,096       76,128     73.7                            
   14:03-14:18, 20MAR2001     3,872         50,032       53,904     92.8                            
   14:18-14:32, 20MAR2001    12,624         57,680       70,304     82.0                            
   14:32-14:47, 20MAR2001     3,056        210,528      213,584     98.6                            
                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS3           DB2 SUBSYSTEM: DB3P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   25                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 3: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 3 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:33-13:48, 20MAR2001    14,991          2,498       17,489     14.3                            
   13:48-14:03, 20MAR2001     6,152          3,002        9,154     32.8                            
   14:03-14:18, 20MAR2001    13,052          4,498       17,550     25.6                            
   14:18-14:32, 20MAR2001    17,761          5,124       22,885     22.4                            
   14:32-14:47, 20MAR2001     3,204          2,918        6,122     47.7                            
                                                                                                    
                                                                                                    
RULE DB2-235: DYNAMIC SEQUENTIAL PREFETCH WAS HIGH                                               .
                                                                            
   Buffer Pool 4: DB2 invoked dynamic sequential prefetch a large percent                           
   of total sequential prefetch, as a result of DB2's sequential detection.                         
   Please review the discussion in the DB2 Component User Manual related                            
   to this finding.  You should either use the RUNSTATS utility to reduce                           
   the percent of dynamic detection, or use the PCTDPF guidance variable                            
   to alter CPExpert's analysis of dynamic sequential prefetch for this                             
   buffer pool. This situation occurred for Buffer Pool 4 during the                                
   intervals shown below:                                                                           
                                                                                                    
                             --SEQUENTIAL PREFETCH OPERATIONS--   PERCENT                           
   MEASUREMENT INTERVAL      NORMAL        DYNAMIC        TOTAL   DYNAMIC                           
   13:03-13:18, 20MAR2001       532          9,536       10,068     94.7                            
   13:18-13:33, 20MAR2001       980         22,464       23,444     95.8                            
   13:33-13:48, 20MAR2001     6,124         76,560       82,684     92.6                            
   13:48-14:03, 20MAR2001     1,248         58,456       59,704     97.9                            
   14:03-14:18, 20MAR2001    18,076         36,848       54,924     67.1                            
   14:18-14:32, 20MAR2001     9,208         83,168       92,376     90.0                            
   14:32-14:47, 20MAR2001     3,392         31,232       34,624     90.2                            
                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS3           DB2 SUBSYSTEM: DB3P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   26                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-226: DWQT, VDWQT, OR VPSEQT MIGHT BE TOO SMALL                                          .
                                                                            
   Buffer Pool 7:  CPExpert noticed that this buffer pool is used for                               
   sort activity.  The DWQT threshold was specified as 50, the VDWQT                                
   threshold was specified as 10, and the VPSEQT threshold was specified                            
   as 95.  While it is normally suggested that the DWQT and VDWQT values                            
   be relatively low to avoid "spikes" in page write activity, this advice                          
   does not apply to buffer pools heavily used by sort processing.                                  
   Instead, the advice is reversed for these buffer pools, to allow sort                            
   processing to process sorted data in central storage. This situation                             
   occurred for Buffer Pool 7 during the intervals shown below:                                     
                                                                                                    
                             BUFFERS   TOTAL MERGE   THRESHOLDS REACHED                             
   MEASUREMENT INTERVAL     ALLOCATED     PASSES     DWQT         VDWQT                             
   13:03-13:18, 20MAR2001     7,000           93        0           735                             
   13:18-13:33, 20MAR2001     7,000           93        0           677                             
   13:33-13:48, 20MAR2001     7,000           41        0           296                             
   13:48-14:03, 20MAR2001     7,000           71        0           768                             
   14:03-14:18, 20MAR2001     7,000           97        0           471                             
   14:18-14:32, 20MAR2001     7,000           74        0           459                             
   14:32-14:47, 20MAR2001     7,000           52        0           527                             

 


 
DB2 Subsystem DB3P, Lock Manager Findings

                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS3           DB2 SUBSYSTEM: DB3P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   27                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-303: LOCKS WERE ESCALATED TO EXCLUSIVE MODE                                             .
                                                                            
   Lock escalation releases a large number of page or row locks, held by                            
   an application process on a single table or table space, to acquire a                            
   table or table space lock, or a set of partition locks, of mode S or X.                          
   Escalation can cause unpredictable response times.  Lock escalation                              
   should happen only when an application process updates or references                             
   (if repeatable read is used) more pages or rows than it normally does.                           
   This situation occurred during the intervals shown below:                                        
                                                                                                    
                                     NUMBER OF         NUMBER OF LOCKS                              
   MEASUREMENT INTERVAL            LOCK REQUESTS   ESCALATED TO EXCLUSIVE                           
   14:32-14:47, 20MAR2001              406,528                  1                                   
                                                                                                    
                                                                                                    
RULE DB2-305: LOCK ESCALATION WAS NOT EFFECTIVE                                                  .
                                                                            
   Lock escalation releases a large number of page or row locks, held by                            
   an application process on a single table or table space, to acquire a                            
   table or table space lock, or a set of partition locks, of mode S or X.                          
   Lock escalation balances concurrency with performance by using page or                           
   row locks while a process accesses relatively few pages or rows, then                            
   changing to table space, table, or partition locks when the process                              
   accesses many pages or rows.  IBM states that, as a rough estimate,                              
   escalation is not effective if more than one quarter of the lock                                 
   escalations cause timeouts or deadlocks.  CPExpert detected that more                            
   than 0% of the escalations resulted in timeouts or deadlocks, during                             
   the intervals shown below:                                                                       
                                                                                                    
                           NUMBER OF LOCKS                                                          
   MEASUREMENT INTERVAL       ESCALATED    TIMEOUTS  DEADLOCKS    PERCENT                           
   14:32-14:47, 20MAR2001          1            0          2       200.0                            
                                                                                                    
                                                                                                    
RULE DB2-311: DEADLOCKS WERE DETECTED                                                            .
                                                                            
   A deadlock occurs when two or more application processes each hold                               
   locks on resources that the others need and without which they cannot                            
   proceed.  DB2 scans for deadlocked processes at regular intervals,                               
   with the scan interval set by the DEADLOCK TIME on Installation Panel                            
   DSNTIPJ.  Upon detecting a deadlock, DB2 can roll back the current unit                          
   of work for one of the processes or request a process to terminate.                              
   Deadlocks can significantly affect performance and should be avoided.                            
   This situation occurred during the intervals shown below:                                        
                                                                                                    
                                   LOCK       LOCK       LOCK                                       
   MEASUREMENT INTERVAL          REQUESTS  SUSPENSION  TIMEOUTS  DEADLOCKS                          
   14:03-14:18, 20MAR2001         898,560       38          0        1                              
   14:32-14:47, 20MAR2001         406,528       47          0        2                              
                                                                                                    
                                                                                                    
            ANALYSIS OF DB2 PERFORMANCE CONSTRAINTS, 20MAR2001                                      
                SYSTEM: SYS3           DB2 SUBSYSTEM: DB3P                                          
                Analyzed by Version 11.2.0 of CPExpert(tm)                                          
          (C) Copyright 2001, Computer Management Sciences, Inc.                                    
                               PAGE:   28                                                           
                                                                                                    
                                                                                                    
                                                                                                    
RULE DB2-512: LOG READS WERE SATISFIED FROM ACTIVE LOG DATA SET                                  .
                                                                            
   The DB2 Log Manager statistics revealed that more than 25% of the                                
   log reads were satisfied from the active log data set. It is                                     
   preferable that the data be in the output buffer, but this is not                                
   always possible with an active DB2 environment.  However, if a large                             
   percent of reads are satisfied from the active log, you should ensure                            
   that the output buffer is as large as possible. This finding occurred                            
   during the intervals shown below:                                                                
                                                                                                    
                             TOTAL LOG       LOG READS FROM                                         
   MEASUREMENT INTERVAL        READS      ACTIVE LOG DATA SET   PERCENT                             
   13:48-14:03, 20MAR2001     16,475              6,243           37.9