LOCK_EVMON_HTML_REPORT_TO_FILE

The Stored Procedure LOCK_EVMON_HTML_REPORT_TO_FILE can be used to generate local html reports at the db-server.

Additionally to the common parameters, you must provide a target existing directory (OUT_DIR), and a file name (OUT_FILE) to generate the html report.

• The procedure does not creates any directory by it's own. The target directory must already exist, and have the proper privileges to allow the Instance User Id to create files on it.
• The file name must end with '.html', in order to avoid overwriting accidentally any other important file on the db server.

The other common parameters allows you to specify the base UE event monitor table, and also the filter criteria to select the desired locking events.

    Syntax: 
        DBAEVM.LOCK_EVMON_HTML_REPORT_TO_FILE ( ?, OUT_DIR, OUT_FILE,  XLS_DOC_NAME, EVMON_TABLE_SCHEMA, EVMON_UE_TABLE, MEMBER, EVENT_TYPE, EVENT_START, EVENT_END  )

    OutPut Params: 
        RESULT_MSG : string output indicating successfull or failures for the sp execution. 

    In-Out Params: 
        OUT_DIR            : Target directory where the html report shall be saved. This must be a local directory at the database server. 
                              Directory must already exist. the SP will not create any directory.  
                              It also must have write permission to the Instance Owner ID. 

        OUT_FILE           : Name of the html report file that will be created. File must end with '.html' 

    Input Params: 
        XSL_DOC_NAME       : xsl docname to be assigned  "xml-stylesheet" attribute in the final xml declaration. 
                              Must be a valid xls doc name registered in DBAEVM.EVMON_XML_XSLT table. 

        EVMON_TABLE_SCHEMA : SCHEMA where the desired base UE table holding the event monitor data resides 

        EVMON_UE_TABLE     : UE table name where data is captured by the desired event monitor 

        MEMBER             : extract only events capured at the specified db-partition/member. 
                             If null, all members will be extracted. 
                             If -1,  only data from current member will be extracted. 

        EVENT_TYPE         : extract only events of the desired type.  Eg. LOCKTIMEOUT , DEADLOCK,  LOCKWAIT 
                             if null,  all event types are extracted. 

        EVENT_START        : extract only events newer than or equal to the specified timestamp (>=) 
                             if null,  no filter is applied.

        EVENT_END          : extract only events older than or equal to the specified timestamp (<=)
                             if null, no filter is applied. 

Examples:

Generate a html report at /tmp, named lock_events_rpt.html using default xsl, from the DBAEVM.LOCKEVEMON_UE base table without any filter (all lock events will be extracted, may not fit if you have too much records)

CALL DBAEVM.LOCK_EVMON_HTML_REPORT_TO_FILE(? , '/tmp', 'lock_events_rpt.html', 'db2_EvMonLocking_html.xsl', 'DBAEVM', 'LOCKEVMON_UE') 

 Value of output parameters
  --------------------------
  Parameter Name  : RESULT_MSG
  Parameter Value : HTML Report file generated successfully at desired location
  Parameter Name  : OUT_DIR
  Parameter Value : /tmp
  Parameter Name  : OUT_FILE
  Parameter Value : lock_events_rpt.html
  Return Status = 0

Generate a html report at C:\db2\lock_reports named deadlocks_since_20201022.html, just for DEADLOCKs events that happened since 2020-10-22.

CALL DBAEVM.LOCK_EVMON_HTML_REPORT_TO_FILE(? , 'C:\db2\lock_reports', 'deadlocks_since_20201022.html', 'db2_EvMonLocking_html.xsl', 'DBAEVM', 'LOCKEVMON_UE', NULL, 'DEADLOCKS','2020-10-22') 

 Value of output parameters
  --------------------------
  Parameter Name  : RESULT_MSG
  Parameter Value : HTML Report file generated successfully at desired location
  Parameter Name  : OUT_DIR
  Parameter Value : C:\db2\lock_reports
  Parameter Name  : OUT_FILE
  Parameter Value : deadlocks_since_20201022.html
  Return Status = 0