Command Reference

LIST TABLESPACES

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:

sysadm

sysctrl

sysmaint

dbadm

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 data only, 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. db2tbst - Get Tablespace State 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
     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:

• Total number of pages
• Number of useable 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 (displayed only if not zero)
• State change tablespace 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")
• Tablespace ID and Object ID for each quiescer (displayed only if the number of quiescers is greater than zero).

Example

The following is sample output 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.

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.

See Also

db2tbst - Get Tablespace State

[ DB2 List of Books | Search the DB2 Books ]