DB2 Version 9.7 for Linux, UNIX, and Windows

LIST TABLESPACES command

Lists table spaces and information about table spaces for the current database.

Important: This command or API has been deprecated and might be removed in a future release. You can use the MON_GET_TABLESPACE and the MON_GET_CONTAINER table functions instead which return more information. For more information, see LIST TABLESPACES and LIST TABLESPACE CONTAINERS commands have been deprecated.

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

One of the following:

SYSADM
SYSCTRL
SYSMAINT
SYSMON
DBADM
LOAD authority

Required connection

Database

Command syntax


>>-LIST TABLESPACES--+-------------+---------------------------><
                     '-SHOW DETAIL-'

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 is 0x000c. 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 prior to 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 to Load pending or Delete 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.