作为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