backup_timestamp - Backup timestamp monitor element

Timestamp of the backup image.

Table 1. Event Monitoring Information
Event Type Logical Data Grouping Monitor Element Collection Level
Change History changesummary Always collected

Usage

For the change history event monitor:
  • If UTILITY_TYPE is BACKUP and EVENT_TYPE is UTILSTART, the BACKUP_TIMESTAMP value is the timestamp of the backup image. If UTILITY_TYPE is RESTORE and EVENT_TYPE is UTILSTOP, the BACKUP_TIMESTAMP value is the timestamp of the backup image. For all other cases, the BACKUP_TIMESTAMP is an empty string.
  • For RESTORE, the image timestamp is not always known at the start time of the utility.

A BACKUP_TIMESTAMP can be correlated with information stored in the database history file (for example, Lookup sequence information) using the SYSIBMADM.DB_HISTORY administration view

binds_precompiles - Binds/Precompiles Attempted monitor element

The number of binds and pre-compiles attempted.

Table 3. Table function monitoring information
Table function Monitor element collection level
MON_GET_CONNECTION table function - Get connection metrics REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_DATABASE table function - Get database level information REQUEST METRICS BASE
MON_GET_DATABASE_DETAILS table function - Get database information metrics REQUEST METRICS BASE
MON_GET_ROUTINE table function - get aggregated execution metrics for routines REQUEST METRICS BASE
MON_GET_ROUTINE_DETAILS table function - get aggregated execution metric details for routines REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS table function - Get service subclass metrics REQUEST METRICS BASE
MON_GET_SERVICE_SUPERCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document) REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics REQUEST METRICS BASE
Table 4. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase Basic
Application appl Basic
For snapshot monitoring, this counter can be reset.
Table 5. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Connection event_conn Always collected
Database event_db Always collected
Statistics event_scmetrics REQUEST METRICS BASE
Statistics event_superclassmetrics REQUEST METRICS BASE
Statistics event_wlmetrics REQUEST METRICS BASE
Unit of work uow_metrics REQUEST METRICS BASE
Usage
You can use this element to gain insight into the current level of activity within the database manager.

This value does not include the count of int_auto_rebinds, but it does include binds that occur as a result of the REBIND PACKAGE command.

block_ios - Number of block I/O requests monitor element

The number of block I/O requests. More specifically, the number of times the database performs sequential prefetching of pages into the block area of the buffer pool.

Table 7. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Buffer Pool bufferpool Buffer Pool

Usage

If block-based buffer pool is enabled, this monitor element will report how often block I/O is being done. Otherwise, this monitor element will return 0. The number of block I/O requests is monitored only during sequential prefetching when using block-based buffer pools.

If block-based buffer pool is enabled and this number is very low, or close to the number of vectored I/Os (the value of the vectored_ios monitor element), consider changing the block size. This state can be an indication of the following occurrences:
  • The extent size of one or more table spaces bound to the buffer pool is smaller than the block size specified for the buffer pool.
  • Some pages requested in the prefetch request are already present in the page area of the buffer pool.

The prefetcher allows some wasted pages in each buffer pool block, but if too many pages are wasted, then the prefetcher will decide to perform vectored I/O into the page area of the buffer pool.

To take full advantage of the sequential prefetch performance improvements that block-based buffer pools provide, it is essential to choose an appropriate value for the block size. This can, however, be difficult because multiple table spaces with different extent sizes can be bound to the same block-based buffer pool. For optimal performance, it is recommended that you bind table spaces with the same extent size to a block-based buffer pool with a block size equal to the extent size. Good performance can be achieved when the extent size of the table spaces are greater than the block size, but not when the extent size is smaller than the block size.

For example, if the extent size is 2 and the block size is 8, vectored I/O would be used instead of block I/O (block I/O would have wasted 6 pages). A reduction of the block size to 2 would solve this problem.

blocking_cursor - Blocking Cursor monitor element

This element indicates if the statement being executed is using a blocking cursor.

