--------------------------------------
-- 确认表范围
-- 查看前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
--------------------------------------