LIST TABLESPACES command
The LIST TABLESPACES command lists table spaces and information about table spaces for the current database.
Information displayed by this command is also available in the table space snapshot.
Scope
This command returns information only for the database partition on which it is executed.
Authorization
- SYSADM
- SYSCTRL
- SYSMAINT
- SYSMON
- DBADM
- LOAD authority
Required connection
Database
Command syntax
Command parameters
- SHOW DETAIL
- If this option is not specified, only the following basic information
about each table space is provided:
- Table space ID
- Name
- Type (system managed space or database managed space)
- Contents (any data, long or index data, or temporary data)
- State, a hexadecimal value indicating the current table space state. The externally visible
state of a table space is composed of the hexadecimal sum of certain state values. For example, if
the state is "quiesced: EXCLUSIVE" and "Load pending", the value is
0x0004
+0x0008
, which is0x000c
. The db2tbst (Get Tablespace State) command can be used to obtain the table space state associated with a given hexadecimal value. Following are the bit definitions listed in sqlutil.h: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 0x4000 Offline and not accessible 0x8000 Drop pending 0x10000 Suspend Write 0x20000 Load in progress 0x2000000 Storage may be defined 0x4000000 StorDef is in 'final' state 0x8000000 StorDef was change before roll forward 0x10000000 DMS rebalance in progress 0x20000000 Table space deletion in progress 0x40000000 Table space creation in progress
Note: Db2® LOAD does not set the table space state toLoad pending
orDelete pending
.
If this option is specified, the following additional information about each table space is provided:- Total number of pages
- Number of usable pages
- Number of used pages
- Number of free pages
- High water mark (in pages)
- Page size (in bytes)
- Extent size (in pages)
- Prefetch size (in pages)
- Number of containers
- Minimum recovery time (earliest point in time to which a table space can be rolled forward; timestamp expressed in UTC time, displayed only if not zero)
- State change table space ID (displayed only if the table space state is "load pending" or "delete pending")
- State change object ID (displayed only if the table space state is "load pending" or "delete pending")
- Number of quiescers (displayed only if the table space state is "quiesced: SHARE", "quiesced: UPDATE", or "quiesced: EXCLUSIVE")
- Table space ID and object ID for each quiescer (displayed only if the number of quiescers is greater than zero).
Examples
The following are two sample outputs from LIST TABLESPACES SHOW DETAIL.
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database 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 = Database 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 partitioned database environment,
this command does not return all the table spaces in the database.
To obtain a list of all the table spaces, query SYSCAT.TABLESPACES
.
When the LIST TABLESPACES SHOW DETAIL command is issued, it will attempt to free all pending free extents in the table space. If the pending free extents are freed successfully, a record will be logged.
During a table space rebalance, the number of usable pages includes pages for the newly added container, but these new pages are not reflected in the number of free pages until the rebalance is complete. When a table space rebalance is not in progress, the number of used pages plus the number of free pages equals the number of usable pages.
For additional information about table space states, see the Related links section.