Monitoring fragmentation with the onstat -g ppf command

With the onstat -g ppf command, you can view partition information and monitor the I/O activity to verify your strategy and determine whether the I/O is balanced across fragments.

About this task

The onstat -g ppf output includes the number of read-and-write requests sent to each fragment that is currently open. Because a request can trigger multiple I/O operations, these requests do not indicate how many individual disk I/O operations occur, but you can get a good idea of the I/O activity from the displayed columns.

The brfd column in the output displays the number of buffer reads in pages. (Each buffer can contain one page.) This information is useful if you need to monitor the time a query takes to execute. Typically query execution time has a strong dependency on the number of required buffer reads. If the size of client-server buffering is small and your database contains TEXT data, query execution time can involve significantly more buffer reads, because the server reads the prior TEXT data.

The onstat -g ppf output by itself does not identify the table in which a fragment is located. To determine the table for the fragment, join the partnum column in the output to the partnum column in the sysfragments system catalog table. The sysfragments table displays the associated table id. You can also find the table name for the fragment by joining the table id column in sysfragments to the table id column in systables.

Procedure

To determine the table name in onstat -g ppf output:

  1. Obtain the value in the partnum field of the onstat -g ppf output.
  2. Join the tabid column in the sysfragments system catalog table with the tabid column in the systables system catalog table to obtain the table name from systables.

    Use the partnum field value that you obtain in step 1 in the SELECT statement.

    SELECT a.tabname FROM systables a, sysfragments b
   WHERE a.tabid = b.tabid
      AND partn = partnum_value;