Element identifier
blocking_cursor
Element type
information
Table 8. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Application stmt Statement
DCS Statement dcs_stmt Statement
Table 9. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Deadlocks with Details event_detailed_dlconn Always collected
Statements event_stmt Always collected
Usage
Using blocking for data transfer for a query can improve its performance. The SQL used for a query can affect the use of blocking and might require some modification.

blocks_pending_cleanup - Pending cleanup rolled-out blocks monitor element

The total number of MDC table blocks in the database that are pending asynchronous cleanup following a roll out delete.

Table 10. Table Function Monitoring Information
Table Function Monitor Element Collection Level
ADMINTABINFO administrative view and ADMIN_GET_TAB_INFO table function - retrieve table size and state information Always collected
Table 11. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database dbase -
Database event_db -

Usage

Use this element to determine the number of MDC table blocks that, following the deletion of a defer cleanup roll out, have not been released back to the system as available storage.

bottom - Histogram bin bottom monitor element

The exclusive bottom end of the range of a histogram bin. The value of this monitor element is also the top inclusive end of the range of the previous histogram bin, if there is one.

Table 12. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Statistics event_histogrambin -

Usage

Use this element with the corresponding top element to determine the range of a bin within a histogram.

boundary_leaf_node_splits - Boundary leaf node splits monitor element

A boundary leaf node split is when a leaf node split is triggered by the insertion of a new highest or new lowest key into an index. The boundary_leaf_node_splits monitor element returns the number of times a boundary leaf node was split during an insert operation.

Table 13. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_INDEX table function - Get index metrics Always collected

bp_cur_buffsz - Current Size of Buffer Pool monitor element

The current buffer pool size, in pages.

Table 14. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics Always collected
Table 15. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Buffer Pool bufferpool_nodeinfo Buffer Pool

bp_id - Buffer pool identifier monitor element

This element contains the buffer pool identifier for the buffer pool that is being monitored.

Table 16. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Buffer Pool bufferpool Basic

bp_name - Buffer pool name monitor element

The name of the buffer pool.

Table 17. Table Function Monitoring Information
Table Function Monitor Element Collection Command and Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics Always collected
Table 18. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Buffer Pool bufferpool Basic
Usage
Each database requires at least one buffer pool. Depending on your needs, you may choose to create several buffer pools, each of a different size, for a single database. The CREATE, ALTER, and DROP BUFFERPOOL statements allow you to create, change, or remove a buffer pool.
When a database is created, it has a default buffer pool called IBMDEFAULTBP with a size determined by the platform. It also has a set of system buffer pools, each corresponding to a different page size:
  • IBMSYSTEMBP4K
  • IBMSYSTEMBP8K
  • IBMSYSTEMBP16K
  • IBMSYSTEMBP32K
These system buffer pools cannot be altered.

bp_new_buffsz - New Buffer Pool Size monitor element

The size the buffer pool will be changed to once the database is restarted. When the ALTER BUFFERPOOL statement is executed as DEFERRED, the buffer pool size is not changed until the database is stopped and restarted.

Table 19. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Buffer Pool bufferpool_nodeinfo Buffer Pool

bp_pages_left_to_remove - Number of Pages Left to Remove monitor element

The number of pages left to remove from the buffer pool before the buffer pool resize is completed. This applies only to buffer pool resize operations invoked by ALTER BUFFERPOOL statements executed as IMMEDIATE.

Table 20. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics Always collected
Table 21. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Buffer Pool bufferpool_nodeinfo Buffer Pool

bp_tbsp_use_count - Number of Table Spaces Mapped to Buffer Pool monitor element

The number of table spaces using this buffer pool.

Table 22. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_BUFFERPOOL table function - Get buffer pool metrics Always collected
Table 23. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Buffer Pool bufferpool_nodeinfo Buffer Pool

buff_auto_tuning - FCM buffer auto-tuning indicator monitor element

Indicates whether the number of fast communication manager (FCM) buffers is set and tuned automatically. A value of 1 means "Yes"; a value of 0 means "No".

Table 24. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM - Get FCM metrics Always collected

Usage

FCM buffer auto-tuning is enabled by setting the fcm_num_buffers configuration parameter to AUTOMATIC.

