Purpose
These statements calculates the buffer pool hit ratio for both BLU and non-BLU activity. This post includes three SQLs – for calculating at the database, bufferpool, and tablespace level. The separate BLU calculations are not yet included in sysibadm.bp_hitratio as of the writing of this article.
Source
While I’ve modified this statement, it comes from these two sources, both influenced by David Kalmuk:
http://www.ibm.com/developerworks/data/library/techarticle/dm-1407monitor-bluaccel/index.html
http://www.dbisoftware.com/blog/db2nightshow.php?id=619
Statement for Database Level
WITH POOL_DATA( ROW_DATA_PAGES_FOUND,
COL_DATA_PAGES_FOUND,
DATA_PAGES_FOUND,
ROW_DATA_L_READS,
COL_DATA_L_READS,
DATA_L_READS )
AS (SELECT SUM(POOL_DATA_LBP_PAGES_FOUND -
POOL_ASYNC_DATA_LBP_PAGES_FOUND) AS ROW_DATA_PAGES_FOUND,
SUM(POOL_COL_LBP_PAGES_FOUND -
POOL_ASYNC_COL_LBP_PAGES_FOUND) AS COL_DATA_PAGES_FOUND,
SUM(POOL_DATA_LBP_PAGES_FOUND +
POOL_COL_LBP_PAGES_FOUND -
POOL_ASYNC_DATA_LBP_PAGES_FOUND -
POOL_ASYNC_COL_LBP_PAGES_FOUND) AS DATA_PAGES_FOUND,
SUM(POOL_DATA_L_READS +
POOL_TEMP_DATA_L_READS) AS ROW_DATA_L_READS,
SUM(POOL_COL_L_READS +
POOL_TEMP_COL_L_READS) AS COL_DATA_L_READS,
SUM(POOL_DATA_L_READS +
POOL_COL_L_READS +
POOL_TEMP_DATA_L_READS +
POOL_TEMP_COL_L_READS) AS DATA_L_READS
FROM TABLE(MON_GET_DATABASE(-2)) AS T)
SELECT CASE WHEN DATA_L_READS > 0
THEN DEC((FLOAT(DATA_PAGES_FOUND) /
FLOAT(DATA_L_READS)) * 100, 5, 2)
ELSE NULL
END AS DATA_PAGE_HIT_RATIO,
CASE WHEN ROW_DATA_L_READS > 0
THEN DEC((FLOAT(ROW_DATA_PAGES_FOUND) /
FLOAT(ROW_DATA_L_READS)) * 100, 5, 2)
ELSE NULL
END AS ROW_DATA_PAGE_HIT_RATIO,
CASE WHEN COL_DATA_L_READS > 0
THEN DEC((FLOAT(COL_DATA_PAGES_FOUND) /
FLOAT(COL_DATA_L_READS)) * 100, 5, 2)
ELSE NULL
END AS COL_DATA_PAGE_HIT_RATIO
FROM POOL_DATA
with ur;
Sample Output for BPHR at the Database Level
DATA_PAGE_HIT_RATIO ROW_DATA_PAGE_HIT_RATIO COL_DATA_PAGE_HIT_RATIO
------------------- ----------------------- -----------------------
98.61 99.68 94.98
1 record(s) selected.
Statement for Buffer Pool Level
WITH POOL_DATA( BP_NAME,
ROW_DATA_PAGES_FOUND,
COL_DATA_PAGES_FOUND,
DATA_PAGES_FOUND,
ROW_DATA_L_READS,
COL_DATA_L_READS,
DATA_L_READS )
AS (SELECT substr(BP_NAME,1,18),
(POOL_DATA_LBP_PAGES_FOUND -
POOL_ASYNC_DATA_LBP_PAGES_FOUND) AS ROW_DATA_PAGES_FOUND,
(POOL_COL_LBP_PAGES_FOUND -
POOL_ASYNC_COL_LBP_PAGES_FOUND) AS COL_DATA_PAGES_FOUND,
(POOL_DATA_LBP_PAGES_FOUND +
POOL_COL_LBP_PAGES_FOUND -
POOL_ASYNC_DATA_LBP_PAGES_FOUND -
POOL_ASYNC_COL_LBP_PAGES_FOUND) AS DATA_PAGES_FOUND,
(POOL_DATA_L_READS +
POOL_TEMP_DATA_L_READS) AS ROW_DATA_L_READS,
(POOL_COL_L_READS +
POOL_TEMP_COL_L_READS) AS COL_DATA_L_READS,
(POOL_DATA_L_READS +
POOL_COL_L_READS +
POOL_TEMP_DATA_L_READS +
POOL_TEMP_COL_L_READS) AS DATA_L_READS
FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS T)
SELECT BP_NAME,
CASE WHEN DATA_L_READS > 0
THEN DEC((FLOAT(DATA_PAGES_FOUND) /
FLOAT(DATA_L_READS)) * 100, 5, 2)
ELSE NULL
END AS DATA_PAGE_HIT_RATIO,
CASE WHEN ROW_DATA_L_READS > 0
THEN DEC((FLOAT(ROW_DATA_PAGES_FOUND) /
FLOAT(ROW_DATA_L_READS)) * 100, 5, 2)
ELSE NULL
END AS ROW_DATA_PAGE_HIT_RATIO,
CASE WHEN COL_DATA_L_READS > 0
THEN DEC((FLOAT(COL_DATA_PAGES_FOUND) /
FLOAT(COL_DATA_L_READS)) * 100, 5, 2)
ELSE NULL
END AS COL_DATA_PAGE_HIT_RATIO
FROM POOL_DATA
with ur;
Sample Output for BPHR at the Database Level
BP_NAME DATA_PAGE_HIT_RATIO ROW_DATA_PAGE_HIT_RATIO COL_DATA_PAGE_HIT_RATIO ------------------ ------------------- ----------------------- ----------------------- IBMDEFAULTBP 99.97 99.97 - BUFF32K 94.95 98.74 94.70 BUFF16K 99.98 99.98 99.98 BUFF8K 99.24 99.24 - BUFF4K 99.13 99.13 - IBMSYSTEMBP4K - - - IBMSYSTEMBP8K - - - IBMSYSTEMBP16K - - - IBMSYSTEMBP32K - - - 9 record(s) selected.
Statement for Tablespace Level
WITH POOL_DATA( TBSP_NAME,
ROW_DATA_PAGES_FOUND,
COL_DATA_PAGES_FOUND,
DATA_PAGES_FOUND,
ROW_DATA_L_READS,
COL_DATA_L_READS,
DATA_L_READS )
AS (SELECT substr(TBSP_NAME,1,30),
(POOL_DATA_LBP_PAGES_FOUND -
POOL_ASYNC_DATA_LBP_PAGES_FOUND) AS ROW_DATA_PAGES_FOUND,
(POOL_COL_LBP_PAGES_FOUND -
POOL_ASYNC_COL_LBP_PAGES_FOUND) AS COL_DATA_PAGES_FOUND,
(POOL_DATA_LBP_PAGES_FOUND +
POOL_COL_LBP_PAGES_FOUND -
POOL_ASYNC_DATA_LBP_PAGES_FOUND -
POOL_ASYNC_COL_LBP_PAGES_FOUND) AS DATA_PAGES_FOUND,
(POOL_DATA_L_READS +
POOL_TEMP_DATA_L_READS) AS ROW_DATA_L_READS,
(POOL_COL_L_READS +
POOL_TEMP_COL_L_READS) AS COL_DATA_L_READS,
(POOL_DATA_L_READS +
POOL_COL_L_READS +
POOL_TEMP_DATA_L_READS +
POOL_TEMP_COL_L_READS) AS DATA_L_READS
FROM TABLE(MON_GET_TABLESPACE('',-2)) AS T)
SELECT TBSP_NAME,
ROW_DATA_L_READS,
COL_DATA_L_READS,
CASE WHEN DATA_L_READS > 0
THEN DEC((FLOAT(DATA_PAGES_FOUND) /
FLOAT(DATA_L_READS)) * 100, 5, 2)
ELSE NULL
END AS DATA_PAGE_HIT_RATIO,
CASE WHEN ROW_DATA_L_READS > 0
THEN DEC((FLOAT(ROW_DATA_PAGES_FOUND) /
FLOAT(ROW_DATA_L_READS)) * 100, 5, 2)
ELSE NULL
END AS ROW_DATA_PAGE_HIT_RATIO,
CASE WHEN COL_DATA_L_READS > 0
THEN DEC((FLOAT(COL_DATA_PAGES_FOUND) /
FLOAT(COL_DATA_L_READS)) * 100, 5, 2)
ELSE NULL
END AS COL_DATA_PAGE_HIT_RATIO
FROM POOL_DATA
with ur;
Sample Output for BPHR at the Tablespace Level
TBSP_NAME ROW_DATA_L_READS COL_DATA_L_READS DATA_PAGE_HIT_RATIO ROW_DATA_PAGE_HIT_RATIO COL_DATA_PAGE_HIT_RATIO ------------------------------ -------------------- -------------------- ------------------- ----------------------- ----------------------- SYSCATSPACE 112771783 0 99.92 99.92 - TEMPSPACE1 273494060 0 99.99 99.99 - USERSPACE1 265 0 83.01 83.01 - LOBSPACE1 13381 0 80.24 80.24 - CMIPS_TS16K 83283779 11317952 99.98 99.98 99.98 AG_IE_RSLT_TS 512863 4686219 82.48 99.84 80.58 AG_IE_RSN_TS 411 280 79.59 84.42 72.50 TAB16K 265 0 99.24 99.24 - SSIRS_LG_TBL_TS 38312 8598834 72.95 71.75 72.96 CIS_ODS_LG_TBL_TS 226824 1900955 82.21 99.63 80.13 WF_ODS_LG_TBL_TS 13168 12020654 67.19 97.71 67.16 CIS_ODS_TS32K 6848238 11398098 96.43 99.71 94.45 SSIRS_TS32K 4104862 76690676 99.44 98.72 99.48 EBT_ODS_TS32K 9933 5329227 74.42 90.58 74.39 WEBFILES_ODS_TS32K 1212 177281 92.36 85.23 92.41 SMART_ODS_TS32K 3311 9262 76.31 94.23 69.90 CWS_ODS32K 1471139 75392240 99.75 97.88 99.78 GIS_UTIL_TS32K 89161 1769118 85.44 56.74 86.88 TAB4K 224206209 0 99.13 99.13 - TAB8K 265 0 99.24 99.24 - TAB32K 70847 1170441 84.40 87.02 84.24 SSIRS_DMART_STG_TS 265 0 77.73 77.73 - SYSTOOLSPACE 3061926 0 99.82 99.82 - SYSTOOLSTMPSPACE 0 0 - - - 24 record(s) selected.
