DB2表空间相关指令

我是你的DMW

已于 2023-12-14 11:45:16 修改

阅读量716

点赞数 10

分类专栏： DB2 文章标签：数据库

于 2023-12-14 11:42:17 首次发布

本文链接：https://blog.csdn.net/qq_42284070/article/details/134991678

版权

DB2 专栏收录该内容

2 篇文章

订阅专栏

1.查看表空间详细信息：

db2 list tablespaces show detail

2.列出xxx数据库容器详细信息：

 db2pd -db xxx -tablespaces

3.查看表空间的实际文件位置

db2 list tablespaces show detail

db2 list tablespace containers for [Tablespace ID] [show detail]

db2 list tablespace containers for 3

4.查看表状态

DB2 LOAD QUERY TABLE TEST

5.表空间resize

db2 "alter TABLESPACE USERSPACE1 (FILE 'D:\DB2\NODE0000\SAMPLE\T0000002\C0000000.LRG' resize 500m);"

6.表空间extend

db2 "alter TABLESPACE USERSPACE1 (FILE 'D:\DB2\NODE0000\SAMPLE\T0000002\C0000000.LRG' extend 500m);"

7.查看表空间大小：

SELECT A.SNAPSHOT_TIMESTAMP,

 A.TBSP_NAME,

 B.TBSP_TYPE,

 A.TBSP_STATE,

 A.TBSP_USABLE_PAGES * B.TBSP_PAGE_SIZE / 1024 / 1024 / 1024 AS TOTAL,

 A.TBSP_USED_PAGES * B.TBSP_PAGE_SIZE / 1024 / 1024 / 1024 AS USED,

 (A.TBSP_USABLE_PAGES - TBSP_USED_PAGES) * B.TBSP_PAGE_SIZE / 1024 / 1024 / 1024 AS FREE

FROM SYSIBMADM.SNAPTBSP_PART A

INNER JOIN SYSIBMADM.SNAPTBSP B ON A.TBSP_ID = B.TBSP_ID;

8.设置表空间自动扩展

db2  "ALTER TABLESPACE EDCTBS04 AUTORESIZE YES"

9.查看表的实际大小

select TABNAME,sum(x.DATA_OBJECT_P_SIZE+x.INDEX_OBJECT_P_SIZE+x.LOB_OBJECT_P_SIZE+x.LONG_OBJECT_P_SIZE+x.XML_OBJECT_P_SIZE) from SYSIBMADM.ADMINTABINFO x group by TABNAME;

10.查看表的card，npages，物理size

SELECT t.TABSCHEMA,t.TABNAME,t.CARD,t.NPAGES,t.FPAGES,sum(x.DATA_OBJECT_P_SIZE+x.INDEX_OBJECT_P_SIZE+x.LOB_OBJECT_P_SIZE+x.LONG_OBJECT_P_SIZE+x.XML_OBJECT_P_SIZE)/1024/1024 AS SIZE FROM syscat.tables  t LEFT JOIN SYSIBMADM.ADMINTABINFO x
ON t.TABNAME=x.TABNAME
WHERE t.TABSCHEMA='SPC'
GROUP BY t.TABNAME,t.CARD,t.NPAGES,t.FPAGES,t.TABSCHEMA

11.查看分区表每个分区的大小

SELECT t.TABSCHEMA,t.TABNAME,x.DATA_PARTITION_ID,t.CARD,t.NPAGES,t.FPAGES,x.DATA_OBJECT_P_SIZE/1024/1024 AS DATA_SIZE,x.INDEX_OBJECT_P_SIZE/1024/1024 AS INDEX_SIZE  FROM syscat.tables  t
LEFT JOIN SYSIBMADM.ADMINTABINFO x
ON t.TABNAME=x.TABNAME
WHERE t.tabname='F1_GLASS_DEFECT_DATA'
GROUP BY t.TABNAME,t.CARD,x.DATA_PARTITION_ID,t.NPAGES,t.FPAGES,t.TABSCHEMA,x.DATA_OBJECT_P_SIZE,x.INDEX_OBJECT_P_SIZE

13.创建数据大表空间

create large tablespace EDCTBS03 pagesize 8k managed by  DATABASE USING (FILE '/EDCdata/data/edcdb/tablespace/EDCTBS03' 1000G)  bufferpool EDC8KBP01

14.创建索引大表空间

create large tablespace EDCIDX03 pagesize 8k managed by  DATABASE USING (FILE '/EDCdata/data/edcdb/tablespace/EDCIDX03' 50G)  bufferpool EDC8KBP01

更改表空间名

RENAME TABLESPACE RPTTBS03 TO EDCTBS03

创建bufferpool

db2 create bufferpool new_bufferpool pagesize 8192