- 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
