In the Event Manager (EM) configuration file (80EventManager.xml) there is the following setting:

<!-- <task-execution-listener>
    com.lombardisoftware.server.scheduler.DbTaskExecutionListener
</task-execution-listener> -->

As you can see it's commented out by default. If you un-comment or make an override in custom xml file as described here EM will start writing data to LSW_EM_TASK_HISTORY table (you will need to restart BPM for the changes in configuration files to be picked up).

I have mentioned that the data from this table can be used to troubleshoot EM related issue in my EM missing manual article.

In this article I would like to focus on how you can use this table to measure EM tasks performance in your IBM BPM environment. Using the queries I will provide along with some explanation you can build performance metrics reports for your IBM BPM environments.

Initial information can be found in the following dwAnswers post by Mark Filley.

I would like to go a bit deeper into the weeds. Please note that all of the SQL's syntax in this article are Oracle specific, so, you might need to slightly modify particular query(ies) for DB2 and/or MSSQL syntax(es).

To give you some advise on SQL operands and functions used in this article and their analogues in MSSQL and DB2:

MIN, MAX, and AVG should work similar on MSSQL and DB2.

One more note - I know customers who had this table enabled at all times in Production. It does not affect performance much, it does have some overhead on the database of course but it's minimal. One thing to keep in mind - you might want to periodically truncate it because it might grow big pretty fast (it's safe to truncate because it does not have any foreign key constraints, there is basically no relation to other tables in BPM db).

The following queries include gap analysis for the difference between execution time and scheduled time as well as calculations for execution duration with an option to limit by queue id and execution_time. You may also see distribution by nodes in the cluster.

Query1 - Gap by node: 

select 
    em_name, to_char(execution_time, 'dd-mon-yyyy hh24:mi') 
    as "Execution Time", count(*) as "# EPM",
    min((execution_time - scheduled_time)*24*60) as "Min Gap (minutes)",
    max((execution_time - scheduled_time)*24*60) as "Max Gap (minutes)",
    avg((execution_time - scheduled_time)*24*60) as "Avg Gap (minutes)",
    min(execution_duration) as "Min Exec Dur", max(execution_duration) 
    as "Max Exec Dur", 
    avg(execution_duration) as "Avg Exec Dur"
from 
    lsw_em_task_history
group by 
    em_name, to_char(execution_time, 'dd-mon-yyyy hh24:mi')
order by 
    to_char(execution_time, 'dd-mon-yyyy hh24:mi') desc, em_name;

Query2 - Total gap ordered by max gap:

select 
    to_char(execution_time, 'dd-mon-yyyy hh24:mi') || ':00'
    as "Execution Time", TASK_EXECUTION_CLASS,
    max((execution_time - scheduled_time)*24*60) as "Max Gap (minutes)"
from 
    lsw_em_task_history
group by 
    TASK_EXECUTION_CLASS, to_char(execution_time, 
    'dd-mon-yyyy hh24:mi') || ':00'
order by 
    max((execution_time - scheduled_time)*24*60) desc

Query3 - gap average by queue

select 
    to_char(execution_time, 
    'dd-mon-yyyy_hh24:mi') || ':00' as "Execution Time",  
    queue_id, round(avg((execution_time - scheduled_time)*24*60),2) 
    as "Average Gap (minutes)"
from 
    lsw_em_task_history
where
    EXECUTION_TIME > to_date('xx/xx/xxxx','mm/dd/yyyy')
group by 
    to_char(execution_time, 
    'dd-mon-yyyy_hh24:mi') || ':00', 
    queue_id order by queue_id, 
    to_char(execution_time, 'dd-mon-yyyy_hh24:mi') || ':00'

Query4 - Hourly average gap

select 
    ExecTime, AvgGapMinutes as "Avg Gap Minutes" 
from 
    (select trunc(execution_time,'HH24') ExecTime,
    avg((execution_time - scheduled_time)*24*60) AvgGapMinutes
from 
    lsw_em_task_history
where 
    execution_time >= to_date('xx/xx/xxxx 00:00:00','mm/dd/yyyy hh24:mi:ss')
group by 
    trunc(execution_time,'HH24'))
where 
    AvgGapMinutes > 0.25
order 
    by ExecTime

Query5 - average (per minute) execution duration per EM, execution_time and class (take :MI part out and you will get per hour duration)

select
    em_name,
    task_execution_class, 
    to_char(execution_time, 'MM/DD HH24:MI') || ':00' hour,
    count(*),
    avg(execution_duration)
from
    lsw_em_task_history
group by
    em_name,
    task_execution_class,
    to_char(execution_time, 'MM/DD HH24:MI')
order by
    avg(execution_duration) desc,
    task_execution_class,
    em_name,
    to_char(execution_time, 'MM/DD HH24:MI')

Query6 - Number of EM tasks per minute per node

select 
    em_name,
    to_char(execution_time, 'MM/DD HH24:MI') || ':00' hour,
    count(*) number_of_tasks
from
    lsw_em_task_history
group by
    em_name,
    to_char(execution_time, 'MM/DD HH24:MI')
order by
    em_name,
    to_char(execution_time, 'MM/DD HH24:MI')