What is TBLspace TBLspace?

Question

This document explains what a tblspace-tblspace (also referred as tablespace-tablespace and partition-partition) is and how it relates to other tables in an instance.

Answer

What is TBLspace TBLspace?

You are running IBM® Informix® Dynamic Server (IDS) database server and create a regular Dbspace. This Dbspace contains an internal table called TBLspace TBLspace. You cannot access the table using SQL commands.

What it does?

The TBLspace TBLspace table tracks all the partitions (tables or indexes) that you create in a Dbspace, including itself. You can track every table, detached index, or table fragment in the TBLspace TBLspace table through a partition page. The first partition page contains information about the TBLspace TBLspace table itself and the following partition pages track other tables. The partition page contains important information about a table and you can see part the data using theoncheck -pt command (oncheck  -pt database:table):

oncheck   -pt database : table 


database
name of the database 

table
name of the table 

When you create a TBLspace TBLspace table, it has an initial extent of 50 pages on version 7.x and 250 pages on 9.x. In addition, when the first extent gets full, a new extent is allocated in one of the chunks of the Dbspace. This may cause a problem because if you create a new extent of the TBLspace TBLspace table on a chunk that needs to be dropped the server will report an error saying that the chunk is not empty. This will happen even if there is no user data in it. 
Even though the TBLspace TBLspace is not accessible through SQL you can still see the extents that are allocated in the chunks of the Dbspace using theoncheck -pe command. 

Example: 

The following is a partial output of an  oncheck -pe run on an IDS 7.x system. It is the first extent (50 pages) of the TBLspace TBLspace in chunk 2:

DBspace Usage Report: dbs1        Ownner: informix  Created: 10/11/2003 

Chunk: 2 /informix/dbs1_chunk1 Size Used  Free 
                                5000   53  4947 

Disk usage for Chunk 2      Start  Length 

------------------------------------------- 
 OTHER RESERVED Pages         0      2 
 CHUNK FREE LIST PAGE         2      1 
  TBLSPACE TBLSPACE             3      50 
 FREE                        53     4947 

Chunk: 3 /informix/dbs1_chunk2 Size Used Free 
                                5000   3  4997 
Disk usage for Chunk 3        Start   Length 
-------------------------------------------- 
   OTHER RESERVED Pages         0      2 
   CHUNK FREE LIST PAGE         2      1 
   FREE