Monitoring Tblspaces and Extents

Monitor tblspaces and extents to determine disk usage by database, table, or table frag ment. Monitoring disk usage by table is particularly important when you are using table fragmentation, and you want to ensure that table data and table index data are distributed appropriately over the fragments.

Using Command-Line Utilities

You can use the following command-line utilities to monitor tblspaces and extents.

You can include a database-name or table-name parameter with the command. The command displays the following information:

Figure 71 shows sample output. The table in the example is fragmented over multiple dbspaces. Because each fragment of a fragmented table resides in a separate tblspace, the onutil CHECK TABLE INFO option always displays separate information for each fragment. The number of pages of table data in each fragment is displayed.

Figure 71. onutil CHECK TABLE INFO Output

TBLspace Report for tpc:informix.account

                  Table fragment in DBspace rootdbs

    Physical Address               100033
    Creation date                  03/31/99 13:25:21
    TBLspace Flags                 2          Row Locking
    Maximum row size               100
    Number of special columns      0
    Number of keys                 0
    Number of extents              2
    Current serial value           1
    First extent size              50
    Next extent size               25
    Number of pages allocated      2375
    Number of pages used           2370
    Number of data pages           2369
    Number of rows                 45001
   Partition partnum              2097154
    Partition lockid               2097154

    Extents
         Logical Page  Physical Page        Size
                    0         100ad5          50
                   50         100b2f        2325

                  Table fragment in DBspace dbspace2

    Physical Address               200005
    Creation date                  03/31/99 13:25:21
    TBLspace Flags                 2          Row Locking
    Maximum row size               100
    Number of special columns      0
    Number of keys                 0
    Number of extents              1
    Current serial value           1
    First extent size              50
    Next extent size               25
    Number of pages allocated      550
    Number of pages used           528
    Number of data pages           527
    Number of rows                 10000
    Partition partnum              3145730
    Partition lockid               2097154

    Extents
         Logical Page  Physical Page        Size
                    0         200035         550
...

onutil CHECK TABLE ALLOCATION INFO

The onutil CHECK TABLE ALLOCATION INFO returns all of the information from the onutil CHECK TABLE INFO option as well as additional information.

Figure 72 shows sample output. Each tblspace in the database or table that you supply is listed.

Figure 72. Additional Information shown by onutil CHECK TABLE ALLOCATION INFO

TBLSpace Usage Report for tpc:chrisw.account

    Type                  Pages      Empty  Semi-Full       Full  Very-Full
    ---------------- ---------- ---------- ---------- ---------- ----------
    Free                     20
    Bit-Map                   1
    Index                   471
    Data (Home)            3158
                     ----------
    Total Pages            3650

    Unused Space Summary

        Unused data slots                                 2
        Unused bytes per data page                       44
        Total unused bytes in data pages             138952

Index Usage Report for index iaccount on tpc:chrisw.account

                    Average    Average
    Level    Total No. Keys Free Bytes
    ----- -------- -------- ----------
        1        1        4       1973
        2        4      116        506
        3      466      128        217
    ----- -------- -------- ----------
    Total      471      128        223

Using SMI Tables

Query the systabnames table to obtain information about each tblspace. The systabnames table has columns that indicate the corresponding table, database, and table owner for each tblspace.

Query the sysextents table to obtain information about each extent. The sysextents table has columns that indicate the database and the table that the extent belongs to, as well as the physical address and size of the extent.

Using System Catalog Tables

Query the sysfragments table to obtain information about all tblspaces that hold a fragment. This table has a row for each tblspace that holds a table fragment or an index fragment. The sysfragments table includes the following columns.

Not all columns of sysfragments are documented in the preceding list. For a complete listing of columns, see the IBM Informix: Guide to SQL Reference.