DB2: 查询表的大小的SQL（支持分区表、DPF环境） |
您所在的位置：网站首页 › mysql查询表空间大小及使用情况 › 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 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |