db2 最大分区数_DB2: 查询表的大小的SQL（支持分区表、DPF环境）

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

from 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.0000000000

9 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_MB

from (

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_MB

from 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的基础上再来一层

select

TABSCHEMA,

TABNAME,

TOTAL_DATA_SZ_MB+TOTAL_INDEX_SZ_MB+TOTAL_LONG_LOB_XML_SZ_MB as TOTAL_SZ_MB

from

(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_MB

from (

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_MB

from 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