Usage

This element can be used with the sc_work_class_id element to uniquely identify the service class work class of the activity, if one exists.

Usage

This element can be used with the sc_work_action_set_id element to uniquely identify the service class work class of the activity, if one exists.

Usage

Usage

Use this element with the section explain procedures to explain the statement and view the access plan for the statement.

Usage

This element and the num_columns_referenced element can be used to determine the average number of columns being accessed from a table during execution of the runtime section for an SQL statement. This average column access count can help identify row-organized tables that might be candidates for conversion to column-organized tables (for example, wide tables where only a few columns are typically accessed).

The element can also be used to help understand the efficiency of access to column-organized tables (for example, the number of columns typically read when scanning the table).

Usage

For static SQL statements, you can use this element along with creator, package_version_id, and package_name monitor elements to query the SYSCAT.STATEMENTS system catalog table and obtain the static SQL statement text, using the sample query as follows:

 
    SELECT SEQNO, SUBSTR(TEXT,1,120)
           FROM SYSCAT.STATEMENTS
           WHERE PKGNAME   = 'package_name' AND
                 PKGSCHEMA = 'creator'      AND
                 VERSION = 'package_version_id' AND
                 SECTNO    = section_number
           ORDER BY SEQNO

Usage

Usage

Use this element to determine how much actual time is spent waiting for data from this data source. This can be useful in capacity planning and tuning the CPU speed and communication rates in SYSCAT.SERVERS. Modifying these parameters can impact whether the optimizer does or does not send requests to the data source.

The response time is measured as the difference in time between the time the federated server requests a row from the data source, and the time the row is available for the federated server to use.

Usage

Use this element with the query statement ID monitor element (stmtid) to identify an SQL statement. The semantic compilation environment ID is used to distinguish queries that have the same statement text, but are semantically different because they reference different objects. For example, the table that is referenced in the statement SELECT * FROM T1 depends on the value of the default schema in the compilation environment. If two users with different default schemas issued this statement, there would be two entries for the statement in the package cache. The two entries would have the same stmtid value, but would have different values for semantic_env_id.

Usage

Usage

The value of this element matches a value from column SERVICECLASSID of view SYSCAT.SERVICECLASSES. Use this element to look up the service subclass name, or link information about a service subclass from different sources. For example, join service class statistics with histogram bin records.

Usage

This monitor element is an alias for the sc_work_action_set_id monitor element.

Usage

This monitor element is an alias for the sc_work_class_id monitor element.

Usage

Use this element in conjunction with other activity elements for analysis of the behavior of an activity or with other statistics elements for analysis of a service class or threshold queue.

Usage

Use this element in conjunction with other activity elements for analysis of the behavior of an activity or with other statistics elements for analysis of a service class or threshold queue.

Usage

You can use this element to determine what authorization ID is being used to prepare SQL statements, execute SQL statements, or both. This monitor element does not report any session authorization ID values set within executing stored procedures.

Usage

Usage

• The page is a new page, and is not yet created on disk.
• Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
• An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

Usage

• The page is a new page, and is not yet created on disk.
• Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
• An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

Usage

Usage

• The page is a new page, and is not yet created on disk.
• Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
• An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

Usage

• The page is a new page, and is not yet created on disk.
• Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
• An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

Usage

• The page is a new page, and is not yet created on disk.
• Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
• An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

Usage

This monitor element, along with the other skipped_prefetch_uow_*_p_reads elements tells you the number of pages that were in a prefetch request that were read directly by an agent in the same unit of work that caused the prefetch request to be created. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the num_ioservers configuration parameter to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the num_ioservers configuration parameter. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

Usage

Usage

Usage

This monitor element, along with the other skipped_prefetch_uow_*_p_reads elements tells you the number of pages that were in a prefetch request that were read directly by an agent in the same unit of work that caused the prefetch request to be created. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the num_ioservers configuration parameter to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the num_ioservers configuration parameter. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

Usage

Usage

Usage

