MON_GET_TRANSACTION_LOG table function - Get log information

The MON_GET_TRANSACTION_LOG table function returns information about the transaction logging subsystem for the currently connected database.

Syntax

Read syntax diagramSkip visual syntax diagramMON_GET_TRANSACTION_LOG(member )

The schema is SYSPROC.

Table function parameters

member
An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all active database members. If the null value is specified, -1 is set implicitly.

Authorization

One of the following authorities is required to execute the routine:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

None

Example

Select MEMBER, CUR_COMMIT_DISK_LOG_READS, CURRENT_ACTIVE_LOG, 
APPLID_HOLDING_OLDEST_XACT from table(mon_get_transaction_log(-1)) as t
order by member asc

MEMBER  CUR_COMMIT_DISK_LOG_READS  CURRENT_ACTIVE_LOG  APPLID_HOLDING_OLDEST_XACT
------  -------------------------  ------------------  --------------------------
     0                       9999                   1                           7

Information returned

Table 1. Information returned for MON_GET_TRANSACTION_LOG. There will be one row per member for both Db2® pureScale® and partitioned database environments.
Column Name Data Type Description
MEMBER SMALLINT member - Database member monitor element
TOTAL_LOG_AVAILABLE BIGINT total_log_available - Total Log Available monitor element
TOTAL_LOG_USED BIGINT total_log_used - Total Log Space Used monitor element
SEC_LOG_USED_TOP BIGINT sec_log_used_top - Maximum Secondary Log Space Used monitor element
TOT_LOG_USED_TOP BIGINT tot_log_used_top - Maximum Total Log Space Used monitor element
SEC_LOGS_ALLOCATED BIGINT sec_logs_allocated - Secondary Logs Allocated Currently monitor element
LOG_READS BIGINT log_reads - Number of Log Pages Read monitor element
LOG_READ_TIME BIGINT log_read_time - Log Read Time monitor element
LOG_WRITES BIGINT log_writes - Number of Log Pages Written monitor element
LOG_WRITE_TIME BIGINT log_write_time - Log Write Time monitor element
NUM_LOG_WRITE_IO BIGINT num_log_write_io - Number of Log Writes monitor element
NUM_LOG_READ_IO BIGINT num_log_read_io - Number of Log Reads monitor element
NUM_LOG_PART_PAGE_IO BIGINT num_log_part_page_io - Number of Partial Log Page Writes monitor element
NUM_LOG_BUFFER_FULL BIGINT num_log_buffer_full - Number of times full log buffer caused agents to wait monitor element
NUM_LOG_DATA_FOUND_IN_BUFFER BIGINT num_log_data_found_in_buffer - Number of Log Data Found In Buffer monitor element
APPLID_HOLDING_OLDEST_XACT BIGINT Application handle holding the oldest transaction
LOG_TO_REDO_FOR_RECOVERY BIGINT log_to_redo_for_recovery - Amount of Log to be Redone for Recovery monitor element
LOG_HELD_BY_DIRTY_PAGES BIGINT log_held_by_dirty_pages - Amount of Log Space Accounted for by Dirty Pages monitor element
FIRST_ACTIVE_LOG BIGINT first_active_log - First Active Log File Number monitor element
LAST_ACTIVE_LOG BIGINT last_active_log - Last Active Log File Number monitor element
CURRENT_ACTIVE_LOG BIGINT current_active_log - Current Active Log File Number monitor element
CURRENT_ARCHIVE_LOG BIGINT current_archive_log - Current Archive Log File Number monitor element
CUR_COMMIT_DISK_LOG_READS BIGINT The number of times the currently committed version of a row was retrieved via a log read from disk (versus log buffer)
CUR_COMMIT_TOTAL_LOG_READS BIGINT The total number of times the currently committed version of a row was retrieved from the logs (log buffer and disk)
CUR_COMMIT_LOG_BUFF_LOG_READS BIGINT This is calculated as CUR_COMMIT_TOTAL_LOG_READS - CUR_COMMIT_DISK_LOG_READS
ARCHIVE_METHOD1_STATUS SMALLINT The result of the most recent log archive attempt. Possible values are 1 for Success or 0 for Failure or Null if not applicable.
METHOD1_NEXT_LOG_TO_ARCHIVE BIGINT The next log file to be archived.
METHOD1_FIRST_FAILURE BIGINT The first log file that was unsuccessfully archived.
ARCHIVE_METHOD2_STATUS SMALLINT The result of the most recent secondary log archive attempt. Possible values are 1 for Success or 0 for Failure or Null if not applicable.
METHOD2_NEXT_LOG_TO_ARCHIVE BIGINT The next secondary log file to be archived.
METHOD2_FIRST_FAILURE BIGINT The first secondary log file that was unsuccessfully archived.
LOG_CHAIN_ID BIGINT The identifier of the log chain number
CURRENT_LSO BIGINT The current log sequence offset
CURRENT_LSN BIGINT The current log sequence number
OLDEST_TX_LSN BIGINT The log sequence number associated with the oldest transaction running.
NUM_LOGS_AVAIL_FOR_RENAME INTEGER If using log archiving, this is the number of log files maintained in the log path for the purpose of reusing them. Otherwise its value is 0.
NUM_INDOUBT_TRANS BIGINT num_indoubt_trans - Number of Indoubt Transactions monitor element
LOG_HADR_WAIT_TIME BIGINT Time spent waiting on HADR processing.
LOG_HADR_WAITS_TOTAL BIGINT Total number of times the transaction log sub-system waited for HADR processing.
LOG_EXTRACTION_PROCESSED_BYTES BIGINT The number of bytes analyzed for extraction
LOG_EXTRACTION_PROCESSING_TIME BIGINT The amount of time taken to extract log records
LOG_EXTRACTION_WRITTEN_BYTES BIGINT The number of bytes written to extract log files
LOG_EXTRACTION_WRITE_TIME BIGINT The amount of time spent writing to extraction log files
LOG_EXTRACTION_ROLLBACK_READS BIGINT The number of rollback lookups in the extraction log file.
LOG_EXTRACTION_ROLLBACK_TIME BIGINT The time spent for rollback lookups in the extraction log files.
LOG_EXTRACTION_CUR_COMMIT_READS BIGINT The number of lookups in the extraction files for currently committed operations.
LOG_EXTRACTION_CUR_COMMIT_TIME BIGINT The amount of time spent on currently committed lookups in the extraction log files.
LOG_EXTRACTION_DISK_SPACE_USED_TOTAL BIGINT The amount of disk space, in bytes, used for the extraction logs.
LOG_EXTRACTION_DISK_SPACE_USED_TOTAL_TOP BIGINT The high watermark value of LOG_EXTRACTION_DISK_SPACE_USED_TOTAL, since database member activation.
LOG_EXTRACTION_LAST_EXTRACTED_LOG BIGINT The log extent number of the last log file to be successfully extracted.
LOG_EXTRACTION_PROCESSED_LSO BIGINT The log sequence offset (LSO) of the last processed log record for extraction.
LOG_EXTRACTION_PROCESSED_LSN BIGINT The log sequence number (LSN) of the last processed log record for extraction.
LOG_EXTRACTION_NUM_DISK_FULL BIGINT The number of times log extraction stopped, because there was not enough disk space in the active log path.
LOG_EXTRACTION_STATUS SMALLINT The current status of extraction. See log_extraction_status - Current status of extraction monitor element.
LOG_EXTRACTION_THROTTLE_REASON VARCHAR(32) The reason why extraction is throttled, if applicable. See log_extraction_throttle_reason - Reason for extraction throttling monitor element.
LAST_LOG_VALIDATION_ERROR BIGINT The last log that failed validation on archive. If validation is not enabled or if there is no previous validation error, Null is returned.