# 创建默认页面大小缓冲池
CREATE BUFFERPOOL BP_DEFAULT SIZE 100000;

# 创建8K页面大小缓冲池
CREATE BUFFERPOOL BP8K SIZE 50000 PAGESIZE 8K;

# 创建16K页面大小缓冲池
CREATE BUFFERPOOL BP16K SIZE 25000 PAGESIZE 16K;

# 创建32K页面大小缓冲池
CREATE BUFFERPOOL BP32K SIZE 12500 PAGESIZE 32K;

# 查看缓冲池配置
SELECT
BPNAME,
PAGESIZE,
NPAGES,
CURRENTSIZE
FROM SYSIBMADM.BP_HITRATIO;

# 修改缓冲池大小
ALTER BUFFERPOOL BP_DEFAULT SIZE 200000;

# 删除缓冲池
DROP BUFFERPOOL BP8K;

# 查看缓冲池命中率
SELECT
BPNAME,
DATA_HIT_RATIO_PERCENT,
INDEX_HIT_RATIO_PERCENT,
XDA_HIT_RATIO_PERCENT
FROM SYSIBMADM.BP_HITRATIO;

# 查看缓冲池使用情况
SELECT
BPNAME,
POOL_DATA_L_READS,
POOL_DATA_P_READS,
POOL_DATA_WRITES,
POOL_INDEX_L_READS,
POOL_INDEX_P_READS
FROM SYSIBMADM.SNAPBP;

# 计算命中率
SELECT
BPNAME,
CASE
WHEN POOL_DATA_L_READS > 0
THEN ROUND((POOL_DATA_L_READS – POOL_DATA_P_READS) * 100.0 / POOL_DATA_L_READS, 2)
ELSE 0
END AS DATA_HIT_RATIO,
CASE
WHEN POOL_INDEX_L_READS > 0
THEN ROUND((POOL_INDEX_L_READS – POOL_INDEX_P_READS) * 100.0 / POOL_INDEX_L_READS, 2)
ELSE 0
END AS INDEX_HIT_RATIO
FROM SYSIBMADM.SNAPBP;

# 缓冲池建议
# 命中率 > 95%：缓冲池大小合适
# 命中率 < 95%：考虑增大缓冲池 # 命中率 < 90%：必须增大缓冲池

# 配置数据库内存
UPDATE DATABASE CONFIGURATION USING DATABASE_MEMORY AUTOMATIC;

# 配置缓冲池内存
UPDATE DATABASE CONFIGURATION USING BUF_PAGE_SZ 4096;

# 配置排序堆
UPDATE DATABASE CONFIGURATION USING SORTHEAP 8192;
UPDATE DATABASE CONFIGURATION USING SHEAPTHRES 65536;

# 配置包缓存
UPDATE DATABASE CONFIGURATION USING PCKCACHESZ 8192;

# 配置锁列表
UPDATE DATABASE CONFIGURATION USING LOCKLIST 4096;
UPDATE DATABASE CONFIGURATION USING MAXLOCKS 20;

# 配置日志缓冲区
UPDATE DATABASE CONFIGURATION USING LOGBUFSZ 1024;

# 配置应用内存
UPDATE DATABASE CONFIGURATION USING APPL_MEMORY AUTOMATIC;

# 查看内存配置
GET DATABASE CONFIGURATION;

# 配置实例内存
UPDATE DATABASE MANAGER CONFIGURATION USING INSTANCE_MEMORY AUTOMATIC;

# 配置代理内存
UPDATE DATABASE MANAGER CONFIGURATION USING AGENT_STACK_SZ 256;

# 配置共享内存
UPDATE DATABASE MANAGER CONFIGURATION USING SHEAPTHRES_INST 0;

# 配置监控内存
UPDATE DATABASE MANAGER CONFIGURATION USING MON_HEAP_SZ 1024;

# 配置查询堆
UPDATE DATABASE MANAGER CONFIGURATION USING QUERY_HEAP_SZ 32768;

