db2pd -d testy -tablespace
Database Member 1001 -- Database TESTY -- Active -- Up 0 days 00:04:35 -- Date 2020-01-15-20.51.57.160934
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007FFF814628C0 0 DMS Regular 4096 4 Yes 4 1 1 Off 1 0 3 Yes SYSCATSPACE
0x00007FFF81470280 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007FFF8147FBC0 2 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 Yes USERSPACE1
0x00007FFF88E80080 3 DMS Large 4096 4 Yes 4 1 1 Off 1 0 3 Yes SYSTOOLSPACE
0x00007FFF88E8D240 4 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 Yes DMS_DATA
0x00007FFF88E9A400 5 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 Yes DMS_INDEX
0x00007FFF88EA75C0 6 SMS UsrTmp 4096 4 Yes 4 1 1 On 1 0 3 No SYSTOOLSTMPSPACE
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState
0x00007FFF814628C0 0 32768 32764 29096 0 3668 29096 29096 0x00000000 0 0 No n/a
0x00007FFF81470280 1 1 1 1 0 0 - - 0x00000000 0 0 No n/a
0x00007FFF8147FBC0 2 8192 8160 96 0 8064 96 96 0x00000000 0 0 No n/a
0x00007FFF88E80080 3 8192 8188 160 0 8028 160 160 0x00000000 1533197654 0 No n/a
0x00007FFF88E8D240 4 51200 51168 224 0 50944 224 224 0x00000000 1540884499 0 No n/a
0x00007FFF88E9A400 5 12800 12768 96 0 12672 96 96 0x00000000 1468504540 0 No n/a
0x00007FFF88EA75C0 6 1 1 1 0 0 - - 0x00000000 1533197685 0 No n/a
Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x00007FFF814628C0 0 Yes Yes 33554432 -1 No None None No
0x00007FFF81470280 1 Yes No 0 0 No 0 None No
0x00007FFF8147FBC0 2 Yes Yes 33554432 -1 No None None No
0x00007FFF88E80080 3 Yes Yes 33554432 -1 No None None No
0x00007FFF88E8D240 4 No No 0 0 No 0 None No
0x00007FFF88E9A400 5 No No 0 0 No 0 None No
0x00007FFF88EA75C0 6 Yes No 0 0 No 0 None No
Tablespace Storage Statistics:
Address Id DataTag Rebalance SGID SourceSGID
0x00007FFF814628C0 0 0 No 0 -
0x00007FFF81470280 1 0 No 0 -
0x00007FFF8147FBC0 2 -1 No 0 -
0x00007FFF88E80080 3 -1 No 0 -
0x00007FFF88E8D240 4 0 No - -
0x00007FFF88E9A400 5 0 No - -
0x00007FFF88EA75C0 6 0 No 0 -
Containers:
Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
0x00007FFF8146FFA0 0 0 File 32768 32764 0 0 /db2data/db2inst1/NODE0000/TESTY/T0000000/C0000000.CAT
0x00007FFF8147D840 1 0 Path 1 1 0 0 /db2data/db2inst1/NODE0000/TESTY/T0000001/C0000000.TMP
0x00007FFF8148D2A0 2 0 File 8192 8160 0 0 /db2data/db2inst1/NODE0000/TESTY/T0000002/C0000000.LRG
0x00007FFF8148DAA0 3 0 File 8192 8188 0 0 /db2data/db2inst1/NODE0000/TESTY/T0000003/C0000000.LRG
0x00007FFF8148E2A0 4 0 File 51200 51168 - 0 /db2data/db2inst1/NODE0000/TESTY/T0000004/C0000000.LRG
0x00007FFF8148EAA0 5 0 File 12800 12768 - 0 /db2data/db2inst1/NODE0000/TESTY/T0000005/C0000000.LRG
0x00007FFF8148F180 6 0 Path 1 1 0 0 /db2data/db2inst1/NODE0000/TESTY/T0000006/C0000000.UTM
db2 list tablespace containers for 0 show detail
Tablespace Containers for Tablespace 0
Container ID = 0
Name = /db2data/db2inst1/NODE0000/TESTY/T0000000/C0000000.CAT
Type = File
Total pages = 32768
Useable pages = 32764
Accessible = Yes
db2 "select TBSP_NAME,tbsp_using_auto_storage from sysibmadm.tbsp_utilization"
TBSP_NAME TBSP_USING_AUTO_STORAGE
-------------------------------------------------------------------------------------------------------------------------------- -----------------------
SYSCATSPACE 1
TEMPSPACE1 1
USERSPACE1 1
SYSTOOLSPACE 1
DMS_DATA 0
DMS_INDEX 0
SYSTOOLSTMPSPACE 1
手工关闭自动自动扩展
db2 " ALTER TABLESPACE SYSCATSPACE AUTORESIZE NO"
DB20000I The SQL command completed successfully.
[db2inst1@node ~]$ db2 "? SQL20318N "
SQL20318N The ALTER TABLESPACE statement failed because the change is
not allowed for the type of table space. Table space name:
"<tablespace-name>". Table space type: "<tablespace-type>".
Incompatible clause: "<clause>".
Explanation:
The following are operations that are incompatible with a corresponding
table space type, as indicated.
* A table space defined as MANAGED BY AUTOMATIC STORAGE has its
containers managed by the database manager. This means that the
database manager will automatically extend existing containers or
create new containers based on the storage paths defined for the
storage group with which the table space is associated. Containers
for table space defined as MANAGED BY AUTOMATIC STORAGE cannot be
altered by the ALTER TABLESPACE statement.
* The INHERIT option cannot be specified for the DATA TAG, OVERHEAD, or
TRANSFERRATE clauses with DMS or SMS table spaces
* The clause 'USING STOGROUP' cannot be used with DMS or SMS table
spaces
* STOGROUP cannot be changed for temporary automatic storage table
spaces
* DATA TAG cannot be set for a temporary table space or a system
catalog table space
User response:
Rewrite the ALTER TABLESPACE statement to ensure that the operations are
compatible with the table space type.
sqlcode: -20318
sqlstate: 42858
Related information:
ALTER TABLESPACE statement
[db2inst1@node ~]$ db2 get snapshot for tablespaces on testy|more
Tablespace Snapshot
First database connect timestamp = 01/15/2020 21:43:20.813160
Last reset timestamp =
Snapshot timestamp = 01/15/2020 21:53:31.419603
Database name = TESTY
Database path = /db2data/db2inst1/NODE0000/SQL00001/MEMBER0000/
Input database alias = TESTY
Number of accessed tablespaces = 7
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 = No
File system caching = No
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 = 29096
Number of pending free pages = 0
Number of free pages = 3668
High water mark (pages) = 29096
Initial tablespace size (bytes) = 33554432
Current tablespace size (bytes) = 134217728
Rebalancer Mode = No Rebalancing
Storage paths have been dropped = No
Minimum Recovery Time =
Number of quiescers = 0
Number of containers = 1
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/r0024235.html
