分析一个长时间运行SQL的执行计划，怀疑优化器的估计值和实际值出入很大，造成执行计划不准确，于是采用下面的方法在执行计划中把实际值和估计值都输出。

####修改DB级别的参数
db2 update db cfg for SAMPLE using SECTION_ACTUALS BASE

####连接数据库
db2 connect to SAMPLE

####创建EXPLAIN表，如果已有，请忽略这一步
db2 "call sysproc.sysinstallobjects('EXPLAIN','C',null,null)"

####创建activity event monitor
db2 "
create event monitor actEvmon for activities write to table
activity (table activity, in USERSPACE1),
activityvals (table activityvals, in USERSPACE1),
activitystmt (table activitystmt, in USERSPACE1),
activitymetrics (table activitymetrics, in USERSPACE1),
control (table control, in USERSPACE1)
manualstart"

db2 "set event monitor actEvmon state 1"

####查看当前的连接
db2 "values sysproc.mon_get_application_id()"

1                           
----------------------------
*LOCAL.db2inst1.200403101703

  1 record(s) selected.

db2 "values sysproc.mon_get_application_handle()"

1
--------------------
               10372

  1 record(s) selected.

####Turn on the collection of activity data for this specific application-handle
db2 "CALL WLM_SET_CONN_ENV(10372, '<collectactdata> WITH DETAILS, SECTION AND VALUES </collectactdata><collectactpartition> ALL </collectactpartition>')"

####执行SQL
db2 -tvf s1.sql > s1.out

db2 flush event monitor actEvmon buffer

####查看所执行SQL的UOW_ID和ACTIVITY_ID
db2 "select a.APPL_ID, a.ACTIVITY_ID, a.UOW_ID, a.ACT_EXEC_TIME, a.TIME_CREATED, SUBSTR (b.STMT_TEXT, 1, 20) as STMT_TEXT
from activity a, activitystmt b
where a.APPL_ID=b.APPL_ID and a.ACTIVITY_ID=b.ACTIVITY_ID and a.UOW_ID=b.UOW_ID
AND a.APPL_ID like '%db2inst1.200403101703%'
order by ACT_EXEC_TIME desc"

APPL_ID                      ACTIVITY_ID  UOW_ID      ACT_EXEC_TIME        TIME_CREATED               STMT_TEXT
---------------------------- ------------ ----------- -------------------- -------------------------- --------------------
*LOCAL.db2inst1.200403101703            1           8           1797313075 2020-04-03-05.18.33.868968 with temp as ( selec
*LOCAL.db2inst1.200403101703            1           9                 1549 2020-04-03-05.48.52.396869 flush event monitor
*LOCAL.db2inst1.200403101703            1           7                   50 2020-04-03-05.18.33.860221 select current CLIEN

  3 record(s) selected.


####调用EXPLAIN_FROM_ACTIVITY
db2 "CALL EXPLAIN_FROM_ACTIVITY ( '*LOCAL.db2inst1.200403101703', 8, 1, 'ACTEVMON', 'DB2INST1',?, ?, ?, ?, ? )"

####生成执行计划
db2exfmt -1 -d SAMPLE -o explain.out

####关闭监控
db2 "CALL WLM_SET_CONN_ENV(10372, '<collectactdata>NONE</collectactdata>')"
db2 "set event monitor actEvmon state 0"