How AUS works

The Auto Update Statistics (AUS) maintenance system uses a combination of Scheduler sensors, tasks, thresholds, and tables to evaluate and update statistics.

The Scheduler tasks, sensors, thresholds, and tables reside in the sysadmin database. By default, only user informix is granted access to the sysadmin database.

The following sequence of events describes how statistics are automatically updated:

  1. The mon_table_profile sensor of the Scheduler runs every day to read data from the systables table in the sysmaster database. The sensor updates the mon_table_profile table in the sysadmin database with information about how much each table has changed.
  2. The Auto Update Statistics Evaluation task gathers information every day from the mon_table_profile table and the systable, sysdistrib, syscolumns, and sysindices tables in the sysmaster database.
  3. The Auto Update Statistics Evaluation task determines which tables need updates based on the expiration policies.
  4. The Auto Update Statistics Evaluation task generates UPDATE STATISTICS statements and inserts them into the aus_command table in the sysadmin database.
  5. The Auto Update Statistics Refresh task runs the UPDATE STATISTICS statements from the aus_command table on Saturday and Sunday mornings between 1:00 AM and 5:00 AM and inserts the results back into the aus_command table. Any UPDATE STATISTICS statements that did not complete before 5:00 AM remain in the aus_command table.

The following table describes the tasks, sensors, thresholds, tables, and views in the sysadmin database that comprise the AUS maintenance system.

Table 1. AUS components
Component Type Description
mon_table_profile sensor Compiles table profile information, including the total number of updates, inserts, and deletes that occurred on each table.

Defined in the ph_task table.
mon_table_profile table Stores table profile information gathered by its sensor. Many other Scheduler tasks use information from this table.
Auto Update Statistics Evaluation task Identifies tables with stale statistics, based on expiration policies, and generates UPDATE STATISTICS statements for those tables.

Defined in the ph_task table.
aus_command table Stores a list of prioritized UPDATE STATISTICS statements that are scheduled to be run, and the results of those statements after they are run.

The aus_cmd_state column indicates the status of each UPDATE STATISTICS statement:

  • P = Pending
  • I = In progress
  • E = Error
  • C = Complete without errors

If the command status is E, the associated SQL error code is listed in the aus_cmd_err_sql column and the associated ISAM error code is listed in the aus_cmd_err_isam column.

The aus_cmd_runtime shows the time that is elapsed for the update statistics command to complete. The aus_cmd_time shows the start time for the update statistics command.
Auto Update Statistics Refresh task Runs the prepared UPDATE STATISTICS statements on Saturdays and Sundays between 1:00 AM and 5:00 AM.

Defined in the ph_task table.
expiration policies thresholds Define the criteria for when to update statistics.

Defined in the ph_threshold table.
aus_cmd_comp view Shows information from the aus_command table about UPDATE STATISTICS statements that were run successfully.
aus_cmd_list view Shows information from the aus_command table about UPDATE STATISTICS statements that are scheduled to be run.
For information about other features of the Scheduler, see its description in the HCL OneDB™ Administrator's Guide. For information about the sysadmin database, see the HCL OneDB Administrator's Reference.