onstat -t/-T Active TBLSpaces / All TBLSpaces

The onstat -t command displays TBLSpaces (system and user) that are currently being accessed by a userthread (i.e., ucnt > 0). The onstat -T command displays all TBLSpaces that have been opened.

Tblspaces 
 n address  flgs ucnt tblnum   physaddr npages nused  npdata nrows  nextns resident
 1 14a957c0 0    1    100001   10000e   350    350    0      0      3      0  
 4 14a95ac0 0    1    200001   200004   50     2      0      0      1      0 
 5 14a553f0 0    1    300001   300004   50     6      0      0      1      0 
10 14a55d30 0    1    400001   400004   450    442    0      0      9      0 
11 14ac3a70 0    2    400002   400005   24     21     10     156    3      0 
44 142cc840 0    1    400042   4002b5   24     18     17     17     3      0
....
 37 active, 164 total

Output Description

Heading Description Format See Also
n A sequential counter incremented for each table as it is opened. Dec onstat -g opn
address The in-memory address of the tblspace. Hex  
flgs Describes the status of the tblspace using the following hexadecimal values:
    0x00000000 No modifications
    0x00000001 Partition structure being initialized
    0x00000002 Partition has been modified
    0x00000004 Partition is being dropped
    0x00000008 Pseudo table partition
    0x00000010 Partition being altered (add/drop index)
    0x00000020 Partition being altered by ALTER TABLE
    0x00000040 Partition dropped with structs active
    0x00000080 Dropping this partition while dbspace is down
    0x00000100 Don't delete blobspace blobs when table dropped
           (used by alter fragment)
    0x00000200 Partition alter page count updated
    0x00000400 Pages altered to latest schema
    0x00000800 System temp table
    0x00001000 User temp table
    0x00002000 Resident partition
    0x00004000 Index operations deferred during recovery
    0x00008000 Partition is being truncated
    0x00010000 Partition is partially truncated
Hex  
ucnt The number of userthreads currently accessing the table. Dec  
tblnum The tblspace number in hexadecimal format. Hex TBLSpace number
physaddr The physical address of the tblspace on disk. Hex  
npages The total number of pages allocated to the tblspace. Dec  
nused The number of pages used of those allocated. Dec  
npdata The number of used pages that are data pages. Dec  
nrows The number of rows contained in the tblspace. Dec  
nextns The number of extents for this tblspace. Dec  
resident Indicates this tblspace is set for residency (0 or 0x2000). Hex onstat -P

Notes

    TBLSpace 0x100001 is the root DBSpace TBLSpace partition. The first TBLSpace in each DBSpace will be a TBLSpace partition.

    TBLSpace 0x100002 is the database TBLSpace.

    Upon startup, the Informix engine opens the TBLSpace partition in each DBSpace.

    The residency of a table must be re-established each time the Informix server is started.

Monitoring and Tuning

    If there appears to be a sudden performance hit on the system, the onstat -t command can be used to monitor what tables are currently being accessed. This information can be used in conjunction with information provided by onstat -g sql to identify a potential problem table. An analysis of the nextns (number of extensions) column might identify a table with a large number of extensions.

    One method for identifying tables with an excessive number of extents is to execute the following query against the sysmaster database:
    SELECT dbsname, tabname, nextns
    FROM systabnames t, sysptnhdr p
    WHERE t.partnum = p.partnum
    AND p.nextns > 10
    ORDER BY 3 DESC;
    37 active, 164 total