db2表压缩分析

/*
db2表压缩分析
适用范围：DB29.5,DB29.7
*/
--------------------------------------
-- 确认表范围
-- 查看前10表占用空间最大的表
SELECT * FROM syscat.tables
WHERE TABSCHEMA= 'user_schema'
ORDER BY npages DESC
FETCH FIRST 10 ROWS ONLY

-- 启用表压缩
ALTER TABLE user_table_name compress yes;

-- 行压缩率评估
-- 导出行压缩评估信息
db2 inspect rowcompestimate table name user_table_name schema user_schema results keep user_table_name_out;
-- 格式化输出文件格式，压缩比在50%以上时，建议启用表压缩
db2inspf user_table_name_out user_table_name_out.txt

-- 重组表
db2 reorg table user_table_name keepdictionary

-- 收集关于表和索引的当前统计信息
db2 RUNSTATS ON TABLE user_schema.user_table_name WITH DISTRIBUTION AND DETAILED INDEXES ALL

--------------------------------------
-- 查看表压缩比
SELECT tabname, card, npages, compression, avgrowcompressionratio, avgrowsize, pctpagessaved
FROM syscat.tables
WHERE TABSCHEMA= 'DB2INST1'
AND COMPRESSION = 'R'
ORDER BY tabname

-- 表空间剩余空间计算
SELECT TBSP_NAME
, tbsp_total_pages * 16 /1024/1024 AS tbsp_total_size_g
, tbsp_usable_pages * 16 /1024/1024 AS tbsp_usable_size_g
, tbsp_used_pages * 16 /1024/1024 AS tbsp_used_size_g
, tbsp_free_pages * 16 /1024/1024 AS tbsp_free_size_g
FROM SYSIBMADM.SNAPTBSP_PART
--------------------------------------