版本:DB2V10.1.2
建表时,采用压缩方式(static)大大节省存储空间,提高IO效率。当系统运行一段时间后,如何计算数据库压缩前数据量?根据自身遇到得实际情景,整理以下文档。
------------------------------------------
计算指定schema 压缩后数据量(G)
------------------------------------------
数据库当前数据量(压缩后)比较容易计算出来:
db2 "select char(TABSCHEMA,8),char(TABNAME,30),card,SUM(NPAGES*32/1024/1024) from syscat.tables where TABSCHEMA='PAN' order by NPAGES desc "
------------------------------
计算表压缩前大小(G)
------------------------------
AVGROWCOMPRESSIONRATIO 值的意思是压缩前与压缩后的比值,当没有压缩时,则为0。当AVGROWCOMPRESSIONRATIO 为0时,NAPGES就是原表大小。当不为零时,AVGROWCOMPRESSIONRATIO与 NPAGES的乘积就是原始表大小,语句中通过case when else 语句进行判断。
将结果由科学计数法转为Decimal输出如下:
db2 "select char(TABSCHEMA,8),char(TABNAME,30),card,NPAGES,AVGROWCOMPRESSIONRATIO,case when AVGROWCOMPRESSIONRATIO = 0 then decimal(NPAGES*32/1024/1024,6,3)else decimal(NPAGES*AVGROWCOMPRESSIONRATIO*32/1024/1024,6,3) end from syscat.tables where TABSCHEMA='PAN' order by NPAGES desc "
输出的结果和真实表大小有一定出入,可通过命令行计算单表大小 echo NPAGES*32/1024/1024|bc -l ;原来误差是因为NPAGES*AVGROWCOMPRESSIONRATIO*32/1024/1024中,32/1024/1024都是整数,计算机按整型计算,修改为NPAGES*AVGROWCOMPRESSIONRATIO*32.0/1024.0/1024.0 后,数值正确了。
SQL语句如下:
db2 -x "select char(TABSCHEMA,8),char(TABNAME,30),card,NPAGES,AVGROWCOMPRESSIONRATIO,case when AVGROWCOMPRESSIONRATIO = 0 then decimal(NPAGES*32.0/1024.0/1024.0,6,3)else decimal(NPAGES*AVGROWCOMPRESSIONRATIO*32.0/1024.0/1024.0,6,3) end from syscat.tables where TABSCHEMA='PAN' order by NPAGES desc "
---------------------------
SQL语句执行
---------------------------
$db2 "select char(TABSCHEMA,8)as TABSCHEMA ,char(TABNAME,30) as TABNAME,card,(case when AVGROWCOMPRESSIONRATIO = 0 then decimal(NPAGES*32.0/1024.0/1024.0,6,3)else decimal(NPAGES*AVGROWCOMPRESSIONRATIO*32.0/1024.0/1024.0,6,3) end ) as orig_siz,decimal(NPAGES*32.0/1024.0/1024.0,6,3) as now_siz,decimal(AVGROWCOMPRESSIONRATIO,6,3) as COMPRESSIONRATIO,TYPE from syscat.tables where TABSCHEMA='PE' ORDER by NPAGES desc "
TABSCHEMA TABNAME CARD ORIG_SIZ NOW_SIZ COMPRESSIONRATIO TYPAN
--------- ------------------------------ -------------------- -------- -------- ---------------- ----
PAN AB_YW_B_KJHSPZ 263474848 171.177 171.177 0.000 T
PAN AB_SJ_ABCPLHZ_BBDS 326918400 69.814 23.908 2.920 T
PAN AB_YW_ZTXX_DS 37127168 12.451 12.451 0.000 T
PAN AB_SJ_ABCPLHZ_BBDS_TEMP 151719424 33.441 10.814 3.092 T
PAN AB_YW_ZTYELS_DS 280818528 20.639 8.487 2.431 T
PAN AB_YW_FCPJ_ZTXX_DS 22260320 3.465 3.465 0.000 T
PAN AB_YW_B_KJHSPZ_NEW 4897760 3.208 3.208 0.000 T
PAN AB_YW_ZTXX_DG 6550144 2.450 2.450 0.000 T
PAN AB_YW_ADSPA 53703680 16.172 2.447 6.608 T
PAN AB_YW_ADSPA_TEMP 5601152 1.441 1.441 0.000 T
PAN AB_YW_EMVCCTP 952608 0.218 0.218 0.000 T
PAN AB_JC_LCPZ 3584 0.001 0.001 0.000 T
PAN AB_JC_GDITA 1568 0.000 0.000 0.000 T
PAN AB_JC_HL 3466 0.000 0.000 0.000 T
PAN AB_JC_SLHXJG 127 0.000 0.000 0.000 T
PAN R_AB_JC_SLHXJG 127 0.000 0.000 0.000 S
PAN AB_JC_B_BZ 216 0.000 0.000 0.000 T
PAN AB_JC_SFLX 309 0.000 0.000 0.000 T
PAN AB_JC_ABXX 0 0.000 0.000 0.000 T
PAN GET_ZTXXV -1 0.000 0.000 -1.000 V
PAN TEST1 -1 0.000 0.000 -1.000 T
---------------------------
快捷导入EXCEL,形成统计报告
---------------------------
计算出结果后,如何导出整个表结果(每一列和每一行对应每一表格中)放入excel表格中?
人工逐列筛选,复制粘贴到excel费事费力;经高手指点采用export (modified by coldel0x09)方式以tab分列符方式导出,通过FTP方式传输到本地,放入表格中。大大节省时间。
--深刻体会到与其费力干活,不如多多思考如何省力省时。。。。
$ db2 -x "export to PAN.del of del modified by coldel0x09 select char(TABSCHEMA,8)as TABSCHEMA ,char(TABNAME,30) as TABNAME,card,(case when AVGROWCOMPRESSIONRATIO = 0 then decimal(NPAGES*32.0/1024.0/1024.0,6,3)else decimal(NPAGES*AVGROWCOMPRESSIONRATIO*32.0/1024.0/1024.0,6,3) end ) as orig_siz,decimal(NPAGES*32.0/1024.0/1024.0,6,3) as now_siz,decimal(AVGROWCOMPRESSIONRATIO,6,3) as COMPRESSIONRATIO,TYPE from syscat.tables where TABSCHEMA='PAN' ORDER by NPAGES desc " <
SQL3104N The Export utility is beginning to export data to file "PAN.del".
SQL3105N The Export utility has finished exporting "21" rows.
Number of rows exported: 21
$ more PAN.del
"PAN " "AB_YW_B_KJHSPZ " 263474848 +171.177 +171.177 +000.000 "T"
"PAN " "AB_SJ_ABCPLHZ_BBDS " 326918400 +069.814 +023.908 +002.920 "T"
"PAN " "AB_YW_ZTXX_DS " 37127168 +012.451 +012.451 +000.000 "T"
"PAN " "AB_SJ_ABCPLHZ_BBDS_TEMP " 151719424 +033.441 +010.814 +003.092 "T"
"PAN " "AB_YW_ZTYELS_DS " 280818528 +020.639 +008.487 +002.431 "T"
"PAN " "AB_YW_FCPJ_ZTXX_DS " 22260320 +003.465 +003.465 +000.000 "T"
"PAN " "AB_YW_B_KJHSPZ_NEW " 4897760 +003.208 +003.208 +000.000 "T"
"PAN " "AB_YW_ZTXX_DG " 6550144 +002.450 +002.450 +000.000 "T"
"PAN " "AB_YW_ADSPA " 53703680 +016.172 +002.447 +006.608 "T"
"PAN " "AB_YW_ADSPA_TEMP " 5601152 +001.441 +001.441 +000.000 "T"
"PAN " "AB_YW_EMVCCTP " 952608 +000.218 +000.218 +000.000 "T"
"PAN " "AB_JC_LCPZ " 3584 +000.001 +000.001 +000.000 "T"
"PAN " "AB_JC_GDITA " 1568 +000.000 +000.000 +000.000 "T"
"PAN " "AB_JC_HL " 3466 +000.000 +000.000 +000.000 "T"
"PAN " "AB_JC_SLHXJG " 127 +000.000 +000.000 +000.000 "T"
"PAN " "R_AB_JC_SLHXJG " 127 +000.000 +000.000 +000.000 "S"
"PAN " "AB_JC_B_BZ " 216 +000.000 +000.000 +000.000 "T"
"PAN " "AB_JC_SFLX " 309 +000.000 +000.000 +000.000 "T"
"PAN " "AB_JC_ABXX " 0 +000.000 +000.000 +000.000 "T"
"PAN " "GET_ZTXXV " -1 +000.000 +000.000 -001.000 "V"
"PAN " "TEST1 " -1 +000.000 +000.000 -001.000 "T"
稍加整理,计算schema下所有表的压缩前数据量(G)如下:
db2 -x "export to PAN.del of del modified by coldel0x09 select char(TABSCHEMA,8),char(TABNAME,30),card,NPAGES,AVGROWCOMPRESSIONRATIO,case when AVGROWCOMPRESSIONRATIO = 0 then decimal(NPAGES*32.0/1024.0/1024.0,6,3)else decimal(NPAGES*AVGROWCOMPRESSIONRATIO*32.0/1024.0/1024.0,6,3) end from syscat.tables where TABSCHEMA='UPEP' order by NPAGES desc " |awk 'BEGIN{a=0}{a=$6+a}END{print a}'
计算数据库压缩前大小:
db2 -x "export to PAN.del of del modified by coldel0x09 select char(TABSCHEMA,8)as TABSCHEMA ,char(TABNAME,30) as TABNAME,card,(case when AVGROWCOMPRESSIONRATIO = 0 then decimal(NPAGES*32.0/1024.0/1024.0,6,3)else decimal(NPAGES*AVGROWCOMPRESSIONRATIO*32.0/1024.0/1024.0,6,3) end ) as orig_siz,decimal(NPAGES*32.0/1024.0/1024.0,6,3) as now_siz,decimal(AVGROWCOMPRESSIONRATIO,6,3) as COMPRESSIONRATIO,TYPE from syscat.tables where TABSCHEMA='WAN' or TABSCHEMA='PAN' or TABSCHEMA='PING' ORDER by TABSCHEMA, NPAGES desc "|awk 'BEGIN{a=0}{a=$4+a}{print a}'
计算数据库当前大小:
db2 -x "export to PAN.del of del modified by coldel0x09 select char(TABSCHEMA,8)as TABSCHEMA ,char(TABNAME,30) as TABNAME,card,(case when AVGROWCOMPRESSIONRATIO = 0 then decimal(NPAGES*32.0/1024.0/1024.0,6,3)else decimal(NPAGES*AVGROWCOMPRESSIONRATIO*32.0/1024.0/1024.0,6,3) end ) as orig_siz,decimal(NPAGES*32.0/1024.0/1024.0,6,3) as now_siz,decimal(AVGROWCOMPRESSIONRATIO,6,3) as COMPRESSIONRATIO,TYPE from syscat.tables where TABSCHEMA='WAN' or TABSCHEMA='PAN' or TABSCHEMA='PING' ORDER by TABSCHEMA, NPAGES desc "|awk 'BEGIN{a=0}{a=$5+a}{print a}'
添加新评论0 条评论