DB2 TABLESPACE 表空间状态查询

数据-脚本-资源-管道

已于 2023-08-16 10:36:17 修改

阅读量1k

点赞数

文章标签：数据库 sql

于 2023-08-16 10:09:26 首次发布

本文链接：https://blog.csdn.net/cd0_0/article/details/132313313

版权

两种方法

1.命令行方式:

su - db2inst1
db2 connect to testdb
db2 list tablespaces

结果如下

           Tablespaces for Current Database

 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID                        = 3
 Name                                 = SYSTOOLSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID                        = 4
 Name                                 = MY_TABLESPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal

2.sql查询方式可以使用sql工具连接后查询

以下SQL仅适用于v11.5社区版，其他版本是否适用未知。

SELECT
    TU.TBSP_NAME,
    TU.TBSP_TYPE,
    TU.TBSP_STATE,
  	TS.OWNER,
  	TS.DEFINER,
    CAST((TU.TBSP_TOTAL_SIZE_KB / 1024) AS VARCHAR) || 'MB' AS TOTAL_SIZE_MB,
    CAST((TU.TBSP_USED_SIZE_KB / 1024) AS VARCHAR) || 'MB' AS USED_SIZE_MB,
    CAST((TU.TBSP_FREE_SIZE_KB / 1024) AS VARCHAR) || 'MB' AS FREE_SIZE_MB,
    TU.TBSP_TOTAL_PAGES,
	TU.TBSP_USABLE_PAGES,
	TU.TBSP_USED_PAGES,
	TU.TBSP_FREE_PAGES,
	TU.TBSP_PAGE_TOP,
	TU.TBSP_PAGE_SIZE
FROM
    SYSIBMADM.TBSP_UTILIZATION TU
LEFT JOIN SYSCAT.TABLESPACES TS ON TS.TBSPACEID = TU.TBSP_ID

查询结果如下