Example: Listing changes performed by the STMM using the change history event monitor

You can use the change history event monitor to list changes performed by the self tuning memory manager (STMM).

Scenario

In this example, the database administrator (DBA) wants to monitor any changes performed by the STMM. Since the STMM can modify configuration parameters and buffer pool sizes, the DBA has created an event monitor called HIST to capture configuration and DDL changes.
Changes initiated by the STMM can be found by querying the event monitor for records containing one of the following information:
  • The application name (appl_name) is db2stmm.
  • The DDL statement text (stmt_text) contains a comment with the keyword db2stmm. Note that some DDL changes are performed on behalf of the STMM by other applications.
SELECT A.EVENT_TIMESTAMP,
      VARCHAR(A.EVENT_TYPE, 20) AS EVENT_TYPE, A.MEMBER
   FROM CHANGESUMMARY_HIST A LEFT OUTER JOIN
      DDLSTMTEXEC_HIST B
   ON A.EVENT_TIMESTAMP = B.EVENT_TIMESTAMP AND
      A.MEMBER = B.MEMBER AND
      A.EVENT_ID = B.EVENT_ID
   WHERE (A.APPL_NAME = 'db2stmm' OR
      B.STMT_TEXT LIKE '%db2stmm%');
The query might return output similar to the following example:
EVENT_TIMESTAMP            EVENT_TYPE           MEMBER
-------------------------- -------------------- ------
2011-04-22-12.12.17.832316 DBCFG                     0
2011-04-22-12.22.35.227550 DBCFG                     0
2011-04-22-12.12.17.530274 DBCFG                     0
2011-04-22-12.12.17.721403 DBCFG                     0
2011-04-22-12.12.17.776889 DBCFG                     0
2011-04-22-12.22.35.172119 DBCFG                     0
2011-04-22-12.12.17.665098 DBCFG                     0
2011-04-22-12.22.35.116343 DBCFG                     0
2011-04-22-12.29.47.092822 DBCFG                     0
2011-04-22-12.29.47.037709 DBCFG                     0
2011-04-22-12.12.17.600511 DBCFG                     0
2011-04-22-12.22.35.283320 DBCFG                     0
2011-04-22-12.29.46.752477 DBCFG                     0
2011-04-22-12.29.47.148562 DBCFG                     0

 14 record(s) selected.