observe live statement statistics with new onstat -g spf command

Answer

If you want to observe what a statement is currently doing a new "onstat -g spf" command is introduced in version 12.10.xC4W1 of Informix. It requires you to have statement tracing active (it may be suspended though) to enable the collection of the relevant internal figures.

You can use "onstat -g spf" to monitor the whole instance activity or you can focus on a single session by using "onstat -g spf session_id".

The output will be similar to the RSAM statistic part of a "select * from syssdblock" run against the sysmaster database which you can also use as alternative to this onstat command. A sample output from an "onstat -g spf 36" could look like

IBM Informix Dynamic Server Version 12.10.FC4W1 -- On-Line -- Up 00:09:37 -- 2507408 Kbytes

Statement profiles
sid   sdb      tottm  execs runtm  pdq scans sorts bfrd   pgrd   bfwrt  pgwrt  lkrqs  lkwts
36    4906d028 0.00   0     0.00   0   0     0     11     1      0      0      50     0  

The (short) explanation of the fields can be looked up in comments of the CREATE TABLE statement for the syssdblock table (please refer to $INFORMIXDIR/etc/sysmaster.sql).

This table syssdblock does exist in earlier versions as well. So if your current version does not support the "onstat -g spf" command you may still be able to use an SQL query of this table to achieve this type of information.

Please note that the "onstat -g spf" output as well as the query of syssdblock is showing the current status of a running statement. It does not provide accumulation of this information over time.