Lists table spaces for the current database.
Scope
This command returns information only for the node on which it is executed.
Authorization
One of the following:
Required Connection
Database
Command Syntax
>>-LIST TABLESPACES--+-------------+---------------------------><
'-SHOW DETAIL-'
|
Command Parameters
0x0 Normal 0x1 Quiesced: SHARE 0x2 Quiesced: UPDATE 0x4 Quiesced: EXCLUSIVE 0x8 Load pending 0x10 Delete pending 0x20 Backup pending 0x40 Roll forward in progress 0x80 Roll forward pending 0x100 Restore pending 0x100 Recovery pending (not used) 0x200 Disable pending 0x400 Reorg in progress 0x800 Backup in progress 0x1000 storage must be defined 0x2000 Restore in progress 0x2000000 storage may be defined 0x4000000 storDef is in 'final' state 0x8000000 storDef was changed prior to rollforward 0x10000000 dms rebalancer is active 0x20000000 TBS deletion in progress 0x40000000 TBS creation in progress 0x8 For service use only
If this option is specified, the following additional information about each table space is provided:
Examples
The following are two sample outputs from LIST TABLESPACES SHOW DETAIL.
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 895
Useable pages = 895
Used pages = 895
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 = 1
Name = TEMPSPACE1
Type = System managed space
Contents = 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 = System managed space
Contents = Any data
State = 0x000c
Detailed explanation:
Quiesced: EXCLUSIVE
Load pending
Total pages = 337
Useable pages = 337
Used pages = 337
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
State change tablespace ID = 2
State change object ID = 3
Number of quiescers = 1
Quiescer 1:
Tablespace ID = 2
Object ID = 3
DB21011I In a partitioned database server environment, only the table spaces
on the current node are listed.
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1200
Useable pages = 1200
Used pages = 1200
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 = 1
Name = TEMPSPACE1
Type = System managed space
Contents = 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 = System managed space
Contents = Any 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 = 3
Name = DMS8K
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 2000
Useable pages = 1952
Used pages = 96
Free pages = 1856
High water mark (pages) = 96
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 2
Tablespace ID = 4
Name = TEMP8K
Type = System managed space
Contents = 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) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
DB21011I In a partitioned database server environment, only the table spaces
on the current node are listed.
Usage Notes
In a multi-node environment, this command does not return all the table spaces in the database. To obtain a list of all the table spaces, query SYSCAT.SYSTABLESPACES.
During a table space rebalance, the number of useable pages will include pages for the newly added container, but these new pages will not be reflected in the number of free pages until the rebalance is complete. When a table space rebalance is not taking place, the number of used pages plus the number of free pages will equal the number of useable pages.
See Also
db2tbst - Get Tablespace State