Db2 (On Premises and Cloud)

Expand all | Collapse all

Enabling Event Monitor for Utility Fails for DMC 3.1.5.1 - ONLINERECOVERY Option Not Accepted

Jump to Best Answer
  • 1.  Enabling Event Monitor for Utility Fails for DMC 3.1.5.1 - ONLINERECOVERY Option Not Accepted

    Posted Tue August 31, 2021 06:33 AM
    Hello DMC Team,

    with DMC V3.1.5.1 (Repository DB is DB2 LUW V11.1; monitored DB is DB2 LUW V11.1) I tried to enable the event monitor for tracking utility executions. I selected all 9 utility types as subjects to be tracked.

    The GUI reports successful processing when saving the new settings. Actually, the event monitor has not been created in the monitored database. Also the event monitor is not listed under the the target DB on the Event Monitor Profile screen. Besides, the DBs shown on this screen seems to be random. Sometimes no DB is shown although event monitors were enabled for some, sometimes a subset of DBs is shown, sometimes all. There seems to be an issue with the cache.

    I checked in the monitor.0 log file and found the following error message:

    ### Error updating database. Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: An unexpected token "ONLINERECOVERY" was found following "VETABLE
    ,
    ". Expected tokens may include: "<evmon_change_history_collect_opti".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.27.25
    ### The error may involve com.ibm.aps.tools.monitor.collector.mybatis.mapper.EventMonitorUtilityMapper.createEvmon-Inline
    ### The error occurred while setting parameters
    ### SQL: CREATE EVENT MONITOR RTMON_EVMON_UTILITY FOR CHANGE HISTORY WHERE EVENT IN ( BACKUP , LOAD , MOVETABLE , ONLINERECOVERY , REDISTRIBUTE , REORG , RESTORE , ROLLFORWARD , RUNSTATS ) WRITE TO TABLE CHANGESUMMARY (TABLE IBM_RTMON.UTIL_COMMON in TS4MONITOR PCTDEACTIVATE 100 ), UTILSTART (table IBM_RTMON.UTIL_START in TS4MONITOR PCTDEACTIVATE 100 ), UTILSTOP (table IBM_RTMON.UTIL_STOP in TS4MONITOR PCTDEACTIVATE 100 ), UTILLOCATION (table IBM_RTMON.UTIL_LOCATION in TS4MONITOR PCTDEACTIVATE 100 ) manualstart
    ### Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: An unexpected token "ONLINERECOVERY" was found following "VETABLE,
    ". Expected tokens may include: "<evmon_change_history_collect_opti".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.27.25 RTMON_EVMON_UTILITY is created failed


    The CREATE EVENT MONITOR statement fails because of the ONLINERECOVERY key word which is according to DB2 LUW documentation a valid keyword (CREATE EVENT MONITOR (change history) statement).

    After de-selecting the ONLINERECOVERY utility from the utility list the utility event monitor has been created successfully (together with the respective SYSTOOLS table). 

    Besides, the creation of the utility event monitor was again not immediately reflected on the Event Monitor Profile screen. It took several minutes and logout/logins to show the new event monitor on the list of event monitors activated on the target database. In the target database itself the event monitor and event monitor tables were immediately visible.

    Now the question is: is the ONLINERECOVERY keyword issue caused by DMC or a bug in DB2?

    Kind regards

    Matthias

    ------------------------------
    Matthias Gärtner-Schür
    DBA
    ------------------------------


  • 2.  RE: Enabling Event Monitor for Utility Fails for DMC 3.1.5.1 - ONLINERECOVERY Option Not Accepted
    Best Answer

    Posted Wed September 01, 2021 06:19 AM
    Hi Matthias,

    Thank you for reporting this issue.

    It seems that the ONLINERECOVERY keyword issue DB2 related. We have a git issue to track this issue. And maybe you can test whether it works on this particular DB by executing the following utility event monitor creation sql:

    CREATE EVENT MONITOR RTMON_EVMON_UTILITY FOR CHANGE HISTORY WHERE EVENT IN
    (BACKUP,LOAD,MOVETABLE,ONLINERECOVERY,REDISTRIBUTE,REORG,RESTORE,ROLLFORWARD,RUNSTATS)
    WRITE TO TABLE
    CHANGESUMMARY (TABLE IBM_RTMON.UTIL_COMMON
    in TS4MONITOR PCTDEACTIVATE 100
    ),
    UTILSTART (table IBM_RTMON.UTIL_START
    in TS4MONITOR PCTDEACTIVATE 100
    ),
    UTILSTOP (table IBM_RTMON.UTIL_STOP
    in TS4MONITOR PCTDEACTIVATE 100
    ),
    UTILLOCATION (table IBM_RTMON.UTIL_LOCATION
    in TS4MONITOR PCTDEACTIVATE 100
    ) manualstart

    For the creation of the utility event monitor was again not immediately reflected on the Event Monitor Profile screen, it is a normal phenomenon, the info is updated in the backend periodically (with monitor collection interval). We also have an issue to keeping track of this improvement, will improve in the next release.


    ------------------------------
    Rui Li
    ------------------------------