Transitioning from Query Patroller historical tables to WLM & Event Monitors in DB2 LUW.

- David Sciaraffa, IBM IM/DB2 Accelerated Value Leader (Premium Support Manager).

Prior to DB2 v9.5, the Query Patroller (QP) feature provided the ability to perform historical analysis of query workloads. This was accomplished by enabling QP to intercept queries; enabling QP to archive this query data into historical data control tables; generating metrics of the data in these control tables; and periodically purging these control tables.
QP was deprecated in DB2 9.5 and discontinued in DB2 10.1. This blog entry provides an example of how to use the Activity Event Monitor and wlmhist.pl script to capture and generate historical information which is compatible with the QP historical data control tables.

---

High-level Overview:

In DB2 9.5 and newer releases, you will use the Activity Event Monitor and wlmhist.pl script to capture and generate historical query information, using the following guidelines:
i. Enable an activity event monitor to capture information about executing statements.  You can configure an activity event monitor to capture information on all queries (as my example below), or just a subset of workloads.  This data is stored in the activity event monitor tables.
ii. Use the wlmhist.pl perl script to collect explain plan information for the statements that were captured by the activity event monitor.  (This is the equivalent of running the GENERATE HISTORY DATA command in QP).  This information is stored in the WLMHIST table (which is created automatically by the perl script).
iii.  Formulate your queries by joining the activity event monitor tables with the WLMHIST table.
iv. Periodically prune data from the activity event monitor tables and WLMHIST table. (We don't want these tables to grow forever).

 

QP Historical Analysis Control Tables:

To begin, I map most columns of the QP historical data control tables (QUERY_ANALYSIS and TRACK_QUERY_INFO) to the Activity Event Monitor and wlmhist.pl tables:

QP Column	QP Table	EVMON/WLM Column	EVMON/WLM Table
QUERY_ID	QUERY_ANALYSIS	(ACTIVITY_ID + UOW_ID + APPL_ID + ACTIVITY_SECONDARY_ID)	WLMHIST
STATEMENT_TYPE	QUERY_ANALYSIS	ACTIVITY_TYPE	WLMHIST
OBJECT_TYPE	QUERY_ANALYSIS	OBJECT_TYPE	WLMHIST
OBJECT_SCHEMA	QUERY_ANALYSIS	OBJECT_SCHEMA	WLMHIST
OBJECT_NAME	QUERY_ANALYSIS	OBJECT_NAME	WLMHIST
TABLE_SCHEMA	QUERY_ANALYSIS	TABLE_SCHEMA	WLMHIST
TABLE_NAME	QUERY_ANALYSIS	TABLE_NAME	WLMHIST
ID	TRACK_QUERY_INFO	(ACTIVITY_ID + UOW_ID + APPL_ID)	ACTIVITY_<evname>
TYPE	TRACK_QUERY_INFO	ACTIVITY_TYPE	ACTIVITY_<evname>
COMPLETION_STATUS	TRACK_QUERY_INFO	COMPLETION_STATUS	UOW event monitor ***
MANAGED	TRACK_QUERY_INFO	ACTIVITY_<evname>'s service_subclass_name != SYSDEFAULTSUBCLASS
EXPLAIN_RUN	TRACK_QUERY_INFO	Check if row exists in WLMHIST with same (ACTIVITY_ID + UOW_ID + APPL_ID)
QUERY_PRIORITY	TRACK_QUERY_INFO	none	none
STMT_ATTRIBUTES	TRACK_QUERY_INFO	none	none
NESTING_LEVEL	TRACK_QUERY_INFO	STMT_NEST_LEVEL	ACTIVITY_<evname>
ROUTINE_ID	TRACK_QUERY_INFO	ROUTINE_ID	ACTIVITYSTMT_<evname>
PARENT_QUERY_ID	TRACK_QUERY_INFO	(PARENT_ACTIVITY_ID + PARENT_UOW_ID + APPL_ID)	ACTIVITY_<evname>
PACKAGE_SCHEMA	TRACK_QUERY_INFO
PACKAGE_NAME	TRACK_QUERY_INFO	PACKAGE_NAME	ACTIVITYSTMT_<evname>
PACKAGE_VERSION	TRACK_QUERY_INFO	PACKAGE_VERSION_ID	ACTIVITYSTMT_<evname>
SECTION_ENTRY_NUMBER	TRACK_QUERY_INFO	SECTION_NUMBER	ACTIVITYSTMT_<evname>
PROFILE_ID	TRACK_QUERY_INFO	none	none
RESULT_ROWS	TRACK_QUERY_INFO	ROWS_RETURNED	ACTIVITY_<evname>
EXECUTION_TIME_SECONDS	TRACK_QUERY_INFO	TIMESTAMPDIFF(1, TIME_COMPLETED - TIME_STARTED)	ACTIVITY_<evname>
EXECUTION_TIME_MILLI_SECONDS	TRACK_QUERY_INFO	TIMESTAMPDIFF(1, TIME_COMPLETED - TIME_STARTED)	ACTIVITY_<evname>
SYSTEM_TIME_SECONDS	TRACK_QUERY_INFO	SYSTEM_CPU_TIME	ACTIVITY_<evname>
SYSTEM_TIME_MILLI_SECONDS	TRACK_QUERY_INFO	SYSTEM_CPU_TIME	ACTIVITY_<evname>
USER_TIME_SECONDS	TRACK_QUERY_INFO	USER_CPU_TIME	ACTIVITY_<evname>
USER_TIME_MILLI_SECONDS	TRACK_QUERY_INFO	USER_CPU_TIME	ACTIVITY_<evname>
ESTIMATED_COST	TRACK_QUERY_INFO	QUERY_COST_ESTIMATE	ACTIVITY_<evname>
TIME_CREATED	TRACK_QUERY_INFO	TIME_CREATED	ACTIVITY_<evname>
TIME_STARTED	TRACK_QUERY_INFO	TIME_STARTED	ACTIVITY_<evname>
TIME_COMPLETED	TRACK_QUERY_INFO	TIME_COMPLETED	ACTIVITY_<evname>
TIME_RELEASED	TRACK_QUERY_INFO	none	none
USER_ID	TRACK_QUERY_INFO	SESSION_AUTH_ID	ACTIVITY_<evname>
USER_TYPE	TRACK_QUERY_INFO	AUTHORITY_BITMAP	connection-event-monitor
STMT_AUTH_ID	TRACK_QUERY_INFO	SESSION_AUTH_ID	ACTIVITY_<evname>
STMT_AUTH_TYPE	TRACK_QUERY_INFO	SESSION_AUTH_ID	ACTIVITY_<evname>
ACCOUNT_ID	TRACK_QUERY_INFO
APPLICATION	TRACK_QUERY_INFO	APPL_NAME	ACTIVITY_<evname>
APPLICATION_HOST	TRACK_QUERY_INFO	ADDRESS	ACTIVITY_<evname>
CLIENT_USER_ID	TRACK_QUERY_INFO	n/a	n/a
CLIENT_ACCOUNT_ID	TRACK_QUERY_INFO	n/a	n/a
CLIENT_APPLICATION	TRACK_QUERY_INFO	n/a	n/a
CLIENT_WORKSTATION	TRACK_QUERY_INFO	n/a	n/a
REASON_HELD	TRACK_QUERY_INFO
REASON_QUEUED	TRACK_QUERY_INFO
ENVIRONMENT_VALUES	TRACK_QUERY_INFO	COMP_ENV_DESC	ACTIVITYSTMT_<evname>
STATEMENT	TRACK_QUERY_INFO	STMT_TEXT	ACTIVITYSTMT_<evname>

 

1) Create and activate an Activity Event Monitor:

