dbtulsts

This is a tool that lists information about the tablespaces and tablespace containers in a common list.

>>--- dbtulsts --- ALIAS ---+---------------+---><
                            |               |
                            +--- TSTOKEN ---+

Syntax diagram

There are several reasons to list the tablespaces information for a given database. If we want to have information about the tablespace containers, the following steps are needed:

• LIST TABLESPACES SHOW DETAIL
• <lookup the tablespace ID we are interested in>
• LIST TABLESPACE CONTAINERS FOR <ID> SHOW DETAIL

This tool does all the listed steps at once. The alias of the database (ALIAS) for which the information should be listed is a must. Optionally it is possible to specify a tablespace name (or part of it) filtering the output to the relevant tablespaces.

[xca1019][hal][...te/DbtuTools/v00r010][0] > dbtulsts sample 036
    
Tablespace Name (ID)        = TEST036D (3)
Type                        = Database Managed
Content                     = Regular Data
Page size                   = 4096 Bytes
Extent size                 = 32 Pages (=128 KBytes)
Prefetch size               = 64 Pages (=256 KBytes)(=2.000 * Extent size)
Number of containers        = 2
 
State (hex)                 = Normal (0x0000)
Earliest TS rollforward PIT = 2002-07-25-20.05.39.000000
 
ID   Acc Usable   Taken    Type Name
---- --- -------- -------- ---- ---------------------------------------------->
   0 yes    24992    25000 File /tmp/TEST036D.001
   1 yes    24992    25000 File /tmp/TEST036D.002
         -------- --------
  Total:    49984    50000
 - Used:     6656    12512 (now / high water mark)
 = Free:    43328    37472 (now / high water mark)


Tablespace Name (ID)        = TEST036S (4)
Type                        = System Managed
Content                     = Regular Data
Page size                   = 4096 Bytes
Extent size                 = 32 Pages (=128 KBytes)
Prefetch size               = 64 Pages (=256 KBytes)(=2.000 * Extent size)
Number of containers        = 2
 
State (hex)                 = Normal (0x0000)
Earliest TS rollforward PIT = 2002-07-25-20.05.42.000000
 
ID   Acc Usable   Taken    Type Name
---- --- -------- -------- ---- ---------------------------------------------->
   0 yes      539      539 Path /tmp/TEST036S.001
   1 yes      513      513 Path /tmp/TEST036S.002
         -------- --------
  Total:     1052     1052
 - Used:     1052
 = Free:        0

 
[xca1019][hal][...te/DbtuTools/v00r010][0] > _

Sample output

The information for each tablespace is grouped into 3 blocks.

The first block contains information about the attributes and not so flexible settings of the tablespace. The Extent size is listed in number of pages and bytes as well. The Prefetch size is handled the same way and enriched by the factor you get from "Prefetch size / Extent size".

The second block shows the actual status of the tablespace and the earliest point in time that may be specified by a point in time tablespace rollforward.

The third block contains a list of all tablespace containers for the current tablespace. Every line represents one container and lists its:

• ID (container ID)
• Access status ("yes" or "no" for accessible or not accessible)

• Number of usable pages (taken pages from OS minus pages used for metadata)

• Number of taken pages (total number of pages taken from the OS)

• Container type ("Path" = directory, "Disk" = raw device, "File" = file)

• Container name

After all the values are totaled, the last two lines show the total number of used pages (current value) and the total number of free pages (current value). For database-managed tablespaces, additional values are displayed:

• The total number of used pages (high water mark).
• The total number of usable pages (current value) minus the total number of used pages (high water mark).