MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW - Get formatted row-based component times
The MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW table function returns formatted row-based output for the component times contained in an XML metrics document.
Syntax
The schema is SYSPROC.
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
Routine parameters
- xmldoc
- An
input argument of type BLOB(8M) that contains an XML document with
either a system_metrics or activity_metrics element. XML documents
with these elements can be obtained from the following sources:
- Returned by one of the MON_GET_*_DETAILS table functions.
- From the metrics column output by statistics and activity event monitors.
- From the formatted output of the unit of work, or package cache event monitors.
Information returned
Column Name | Data Type | Description |
---|---|---|
METRIC_NAME | VARCHAR(128) | The unique identifier for the total time metric value. |
PROC_METRIC_NAME | VARCHAR(128) | The unique identifier for the processing time metric. |
TOTAL_TIME_VALUE | BIGINT | The total time value in milliseconds corresponding to metric_name. |
PROC_TIME_VALUE | BIGINT | The processing time value in milliseconds corresponding to proc_metric_name |
COUNT | BIGINT | Number of occurrences of this type of interval. |
PARENT_METRIC_NAME | VARCHAR(128) | The identifier of the parent total time metric whose value contains the total_time_value as a subset |
PARENT_PROC_METRIC_NAME | VARCHAR(128) | The identifier of the parent processing time metric whose value contains the proc_time_value as a subset |
XML documents that contain an element of type system_metrics are
generated from the following interfaces:
- MON_GET_CONNECTION_DETAILS
- MON_GET_SERVICE_SUBCLASS_DETAILS
- MON_GET_UNIT_OF_WORK_DETAILS
- MON_GET_WORKLOAD_DETAILS
- DETAILS_XML column from a STATISTICS event monitor
- METRICS column produced by EVMON_FORMAT_UE_TO_TABLES for the UNIT OF WORK event monitor
- XMLREPORT column of EVMON_FORMAT_UE_TO_XML for the UNIT OF WORK event monitor
See Table 2 for the types of metrics and their parent metrics
that are returned from the XML document in this case:
Metric Name | Proc Metric Name | Parent Metric Name | Parent Proc Metric Name | Description of metric or Monitor element |
---|---|---|---|---|
TOTAL_BACKUP_TIME | TOTAL_BACKUP_PROC_TIME | TOTAL_RQST_TIME | TOTAL_RQST_TIME | total_backup_time - Total elapsed time for doing online backups monitor element |
TOTAL_COL_SYNOPSIS_TIME | TOTAL_COL_SYNOPSIS_PROC_TIME | TOTAL_COL_TIME | TOTAL_COL_PROC_TIME | total_col_synopsis_time - Total column-organized synopsis table time monitor element |
TOTAL_COL_TIME | TOTAL_COL_PROC_TIME | TOTAL_SECTION_TIME | TOTAL_SECTION_PROC_TIME | total_col_time - Total column-oriented time |
TOTAL_COMMIT_TIME | TOTAL_COMMIT_PROC_TIME | TOTAL_RQST_TIME | TOTAL_RQST_TIME | total_commit_time - Total commit time |
TOTAL_COMPILE_TIME | TOTAL_COMPILE_PROC_TIME | TOTAL_RQST_TIME | TOTAL_RQST_TIME | total_compile_time - Total compile time |
TOTAL_CONNECT_AUTHENTICATION_TIME | TOTAL_CONNECT_AUTHENTICATION_PROC_TIME | TOTAL_CONNECT_REQUEST_TIME | TOTAL_CONNECT_REQUEST_PROC_TIME | total_connect_authentication_time - Total connection or switch user authentication request time |
TOTAL_CONNECT_REQUEST_TIME | TOTAL_CONNECT_REQUEST_PROC_TIME | TOTAL_RQST_TIME | TOTAL_RQST_TIME | total_connect_request_time - Total connection or switch user request time |
TOTAL_IMPLICIT_COMPILE_TIME | TOTAL_IMPLICIT_COMPILE_PROC_TIME | TOTAL_RQST_TIME | TOTAL_RQST_TIME | total_implicit_compile_time - Total implicit compile time |
TOTAL_INDEX_BUILD_TIME | TOTAL_INDEX_BUILD_PROC_TIME | TOTAL_SECTION_TIME | TOTAL_SECTION_PROC_TIME | total_index_build_time - Total time spent building indexes due to index creation or recreate/rebuild monitor element |
TOTAL_LOAD_TIME | TOTAL_LOAD_PROC_TIME | TOTAL_RQST_TIME | TOTAL_RQST_TIME | total_load_time - Total load time |
TOTAL_REORG_TIME | TOTAL_REORG_PROC_TIME | TOTAL_RQST_TIME | TOTAL_RQST_TIME | total_reorg_time - Total reorganization time |
TOTAL_ROLLBACK_TIME | TOTAL_ROLLBACK_PROC_TIME | TOTAL_RQST_TIME | TOTAL_RQST_TIME | total_rollback_time - Total rollback time |
TOTAL_ROUTINE_USER_CODE_TIME | TOTAL_ROUTINE_USER_CODE_PROC_TIME | TOTAL_RQST_TIME | TOTAL_RQST_TIME | total_routine_user_code_time - Total routine user code time |
TOTAL_RQST_TIME | NULL | NULL | NULL | total_rqst_time - Total request time |
TOTAL_RUNSTATS_TIME | TOTAL_RUNSTATS_PROC_TIME | TOTAL_RQST_TIME | TOTAL_RQST_TIME | total_runstats_time - Total runtime statistics |
TOTAL_SECTION_SORT_TIME | TOTAL_SECTION_SORT_PROC_TIME | TOTAL_SECTION_TIME | TOTAL_SECTION_PROC_TIME | total_section_sort_time - Total section sort time |
TOTAL_SECTION_TIME | TOTAL_SECTION_PROC_TIME | TOTAL_RQST_TIME | TOTAL_RQST_TIME | total_section_time - Total section time |
TOTAL_STATS_FABRICATION_TIME | TOTAL_STATS_FABRICATION_PROC_TIME | TOTAL_COMPILE_TIME | TOTAL_COMPILE_PROC_TIME | total_stats_fabrication_time - Total statistics fabrication time |
TOTAL_SYNC_RUNSTATS_TIME | TOTAL_SYNC_RUNSTATS_PROC_TIME | TOTAL_COMPILE_TIME | TOTAL_COMPILE_PROC_TIME | total_sync_runstats_time - Total synchronous runstats time |
XML documents that contain an element of type activity_metrics are
generated from the following interfaces:
- MON_GET_ACTIVITY_DETAILS
- MON_GET_PKG_CACHE_STMT_DETAILS
- DETAILS_XML column from an ACTIVITY event monitor
- METRICS column produced by EVMON_FORMAT_UE_TO_TABLES for the PACKAGE CACHE event monitor
- XMLREPORT column of EVMON_FORMAT_UE_TO_XML for the PACKAGE CACHE event monitor
See Table 3 for the types of metrics and their parent metrics
that are returned from the XML document in this case:
Metric Name | Proc Metric Name | Parent Metric Name | Parent Proc Metric Name | Description or Monitor element |
---|---|---|---|---|
STMT_EXEC_TIME | NULL | NULL | NULL | stmt_exec_time - Statement execution time |
TOTAL_COL_TIME | TOTAL_COL_PROC_TIME | TOTAL_SECTION_TIME | TOTAL_SECTION_PROC_TIME | total_col_time - Total column-oriented time |
TOTAL_INDEX_BUILD_TIME | TOTAL_INDEX_BUILD_PROC_TIME | TOTAL_SECTION_TIME | TOTAL_SECTION_PROC_TIME | total_index_build_time - Total time spent building indexes due to index creation or recreate/rebuild monitor element |
TOTAL_ROUTINE_NON_SECT_TIME | TOTAL_ROUTINE_NON_SECT_PROC_TIME | TOTAL_ROUTINE_TIME | STMT_EXEC_TIME | total_routine_non_sect_time - Non-section routine execution time |
TOTAL_ROUTINE_TIME | NULL | STMT_EXEC_TIME | NULL | total_routine_time - Total routine time |
TOTAL_ROUTINE_USER_CODE_TIME | TOTAL_ROUTINE_USER_CODE_PROC_TIME | TOTAL_ROUTINE_NON_SECT_TIME | TOTAL_ROUTINE_NON_SECT_PROC_TIME | total_routine_user_code_time - Total routine user code time |
TOTAL_SECTION_SORT_TIME | TOTAL_SECTION_SORT_PROC_TIME | TOTAL_SECTION_TIME | TOTAL_SECTION_PROC_TIME | total_section_sort_time - Total section sort time |
TOTAL_SECTION_TIME | TOTAL_SECTION_PROC_TIME | STMT_EXEC_TIME | STMT_EXEC_TIME | total_section_time - Total section time |
Example
The following example returns the breakdown of component times within the database manager for service subclasses, which shows both the total time spent in any given component, as well as the amount of time that was actually spent processing, rather than waiting, in a component.
SELECT SUBSTR(T.SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS,
SUBSTR(T.SERVICE_SUBCLASS_NAME,1,19) AS SUBCLASS,
T.MEMBER,
SUBSTR(COMP.METRIC_NAME,1,15) AS METRIC_NAME
SUBSTR(COMP.PARENT_METRIC_NAME,1,15) AS PARENT_NAME
COMP.TOTAL_TIME_VALUE AS TOTAL_TIME,
COMP.PROC_TIME_VALUE AS TOTAL_PROC_TIME,
COMP.COUNT
FROM TABLE (MON_GET_SERVICE_SUBCLASS_DETAILS(NULL,
NULL,-2)) AS T,
TABLE(MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW(
T.DETAILS
)) AS COMP
WHERE COMP.PARENT_METRIC_NAME IS NOT NULL;
The following is an example of output from this query.
SUPERCLASS SUBCLASS MEMBER METRIC_NAME PARENT_NAME ...
------------------- ------------------ ------ --------------- ---------------...
MYSC MYSSC 0 TOTAL_COMPILE_T TOTAL_RQST_TIME...
MYSC MYSSC 0 TOTAL_IMPLICIT_ TOTAL_RQST_TIME...
MYSC MYSSC 0 TOTAL_SECTION_T TOTAL_RQST_TIME...
MYSC MYSSC 0 TOTAL_COMMIT_TI TOTAL_RQST_TIME...
MYSC MYSSC 0 TOTAL_ROLLBACK_ TOTAL_RQST_TIME...
MYSC MYSSC 0 TOTAL_RUNSTATS_ TOTAL_RQST_TIME...
MYSC MYSSC 0 TOTAL_REORG_TIM TOTAL_RQST_TIME...
MYSC MYSSC 0 TOTAL_LOAD_TIME TOTAL_RQST_TIME...
MYSC MYSSC 0 TOTAL_SECTION_S TOTAL_SECTION_T...
9 record(s) selected.
The following is a continuation of sample output from this query.
...TOTAL_TIME TOTAL_PROC_TIME COUNT
...-------------------- -------------------- --------------------
... 100 100 1
... 0 0 0
... 1253 953 0
... 213 153 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
... 0 0 0
9 record(s) selected.