I create an activity event monitor (data collection does not begin yet):

$ db2 create event monitor MYACTEVMON1 for activities write to table
$ db2 set event monitor MYACTEVMON1 state 1

Next, I enable the collection of activity data for all workloads (ie. everything under the default SYSDEFAULTSUBCLASS workload class).  However you can customize this to only capture certain activities or workloads by separating workloads into different classes (the broad aspects of WLM are outside the scope of this blog entry).

$ db2 "ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER SYSDEFAULTUSERCLASS COLLECT ACTIVITY DATA WITH DETAILS"

Next, I wait for some time to allow activity data to percolate into my activity event monitor tables.

Next, I flush the activity-event-monitor memory buffer.

$ db2 flush event monitor MYACTEVMON1 buffer.

 

2) Run the wlmhist.pl Script

First, I create the explain-tables. These tables are used by the wlmhist.pl script when generating query access-plan ‘explain’ information. My explain tables will have schema-name ‘DSCIARAF’ like the userid which I connected to my database with.  This only needs to be done once.

$ db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), 'DSCIARAF')"

Next, I execute the sqllib/samples/perl/wlmhist.pl script.
This script will query the statements captured in the activity event monitor tables, run an explain on each statement, and create/populate the WLMHIST table.
You have some control over which statements are processed by specifying the fromTime/ToTime or the workloadid/ServiceClass/SubclassName arguments.
Note: The script will assume the explain-tables belong to the same schema as the userid executing the script (ie. ‘dsciaraf’ in my case).

