Executable list information

When collecting unit of work information, you have the option to also collect a list of the executable IDs of statements that run as part of each unit of work.

The information about the executable IDs is written to an unformatted event (UE) table or to a regular table. There are two ways to capture this information:

Use the EXECUTABLE LIST option for the COLLECT UNIT OF WORK DATA clause of the CREATE WORKLOAD or ALTER WORKLOAD statement to collect information for specific workloads. Information for the units of work that are executed under the workload that you identify in the statement, including executable IDs, is sent to the active unit of work (UOW) event monitors.
Use configuration parameters to have information about all units of work that are executed on the data server, including executable information, sent to active unit of work event monitors. To collect executable ID information, set the mon_uow_data configuration parameter to BASE, and set the mon_uow_execlist configuration parameter to ON.

The following data is collected for the executable listing:

Unit of Work Level

executable_list_size: The number of entries that are present within the executable ID listing for a particular unit of work.
executable_list_truncated: A YES or NO value indicating whether the list is truncated. The list can be truncated if there is insufficient memory available to store the entire executable list during processing.

Executable ID List

executable_id (executable_id - Executable ID monitor element): An opaque binary token generated on the data server that uniquely identifies the SQL statement section that was executed.
num_executions (num_executions - Statement executions monitor element): The number of times that an SQL statement has been executed.
rows_read (rows_read - Rows read monitor element): The number of rows read from the table.
total_cpu_time (total_cpu_time - Total CPU time monitor element): The total amount of CPU time used while in the DB2 product. Represents total of both user and system CPU time. This value is given in microseconds.
total_act_time (total_act_time - Total activity time monitor element): The total amount of time spent executing activities. This value is given in milliseconds.
total_act_wait_time (total_act_wait_time - Total activity wait time monitor element): Total time spent waiting within the DB2 database server, while processing an activity. The value is given in milliseconds.
lock_wait_time (lock_wait_time - Time waited on locks monitor element): The total elapsed time spent waiting for locks. The value is given in milliseconds.
lock_waits (lock_waits - Lock waits monitor element): The total number of times that applications or connections waited for locks.
total_sorts (total_sorts - Total sorts monitor element): The total number of sorts that have been executed.
post_threshold_sorts (post_threshold_sorts - Post threshold sorts monitor element): The number of sorts that have requested heaps after the sort heap threshold has been exceeded.
post_shrthreshold_sorts (post_shrthreshold_sorts - Post shared threshold sorts monitor element): The total number of sorts that were throttled back by the sort memory throttling algorithm. A throttled sort is a sort that was granted less memory than requested by the sort memory manager
sort_overflows (sort_overflows - Sort overflows monitor element): The total number of sorts that ran out of sort heap and may have required disk space for temporary storage.

How execution lists are written to UE tables

At least two separate records can be written to the UE table when base data and executable ID list data is collected for an UOW event monitor. The first record contains information about the UOW event that contains the base UOW data. The second record is the UOW_EXEC_LIST event that contains the executable ID listing data. This second record might consist of multiple records because of the potentially large number of unique executable IDs for a single UOW. These records are written as separate rows to the UE table to ensure that each event is contained within the inlined LOB space available. You can use the interfaces for formatting the UE table to merge information for these events. When the executable ID list is not collected, an associated record is not created; the table does not contain any rows.

How execution lists are written to regular tables

When you use regular tables for event monitor output, executable list information is captured as part of the uow_executable_list logical data group. As each unit of work is completed, one or more rows are added to the table UOW_EXECUTABLE_LIST_evmon_name, with one column for each monitor element in the logical data group. The number of rows that are added to the table depends on how many unique executable IDs ran as part of the unit of work.

Executable listing output

When the event monitor writes to a UE table, the unit of work event monitor writes two records to the UE table when collecting execution information. Each of the interfaces for displaying the data in a UE table provides a mechanism for viewing the information contained in the two UE table records. The db2evmonfmt tool combines the information in each record into a single report. The EVMON_FORMAT_UE_TO_TABLES procedure produces relational tables that you can join; the table UOW_EXECUTABLE_LIST contains the executable list information. The EVMON_FORMAT_UE_TO_XML table function produces a single XML document that contains the information from both records. For more information, see Accessing event data that is captured by a unit of work event monitor.

When the event monitor writes directly to relational tables, the executable list information is written to the table UOW_EXECUTABLE_LIST_evmon_name.

In a partitioned database environment, the executable ID list is generated per member, including per coordinator agent member and data member. In a DB2 pureScale® environment, the list is generated from the coordinator member, which is similar to the situation in non-partitioned configurations.

Examples

The following sample information was collected for an application that executes five different SQL statement sections within a UOW. This output provides a logical view with sample columns; the actual output depends on the tool or query that you run.

EXECUTABLE_ID                          NUM_EXECUTIONS ROWS_READ TOTAL_CPU_TIME
------------------------------------   -------------- --------- --------------
x'01007A00000020020081126171554951791'              1     23456          76888
x'01007900000020020081126171533551120'             55       345            768
x'01007C00000020020081126171720728997'            234        67            232
x'01007B00000020020081126171657272914'           3456       347           1223
x'01007D00000020020081126172409987719'          22242      2244         432444

In this example, there are five entries in the executable ID list to correspond to the five different sections that were executed. The five sections were executed a different number of times, as illustrated by the NUM_EXECUTIONS column, but only one entry is provided for each unique section. The first row might indicate a problematic activity statement because it consumed excessive CPU time in just one execution.