• The page is a new page, and is not yet created on disk.
• Another agent might need the same page, and thus it was loaded it into the buffer pool by a different prefetch request. In this case, along with the preceding one, an increase in skipped prefetch requests might not be a problem, because the additional prefetch request that was generated was redundant.
• An agent retrieved them from disk directly before the prefetcher was able to complete the prefetch operation. Agents might be forced to read a page directly from disk if a system has an insufficient number of prefetchers configured, or if there is another type of prefetching bottleneck. . For example, in an OLTP system, where most of the workload is generally transactional in nature, it might be the case that the minimum number of prefetchers is configured by setting the configuration parameter num_ioservers to 1. However, if an operation that uses prefetching, such as a table scan is performed, the single prefetcher might not be able to keep up, and so the agent requests the pages directly. This behavior can cause a degradation in performance, because the application waits on IO that otherwise would have been performed by prefetchers. In this case, consider increasing the number of prefetchers by adjusting the configuration parameter num_ioservers. Other potential causes include having an excessively large prefetch size, which can cause prefetch times that are longer than normal, or the db2_parallel_io registry variable not being set, which can restrict parallel prefetching within a table space container.

Usage

Use this element with other sort memory high watermark monitor elements to determine what activities are the heaviest users of sort heap memory. For example, issue the MON_GET_ACTIVITY table function to get a list of current activities. You can determine which activities use the most sort memory by noting the values of the sort_consumer_heap_top, sort_consumer_shrheap_top, sort_heap_top, and sort_shrheap_top monitor elements. If the heaviest memory users are negatively affecting other activities, reduce the memory requirements of the heaviest users to help improve concurrency.

Usage

Use this element with other sort memory high watermark monitor elements to determine what activities are the heaviest users of sort heap memory. For example, issue the MON_GET_ACTIVITY table function to get a list of current activities. You can determine which activities use the most sort memory by noting the values of the sort_consumer_heap_top, sort_consumer_shrheap_top, sort_heap_top, and sort_shrheap_top monitor elements. If the heaviest memory users are negatively affecting other activities, reduce the memory requirements of the heaviest users to help improve concurrency.

Usage

At a database or application level, use this element in conjunction with total_sorts to calculate the percentage of sorts that had to overflow to disk. If this percentage is high, you may want adjust the database configuration by increasing the value of sortheap.

At a statement level, use this element to identify statements that require large sorts. These statements may benefit from additional tuning to reduce the amount of sorting required.

When a sort overflows, additional processing time is required because the sort will require a merge phase and can potentially require more I/O, if data needs to be written to disk.

This element provides information for one statement, one application, or all applications accessing one database.

A value of -1 indicates that the data collection switch DFT_MON_SORT is turned off.

Usage

Use this element to trace the path of an activity through the service classes to which it was remapped. It can also be used to compute aggregates of how many activities were mapped out of a given service subclass.

Usage

This value is only reported for object-relative table spaces, that is table spaces that have been enabled for reclaimable storage. Use the reclaimable_space_enabled monitor element to determine if the table space has been enabled for reclaimable storage.

Since Extent Map Pages (EMPs) are metadata, EMPs are included in the value of this monitor element.

Data space map pages contain user data, therefore they are included in the value of the page_reclaims_x monitor element, in addition to being included the value of the spacemappage_page_reclaims_x monitor element. Index space map pages do not contain user data, therefore they are included only in the value of the spacemappage_page_reclaims_x monitor element.

Usage

This value is only reported for object-relative table spaces, that is table spaces that have been enabled for reclaimable storage. Use the reclaimable_space_enabled monitor element to determine if the table space has been enabled for reclaimable storage.

Since Extent Map Pages (EMPs) are metadata, EMPs are included in the value of this monitor element.

Data space map pages contain user data, therefore they are included in the value of the page_reclaims_s monitor element, in addition to being included the value of the spacemappage_page_reclaims_s monitor element. Index space map pages do not contain user data, therefore they are included only in the value of the spacemappage_page_reclaims_s monitor element.

Usage

This value is only reported for object-relative table spaces, that is table spaces that have been enabled for reclaimable storage. Use the reclaimable_space_enabled monitor element to determine if the table space has been enabled for reclaimable storage.

Since Extent Map Pages (EMPs) are metadata, EMPs are included in the value of this monitor element.