Usage: wlmhist.pl dbAlias userId passwd
         [fromTime toTime workloadid serviceClassName serviceSubclassName activityTable activityStmtTable]

$ perl ~/sqllib/samples/perl/wlmhist.pl DB101 dsciaraf - - - - - - ACTIVITY_MYACTEVMON1 ACTIVITYSTMT_MYACTEVMON1

  Generate historical data for database of dbi:DB2:DB101
  Connecting to database...
  Connected to database.
  Disconnecting from database...
  Disconnected from database.
  Total number of activities processed by historical generator: 5 Total number of activities processed successfully: 5

 

3) Query the Activity Event Monitor tables and WLMHIST table

Finally, we can reference the activity event monitor tables and WLMHIST table, in the same way that the QP QUERY_ANALYSIS and TRACK_QUERY_INFO tables were used.

Here, I’m show a few SQL statements that were provided by a DB2 customer which I support:

Example A:

QP (Before):
select distinct char(user_id,15) USER_id,TO_CHAR(TIME_started,'YYYY-MM-DD HH24:MI:SS') Connected_to_db_on from db2qp.track_query_info
where date(time_started)>=(current date - 1 days)
order by 2, 1;

AEM+WLMHIST (After):
select distinct char(SESSION_AUTH_ID,15) USER_ID, TO_CHAR(TIME_STARTED,'YYYY-MM-DD HH24:MI:SS') CONNECTED_TO_DB_ON from DSCIARAF.ACTIVITY_MYACTEVMON1
where date(TIME_STARTED) >= (CURRENT DATE - 1 days)
order by 2, 1;

USER_ID         CONNECTED_TO_DB_ON          
--------------- ---------------------------
DSCIARAF        2015-06-18 11:33:45     
DSCIARAF        2015-06-18 11:33:48    
DSCIARAF        2015-06-18 11:33:49   
...etc....

Example B:

QP (Before):
select rtrim(substr(rslt.table_schema,1,8))||'.'||substr(rslt.table_name,1,30) Table_Name, substr(rslt.object_name,1,10) column_Name, substr(rslt.USER_ID,1,15) User_id,  date(rslt.TIME_STARTED) date_accessed
,count(distinct rslt.id) Num_times_Accessed
from
(select sql2.id, sql2.time_created, bigint(sql2.estimated_cost) as timeron,
        sql2.USER_ID, sql2.TIME_STARTED, sql2.TIME_COMPLETED, sql1.table_schema, sql1.table_name, sql1.object_name
     from db2qp.track_query_info sql2, db2qp.query_analysis sql1
     where
      sql2.id = sql1.query_id
      and sql1.object_type = 'C'
      and date(time_started)>=(current date - 1 days)
        and sql1.object_name in ('SSN','SSNO','SSN_NBR')
   ) as rslt
