DB2: 查询表的大小的SQL（支持分区表、DPF环境）

最新推荐文章于 2023-12-21 17:36:47 发布

匿_名_用_户

最新推荐文章于 2023-12-21 17:36:47 发布

阅读量3.5k

点赞数 2

分类专栏： DB2

本文链接：https://blog.csdn.net/qingsong3333/article/details/108064217

版权

DB2 专栏收录该内容

181 篇文章 9 订阅

订阅专栏

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

匿_名_用_户

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
2
评论
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_.
复制链接

扫一扫