Example: Listing AI maintenance operations using the change history event monitor

You can use the change history event monitor to track all AI maintenance operations performed on a database. For the AI Query Optimizer, this includes capturing model discovery and training operations.

Scenario

In this example, the database administrator (DBA) wants to capture and list the history of all AI maintenance utility operations on a database. The following steps outline an example of how to capture AI maintenance utility events.

Create a change history event monitor that captures AI maintenance events.

CREATE EVENT MONITOR MON_AIMAINT
   FOR CHANGE HISTORY WHERE EVENT IN (AIMAINTENANCE)
   WRITE TO TABLE
      CHANGESUMMARY (TABLE UTIL_COMMON),
      UTILSTART (TABLE AIMAINT_START),
      UTILSTOP (TABLE AIMAINT_STOP);

Activate the event monitor.
```
SET EVENT MONITOR MON_AIMAINT STATE=1;
```

Query the logical data groups for information about AI maintenance operations executed on the database. For example, the following query lists the start and stop time of all executed AI maintenance utilities. The query shows the start and stop times on all database members. It ignores pause and resume records in order to show the full elapsed time of utility execution.

SELECT C.APPL_ID,
       A.MEMBER,
       A.EVENT_TIMESTAMP AS START_TIME,
       B.EVENT_TIMESTAMP AS STOP_TIME,
       VARCHAR(A.OBJECT_SCHEMA, 25) AS OBJECT_SCHEMA,
       VARCHAR(A.OBJECT_NAME, 25) AS OBJECT_NAME,
       A.OBJECT_TYPE,
       A.UTILITY_OPERATION_TYPE,
       B.SQLCODE,
       VARCHAR(A.UTILITY_DETAIL, 100) AS UTILITY_DETAIL
   FROM AIMAINT_START AS A,
        AIMAINT_STOP AS B,
        UTIL_COMMON AS C
   WHERE A.UTILITY_INVOCATION_ID = B.UTILITY_INVOCATION_ID AND
         A.UTILITY_INVOCATION_ID = C.UTILITY_INVOCATION_ID AND
         A.UTILITY_START_TYPE = 'START' AND
         B.UTILITY_STOP_TYPE = 'STOP' AND
         C.EVENT_TYPE = 'UTILSTART' AND
         A.MEMBER = B.MEMBER
   ORDER BY A.EVENT_TIMESTAMP ASC;

The result of the query shows that four AI maintenance utility operations were executed and provides their start and stop times, the target objects of the AI maintenance operations, and details about the operations that were executed.

APPL_ID                                                          MEMBER START_TIME                 STOP_TIME                  OBJECT_SCHEMA             OBJECT_NAME               OBJECT_TYPE      UTILITY_OPERATION_TYPE SQLCODE     UTILITY_DETAIL
---------------------------------------------------------------- ------ -------------------------- -------------------------- ------------------------- ------------------------- ---------------- ---------------------- ----------- ----------------------------------------------------------------------------------------------------
*N0.test.250226153504                                                 0 2025-02-26-10.58.25.314343 2025-02-26-10.58.25.315542 TEST                      T                         TABLE            D                                0 TABLE CARDINALITY MODEL ON "TEST"."T"
*N0.test.250226153504                                                 0 2025-02-26-10.58.25.316494 2025-02-26-10.58.38.331257 TEST                      T                         TABLE            T                                0 TABLE CARDINALITY MODEL ON "TEST"."T" ON COLUMNS ("C1", "C2", "C3")
*N2.test.250226153504                                                 2 2025-02-26-10.59.09.531814 2025-02-26-10.59.09.531872 TEST                      T2                        TABLE            D                                0 TABLE CARDINALITY MODEL ON "TEST"."T2"
*N2.test.250226153504                                                 2 2025-02-26-10.59.09.533360 2025-02-26-10.59.21.541849 TEST                      T2                        TABLE            T                                0 TABLE CARDINALITY MODEL ON "TEST"."T2" ON COLUMNS ("WORKDEPT", "DEPTNO", "EMPNO")

  4 record(s) selected.