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

Translate