Units of work view shows the user which units of work are consuming the most resources. There are some important scenarios that can be difficult to detect unless you look at metrics on transactions.

Transaction left open

If an application starts a transaction, does some work, and then never commits or rolls back, it will pin the log file where it started. Backup will be unable to free the log file, and depending on how the database is configured a number of bad things can happen, such as filling all available disk space with new log files.

Transaction holding excessive locks

Applications that acquire many locks in a single transaction and are slow to release them impact other work in the database.

Following is the variable used as denominator in this section:

TOTAL_TIME = MON_GET_DATABASE(#MEMBER#).SUM(LOCK_WAIT_TIME + TOTAL_SECTION_SORT_TIME + POOL_READ_TIME + POOL_WRITE_TIME + POOL_ASYNC_READ_TIME + POOL_ASYNC_WRITE_TIME + PREFETCH_WAIT_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME)

The percentage of time spent waiting for locks.

= LOCK_WAIT_TIME / TOTAL_TIME

Source:

LOCK_WAIT_TIME = MON_GET_DATABASE(#MEMBER#).SUM(LOCK_WAIT_TIME)

The percentage of time spent performing sorts while executing a section, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application.

= TOTAL_SECTION_SORT_TIME / TOTAL_TIME

Source:

TOTAL_SECTION_SORT_TIME = MON_GET_DATABASE(#MEMBER#).SUM(TOTAL_SECTION_SORT_TIME)

The percentage of time spent reading and writing in data and index pages from the table space containers (physical) for all types of table spaces.

= BP_IO_TIME / TOTAL_TIME

Source:

BP_IO_TIME = MON_GET_DATABASE(#MEMBER#).SUM(POOL_READ_TIME + POOL_WRITE_TIME)

The percentage of time for asynchronous writes and reads to complete.

= BP_ASYNC_IO_TIME / TOTAL_TIME

Source:

BP_ASYNC_IO_TIME = MON_GET_DATABASE(#MEMBER#).SUM(POOL_ASYNC_READ_TIME + POOL_ASYNC_WRITE_TIME)

The percentage of time an application spent waiting for an I/O server (prefetcher) to finish loading pages into the buffer pool.

= PREFETCH_WAIT_TIME / TOTAL_TIME

Source:

PREFETCH_WAIT_TIME = MON_GET_DATABASE(#MEMBER#).SUM(PREFETCH_WAIT_TIME)

The percentage of time required to perform the direct reads and writes.

= DIRECT_IO_TIME / TOTAL_TIME

Source:

DIRECT_IO_TIME = MON_GET_DATABASE(#MEMBER#).SUM(DIRECT_READ_TIME + DIRECT_WRITE_TIME)

The units of work with max amount of log space used in the current unit of work.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(UOW_LOG_SPACE_USED)

The units of work with maximum amount of CPU time used while within the database system.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(TOTAL_CPU_TIME)

The units of work with maximum elapsed time from the time it first required database resources.

= CURRENT TIMESTAMP - UOW_START_TIME

Source:

CURRENT TIMESTAMP

MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).UOW_START_TIME

The units of work with maximum number of rows read from the table.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(ROWS_READ)

The units of work with maximum number of rows that have been selected and returned to the application.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(ROWS_RETURNED)

The units of work with maximum number of rows inserted, updated, or deleted.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(ROWS_MODIFIED)

The units of work with maximum number of pages which have been requested from the buffer pool (logical).

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS + POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS + POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS + POOL_COL_L_READS + POOL_TEMP_COL_L_READS)

The units of work with maximum number of pages which have been requested from the buffer pool (logical) or been written to disk.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS + POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS + POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS + POOL_COL_L_READS + POOL_TEMP_COL_L_READS + POOL_DATA_WRITES + POOL_INDEX_WRITES + POOL_XDA_WRITES + POOL_COL_WRITES)

The units of work with maximum amount of committed memory in use by this memory pool.

Source: MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, #MEMBER#).SUM(MEMORY_POOL_USED)

The units of work with maximum amount of data received via or distributed by the FCM communications layer

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(FCM_SEND_VOLUME + FCM_RECV_VOLUME)

The units of work with maximum number of locks currently held.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(NUM_LOCKS_HELD)

The units of work with maximum number of times that locks have been escalated from several row locks to a table lock.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(LOCK_ESCALS)

The units of work with maximum number of times that a request to lock an object timed out instead of being granted.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(LOCK_TIMEOUTS)

The units of work with maximum number of times that applications or connections waited for locks.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(LOCK_WAITS)

The units of work with maximum elapsed time spent waiting for locks.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(LOCK_WAIT_TIME)

The units of work with maximum time spent on global lock waits.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(LOCK_WAIT_TIME_GLOBAL)

The units of work with maximum number of lock waits due to the application holding the lock being on a remote member.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(LOCK_WAITS_GLOBAL)

The units of work with maximum amount of time spent communicating with the cluster caching facility.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(CF_WAIT_TIME)

The units of work with maximum number of times that the database system waited while it communicated with a cluster caching facility.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).SUM(CF_WAITS)

A system-wide unique ID for the application.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).APPLICATION_HANDLE

The percentage of application resource usage.

The corresponding resource value.

The name of the application running at the client, as known to the database or Db2 Connect server.

Source: MON_GET_CONNECTION.APPLICATION_NAME

The date and time that the unit of work first required database resources.

Source: MON_GET_UNIT_OF_WORK(NULL, #MEMBER#, 1).UOW_START_TIME

NULL