Skip to content
This repository has been archived by the owner on Jul 22, 2024. It is now read-only.

LOCK_EVMON_HTML_RPT

Jump to bottom
Samuel Pizarro edited this page Mar 15, 2021 · 1 revision

The stored procedure LOCK_EVMON_HTML_RPT can be used generate html reports in a temporary table that can be later exported to a remote client.

The SP will produce the html data into a CLOB (100 M) output parameter (htmlOut). This output could be fetched remotely if you call the SP using any supported application language.

Alternatively, you can use EXPORT utility from a remote CLP session, to save the html report remotely at the client workstation. For that, you must call the SP and instruct it (bKeepRpt = True) to save the html report into a user temporary table (CGTT) named DBAEVM.LOCK_EVMON_HTML_REPORT.

Similar to the LOCK_EVMON_HTML_REPORT_TO_FILE, the other common parameters allows you to select the base UE table, choose the xsl to be used, and filter the events by member, event types and timestamp range.

Syntax: 
    DBAEVM.LOCK_EVMON_HTML_RPT ( ? , bKeepRpt , XLS_DOC_NAME, EVMON_TABLE_SCHEMA, EVMON_UE_TABLE, MEMBER, EVENT_TYPE, EVENT_START, EVENT_END  )

Inputs: 
    bKeepRpt           : Boolean.  Indicates to save the html report in a temporary table: DBAEVM.LOCK_EVMON_HTML_REPORT 
                         Default = FALSE 
                         Data is only temporarily available (session scope). Data is gone after disconected from database. 
                         Only last execution is saved. Data is overwritten on every call (for the same connection) 

    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. 

OutPuts: 
    htmlOut : Final locking event monitor data transformed into html report by specified xsl. 
              CLOB (100 MB). avoid generating too much information in the same html report, as rendering it on browser will consume too much time and resources.

Examples:

Generate the html report, just for locktimeout events that happened at db-partition 5, before 2020-05-05. Don't keep data in the temp table. This assumes you are calling this from a managed application code and fetching the SP result:

CALL DBAEVM.LOCK_EVMON_HTML_RPT(?, FALSE, 'db2_EvMonLocking_html.xsl', 'DBAEVM', 'LOCKEVMON_UE', 5 , 'LOCKTIMEOUT', NULL, '2020-05-05' )

  Value of output parameters
  --------------------------
  Parameter Name  : HTMLOUT
  Parameter Value : <!DOCTYPE html> ...
  Return Status = 0

Generates the html report for lockwaits events, that happened between 2020-06-25 and 2020-07-05 dates, and save it temporarily, so it can be exported remotely.

db2 "CALL DBAEVM.LOCK_EVMON_HTML_RPT(?, TRUE, 'db2_EvMonLocking_html.xsl', 'DBAEVM', 'LOCKEVMON_UE', NULL , 'LOCKWAIT', '2020-06-25', '2020-07-05' )"

  Value of output parameters
  --------------------------
  Parameter Name  : HTMLOUT
  Parameter Value : <!DOCTYPE html> ...
  Return Status = 0

As data has been saved to the temporary table, we can use the export utility to save the hmtl file remotely:

db2 "EXPORT TO C:\Users\SAMUELPIZARRO\tmp\lockwaits_0625-0705.html of DEL MODIFIED BY lobsinsepfiles SELECT HTML_RPT FROM DBAEVM.LOCK_EVMON_HTML_REPORT"
SQL3104N  The Export utility is beginning to export data to file
"C:\Users\SAMUELPIZARRO\tmp\lockwaits_0625-0705.html".

SQL3105N  The Export utility has finished exporting "1" rows.
Number of rows exported: 1

dir C:\Users\SAMUELPIZARRO\tmp\ 
11/26/2020  02:10 PM                35 lockwaits_0625-0705.html
11/26/2020  02:10 PM            53,866 lockwaits_0625-0705.html.001.lob

your html is saved in the .001.lob file.

Clone this wiki locally