Posted by 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.
Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL.
Ember lives in Denver and work from home