# 查看实例内存配置
GET DATABASE MANAGER CONFIGURATION;

# 查看内存使用情况
SELECT
MEMORY_SET_TYPE,
MEMORY_SET_SIZE,
MEMORY_SET_USED,
MEMORY_SET_FREE
FROM SYSIBMADM.SNAPDB_MEMORY_POOL;

# 查看数据库内存使用
SELECT
MEMORY_SET_TYPE,
MEMORY_SET_SIZE / 1024 / 1024 AS SIZE_MB,
MEMORY_SET_USED / 1024 / 1024 AS USED_MB,
MEMORY_SET_FREE / 1024 / 1024 AS FREE_MB,
ROUND(MEMORY_SET_USED * 100.0 / MEMORY_SET_SIZE, 2) AS USAGE_PERCENT
FROM SYSIBMADM.SNAPDB_MEMORY_POOL
WHERE MEMORY_SET_TYPE = ‘DATABASE’;

# 查看缓冲池内存使用
SELECT
BPNAME,
CURRENTSIZE / 1024 / 1024 AS SIZE_MB,
POOL_DATA_LBPAGES,
POOL_INDEX_LBPAGES,
POOL_XDA_LBPAGES
FROM SYSIBMADM.SNAPBP;

# 查看排序堆使用
SELECT
TOTAL_SORTS,
TOTAL_SORT_OVERFLOWS,
POST_THRESHOLD_SORTS,
POST_SHRTHRESHOLD_SORTS
FROM SYSIBMADM.SNAPDB;

# 查看锁列表使用
SELECT
LOCK_LIST_IN_USE,
LOCKS_HELD,
LOCK_WAITS,
LOCK_TIMEOUTS,
DEADLOCKS
FROM SYSIBMADM.SNAPDB;

# 查看包缓存使用
SELECT
PKG_CACHE_LOOKUPS,
PKG_CACHE_INSERTS,
PKG_CACHE_NUM_OVERFLOWS
FROM SYSIBMADM.SNAPDB;

# 内存性能基线
SELECT
SNAPSHOT_TIMESTAMP,
MEMORY_SET_TYPE,
MEMORY_SET_SIZE,
MEMORY_SET_USED
FROM SYSIBMADM.SNAPDB_MEMORY_POOL
ORDER BY SNAPSHOT_TIMESTAMP DESC;

# 内存增长趋势
SELECT
DATE(SNAPSHOT_TIMESTAMP) AS SNAP_DATE,
MAX(MEMORY_SET_USED) AS MAX_USED,
MIN(MEMORY_SET_USED) AS MIN_USED,
AVG(MEMORY_SET_USED) AS AVG_USED
FROM SYSIBMADM.SNAPDB_MEMORY_POOL
WHERE MEMORY_SET_TYPE = ‘DATABASE’
GROUP BY DATE(SNAPSHOT_TIMESTAMP)
ORDER BY SNAP_DATE DESC;

# 内存告警
CREATE OR REPLACE PROCEDURE SP_CHECK_MEMORY_USAGE()
LANGUAGE SQL
BEGIN
DECLARE v_usage_percent DECIMAL(5, 2);

SELECT MAX(MEMORY_SET_USED * 100.0 / MEMORY_SET_SIZE)
INTO v_usage_percent
FROM SYSIBMADM.SNAPDB_MEMORY_POOL
WHERE MEMORY_SET_TYPE = ‘DATABASE’;

IF v_usage_percent > 80 THEN
INSERT INTO ALERT_LOG (
ALERT_TIME, ALERT_TYPE, ALERT_MESSAGE
) VALUES (
CURRENT TIMESTAMP, ‘MEMORY’,
‘Database memory usage exceeds 80%: ‘ || CHAR(v_usage_percent) || ‘%’
);
END IF;
END;

# 定期执行监控
CALL SP_CHECK_MEMORY_USAGE();