DB2 10.5 for Linux, UNIX, and Windows

MON_GET_AUTO_MAINT_QUEUE table function - Get information about the automatic maintenance jobs

The MON_GET_AUTO_MAINT_QUEUE table function returns information about all automatic maintenance jobs (with the exception of real-time statistics which does not submit jobs on the automatic maintenance queue) that are currently queued for execution by the autonomic computing daemon (db2acd).

Syntax


>>-MON_GET_AUTO_MAINT_QUEUE--()--------------------------------><

The schema is SYSPROC.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

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 MON_GET_AUTO_MAINT_QUEUE table function returns information about all queued and executing automatic maintenance jobs on all members. Note that there is a separate automatic maintenance queue per member.

Note: Jobs in the automatic maintenance queue are ordered first by earliest start time (such as start of next maintenance window where job may run), priority (for entries with same earliest start time) and queue entry time (for entries with the same earliest start time and priority).

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

Table 1. Information returned for MON_GET_AUTO_MAINT_QUEUE
Column Name	Data Type	Description
DB_NAME	VARCHAR(128)	db_name - Database name monitor element
MEMBER	SMALLINT	member - Database member monitor element
OBJECT_TYPE	VARCHAR(8)	Type of object. One of: DATABASE NICKNAME TABLE VIEW
OBJECT_SCHEMA	VARCHAR(128)	object_schema - Object schema monitor element
OBJECT_NAME	VARCHAR(128)	object_name - Object name monitor element
JOB_TYPE	VARCHAR(12)	Type of automatic maintenance job. One of: RUNSTATS REORG BACKUP
JOB_DETAILS	VARCHAR(256)	Details about the maintenance job if type is RUNSTATS or REORG. For RUNSTATS, indicates if the runstats job is doing a full runstats or just sampling. For REORG, lists the keywords that will be applied to modify the behavior of the REORG utility (for example, INDEXES, CLEANUP, and so on). For an index REORG, if the keywords indicate both CLEANUP and RECLAIM EXTENTS separated by a semicolon, index reorg cleanup is done followed by the evaluation of and potential run of index reclaim extents (reclaim extents is done if the evaluation of reclaimable space compared against the reclaimExtentsSizeForIndexObjects setting indicates reclaim is necessary).
JOB_STATUS	VARCHAR(10)	Current status of the job. One of: QUEUED EXECUTING
JOB_PRIORITY	INTEGER	Numeric priority of job in the queue. Priority is only important for jobs with the same value for EARLIEST_START_TIME.
MAINT_WINDOW_TYPE	VARCHAR(8)	Indicates which type of maintenance window will be used for the job. One of: ONLINE OFFLINE
QUEUE_POSITION	INTEGER	Indicates the position of the job in the automatic maintenance queue.
QUEUE_ENTRY_TIME	TIMESTAMP	Time that the job was added to the automatic maintenance queue.
EXECUTION_START_TIME	TIMESTAMP	Time that job started execution, if status is EXECUTING. NULL otherwise.
EARLIEST_START_TIME	TIMESTAMP	Start time of next maintenance window where job is eligible to run.