Structure of the Tblspace Tblspace

Each dbspace contains a tblspace called the tblspace tblspace that describes all tblspaces in the dbspace. When the database server creates a tblspace, it places an entry in the tblspace tblspace that describes the characteristics of the newly created tblspace. You cannot drop or move a chunk containing a tblspace tblspace.

A dbspace can have a maximum number of 2**20 tblspaces.

The default size of the first and next extents depends on whether the dbspace is the root dbspace or not, as shown in the following table.

You can specify a non-default size for the first and next extents for a tblspace tblspace in the following ways:

• For the root dbspace, set the TBLTBLFIRST and TBLTBLNEXT configuration parameters.
• For non-root dbspaces, use the onspaces utility -ef and -en options when you create a dbspace.

Tblspace Tblspace Entries

To display information on the tblspace, use the oncheck -pt command. For more information, see Display Tblspaces for a Table or Fragment with -pt and -pT.

Tblspace Numbers

Each tblspace that is described in the tblspace tblspace receives a tblspace number. This tblspace number is the same value that is stored as the partnum field in the systables system catalog table and as the partn field in the sysfragments system catalog table.

The following SQL query retrieves the partnum for every table in the database (these can be located in several different dbspaces) and displays it with the table name and the hexadecimal representation of partnum:

SELECT tabname, tabid, partnum, HEX(partnum) hex_tblspace_name FROM systables

If the output includes a row with a table name but a partnum of 0, this table consists of two or more table fragments, each located in its own tblspace. For example, Figure 6 shows a table called account that has partnum 0.

tabname        tabid   partnum          hex_tblspace_name

sysfragments    25     1048611          0x00100023
branch         100     1048612          0x00100024
teller         101     1048613          0x00100025
account        102           0          0x00000000
history        103     1048615          0x00100027
results        104     1048616          0x00100028

To obtain the actual tblspace numbers for the fragments that make up the table, you must query the sysfragments table for the same database. Figure 7 shows that the account table from Figure 6 has three table fragments and three index fragments.

tabid  fragtype     partn          hex_tblspace_name

  102  T            1048614        0x00100026
  102  T            2097154        0x00200002
  102  T            3145730        0x00300002
  102  I            1048617        0x00100029
  102  I            2097155        0x00200003
  102  I            3145731        0x00300003

Tblspace Number Elements

The first page in a tblspace is logical page 0. (Physical page numbers refer to the address of the page in the chunk.) The root space tblspace tblspace is always contained in the first dbspace and on logical page 1 within the tblspace tblspace. (The bitmap page is page 0.)

Tblspace Tblspace Size

These tblspace tblspace pages are allocated as an extent when the dbspace is initialized. If the database server attempts to create a table, but the tblspace tblspace is full, the database server allocates a next extent to the tblspace.

When a table is removed from the dbspace, its corresponding entry in the tblspace tblspace is deleted.

Tblspace Tblspace Bitmap Page

The first page of the tblspace tblspace, like the first page of any initial extent, is a bitmap that describes the page fullness of the following pages. Each page that follows has an entry on the bitmap page. If needed, additional bitmap pages are located throughout the contiguous space allocated for the tblspace, arranged so that each bitmap describes only the pages that follow it, until the next bitmap or the end of the dbspace. Bitmap pages fall at distinct intervals within tblspaces pages. Each bitmap page describes a fixed number of pages that follow it.