Monitoring SQL INCLUDING the content of host variables – check out the activity event monitor

In DB2 9.5 for Linux, UNIX and Windows the Workload Management Feature (WLM) was introduced. Besides the obvious capability of managing workloads it added a whole bunch of new monitoring possibilities. These new options provide great ability to focus database monitoring on the really important points. While WLM is an extra-priced feature – unless you got the AESE version of DB2 or any InfoSphere Warehouse editions – you can use some monitoring functions even without this feature and this article is concentrating on exactly these. 

SQL monitoring can be done with the Activity Event Monitor and this monitor even allows it to catch content of host variables – something the traditional event monitor for statements was never able to do and the new one is much easier to interpret because only a single row per SQL statement is returned. Some clarifications about WLM terms we are going to use here:An “Activity” is anything which is executed in the database like a LOAD, DDL, DML or a call of a stored procedure. “Workload” is a grouping of connects in order to identify incoming work by source, application name or user for example. “Service Class” is a WLM object and the primary point of resource assignment.  

Monitoring without the WLM license is allowed if you do not create any new WLM objects. This means we have to stick to the default ones available in very database since DB 9.5 which is the default workload SYSDEFAULTUSERWORKLOAD and SYSDEFAULTUSERCLASS service class. So in the end we can do monitoring on database level and for a more focused approach a WLM license is needed.

Following command creates and activates the Activity event monitor.  

create event monitor DB2ACTIVITIES       FOR ACTIVITIES write to table; set event monitor DB2ACTIVITIES state 1;

There is a script with these and even more commands available under: sqllib\misc\wlmevmon.ddl.

Because it is a WLM monitor we have to enable monitoring on WLM objects – while it is possible on workload and service classes – but one location is enough:   

alter workload SYSDEFAULTUSERWORKLOAD  COLLECT ACTIVITY DATA on coordinator WITH DETAILS AND VALUES;

The COLLECT ACTIVITY DATA enables monitoring and the detail clause WITH DETAILS will additionally collect statement and environmental information and finally the AND VAULES clause will include input data in the result tables as well.
This results in the creation of following tables:

CONTROL [5 columns] (data about the event monitor itself)ACTIVITY_DB2ACTIVITIES [81 columns] (general activity monitoring information)ACTIVITYMETRICS_DB2ACTIVITIES [164 columns] (since DB2 9.7 FP 4 metrics for activities are reported)ACTIVITYSTMT_DB2ACTIVITIES [29 columns]  (statement related information as a result of the WITH DETAILS option)ACTIVITYVALS_DB2ACTIVITIES [12 columns]  (statement values related information as a result of the AND VALUES option)

A full detailed description of the related tables and their content can be found in the information center under: http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.mon.doc%2Fdoc%2Fr0059038.html or in short  http://goo.gl/Ohvtg

These tables do not have primary keys but the combination of activity_id, uow_id and appl_id (or agent_id) can be used to join these tables.

The number of columns gives a rough idea of the massive content – specially the additional table ACTIVITYMETRICS_<monname> holds lots of details.

Some statements to evaluate the monitored data:  

select a.AGENT_ID, a.APPL_ID, a.UOW_ID, a.ACTIVITY_TYPE,       s.STMT_TEXT,       v.STMT_VALUE_INDEX, v.STMT_VALUE_DATA,       a.QUERY_CARD_ESTIMATE, a.QUERY_COST_ESTIMATE,       a.ACT_EXEC_TIME, a.ROWS_RETURNED  from ACTIVITY_DB2ACTIVITIES a         inner join ACTIVITYSTMT_DB2ACTIVITIES s           on a.ACTIVITY_ID = s.ACTIVITY_ID            and a.APPL_ID = s.APPL_ID            and a.UOW_ID = s.UOW_ID  left join ACTIVITYVALS_DB2ACTIVITIES v         on a.ACTIVITY_ID = v.ACTIVITY_ID            and a.APPL_ID = v.APPL_ID            and a.UOW_ID 

This statement shows the SQL statement (STMT_TEXT) and all content of host variables. STMT_VALUE_INDEX lists the position of host variable within the statement – useful to distinguish if there are more than one and this will lead to several rows for a single statement. The variable content is shown in STMT_VALUE_DATA.

Additional monitoring information can be retrieved from the ACTIVITYMETRICS table:  

select a.AGENT_ID, a.APPL_ID, a.UOW_ID, a.ACTIVITY_TYPE,        m.ROWS_READ, m.ROWS_MODIFIED, m.TOTAL_CPU_TIME,            m.STMT_EXEC_TIME, m.LOCK_WAIT_TIME         from ACTIVITY_DB2ACTIVITIES a          inner join ACTIVITYMETRICS_DB2ACTIVITIES m            on a.ACTIVITY_ID = m.ACTIVITY_ID             and a.APPL_ID = m.APPL_ID             and a.UOW_ID = m.UOW_ID

Of course there are lots of additional queries to get exactly what you need.Once the workload has been captured do not forget to stop the monitor again.  

set event monitor DB2ACTIVITIES state 0;

Further hints:Check out db2caem tool to capture details for a SQL statementUsing the WITH DETAILS, SECTION AND VALUES clause for the Workload enables the capturing of section explains. 


 Michael Tiefenbacher – IBM Champion –  ids-System GmbH        

Steve Thomas and Michael Tiefenbacher enjoy a conversation at IDUG EMEA in Barcelona.JPG

Recent Stories
The IDUG Board of Directors Slate for 2017-2018 is Open for Voting

Open Board of Directors Position - Apply Before the January 13, 2017 Deadline

Enable Social Login