一、表空间信息查看
# 1.查看所有的表空间
[db2inst1@MaxwellDBA instance]$ db2 list tablespaces
[db2inst1@MaxwellDBA instance]$ db2 list tablespaces show detail
[db2inst1@MaxwellDBA instance]$ db2 connect to books
Database Connection Information
Database server = DB2/LINUXX8664 11.5.7.0
SQL authorization ID = DB2INST1
Local database alias = BOOKS
[db2inst1@MaxwellDBA instance]$ 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
[db2inst1@MaxwellDBA instance]$ db2 list tablespaces show detail
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
Total pages = 32768
Useable pages = 32764
Used pages = 30472
Free pages = 2292
High water mark (pages) = 30472
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8160
Used pages = 864
Free pages = 7296
High water mark (pages) = 864
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8188
Used pages = 152
Free pages = 8036
High water mark (pages) = 152
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
[db2inst1@MaxwellDBA instance]$ #2.查看某个表空间的容器
[db2inst1@MaxwellDBA instance]$ db2 list tablespace containers for 3 show detail
[db2inst1@MaxwellDBA instance]$ db2 list tablespace containers for 3 show detail
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/BOOKS/T0000003/C0000000.LRG
Type = File
Total pages = 8192
Useable pages = 8188
Accessible = Yes
[db2inst1@MaxwellDBA instance]$ # 3.查看表空间的配置信息，使用情况及容器信息
[db2inst1@MaxwellDBA instance]$ db2pd -d -tablespaces
[db2inst1@MaxwellDBA instance]$ db2pd -d -tablespaces
-db option requires <database>
Instance db2inst1 uses 64 bits and DB2 code release SQL11057
with level identifier 0608010F
Informational tokens are DB2 v11.5.7.0, s2111221000, DYN2111221000AMD64, Fix Pack 0.
Operating System Information:
OSName: Linux
NodeName: MaxwellDBA
Version: 4
Release: 18
Machine: x86_64
Distros: CentOS 8.5
CPU Information:
TotalCPU OnlineCPU ConfigCPU Speed(MHz) HMTDegree Cores/Socket
2 2 2 2500 2 1
CPU Cache Information:
L2 L3
1048576 37486592
Physical Memory and Swap (Megabytes):
TotalMem FreeMem AvailMem TotalSwap FreeSwap
3592 114 n/a 0 0
Virtual Memory (Megabytes):
Total Reserved Available Free
3592 n/a n/a 114
Message Queue Information:
MsgSeg MsgMax MsgMap MsgMni MsgTql MsgMnb MsgSsz
n/a 65536 65536 32768 65536 65536 16
Shared Memory Information:
ShmMax ShmMin ShmIds ShmSeg
4398046511104 1 32768 32768
Semaphore Information:
SemMap SemMni SemMns SemMnu SemMsl SemOpm SemUme SemUsz SemVmx SemAem
256000 32768 256000 256000 250 100 n/a 20 32767 32767
CPU Load Information:
Short Medium Long
1.070000 1.090000 1.080000
CPU Usage Information (percent):
Total Usr Sys Wait Idle
51.010101 43.434343 7.575758 0.000000 48.484848
[db2inst1@MaxwellDBA instance]$ # 4.通过snapshot 获取表空间信息
db2 get snapshot for tablespaces on database_name;
[db2inst1@MaxwellDBA instance]$ db2 get snapshot for tablespaces on books;
Tablespace Snapshot
First database connect timestamp = 09/13/2022 11:57:29.603053
Last reset timestamp =
Snapshot timestamp = 09/13/2022 12:06:17.202925
Database name = BOOKS
Database path = /home/db2inst1/db2inst1/NODE0000/SQL00002/MEMBER0000/
Input database alias = BOOKS
Number of accessed tablespaces = 4
Tablespace name = SYSCATSPACE
Tablespace ID = 0
Tablespace Type = Database managed space
Tablespace Content Type = All permanent data. Regular table space.
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 4
Automatic Prefetch size enabled = Yes
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Using automatic storage = Yes
Auto-resize enabled = Yes
File system caching = None
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Tablespace Prefetch size (pages) = 4
Total number of pages = 32768
Number of usable pages = 32764
Number of used pages = 30472
Number of pending free pages = 0
Number of free pages = 2292
High water mark (pages) = 30472
Initial tablespace size (bytes) = 33554432
Current tablespace size (bytes) = 134217728
Maximum tablespace size (bytes) = NONE
Increase size (bytes) = AUTOMATIC
Time of last successful resize =
Last resize attempt failed = No
Rebalancer Mode = No Rebalancing
Storage paths have been dropped = No
Minimum Recovery Time =
Number of quiescers = 0
Number of containers = 1
Container Name = /home/db2inst1/db2inst1/NODE0000/BOOKS/T0000000/C0000000.CAT
Container ID = 0
Container Type = File (extent sized tag)
Total Pages in Container = 32768
Usable Pages in Container = 32764
Stripe Set = 0
Container is accessible = Yes
Table space map:
Range Stripe Stripe Max Max Start End Adj. Containers
Number Set Offset Extent Page Stripe Stripe
[ 0] [ 0] 0 8190 32763 0 8190 0 1 (0)
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Asynchronous pool data page reads = Not Collected
Buffer pool data writes = Not Collected
Asynchronous pool data page writes = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index physical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Asynchronous pool index page reads = Not Collected
Buffer pool index writes = Not Collected
Asynchronous pool index page writes = Not Collected
Total buffer pool read time (millisec) = Not Collected
Total buffer pool write time (millisec) = Not Collected
Total elapsed asynchronous read time = Not Collected
Total elapsed asynchronous write time = Not Collected
Asynchronous data read requests = Not Collected
Asynchronous index read requests = Not Collected
No victim buffers available = Not Collected
Direct reads = Not Collected
Direct writes = Not Collected
Direct read requests = Not Collected
Direct write requests = Not Collected
Direct reads elapsed time (ms) = Not Collected
Direct write elapsed time (ms) = Not Collected
Number of files closed = Not Collected
Tablespace name = TEMPSPACE1
Tablespace ID = 1
Tablespace Type = System managed space
Tablespace Content Type = System Temporary data
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 32
Automatic Prefetch size enabled = Yes
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Using automatic storage = Yes
File system caching = Yes
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Tablespace Prefetch size (pages) = 32
Total number of pages = 0
Number of usable pages = 0
Number of used pages = 0
Storage paths have been dropped = No
Minimum Recovery Time =
Number of quiescers = 0
Number of containers = 1
Container Name = /home/db2inst1/db2inst1/NODE0000/BOOKS/T0000001/C0000000.TMP
Container ID = 0
Container Type = Path
Total Pages in Container = 0
Usable Pages in Container = 0
Stripe Set = 0
Container is accessible = Yes
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Asynchronous pool data page reads = Not Collected
Buffer pool data writes = Not Collected
Asynchronous pool data page writes = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index physical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Asynchronous pool index page reads = Not Collected
Buffer pool index writes = Not Collected
Asynchronous pool index page writes = Not Collected
Total buffer pool read time (millisec) = Not Collected
Total buffer pool write time (millisec) = Not Collected
Total elapsed asynchronous read time = Not Collected
Total elapsed asynchronous write time = Not Collected
Asynchronous data read requests = Not Collected
Asynchronous index read requests = Not Collected
No victim buffers available = Not Collected
Direct reads = Not Collected
Direct writes = Not Collected
Direct read requests = Not Collected
Direct write requests = Not Collected
Direct reads elapsed time (ms) = Not Collected
Direct write elapsed time (ms) = Not Collected
Number of files closed = Not Collected
Tablespace name = USERSPACE1
Tablespace ID = 2
Tablespace Type = Database managed space
Tablespace Content Type = All permanent data. Large table space.
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 32
Automatic Prefetch size enabled = Yes
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Using automatic storage = Yes
Auto-resize enabled = Yes
File system caching = None
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Tablespace Prefetch size (pages) = 32
Total number of pages = 8192
Number of usable pages = 8160
Number of used pages = 864
Number of pending free pages = 0
Number of free pages = 7296
High water mark (pages) = 864
Initial tablespace size (bytes) = 33554432
Current tablespace size (bytes) = 33554432
Maximum tablespace size (bytes) = NONE
Increase size (bytes) = AUTOMATIC
Time of last successful resize =
Last resize attempt failed = No
Rebalancer Mode = No Rebalancing
Storage paths have been dropped = No
Minimum Recovery Time =
Number of quiescers = 0
Number of containers = 1
Container Name = /home/db2inst1/db2inst1/NODE0000/BOOKS/T0000002/C0000000.LRG
Container ID = 0
Container Type = File (extent sized tag)
Total Pages in Container = 8192
Usable Pages in Container = 8160
Stripe Set = 0
Container is accessible = Yes
Table space map:
Range Stripe Stripe Max Max Start End Adj. Containers
Number Set Offset Extent Page Stripe Stripe
[ 0] [ 0] 0 254 8159 0 254 0 1 (0)
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Asynchronous pool data page reads = Not Collected
Buffer pool data writes = Not Collected
Asynchronous pool data page writes = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index physical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Asynchronous pool index page reads = Not Collected
Buffer pool index writes = Not Collected
Asynchronous pool index page writes = Not Collected
Total buffer pool read time (millisec) = Not Collected
Total buffer pool write time (millisec) = Not Collected
Total elapsed asynchronous read time = Not Collected
Total elapsed asynchronous write time = Not Collected
Asynchronous data read requests = Not Collected
Asynchronous index read requests = Not Collected
No victim buffers available = Not Collected
Direct reads = Not Collected
Direct writes = Not Collected
Direct read requests = Not Collected
Direct write requests = Not Collected
Direct reads elapsed time (ms) = Not Collected
Direct write elapsed time (ms) = Not Collected
Number of files closed = Not Collected
Tablespace name = SYSTOOLSPACE
Tablespace ID = 3
Tablespace Type = Database managed space
Tablespace Content Type = All permanent data. Large table space.
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 4
Automatic Prefetch size enabled = Yes
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Using automatic storage = Yes
Auto-resize enabled = Yes
File system caching = None
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Tablespace Prefetch size (pages) = 4
Total number of pages = 8192
Number of usable pages = 8188
Number of used pages = 152
Number of pending free pages = 0
Number of free pages = 8036
High water mark (pages) = 152
Initial tablespace size (bytes) = 33554432
Current tablespace size (bytes) = 33554432
Maximum tablespace size (bytes) = NONE
Increase size (bytes) = AUTOMATIC
Time of last successful resize =
Last resize attempt failed = No
Rebalancer Mode = No Rebalancing
Storage paths have been dropped = No
Minimum Recovery Time =
Number of quiescers = 0
Number of containers = 1
Container Name = /home/db2inst1/db2inst1/NODE0000/BOOKS/T0000003/C0000000.LRG
Container ID = 0
Container Type = File (extent sized tag)
Total Pages in Container = 8192
Usable Pages in Container = 8188
Stripe Set = 0
Container is accessible = Yes
Table space map:
Range Stripe Stripe Max Max Start End Adj. Containers
Number Set Offset Extent Page Stripe Stripe
[ 0] [ 0] 0 2046 8187 0 2046 0 1 (0)
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Asynchronous pool data page reads = Not Collected
Buffer pool data writes = Not Collected
Asynchronous pool data page writes = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index physical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Asynchronous pool index page reads = Not Collected
Buffer pool index writes = Not Collected
Asynchronous pool index page writes = Not Collected
Total buffer pool read time (millisec) = Not Collected
Total buffer pool write time (millisec) = Not Collected
Total elapsed asynchronous read time = Not Collected
Total elapsed asynchronous write time = Not Collected
Asynchronous data read requests = Not Collected
Asynchronous index read requests = Not Collected
No victim buffers available = Not Collected
Direct reads = Not Collected
Direct writes = Not Collected
Direct read requests = Not Collected
Direct write requests = Not Collected
Direct reads elapsed time (ms) = Not Collected
Direct write elapsed time (ms) = Not Collected
Number of files closed = Not Collected
[db2inst1@MaxwellDBA instance]$ 二、表空间、表容器管理
对于DMS（database manage）表空间，提供了几个方法更改表空间容器。
1.Add 用来增加新的容器
2.Drop删除容器
3.Extend扩展已有容器大小
4.Reduce缩小已有容器大小
5.Resize重新设定容器大小
Add和Drop操作，表空间容器之间会发生数据重新平衡(rebalance)，Reduce和Resize操作，需要确保修改后的表空间容器有足够的空间，否
则DB2会拒绝该操作。
日常运维中出现表空间满的情况时，根据对存储空间和对运维的影响，有以下三种方案：
A，增加表空间、表容器，可通过alter tablespace 的extend或resize选项扩展已有表空间容器的大小。
增加一个10000页的表容器文件。
db2 "ALTER TABLESPACE PAYROLL ADD(DEVICE '/dev/rhdisk9' 10000)"
db2 ” ALTER TABLESPACE IBSDATA ADD (DEVICE ‘/dev/ribsdata1’ 10G,DEVICE ‘/dev/ribsdata2’ 10G,DEVICE ‘/dev/ribsdata3’ 10G,DEVICE ‘/dev/ribsdata4’ 10G) ”
增加一个10G表容器文件，表空间的不同容器内会发生reblance
db2 “alter tablespace tbs_dat add ( file ‘/db2tbsp/dat_2’ 10G)”
db2 => alter tablespace user2 add (file ‘/home/db2inst1/db2inst1/NODE0000/TEST1/container2’ 10000)
使用新增加的容器。该选项不会在容器之间做reblance，不会对系统造成性能影响，但它会造成数据偏移。如下：
db2 “alter tablespace tbs_dat begin new stripe set (file ‘/db2tbsp/dat_2’ 10G) ”
2).修改表空间、表容器大小
给表容器扩展50GB：
db2 “alter tablespace tbs_dat extend ( file ‘/db2tbsp/dat_1’ 50G, file ‘/db2tbsp/dat_2’ 50G) “;
将相应的容器都扩大1000页，也就是增加1000页
db2 ” ALTER TABLESPACE TS1 EXTEND (FILE ‘/conts/cont0’ 1000, DEVICE ‘/dev/rcont1’ 1000, FILE ‘cont2’ 1000) ”
将原有的相应容器都改成大小是2000页
db2 ” ALTER TABLESPACE TS1 RESIZE (FILE ‘/conts/cont0’ 2000, DEVICE ‘/dev/rcont1’ 2000, FILE ‘cont2’ 2000) ”
把表空间中所有的容器大小都改成2000页
db2 “ALTER TABLESPACE TS1 RESIZE (ALL 2000)”
3） 删除表空间tbs_dat的容器dat_2
db2 “alter tablespace tbs_dat drop ( file ‘/db2tbsp/dat_2’ )”
仅为一个container缩减10个page
db2 => alter tablespace user2 reduce (file ‘/home/db2inst1/db2inst1/NODE0000/TEST1/container1’ 10)
4）表空间容器的路径变化时，需要建立一个软链接(容器的原路径软链接到容器的新路径)，如：
ln -s /db2tbs/zdb/dat_1 /db2tbsp/dat_1
三、 降低 部分表空间 的高水位，例如：TBS_MXZ
1）ALTER TABLESPACE TBS_LSB LOWER HIGH WATER MARK；
执行完成上述这个命令后，需要过一段时间直到表空间状态由0x80000变成0x00000后才可以继续执行
然后在采用 redue 命令，更改每个文件大小为20G
2）ALTER TABLESPACE TBS_LSB REDUCE (ALL CONTAINERS 20G)；
四、表空间使用率
1. 统计所有节点表空间使用率
select substr(TABLESPACE_NAME,1,20) as TBSPC_NAME,bigint(TOTAL_PAGES * PAGE_SIZE)/1024/1024 as “TOTAL(MB)”,
used_pages*PAGE_SIZE/1024/1024 as “USED(MB)”, free_pages*PAGE_SIZE/1024/1024 as “FREE(MB)”
from table(snapshot_tbs_cfg(‘DB_NAME’, -2)) as snapshot_tbs_cfg2、查看表空间使用率
select substr(tbsp_name,1,20) as TABLESPACE_NAME,substr(tbsp_content_type,1,10) as TABLESPACE_TYPE,sum(tbsp_total_size_kb)/1024 as TOTAL_MB,
sum(tbsp_used_size_kb)/1024 as USED_MB,sum(tbsp_free_size_kb)/1024 as FREE_MB,tbsp_page_size AS PAGE_SIZE
from SYSIBMADM.TBSP_UTILIZATION group by tbsp_name,tbsp_content_type,tbsp_page_size
order by 13、通过存储过程查看数据库大小和容量
db2 call GET_DBSIZE_INFO (?,?,?,0)五、常用查看数据库表空间、容器、页大小方式
1、查看数据库页大小
db2 get db cfg
2、收集表状态信息
db2 runstats on table schema.table_name
3、查询数据表占用页的数量
select tabname, npages from syscat.tables where tabname = ‘table_name’
4、计算表占用磁盘空间大小
表占用磁盘空间大小 = 数据页大小 * 页数量
查看表占磁盘空间大小：select tabname, npages*16384/(1024*1024) from syscat.tables where tabname = ‘XXXXXX’
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
