How to capture explain plan for sql/pl stored procedures?

http://www.db2ude.com/?q=node/85

Dynamic snapshot - capturing delta SQL's

Create table snap1 like sysibmadm.snapdyn_sql
and populate it


Run app

Create table snap2 like sysibmadm.snapdyn_sql and populate it.

To find what is only new/changed in the second snapshot, run:
Select
y.NUM_EXECUTIONS,
y.NUM_COMPILATIONS,
y.PREP_TIME_WORST,
y.PREP_TIME_BEST,
y.INT_ROWS_DELETED,
y.INT_ROWS_INSERTED,
y.INT_ROWS_UPDATED,
y.ROWS_READ,
y.ROWS_WRITTEN,
y.STMT_SORTS,
y.SORT_OVERFLOWS,
y.TOTAL_SORT_TIME,
y.POOL_DATA_L_READS,
y.POOL_DATA_P_READS,
y.POOL_TEMP_DATA_L_READS,
y.POOL_TEMP_DATA_P_READS,
y.POOL_INDEX_L_READS,
y.POOL_INDEX_P_READS,
y.POOL_TEMP_INDEX_L_READS,
y.POOL_TEMP_INDEX_P_READS,
y.POOL_XDA_L_READS,
y.POOL_XDA_P_READS,
y.POOL_TEMP_XDA_L_READS,
y.POOL_TEMP_XDA_P_READS,
y.TOTAL_EXEC_TIME,
y.TOTAL_EXEC_TIME_MS,
y.TOTAL_USR_CPU_TIME,
y.TOTAL_USR_CPU_TIME_MS,
y.TOTAL_SYS_CPU_TIME,
y.TOTAL_SYS_CPU_TIME_MS,
y.STMT_TXT1
From
root.snap1 as x
Right Outer Join
root.snap2 as y
ON
x.INT_ROWS_DELETED=y.INT_ROWS_DELETED
AND x.INT_ROWS_INSERTED=y.INT_ROWS_INSERTED
AND x.INT_ROWS_UPDATED=y.INT_ROWS_UPDATED
AND x.NUM_COMPILATIONS=y.NUM_COMPILATIONS
AND x.NUM_EXECUTIONS=y.NUM_EXECUTIONS
AND x.POOL_DATA_L_READS=y.POOL_DATA_L_READS
AND x.POOL_DATA_P_READS=y.POOL_DATA_P_READS
AND x.POOL_INDEX_L_READS=y.POOL_INDEX_L_READS
AND x.POOL_INDEX_P_READS=y.POOL_INDEX_P_READS
AND x.POOL_TEMP_DATA_L_READS=y.POOL_TEMP_DATA_L_READS
AND x.POOL_TEMP_DATA_P_READS=y.POOL_TEMP_DATA_P_READS
AND x.POOL_TEMP_INDEX_L_READS=y.POOL_TEMP_INDEX_L_READS
AND x.POOL_TEMP_INDEX_P_READS=y.POOL_TEMP_INDEX_P_READS
AND x.POOL_TEMP_XDA_L_READS=y.POOL_TEMP_XDA_L_READS
AND x.POOL_TEMP_XDA_P_READS=y.POOL_TEMP_XDA_P_READS
AND x.POOL_XDA_L_READS=y.POOL_XDA_L_READS
AND x.POOL_XDA_P_READS=y.POOL_XDA_P_READS
AND x.PREP_TIME_BEST=y.PREP_TIME_BEST
AND x.PREP_TIME_WORST=y.PREP_TIME_WORST
AND x.ROWS_READ=y.ROWS_READ
AND x.ROWS_WRITTEN=y.ROWS_WRITTEN
AND x.SORT_OVERFLOWS=y.SORT_OVERFLOWS
AND x.STMT_SORTS=y.STMT_SORTS
AND x.STMT_TXT1=y.STMT_TXT1
AND x.TOTAL_EXEC_TIME=y.TOTAL_EXEC_TIME
AND x.TOTAL_EXEC_TIME_MS=y.TOTAL_EXEC_TIME_MS
AND x.TOTAL_SORT_TIME=y.TOTAL_SORT_TIME
AND x.TOTAL_SYS_CPU_TIME=y.TOTAL_SYS_CPU_TIME
AND x.TOTAL_SYS_CPU_TIME_MS=y.TOTAL_SYS_CPU_TIME_MS
AND x.TOTAL_USR_CPU_TIME=y.TOTAL_USR_CPU_TIME
AND x.TOTAL_USR_CPU_TIME_MS=y.TOTAL_USR_CPU_TIME_MS
WHERE
x.stmt_txt1 is null