Data space map pages contain user data, therefore they are included in the value of the page_reclaims_initiated_x monitor element, in addition to being included the value of the spacemappage_page_reclaims_initiated_x monitor element. Index space map pages do not contain user data, therefore they are included only in the value of the spacemappage_page_reclaims_initiated_x monitor element.

Usage

This value is only reported for object-relative table spaces, that is table spaces that have been enabled for reclaimable storage. Use the reclaimable_space_enabled monitor element to determine if the table space has been enabled for reclaimable storage.

Since Extent Map Pages (EMPs) are metadata, EMPs are included in the value of this monitor element.

Data space map pages contain user data, therefore they are included in the value of the page_reclaims_initiated_s monitor element, in addition to being included the value of the spacemappage_page_reclaims_initiated_s monitor element. Index space map pages do not contain user data, therefore they are included only in the value of the spacemappage_page_reclaims_initiated_s monitor element.

Usage

The SQLCA data structure can be used to determined if the statement completed successfully. For information about the content of the SQLCA, see SQLCA (SQL communications area) or SQLCA data structure.

Usage

Use this element to understand which SQLROWSREAD threshold, if any, was applied to the activity.

Usage

Use this element to understand the value of the SQLROWSREAD threshold applied to the activity, if any.

Usage

Use this element to determine if the activity violated the SQLROWSREAD threshold that was applied to the activity.

Usage

Use this element to understand which SQLROWSREADINSC threshold, if any, was applied to the activity.

Usage

Use this element to understand the value of the SQLROWSREADINSC threshold applied to the activity, if any.

Usage

Use this element to determine if the activity violated the SQLROWSREADINSC threshold that was applied to the activity.

Usage

Use this element to understand which SQLROWSRETURNED threshold, if any, was applied to the activity.

Usage

Use this element to understand the value of the SQLROWSRETURNED threshold applied to the activity, if any.

Usage

Use this element to determine if the activity violated the SQLROWSRETURNED threshold that was applied to the activity.

Usage

Use this element to understand which SQLTEMPSPACE threshold, if any, was applied to the activity.

Usage

Use this element to understand the value of the SQLTEMPSPACE threshold applied to the activity, if any.

Usage

Use this element to determine if the activity violated the SQLTEMPSPACE threshold that was applied to the activity.

Usage

This number relates to the subsection number in the access plan that can be obtained with db2expln command.

Usage

Usage

Usage

This ID is used to differentiate the standbys. This ID is system generated. The mapping from ID to standby might change from query to query. However, the ID "1" is always assigned to the principal standby (or the only standby in single standby systems). Other standbys are not visible when the query is issued on a standby database; in such cases, 0 is always returned.

Usage

The receive and replay positions are reported separately for more detailed standby status. Spooling allows receive and replay positions to differ greatly. standby_log_pos shows receive position. When compared with primary_log_pos, the standby_log_pos indicates risk of data loss in case of failover. standby_replay_log_pos affects how long a takeover (forced and not forced) would take, since the takeover has to complete the replay of all received logs. The standby_replay_log_pos also indicates how up-to-date data read on standby will be. In Version 9.7 and earlier, the reported standby log position is the replay position.

Usage

The gap is measured in number of bytes. It generally will not exceed sum of standby_recv_buf_size and standby_spool_limit. A small amount over the sum is possible due to flexibility in buffer and spool management. When the gap reaches the combined buffer and spool limit, standby will stop receiving logs which will block primary in peer state. Standby may also run out of buffer and spool space when reported receive-replay gap is smaller than sum of buffer and spool, because a partial page can be sent multiple times and occupy multiple pages of space in buffer (always one page in spool though). However, the log gap calculation does not take multiple sends into account.

Usage

Usage

If the spool limit is 0 (spooling disabled) , NULL is returned. If the spool limit is -1 (unlimited spooling), a percentage of spool's page number in active log path size is returned. When the spool percentage reaches 100%, the standby database will stop receiving logs until space is released as replay proceeds. Spooling can stop before the limit is reached if the spool device (standby log path) is full.

Usage

For the change history event monitor, unique identifier of the corresponding starting of a utility event (UTILSTART or UTILSTARTPROC). Use this element with the START_EVENT_TIMESTAMP and member elements to associate the stop record with the corresponding start record.

Usage

Usage

Use this element to determine when this statistics record was generated.

