What is the HMON_ATM_INFO table in DB2 LUW ?

Technote (FAQ)


Question

What is the HMON_ATM_INFO table ?

Cause

Description of the SYSTOOLS.HMON_ATM_INFO table, how to create and drop it.

Answer

- Automatic reorganization shares a control table with automatics statistics collection - SYSTOOLS.HMON_ATM_INFO.

This table is used to track information including:
    - Which tables should be considered for auto-reorg
    - Which tables should be considered for auto-runstats
    - When was the last time auto-reorg looked at a table
    - What is the current state of the table (eg. normal, need reorg, etc)

- This table is created the first time auto-reorg or auto-runstats is executed. It is created in the SYSTOOLSPACE user data tablespace which is also created the first time auto-reorg or auto-runstats is used.

- It can also be created by invoking the SYSINSTALLOBJECTS stored procedure as follows:
    db2 "CALL SYSPROC.SYSINSTALLOBJECTS('DB2AC','C','','')"

- If you get SQL0204N error when invoking the stored procedure to create the table, the SYSTOOLSPACE tablespace may need to be created manually.
    db2 "CALL SYSPROC.SYSINSTALLOBJECTS('DB2AC','C','','')"
    SQL0204N  "SYSTOOLSPACE" is an undefined name.  SQLSTATE=42704

- The SYSTOOLSPACE can be created manually for automatic storage databases by running
    db2 " CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP
             MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4"

- Similarly, it can be dropped by invoking the procedure as follows:
    db2 "CALL SYSPROC.SYSINSTALLOBJECTS('DB2AC','D','','')"

- There is one row in the table for each table in the database. The information in HMON_ATM_INFO is refreshed on each evaluation of the auto-runstats or auto-reorg health indicators.

    db2 describe table SYSTOOLS.HMON_ATM_INFO

                                    Data type                     Column
    Column name                     schema    Data type name      Length     Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    SCHEMA                          SYSIBM    VARCHAR                    128     0 No
    NAME                            SYSIBM    VARCHAR                    128     0 No
    CREATE_TIME                     SYSIBM    TIMESTAMP                   10     6 No
    STATS_FLAG                      SYSIBM    CHARACTER                    1     0 No
    STATS_LOCK                      SYSIBM    CHARACTER                    1     0 No
    STATS_OPTS                      SYSIBM    INTEGER                      4     0 No
    LAST_WAIT                       SYSIBM    INTEGER                      4     0 No
    TO_WAIT                         SYSIBM    INTEGER                      4     0 No
    SIGNAL                          SYSIBM    CHARACTER                    1     0 N
    LEO_TAB                         SYSIBM    CHARACTER                    1     0 N
    EXEC_COUNT                      SYSIBM    BIGINT                       8     0 N
    STATS_STATE                     SYSIBM    SMALLINT                     2     0 N
    STATS_NOTIFY                    SYSIBM    CHARACTER                    1     0 N
    STATS_DETAIL                    SYSIBM    VARCHAR                    200     0 Y
    STATS_HISTORY                   SYSIBM    VARCHAR                    200     0 Y
    STATS_TIME                      SYSIBM    TIMESTAMP                   10     6 Y
    REORG_FLAG                      SYSIBM    CHARACTER                    1     0 N
    REORG_LOCK                      SYSIBM    CHARACTER                    1     0 N
    REORG_STATE                     SYSIBM    SMALLINT                     2     0 N
    REORG_TIME                      SYSIBM    TIMESTAMP                   10     6 Y
    REORG_NOTIFY                    SYSIBM    CHARACTER                    1     0 N
    REORG_DETAIL                    SYSIBM    VARCHAR                    200     0 Y
    REORG_HISTORY                   SYSIBM    VARCHAR                    200     0 Y
    REORG_AVG_RUNTIME               SYSIBM    INTEGER                      4     0 Y
    REORG_INDEX_SCHEMA              SYSIBM    VARCHAR                    128     0 Y
    REORG_INDEX_NAME                SYSIBM    VARCHAR                    128     0 Y
    REORG_INDEX_ONLINE              SYSIBM    CHARACTER                    1     0 Y

Related information

SYSTOOLSPACE and SYSTOOLSTMPSPACE tablespaces

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows
DBA/System Administration - Create/Drop

Software version:

9.5, 9.7, 9.8, 10.1

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1627952

Modified date:

2013-07-18

Translate my page

Machine Translation

Content navigation