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 1 shows a table called account that has partnum 0.

Figure 1. Output from systables Query with partnum Values

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 2 shows that the account table from Figure 1 has three table fragments and three index fragments.

Figure 2. Output from sysfragments Table with partn Values

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.