group by rslt.table_schema, rslt.table_name, rslt.object_name, rslt.USER_ID, date(rslt.TIME_STARTED)
order by rslt.USER_ID,date(rslt.TIME_STARTED), rslt.USER_ID, rslt.object_name with ur;


AEM+WLMHIST (After):
select
  rtrim(substr(rslt.TABLE_SCHEMA,1,8))||'.'||substr(rslt.TABLE_NAME,1,30) TABLE_NAME,
  substr(rslt.OBJECT_NAME,1,10) COLUMN_NAME,
  substr(rslt.USER_ID,1,15) USER_ID, 
  date(rslt.TIME_STARTED) DATE_ACCESSED,
  count(distinct rslt.ID) NUM_TIMES_ACCESSED
from
  (select concat('actid=', concat(actevm.ACTIVITY_ID, concat('.uowid=', concat(actevm.UOW_ID, concat('.applid=', actevm.APPL_ID))))) as ID,
          actevm.TIME_CREATED,
          bigint(actevm.QUERY_COST_ESTIMATE) as TIMERON,
          actevm.SESSION_AUTH_ID as USER_ID,
          actevm.TIME_STARTED,
          actevm.TIME_COMPLETED,
          wlmhist.TABLE_SCHEMA,
          wlmhist.TABLE_NAME,
          wlmhist.OBJECT_NAME
     from ACTIVITY_MYACTEVMON1 actevm,
          WLMHIST wlmhist
     where
      actevm.ACTIVITY_ID = wlmhist.ACTIVITY_ID  and
      actevm.UOW_ID = wlmhist.UOW_ID and
      actevm.APPL_ID = wlmhist.APPL_ID and
      actevm.ACTIVITY_SECONDARY_ID = wlmhist.ACTIVITY_SECONDARY_ID and
      wlmhist.OBJECT_TYPE = 'C' and
      date(wlmhist.TIME_STARTED) >= (CURRENT DATE - 1 DAYS) and
      wlmhist.OBJECT_NAME in ('SSN','SSNO','SSN_NBR')
   ) as rslt
group by rslt.TABLE_SCHEMA, rslt.TABLE_NAME, rslt.OBJECT_NAME, rslt.USER_ID, date(rslt.TIME_STARTED)
order by rslt.USER_ID,date(rslt.TIME_STARTED), rslt.USER_ID, rslt.OBJECT_NAME with ur;

TABLE_NAME                              COLUMN_NAME USER_ID         DATE_ACCESSED NUM_TIMES_ACCESSED
--------------------------------------- ----------- --------------- ------------- ------------------
DSCIARAF.ACTIVITY_MYACTEVMON1           ACTIVATE_T  DSCIARAF        06/18/2015                     2
DSCIARAF.ACTIVITY_MYACTEVMON1           ACTIVITY_I  DSCIARAF        06/18/2015                     2
DSCIARAF.ACTIVITY_MYACTEVMON1           ACTIVITY_S  DSCIARAF        06/18/2015                     2
DSCIARAF.ACTIVITY_MYACTEVMON1           ACTIVITY_T  DSCIARAF        06/18/2015                     2
DSCIARAF.ACTIVITY_MYACTEVMON1           ACT_EXEC_T  DSCIARAF        06/18/2015                     2
DSCIARAF.ACTIVITY_MYACTEVMON1           ADDRESS     DSCIARAF        06/18/2015                     2
...etc...

Example C:

QP (Before):
select rtrim(substr(TABLE_SCHEMA,1,10))||'.'||substr(table_name,1,35) TABLE_NAME, substr(object_name,1,30) COLUMN_NAME, substr(user_id,1,8) as user_id, date(time_started) as date_accessed, count(*) as Num_times_Accessed
from db2qp.query_analysis qp,
   DB2QP.TRACK_QUERY_INFO AL2 ,
    syscat.tables sys