buff_free - FCM Buffers Currently Free monitor element

This element indicates the number of FCM buffers currently free.

Element identifier
buff_free
Element type
gauge
Table 25. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM - Get FCM metrics Always collected
Table 26. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager fcm Basic

Usage

To calculate the percentage of free FCM buffers, use the following formula:
(buff_free/buff_total) * 100

If the percentage of free FCM buffers falls below 20% and if the FCM buffer auto-tuning is enabled, then the database manager will adjust the FCM buffer numbers.

If the percentage of free FCM buffers falls below 20% and if the FCM buffer auto-tuning is not enabled, then you need to tune the fcm_num_buffers configuration parameter.

buff_free_bottom - Minimum FCM Buffers Free monitor element

The lowest number of free FCM buffers reached during processing.

Table 27. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM - Get FCM metrics Always collected
Table 28. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager fcm Basic

Usage

Use this element along with the fcm_num_buffers configuration parameter to determine the maximum FCM buffer pool utilization. If the value of the buff_free_bottom monitor element is low, increase the value of the fcm_num_buffers configuration parameter to ensure that operations do not run out of FCM buffers. If the value of the buff_free_bottom monitor element is high, decrease the value of the fcm_num_buffers configuration parameter to conserve system resources.

buff_max - Maximum possible number of FCM buffers monitor element

Maximum number of fast communication manager (FCM) buffers that can be allocated, based on the amount of virtual memory reserved when the instance was started.

Table 29. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM - Get FCM metrics Always collected
Table 30. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager fcm Basic

Usage

This internal monitor element is used by IBM Support only.

buff_total - Number of currently allocated FCM buffers monitor element

Number of fast communication manager (FCM) buffers currently allocated. This number includes both in-use buffers and free buffers.

Table 31. Table Function Monitoring Information
Table Function Monitor Element Collection Level
MON_GET_FCM - Get FCM metrics Always collected
Table 32. Snapshot Monitoring Information
Snapshot Level Logical Data Grouping Monitor Switch
Database Manager fcm Basic

Usage

If the buff_auto_tuning monitor element indicates that FCM is being tuned automatically, then the value of the buff_total monitor element is adjusted based on the demand for FCM buffers.

To determine the number of FCM buffers currently in use, use the following formula:
buff_total - buff_free
To calculate the percentage of free FCM buffers, use the following formula:
(buff_free/buff_total) * 100

If the percentage of free FCM buffers falls below 20% and if the FCM buffer auto-tuning is enabled, then the database manager will adjust the FCM buffer numbers.

If the percentage of free FCM buffers falls below 20% and if the FCM buffer auto-tuning is not enabled, then you need to tune the fcm_num_buffers configuration parameter.

byte_order - Byte Order of Event Data monitor element

The byte ordering of numeric data, specifically whether the event data stream was generated on a big endian server (for example, a RS/6000®) or little endian server (for example, an Intel-based PC running Windows 2000).

Table 33. Event Monitoring Information
Event Type Logical Data Grouping Monitor Switch
Event Log Header event_log_header Always collected
Usage
This information is needed to allow you to interpret numeric data in the data stream, since the byte order of integers on a big endian server is the reverse of the byte order on a little endian server.

If the application that processes the data recognizes that it is running on one type of computer hardware (for example, a big endian computer), while the event data was produced on the other type of computer hardware (for example, a little endian computer), then the monitoring application will have to reverse the bytes of numeric data fields before interpreting them. Otherwise, byte reordering is not required.

This element can be set to one of the following API constants:
  • SQLM_BIG_ENDIAN
  • SQLM_LITTLE_ENDIAN

bytes_total - Total number of bytes monitor element

Total number of bytes completed by the utility.

Table 34. Event monitoring information
Event type Logical data grouping Monitor switch
Change History UTILSTOP Always collected

Usage

Bytes_total is used only by the BACKUP change history event monitor. This element contains the actual size of the backup image, which is determined at the end of a backup process. In other words, the total number of bytes written by all the backup media sessions combined. This number varies according to the various backup options used; the counter is smaller for a compressed backup image, assuming that compression has already taken place.