ThinkingDb2!: 2015

Thursday, 2 July 2015

How to Estimate the duration of a TAKEOVER HADR operation in DB2

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

A TAKEOVER HADR operation, which instructs a Standby database to take over as the new Primary database, is normally a quick operation. However,
a TAKEOVER HADR operation (when not executed with the emergency WITH FORCE option) must first rollback any uncommitted transactions on the Primary database before the Standby database can take over. This can elongate the HADR TAKEOVER operation and it may appear to hang. This blog entry describes how to identify any large or old uncommitted transactions that would require a lengthy rollback.

When large or old uncommitted transactions exist, the rollback of these transactions can take a long time, causing the TAKEOVER HADR operation to appear to hang.
For large uncommitted transactions which consume a lot of LogSpace, the rollback must replay (undo) many log-records associated with the transaction.
For old uncommitted transactions which do not consume a lot of LogSpace, the rollback must still read and parse through many log-records to eventually find those associated with the transaction to replay (undo).

Determine the Size and Span of Uncommitted Transactions

To determine if there are any uncommitted transactions that will require a lengthy rollback, you can query db2pd-transactions to list transactions with the largest log span (in bytes). This represents the approximate number of bytes of log file data that will have to be parsed through in order to process the log-records required to rollback the transaction.

$ db2pd -db [dbname] -transactions > trans.out;
$ cat trans.out | awk '{diff = $10-$9; printf "%.0f=log_span, firstLSN=%s, lastLSN=%s, AppHndl=%s \n",diff,$9,$10,$2 }' | sort -n -r | less

8648404033=log_span, firstLSN=0x000055D26CD7EAB6, lastLSN=0x000055D4705416F7, AppHndl=25943
1512356109=log_span, firstLSN=0x000055D4162F5C21, lastLSN=0x000055D47054152E, AppHndl=29146
488762756=log_span, firstLSN=0x000055D4533228EB, lastLSN=0x000055D47054166F, AppHndl=25629
55009676=log_span, firstLSN=0x000055D46D0CB109, lastLSN=0x000055D470541295, AppHndl=29424
53676373=log_span, firstLSN=0x000055D46D20AC09, lastLSN=0x000055D47053B55E, AppHndl=29436
51957579=log_span, firstLSN=0x000055D46D3B3180, lastLSN=0x000055D4705400CB, AppHndl=29465
2422301=log_span, firstLSN=0x000055D4702F0366, lastLSN=0x000055D47053F983, AppHndl=29442
152148=log_span, firstLSN=0x000055D47050A4D9, lastLSN=0x000055D47052F72D, AppHndl=30172
136276=log_span, firstLSN=0x000055D47050F536, lastLSN=0x000055D47053098A, AppHndl=30485
6765=log_span, firstLSN=0x000055D47053CC52, lastLSN=0x000055D47053E6BF, AppHndl=29666
6423=log_span, firstLSN=0x000055D4705305A2, lastLSN=0x000055D470531EB9, AppHndl=30774
2561=log_span, firstLSN=0x000055D47052C938, lastLSN=0x000055D47052D339, AppHndl=25326
1779=log_span, firstLSN=0x000055D47053CFC0, lastLSN=0x000055D47053D6B3, AppHndl=25378
1275=log_span, firstLSN=0x000055D470537F7E, lastLSN=0x000055D470538479, AppHndl=25367
526=log_span, firstLSN=0x000055D4705331A7, lastLSN=0x000055D4705333B5, AppHndl=29608
289=log_span, firstLSN=0x000055D470526016, lastLSN=0x000055D470526137, AppHndl=22962
…etc…

If we see one or more transactions with a log span of 500MB or more, we would generally expect it to take some *noticeable* amount of time for the transaction to rollback. But depending on the performance of your system, this threshold could be lower.

To find out more about these particular transactions, you can find their AppHndl's in "db2pd –db [dbname] -apinfo" output.

Estimate the Progress of a Rollback while TAKEOVER HADR is running

Once the TAKEOVER operation is issued, you can see which applications are still rolling back their uncommitted transections by using db2pd-applications:

$ db2pd –db [dbname] –applications | grep "RollbackActive"

Applications:
Address AppHandl [nod-index] NumAgents CoorEDUID Status … Appid …
0x07800000012F0080 9 [000-00009] 1 14394 RollbackActive … *LOCAL.dsciaraf.150703020130

As I mentioned above, you can determine the original LogSpace and log span values for these application’s transactions using the db2pd-transactions and awk command that I provided at top.

$ db2pd –db [dbname] –transactions > trans.out
$ cat trans.out | awk '{diff = $10-$9; printf "%.0f=log_span, firstLSN=%s, lastLSN=%s, AppHndl=%s \n",diff,$9,$10,$2 }' | sort -n -r | less

30000269=log_span, firstLSN=0x00000192863F6B14, lastLSN=0x00000222863F883C, AppHndl=9
0=log_span, firstLSN=0x0000000000000000, lastLSN=0x0000000000000000, AppHndl=11
0=log_span, firstLSN=0x0000000000000000, lastLSN=0x0000000000000000, AppHndl=12
0=log_span, firstLSN=0x0000000000000000, lastLSN=0x0000000000000000, AppHndl=13
0=log_span, firstLSN=0x0000000000000000, lastLSN=0x0000000000000000, AppHndl=14
…

But how about monitoring the actual progress of the Rollback operation?
Unfortunately, since the Takeover operation has forced off all existing database connections and blocked new ones, we cannot use the normal methods for determining the progress of the rollback operations (such as monitoring the ‘Total Work’ and ‘Committed Work’ values of an application snapshot).

However, we can monitor the SpaceReserved value in db2pd-transactions.
(The SpaceReserved value represents the amount of log file space that was reserved for this transaction in case a rollback operation was needed. For every log-record that is replayed (undone) while a transaction is being rolled back, a special log-record is added directly into this reserved log file space. If the rollback were to fail and be restarted again, these special log-records allow DB2 to avoid processing log-records which have already been processed.)

As the rollback operation proceeds, the SpaceReserved value diminishes. This is easily observable for large transactions where many log-records are being replayed (undone). It is not easily observable for old transactions which are not large, since many log-records are parsed but seldom are replayed (undone) as I described earlier.
Here in my example, I simply watch the SpaceReserved value go down. You can extrapolate an estimated time to completion by watching the rate at which the value diminishes (however, since even large transactions can insert log-records into the log file stream in periodic clumps, you may see pauses in the rate at which this value diminishes while you are observing it).

$ db2pd –db [dbname] –transactions | grep ABORT

Address            AppHandl TranHdl … State … Firstlsn          Lastlsn            SpaceReserved   LogSpace …
0x070000004006CF00 7         2        … ABORT   0x00000192863F6B14 0x00000222863F883C 1151342         2312191 …

$ db2pd –db [dbname] –transactions | grep ABORT

Address            AppHandl TranHdl … State … Firstlsn          Lastlsn            SpaceReserved   LogSpace …
0x070000004006CF00 7         2          ABORT   0x00000192863F6B14 0x00000222863F883C 473890         2312191 …

$ db2pd –db [dbname] –transactions | grep ABORT

Address            AppHandl TranHdl … State … Firstlsn          Lastlsn            SpaceReserved   LogSpace …
0x070000004006CF00 7         2          ABORT   0x00000192863F6B14 0x00000222863F883C 304246         2312191 …

I hope this information is useful. If you have any thoughts or questions, feel free to submit a comment below.

Thursday, 25 June 2015

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