Tuesday, February 26, 2013

db2 list tablespaces


o check information about tablespaces in db2 , we can use following commands:

$ db2
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 SDK 8.2.9

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.


db2 =>
db2 =>
db2 => list tablespaces

           Tablespaces for Current Database

 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = System managed space
 Contents                             = Any data
 State                                = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = System managed space
 Contents                             = Any data
 State                                = 0x0000
   Detailed explanation:
     Normal


 Tablespace ID                        = 6
 Name                                 = SYSTOOLSPACE
 Type                                 = System managed space
 Contents                             = Any data
 State                                = 0x0000
   Detailed explanation:
     Normal

db2 =>

To get furthur information about location, and other statistics we can  furthur drill down using following commands(Partial output):

db2 get snapshot for tablespaces on dbname

             Tablespace Snapshot

First database connect timestamp   = 02/24/2013 11:49:31.630696
Last reset timestamp                      =
Snapshot timestamp                       = 02/26/2013 13:08:24.635298
Database name                              = XYZ
Database path                                = /xyz/NODE0000/SQL00001/
Input database alias                        = XYZ
Number of accessed tablespaces    = 7


Tablespace name                            = SYSCATSPACE
  Tablespace ID                              = 0
  Tablespace Type                          = System managed space
  Tablespace Content Type             = Any data
  Tablespace Page size (bytes)        = 4096
  Tablespace Extent size (pages)     = 32
  Automatic Prefetch size enabled   = Yes
  Buffer pool ID currently in use      = 1
  Buffer pool ID next startup           = 1
  Using automatic storage               = No
  File system caching                      = Yes
  Tablespace State                         = 0x'00000000'
   Detailed explanation:
     Normal
  Tablespace Prefetch size (pages)    = 32
  Total number of pages                    = 12304
  Number of usable pages                 = 12304
  Number of used pages                   = 12304
  Minimum Recovery Time                =
  Number of quiescers                      = 0
  Number of containers                     = 1

  Container Name                        = /xyz/NODE0000/SQL00001/SQLT0000.0
      Container ID                         = 0
      Container Type                     = Path
      Total Pages in Container        = 12304
      Usable Pages in Container     = 12304
      Stripe Set                              = 0
      Container is accessible          = Yes




Tablespace name                          = TEMPSPACE1
  Tablespace ID                            = 1
  Tablespace Type                        = System managed space
  Tablespace Content Type           = System Temporary data
  Tablespace Page size (bytes)       = 4096
  Tablespace Extent size (pages)    = 32
  Automatic Prefetch size enabled  = Yes
  Buffer pool ID currently in use    = 1
  Buffer pool ID next startup         = 1
  Using automatic storage              = No
  File system caching                      = Yes
  Tablespace State                         = 0x'00000000'
   Detailed explanation:
     Normal
  Tablespace Prefetch size (pages)    = 32
  Total number of pages                    = 1
  Number of usable pages                 = 1
  Number of used pages                   = 1
  Minimum Recovery Time               =
  Number of quiescers                      = 0
  Number of containers                     = 1

  Container Name                            = /xyz/SQL00001/SQLT0001.0
      Container ID                             = 0
      Container Type                         = Path
      Total Pages in Container            = 1
      Usable Pages in Container         = 1
      Stripe Set                                  = 0
      Container is accessible               = Yes




Tablespace name                            = USERSPACE1
  Tablespace ID                              = 2
  Tablespace Type                          = System managed space
  Tablespace Content Type             = Any data
  Tablespace Page size (bytes)        = 4096
  Tablespace Extent size (pages)     = 32
  Automatic Prefetch size enabled   = Yes
  Buffer pool ID currently in use      = 1
  Buffer pool ID next startup           = 1
  Using automatic storage               = No
  File system caching                      = Yes
  Tablespace State                         = 0x'00000000'
   Detailed explanation:
     Normal
  Tablespace Prefetch size (pages)    = 32
  Total number of pages                    = 256563803
  Number of usable pages                = 256563803
  Number of used pages                   = 256563803
  Minimum Recovery Time               =
  Number of quiescers                      = 0
  Number of containers                     = 1

  Container Name                        = /xyz/SQL00001/SQLT0002.0
      Container ID                         = 0
      Container Type                     = Path
      Total Pages in Container       = 256563803
      Usable Pages in Container    = 256563803
      Stripe Set                             = 0
      Container is accessible          = Yes

No comments:

Post a Comment