MON_GET_AUTO_MAINT_QUEUE table function - Get information about the automatic maintenance jobs

Syntax

The schema is SYSPROC.

Authorization

Default PUBLIC privilege

None

Examples

Display the current jobs on the automatic maintenance queue on each partition.

SELECT MEMBER
       QUEUE_POSITION,
       JOB_STATUS,
       JOB_TYPE,
       VARCHAR(DB_NAME, 10) AS DB_NAME,
       OBJECT_TYPE,
       VARCHAR(OBJECT_SCHEMA, 10) AS OBJECT_SCHEMA,
       VARCHAR(OBJECT_NAME, 10) AS OBJECT_NAME
FROM TABLE(MON_GET_AUTO_MAINT_QUEUE()) AS T
ORDER BY MEMBER, QUEUE_POSITION ASC;

The following is an example of output from this query.

MEMBER QUEUE_POSITION JOB_STATUS JOB_TYPE DB_NAME OBJECT_TYPE OBJECT_SCHEMA
------ -------------- ---------- -------- ------- ----------- -------------
     0              1 EXECUTING  RUNSTATS SAMPLE  TABLE       TEST         
     0              2 QUEUED     REORG    SAMPLE  TABLE       TEST         
     0              3 QUEUED     REORG    SAMPLE  TABLE       TEST        
     
 OBJECT_NAME
 -----------
 EMPLOYEE   
 T1         
 BLAH       
 

3 record(s) selected.

Display the current jobs on the automatic maintenance queue.

SELECT JOB_STATUS,
       JOB_TYPE,
       OBJECT_TYPE, 
       VARCHAR(OBJECT_NAME, 10) AS OBJECT_NAME, 
       VARCHAR(JOB_DETAILS,60) AS JOB_DETAILS
FROM TABLE(MON_GET_AUTO_MAINT_QUEUE()) AS T 
ORDER BY MEMBER, QUEUE_POSITION ASC;

The following is an example of output from this query.

JOB_STATUS JOB_TYPE     OBJECT_TYPE OBJECT_NAME
---------- ------------ ----------- -----------
EXECUTING  REORG        TABLE       TP3        

JOB_DETAILS                                                 
------------------------------------------------------------
REORG INDEXES ALLOW WRITE CLEANUP ALL; RECLAIM EXTENTS      


1 record(s) selected.

Usage notes

The information returned from MON_GET_AUTO_MAINT_QUEUE supplements the information from the HEALTH_DB_HIC interface. HEALTH_DB_HIC shows the automatic maintenance status for each table to which automatic maintenance is applied, as well as the last time the status was updated (such as last time table was checked to see if maintenance is needed). The MON_GET_AUTO_MAINT_QUEUE interface provides a drill down for when the state is AUTOMATED, providing details about where the maintenance job is in the auto maintenance queue, and what other jobs are ahead of the job in the queue.

The MON_GET_AUTO_MAINT_QUEUE table function does not report any automatic maintenance jobs if automatic maintenance is not enabled.

Information returned