作为DBA，可能需要经常查看数据库中表的大小，以下讨论如何使用SQL快速查看。所谓快速，是指不一定准确，因为用的是mon_get_table 表函数，如果想查看真实准确的大小，需要使用ADMIN_GET_TAB_INFO，不过这个耗时比较长，和表大小本身有关系。
查看单个表大小
查看表T3的大小, 其中member表示DPF的分区号，DATA_PARTITION_ID表示分区表的表分区号。
DATA_SZ_MB表示DATA页总大小，INDEX_SZ_MB表示索引页总大小，LONG_LOB_XML_SZ_MB表示LONG+LOB+XML总大小。
SQL如下（注意，执行SQL前要求数据库激活后，表T3曾被访问过）
select varchar(TABSCHEMA,40) as TABNAME,varchar(TABNAME,60) as TABNAME,MEMBER, DATA_PARTITION_ID,mt.TBSP_ID,mt.INDEX_TBSP_ID,mt.LONG_TBSP_ID, COALESCE(DATA_OBJECT_L_PAGES,0) as DATA_PAGES, COALESCE(INDEX_OBJECT_L_PAGES,0) as INDEX_PAGES, COALESCE(LOB_OBJECT_L_PAGES,0)+COALESCE(LONG_OBJECT_L_PAGES,0)+COALESCE(XDA_OBJECT_L_PAGES,0) AS LONG_LOB_XML_PAGES, st1.PAGESIZE as DATA_PAGESIZE, st2.PAGESIZE as INDEX_PAGESIZE, st3.PAGESIZE as LONG_PAGESIZE,COALESCE(DATA_OBJECT_L_PAGES,0)*st1.PAGESIZE/1024.0/1024.0 AS DATA_SZ_MB,COALESCE(INDEX_OBJECT_L_PAGES,0)*st2.PAGESIZE/1024.0/1024.0 AS INDEX_SZ_MB,(COALESCE(LOB_OBJECT_L_PAGES,0)+COALESCE(LONG_OBJECT_L_PAGES,0)+COALESCE(XDA_OBJECT_L_PAGES,0))*st3.PAGESIZE/1024.0/1024.0 AS LONG_LOB_XML_SZ_MBfrom TABLE(MON_GET_TABLE('','',-2)) as mt left join syscat.tablespaces as st1 on mt.TBSP_ID=st1.TBSPACEID left join syscat.tablespaces as st2 on mt.INDEX_TBSP_ID=st2.TBSPACEID left join syscat.tablespaces as st3 on mt.LONG_TBSP_ID=st3.TBSPACEID where TABNAME='T3' ;
这里的输出针对每张表的每个表分区/数据库分区都展示一条记录，比如表T3有3个分区，数据库为DPF架构，共有3个节点，那么结果中有9条记录， 结果如下：
TABNAME TABNAME MEMBER DATA_PARTITION_ID TBSP_ID INDEX_TBSP_ID LONG_TBSP_ID DATA_PAGES INDEX_PAGES LONG_LOB_XML_PAGES DATA_PAGESIZE INDEX_PAGESIZE LONG_PAGESIZE DATA_SZ_MB INDEX_SZ_MB LONG_LOB_XML_SZ_MB
---------------------------------------- ------------------------------------------------------------ ------ ----------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------- -------------- ------------- --------------------------------- --------------------------------- ---------------------------------
DB2DPF T3 1 0 6 6 6 1 0 0 4096 4096 4096 0.0039062500 0.0000000000 0.0000000000
DB2DPF T3 1 1 5 5 5 1 0 0 4096 4096 4096 0.0039062500 0.0000000000 0.0000000000
DB2DPF T3 1 2 5 5 5 1 0 0 4096 4096 4096 0.0039062500 0.0000000000 0.0000000000
DB2DPF T3 0 0 6 6 6 1 0 0 4096 4096 4096 0.0039062500 0.0000000000 0.0000000000
DB2DPF T3 0 1 5 5 5 1 0 0 4096 4096 4096 0.0039062500 0.0000000000 0.0000000000
DB2DPF T3 0 2 5 5 5 1 0 0 4096 4096 4096 0.0039062500 0.0000000000 0.0000000000
DB2DPF T3 2 0 6 6 6 1 0 0 4096 4096 4096 0.0039062500 0.0000000000 0.0000000000
DB2DPF T3 2 1 5 5 5 1 0 0 4096 4096 4096 0.0039062500 0.0000000000 0.0000000000
DB2DPF T3 2 2 5 5 5 1 0 0 4096 4096 4096 0.0039062500 0.0000000000 0.00000000009 record(s) selected.
查看每个表大小
查看每个表(系统表除外)的数据页大小、索引页大小、LONG对象大小， 按照每个表总大小排序
select TABSCHEMA, TABNAME, sum(DATA_SZ_MB) as TOTAL_DATA_SZ_MB,sum(INDEX_SZ_MB) as TOTAL_INDEX_SZ_MB,sum(LONG_LOB_XML_SZ_MB) as TOTAL_LONG_LOB_XML_SZ_MBfrom (select varchar(TABSCHEMA,40) as TABSCHEMA,varchar(TABNAME,60) as TABNAME,MEMBER, DATA_PARTITION_ID,mt.TBSP_ID,mt.INDEX_TBSP_ID,mt.LONG_TBSP_ID, COALESCE(DATA_OBJECT_L_PAGES,0) as DATA_PAGES, COALESCE(INDEX_OBJECT_L_PAGES,0) as INDEX_PAGES, COALESCE(LOB_OBJECT_L_PAGES,0)+COALESCE(LONG_OBJECT_L_PAGES,0)+COALESCE(XDA_OBJECT_L_PAGES,0) AS LONG_LOB_XML_PAGES, st1.PAGESIZE as DATA_PAGESIZE, st2.PAGESIZE as INDEX_PAGESIZE, st3.PAGESIZE as LONG_PAGESIZE,COALESCE(DATA_OBJECT_L_PAGES,0)*st1.PAGESIZE/1024.0/1024.0 AS DATA_SZ_MB,COALESCE(INDEX_OBJECT_L_PAGES,0)*st2.PAGESIZE/1024.0/1024.0 AS INDEX_SZ_MB,(COALESCE(LOB_OBJECT_L_PAGES,0)+COALESCE(LONG_OBJECT_L_PAGES,0)+COALESCE(XDA_OBJECT_L_PAGES,0))*st3.PAGESIZE/1024.0/1024.0 AS LONG_LOB_XML_SZ_MBfrom TABLE(MON_GET_TABLE('','',-2)) as mt left join syscat.tablespaces as st1 on mt.TBSP_ID=st1.TBSPACEID left join syscat.tablespaces as st2 on mt.INDEX_TBSP_ID=st2.TBSPACEID left join syscat.tablespaces as st3 on mt.LONG_TBSP_ID=st3.TBSPACEID ) as T group by TABSCHEMA, TABNAME HAVING TABSCHEMA not like 'SYS%'order by TOTAL_DATA_SZ_MB+TOTAL_INDEX_SZ_MB+TOTAL_LONG_LOB_XML_SZ_MB desc ;
查看每个表的总大小，需要在上面SQL的基础上再来一层
selectTABSCHEMA, TABNAME,TOTAL_DATA_SZ_MB+TOTAL_INDEX_SZ_MB+TOTAL_LONG_LOB_XML_SZ_MB as TOTAL_SZ_MBfrom (select TABSCHEMA, TABNAME, sum(DATA_SZ_MB) as TOTAL_DATA_SZ_MB,sum(INDEX_SZ_MB) as TOTAL_INDEX_SZ_MB,sum(LONG_LOB_XML_SZ_MB) as TOTAL_LONG_LOB_XML_SZ_MBfrom (select varchar(TABSCHEMA,40) as TABSCHEMA,varchar(TABNAME,60) as TABNAME,MEMBER, DATA_PARTITION_ID,mt.TBSP_ID,mt.INDEX_TBSP_ID,mt.LONG_TBSP_ID, COALESCE(DATA_OBJECT_L_PAGES,0) as DATA_PAGES, COALESCE(INDEX_OBJECT_L_PAGES,0) as INDEX_PAGES, COALESCE(LOB_OBJECT_L_PAGES,0)+COALESCE(LONG_OBJECT_L_PAGES,0)+COALESCE(XDA_OBJECT_L_PAGES,0) AS LONG_LOB_XML_PAGES, st1.PAGESIZE as DATA_PAGESIZE, st2.PAGESIZE as INDEX_PAGESIZE, st3.PAGESIZE as LONG_PAGESIZE,COALESCE(DATA_OBJECT_L_PAGES,0)*st1.PAGESIZE/1024.0/1024.0 AS DATA_SZ_MB,COALESCE(INDEX_OBJECT_L_PAGES,0)*st2.PAGESIZE/1024.0/1024.0 AS INDEX_SZ_MB,(COALESCE(LOB_OBJECT_L_PAGES,0)+COALESCE(LONG_OBJECT_L_PAGES,0)+COALESCE(XDA_OBJECT_L_PAGES,0))*st3.PAGESIZE/1024.0/1024.0 AS LONG_LOB_XML_SZ_MBfrom TABLE(MON_GET_TABLE('','',-2)) as mt left join syscat.tablespaces as st1 on mt.TBSP_ID=st1.TBSPACEID left join syscat.tablespaces as st2 on mt.INDEX_TBSP_ID=st2.TBSPACEID left join syscat.tablespaces as st3 on mt.LONG_TBSP_ID=st3.TBSPACEID ) as T group by TABSCHEMA, TABNAME HAVING TABSCHEMA not like 'SYS%'order by TOTAL_DATA_SZ_MB+TOTAL_INDEX_SZ_MB+TOTAL_LONG_LOB_XML_SZ_MB desc) ;
参考资料： IBM KNOWLEDGE CENTER