Use this element along with the last_wlm_reset element to identify the time interval over which the statistics in this statistics record were generated.

This monitor element can also be used to group together all statistics records that were generated for the same collection interval.

Usage

Use this element to determine the size of the current statistics cache. This value changes frequently. In order to evaluate system usage, take the snapshot at specific intervals over an extended period of time. Use this element to adjust the value of the catalogcache_sz configuration parameter.

Usage

Use this element along with stats_fabrications to evaluate the performance impact of real-time statistics gathering at the database level. For snapshot monitor for dynamic SQL, you can use this element along with total_exec_time and num_executions to evaluate the impact of statistics fabrications. For the statement event monitor, you can combine this element with stmt_start and stmt_stop for further evaluation of real-time statistics gathering impact.

Usage

Use this element to determine the frequency of statistics fabrications in the database. This value changes frequently. In order to get a better overview of the system usage, take the snapshot at specific intervals over an extended period of time. When used in conjunction with stats_fabricate_time, this element can help you evaluate the impact of statistics fabrications.

Usage

Use this element and the rts_rows_modified monitor element to help determine if a running of the RUNSTATS command is required.

Usage

Use this element as an indicator of the time it takes for a statement to complete.

This element is composed of two subelements that report time spent as seconds and microseconds (one millionth of a second). The names of the subelements can be derived by adding "_s" and "_ms" to the name of this monitor element. To retrieve the total time spent for this monitor element, the values of the two subelements must be added together. For example, if the "_s" subelement value is 3 and the "_ms" subelement value is 20, then the total time spent for the monitor element is 3.00002 seconds.

Usage

Use this element in conjunction with other statement history entries to see the sequence of SQL statements that caused the deadlock.

Usage

You can use this element to uniquely identify the invocation in which a particular SQL statement has been executed. You can also use this element in conjunction with other statement history entries to see the sequence of SQL statements that caused the deadlock.

Usage

Use this element in conjunction with other statement history entries to see the sequence of SQL statements that caused the deadlock.

Usage

You can use this element in conjunction with other statement history entries to understand the cause of the deadlock and the execution behavior of a particular SQL statement.

Usage

You can use this element, along with stmt_invocation_id monitor element, to uniquely identify the invocation in which a particular SQL statement has been executed. You can also use this element in conjunction with other statement history entries to see the sequence of SQL statements that caused the deadlock.

Usage

You can use this element to determine the operation that is executing or recently finished.

It can be one of the following.

Usage

In a multi-partitioned environment, each partition has a unique statement ID for a cached statement. A given statement may not have the same ID across partitions.

In a global dynamic SQL snapshot, only the first statement ID is returned.

Usage

You can use this element, along with the stmt_nest_level monitor element, to uniquely identify an invocation of a particular SQL statement. You can also use this element in conjunction with other statement history entries to understand the cause of the deadlock.

Usage

You can use this element, along with appl_id monitor element, to uniquely identify the origin of a request to run a particular SQL statement. You can also use this element in conjunction with other statement history entries to understand the cause of the deadlock.

Usage

For application snapshots, this statement text helps you identify what the application was executing when the snapshot was taken, or most recently processed if no statement was being processed right at the time the snapshot was taken.

The information returned by this element is taken from the SQL statement cache and it might not be available if the cache has overflowed. The only guaranteed way to capture the SQL text of a statement is to use an event monitor for statements.

For dynamic SQL statements, this element identifies the SQL text associated with a package.

For statement event monitors, this element is returned only for dynamic statements. If a statement event monitor record cannot fit into the size of the buffer specified by the BUFFERSIZE option of a statement event monitor, the value of the stmt_text monitor may be truncated so that the record can fit.

For the EVENT_STMT_HISTORY event monitor, this element is returned only for dynamic statements. For remaining event monitors, stmt_text is returned for dynamic and static statements only if it is available in the SQL statement cache.

For information about how to query the system catalog tables to obtain static SQL statement text that is not provided due to performance considerations, see the section_number monitor element.

Usage

You can use this element to determine the type of statement that is executing. It can be one of the following values:

Usage

Usage

You can use this element in conjunction with other statement history entries to understand the cause of the deadlock.