bufferpool 监控的SQL

SELECT
  BPNAME
 ,NPAGES
 ,PAGESIZE
FROM
  SYSCAT.BUFFERPOOLS;

SELECT
  DATA_PHYSICAL_READS
 ,INDEX_PHYSICAL_READS
 ,TOTAL_PHYSICAL_READS
 ,BP_NAME
FROM
  SYSIBMADM.BP_HITRATIO
WHERE
  BP_NAME NOT LIKE 'IBMSYSTEM%';

SELECT
  DATA_LOGICAL_READS
 ,INDEX_LOGICAL_READS
 ,TOTAL_LOGICAL_READS
 ,BP_NAME
FROM
  SYSIBMADM.BP_HITRATIO
WHERE
  BP_NAME NOT LIKE 'IBMSYSTEM%';

SELECT
  DATA_HIT_RATIO_PERCENT
 ,INDEX_HIT_RATIO_PERCENT
 ,TOTAL_HIT_RATIO_PERCENT
 ,BP_NAME
FROM
  SYSIBMADM.BP_HITRATIO
WHERE
  BP_NAME NOT LIKE 'IBMSYSTEM%';

SELECT
  SUBSTR(
    BP_NAME
   ,1
   ,15)
    AS BP_NAME
 ,TOTAL_PHYSICAL_READS
 ,AVERAGE_READ_TIME_MS
FROM
  SYSIBMADM.BP_READ_IO
WHERE
  BP_NAME NOT LIKE 'IBMSYSTEM%';

SELECT
  SUBSTR(
    BP_NAME
   ,1
   ,15)
    AS BP_NAME
 ,TOTAL_WRITES
 ,AVERAGE_WRITE_TIME_MS
FROM
  SYSIBMADM.BP_WRITE_IO
WHERE
  BP_NAME NOT LIKE 'IBMSYSTEM%';

下面这个是最常用的

SELECT
  SUBSTR(
    DB_NAME
   ,1
   ,8)
    AS DB_NAME
 ,SUBSTR(
    BP_NAME
   ,1
   ,14)
    AS BP_NAME
 ,TOTAL_HIT_RATIO_PERCENT
 ,DATA_HIT_RATIO_PERCENT
 ,INDEX_HIT_RATIO_PERCENT
 ,XDA_HIT_RATIO_PERCENT
 ,DBPARTITIONNUM
FROM
  SYSIBMADM.BP_HITRATIO
ORDER BY
  DBPARTITIONNUM;