where
qp.query_id = al2.id
and sys.tabname = qp.table_name
and sys.type = 'T'
and qp.object_type = 'C'
and date(time_started)>=(current date - 1 days)
and qp.TABLE_SCHEMA not in ('SYSIBM')
group by table_schema,table_name, object_name, user_id, date(time_started)
order by 1, 3 desc;

AEM+WLMHIST (After):
select
  rtrim(substr(wlmhist.TABLE_SCHEMA,1,8))||'.'||substr(wlmhist.TABLE_NAME,1,30) TABLE_NAME,
  substr(wlmhist.OBJECT_NAME,1,10) COLUMN_NAME,
  substr(actevm.SESSION_AUTH_ID,1,15) USER_ID, 
  date(actevm.TIME_STARTED) DATE_ACCESSED,
  count(*) NUM_TIMES_ACCESSED
from ACTIVITY_MYACTEVMON1 actevm,
     WLMHIST wlmhist,
     SYSCAT.TABLES sys
where
  actevm.ACTIVITY_ID = wlmhist.ACTIVITY_ID  and
  actevm.UOW_ID = wlmhist.UOW_ID  and
  actevm.APPL_ID = wlmhist.APPL_ID  and
  actevm.ACTIVITY_SECONDARY_ID = wlmhist.ACTIVITY_SECONDARY_ID  and
  sys.TABNAME = wlmhist.TABLE_NAME  and
  sys.TYPE = 'T'  and
  wlmhist.OBJECT_TYPE = 'C' and
  date(actevm.TIME_STARTED) >= (CURRENT DATE - 1 DAYS) and
  wlmhist.TABLE_SCHEMA not in ('SYSIBM')
group by wlmhist.TABLE_SCHEMA, wlmhist.TABLE_NAME, wlmhist.OBJECT_NAME, actevm.SESSION_AUTH_ID, date(actevm.TIME_STARTED)
order by 1, 3 desc;

TABLE_NAME                              COLUMN_NAME USER_ID         DATE_ACCESSED NUM_TIMES_ACCESSED
--------------------------------------- ----------- --------------- ------------- ------------------
DSCIARAF.ACTIVITY_MYACTEVMON1           ACTIVATE_T  DSCIARAF        06/18/2015                     2
DSCIARAF.ACTIVITY_MYACTEVMON1           ACTIVITY_I  DSCIARAF        06/18/2015                     2
DSCIARAF.ACTIVITY_MYACTEVMON1           ACTIVITY_S  DSCIARAF        06/18/2015                     2
DSCIARAF.ACTIVITY_MYACTEVMON1           ACTIVITY_T  DSCIARAF        06/18/2015                     2
DSCIARAF.ACTIVITY_MYACTEVMON1           ACT_EXEC_T  DSCIARAF        06/18/2015                     2
DSCIARAF.ACTIVITY_MYACTEVMON1           ADDRESS     DSCIARAF        06/18/2015                     2
..etc...

 

4) Periodic Pruning of the Activity Event Monitor tables and WLMHIST table.

And finally, don't forget that you'll have to perform periodic purging of the activity-event-monitor tables, and the WLMHIST table.  This can be accomplished via sql DELETE (logged) or TRUNCATE (non-logged) of the WLMHIST table, and ACTIVITY_<evname>* tables.

 

Useful Links

Working With Event Monitors: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.mon.doc/doc/t0059044.html
Historical Monitoring with Event Monitors: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.wlm.doc/doc/c0052603.html
Event Monitor Table management: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.mon.doc/doc/c0006121.html
Create Event Monitor (ACTIVITIES) stmt: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0055061.html?cp=SSEPGG_10.5.0%2F2-12-7-64
Tutorial for Workload Management (WLM): http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.wlm.doc/doc/c0053139.html
wlmhist.pl: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.wlm.doc/doc/c0053147.html