Using System Catalog and System-Monitoring Interface Tables

Query the sysfragments and systables system catalog tables to get information about all tblspaces that hold a fragment and the table to which each fragment belongs.

After you obtain the names of tables that have fragments stored in specific chunks, keep the information for later reference. If you alter tables, add tables, or otherwise change the physical layout of the database, retrieve the table information again.

Examining Information in the sysfragments System Catalog Table

The sysfragments system catalog table stores information about fragmented tables. It has a row for each tablespace that holds a table fragment or an index fragment. The sysfragments system catalog table includes the following columns that are useful for monitoring the performance of fragmented tables.

Column Name

Description

tabid

Table identifier

indexname

Index identifier

partn

Partition number (tablespace ID)

strategy

Distribution scheme (round-robin, expression based, range based, system derived)

dbspace

Dbspace name for fragment

npused

Number of data pages or leaf pages

nrows

Number of rows recorded by the most recent UPDATE STATISTICS operation

fragtype

Fragment type: I for index, B for TEXT or BYTE data, T for table data

exprtext

Names of the columns that are hashed

If the fragment is created by a hybrid scheme, the hash columns appear first, followed by the fragmentation expression for the dbslice or list of dbspaces

hybdpos

Relative position of the fragment in the set of fragments created by a hybrid fragmentation scheme

For a complete list of sysfragments columns, see the IBM Informix: Guide to SQL Reference.

Examining Information in the sysptprof System-Monitoring Interface Table

While a table is open, you can query the sysptprof System-Monitoring Interface (SMI) table for current activity information about a tablespace. When the last user who has a table open closes it, all profile statistics are dropped.

You can get the following information directly from sysptprof:

• The database name
• The table name
• The partition (tablespace) number
• Lock information
• Read and write information, both page and buffer
• Sequential scan information.

Queries against sysptprof can provide useful information about how table fragments are used during queries.