We are currently on DB2 V11(NFM). We are planning to perform group buffer pool tuning in our environment after a long time. Upon performing health check we found couple of GBP with very high Directory/Data ratio of 270 (GBP14) and 274 (GBP20). So we are planning to tune these GBP. For GBP14 we see below output for DIS GBPOOL.
DSNB750I -D2PD DISPLAY FOR GROUP BUFFER POOL GBP14 FOLLOWS
DSNB755I -D2PD DB2 GROUP BUFFER POOL STATUS
CONNECTED = YES
CURRENT DIRECTORY TO DATA RATIO = 270
PENDING DIRECTORY TO DATA RATIO = 270
CURRENT GBPCACHE ATTRIBUTE = YES
PENDING GBPCACHE ATTRIBUTE = YES
D XCF command output shows that only 50% (INITSIZE) of the space is allocated as shown below.
DUPLEXING REBUILD NEW STRUCTURE
ALLOCATION TIME: 08/20/2017 06:06:42
CFNAME : P1CFSB2
COUPLING FACILITY: 002964.IBM.02.0000000A46D7
PARTITION: 04 CPCID: 00
STORAGE CONFIGURATION ALLOCATED MAXIMUM %
ACTUAL SIZE: 2798 M 5578 M 50
DB2 Statistics report shows GBP14 has very poor hit ratio GBP SYN.READ(XI) HIT RATIO(%) = 36.03 %.
GROUP BP14 QUANTITY /SECOND /THREAD /COMMIT
----------------------------- -------- ------- ------- -------
GROUP BP R/W RATIO (%) 189.32 N/A N/A N/A
GBP SYN.READ(XI) HIT RATIO(%) 36.03 N/A N/A N/A
GBP-DEPENDENT GETPAGES 2897.3M 4865.67 246.20 1.47
SYN.READ(XI)-DATA RETURNED 1260.2K 2.12 0.11 0.00
SYN.READ(XI)-NO DATA RETURN 2237.7K 3.76 0.19 0.00
I was wondering why AUTO ALTER ( we have enabled it) was only adjusting only the Dir/Data ratio (despite very low Synch read(XI) hit ratio) and did not go for increase in GBP size. Does that mean neither Directory entries nor Data pages reached FULLTHRESHOLD? Would increasing the GBP size (twice) automatically help AUTO ALTER to bring down the Dir/Data ratio in the next allocation? What Dir/Data ratio should I aim for?
Similarly, we have GBP20 with very high Dir/Data ratio of 274 which has 50% (INITSIZE) allocated and has a very good SYNCH READ(XI) HIT RATIO of 99%. So, is it sufficient if I just alter the Dir/Data ratio alone for this GBP20 without increasing the GBP size? What should be an ideal Dir/Data ratio for this GBP20? Please advice.