HP Neoview Repository User Guide HP Part Number: 611086-001 Published: July 2010 Edition: HP Neoview Release 2.
© Copyright 2010 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
Table of Contents About This Document.........................................................................................................9 Supported Release Version Updates (RVUs)..........................................................................................9 Intended Audience.................................................................................................................................9 New and Changed Information in This Edition.............................................
SPACE_Q_TABLE_FULL_VW1.................................................................................................57 SPACE_Q_TABLE_SIZE_VW1...................................................................................................57 SPACE_Q_TOP_10_CATEGORY_SIZE_VW1...........................................................................57 SPACE_Q_TOP_10_NON_PARTITION_DETAIL_VW1...........................................................57 SPACE_Q_TOP_10_PARTITION_DETAIL_VW1....................
VIEW NEO.HP_METRICS.QUERY_RUNTIME_STATS_V1_2400................................................108 Query Statistics for Release 2.4 ..........................................................................................................116 VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V1................................................................116 VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V2................................................................119 VIEW NEO.HP_METRICS.QUERY_RUNTIME_STATS_V1..............
List of Figures 1-1 6 Neoview Repository and Related Products..................................................................................
List of Tables 3-1 3-2 3-3 3-4 3-5 3-6 3-7 3-8 3-9 3-10 3-11 3-12 3-13 3-14 3-15 3-16 3-17 3-18 3-19 3-20 3-21 3-22 3-23 3-24 3-25 C-1 C-2 C-3 E-1 E-2 E-3 E-4 E-5 E-6 E-7 E-8 F-1 F-2 F-3 F-4 F-5 F-6 F-7 QUERY_STATS_VW2 Field Definitions........................................................................................20 SQL_TEXT_VW1 Field Definitions...............................................................................................32 ODBC_SESSION_STATS_V1 Field Definitions.................
List of Examples A-1 A-2 A-3 A-4 A-5 A-6 A-7 A-8 A-9 8 Generate Update Stats Command List..........................................................................................68 Search Event Logs for Abnormal Program Termination...............................................................68 Search Event Logs For a Specific Event Number..........................................................................69 Search Event Logs for Specific Event Text.................................................
About This Document This document describes the views supported by the Neoview Manageability Repository, a Neoview SQL database and set of programs that collect and store statistics and other information about queries, query sessions, tables, disks, processing nodes, and (optionally) selected processes involved in query execution on a Neoview platform. Supported Release Version Updates (RVUs) This manual supports Neoview Release 2.
• • • Created new appendix for STATEMENT_TYPE field of QUERY_STATS_VW2. See Appendix B: “STATEMENT_TYPE Field” (page 73). Moved Processing statistics (including the PROCESS_STATS_V1 AND PROCESS_AGGR_LEVEL1_STATS_V1 views) and Table statistics (including the TABLE_STATS_V2 and TABLE_STATS_DETAIL_V2 views) to new Appendix C: “Repository Views Disabled by Default” (page 75). Moved QUERY_STATS_V1 view to Appendix E: “Pre-R2.5 Query Statistics Views” (page 89).
UPPERCASE LETTERS Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: SELECT Italic Letters Italic letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required. For example: file-name Computer Type Computer type letters within text indicate case-sensitive keywords and reserved words. Type these items exactly as shown.
… Ellipsis An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example: ATTRIBUTE[S] attribute [, attribute]... {, sql-expression}... An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example: expression-n… Punctuation Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown.
Neoview Customer Library The manuals in the Neoview customer library are listed here for your convenience. • Administration Neoview User Management and Security Administration Guide Information about security features on the Neoview platform, including user and role management for database and platform users, support for integration with Lightweight Directory Access Protocol (LDAP) directory servers, password encryption, and database security.
• Connectivity Neoview JDBC Type 4 Driver API Reference information about the HP Neoview JDBC Type 4 Driver API. Reference Neoview JDBC Type 4 Driver Programmer’s Reference Information about using the HP Neoview JDBC Type 4 driver, which provides Java applications on client workstations access to a Neoview database. Neoview ODBC Drivers Manual Information about using HP Neoview ODBC drivers on a client workstation to access a Neoview database. • Neoview ADO.
1 Introduction Repository Features and Interfaces The Neoview Manageability Repository is a Neoview SQL database and set of programs that automatically collect and store statistics and other information about the following entities on a Neoview platform: • • • • • • • Queries initiated through ODBC and JDBC, including queries managed by Workload Management Services (WMS). All query data is written to a single location and accessible through two views, one of which holds the full SQL text.
Use the Neoview Performance Analysis Tools (NPA Tools) client to monitor queries and system resources on the Neoview platform and to reveal, as soon as possible, whether some aspect of system performance is outside an acceptable range. The NPA Tools provide a way to view, terminate, suspend, or resume queries. You can also use the HPDM system monitor and System Offender features to monitor the platform in real-time. NOTE: As of R2.4 SP1, running query information is no longer available from the Repository.
2 Getting Started with Repository Installation The Repository is initially installed on the Neoview platform by HP Manufacturing. Repository upgrades are installed by HP Support in the context of Neoview platform upgrades. Character Set Support in Repository Views The Repository installation script is aware of whether a Neoview platform is configured to support the ISO8859-1, Japan Shift-JIS, or UTF-8 character set.
Repository Metric Retention Time ODBC/JDBC Query compilation and execution statistics 90 days ODBC query statistics and Query runtime statistics 90 days (Release 2.4 statistics) These statistics are static and will never be updated. To remove these statistics from your platform or get help with migration to new tables, contact your HP service provider.
3 Repository Views Overview You can use the following views for access to the Repository: • • • • • • • • • “VIEW NEO.HP_METRICS.QUERY_STATS_VW2” (page 19) “VIEW NEO.HP_METRICS.SQL_TEXT_VW1” (page 32) “VIEW NEO.HP_METRICS.ODBC_SESSION_STATS_V1” (page 33) “VIEW NEO.HP_METRICS.ODBC_SESSION_STATS_V2” (page 36) “VIEW NEO.HP_METRICS.NODE_STATS_V1” (page 40) “VIEW NEO.HP_METRICS.DISK_STATS_V1” (page 42) “VIEW NEO.HP_METRICS.EVENTS_VW1” (page 45) “VIEW NEO.HP_METRICS.
Table 3-1 QUERY_STATS_VW2 Field Definitions Field Name Data Type Description EXEC_START_LCT_TS TIMESTAMP(6) NO DEFAULT ANSI Timestamp, in Local Civil Time, indicating when query execution began. That is, the time when an execute statement is issued for the prepared query. If the query is controlled by WMS, this value reflects the time it was ready to be submitted to the WMS system. Depending on WMS rules and threshold settings, WMS may delay or cancel the actual launch of the query.
Table 3-1 QUERY_STATS_VW2 Field Definitions (continued) Field Name Data Type Description ROLE_NAME CHAR(128) DEFAULT NULL Neoview user role name. Multiple users can have the same role name. Example: DBA START_PRIORITY INTEGER UNSIGNED DEFAULT NULL Starting priority of the Master Executor process for this query. Example: 148 PROCESS_NAME CHAR(64) DEFAULT NULL Process name of the NDCS server that is the parent of the query. The format of this name is \segment_name.$process_name. Example: \NEO0101.
Table 3-1 QUERY_STATS_VW2 Field Definitions (continued) Field Name Data Type Description STATEMENT_TYPE CHARACTER(36) DEFAULT NULL Compiler-provided value, one of: • SQL_SELECT_UNIQUE • SQL_SELECT_NON_UNIQUE • SQL_INSERT_UNIQUE • SQL_INSERT_NON_UNIQUE • SQL_UPDATE_UNIQUE • SQL_UPDATE_NON_UNIQUE • SQL_DELETE_UNIQUE • SQL_DELETE_NON_UNIQUE • SQL_SET_TRANSACTION • SQL_SET_SCHEMA • SQL_OTHER • SQL_CALL_NO_RESULT_SETS • SQL_CALL_WITH_RESULT_SETS • SQL_CAT_UTIL • SQL_EXE_UTIL • SQL_EXEC_INSERT_RWRS For defin
Table 3-1 QUERY_STATS_VW2 Field Definitions (continued) Field Name Data Type Description COMPILE_START_UTC_TS TIMESTAMP(6) DEFAULT NULL ANSI Timestamp, in Coordinated Universal Time, indicating when query compilation began. That is, when a Prepare statement was issued for this query. Example: 2009-08-17 15:47:28.791758 COMPILE_END_LCT_TS TIMESTAMP(6) DEFAULT NULL ANSI Timestamp, in Local Civil Time, indicating when query compilation ended.
Table 3-1 QUERY_STATS_VW2 Field Definitions (continued) Field Name Data Type COMPILE_MANDATORY_CROSS_PRODUCT INTEGER UNSIGNED DEFAULT NULL Description Flags queries in which a join has been specified with no predicates. Joins with no predicates may result from predicate movement during optimization or simply because no predicate was specified for the join.
Table 3-1 QUERY_STATS_VW2 Field Definitions (continued) Field Name Data Type Description EST_ACCESSED_ROWS DOUBLE PRECISION DEFAULT NULL Estimated number of rows to be accessed by SELECT statements from Disk Process at runtime by EID (Executor in Disk Process). For Insert/Update/Delete statements, the value is reported as 0.0.
Table 3-1 QUERY_STATS_VW2 Field Definitions (continued) Field Name Data Type Description CONN_RULE CHAR(84) DEFAULT NULL Connection rule. COMP_RULE CHAR(84) DEFAULT NULL Compilation rule. EXEC_RULE CHAR(84) DEFAULT NULL Execution rule. QUERY_STATUS CHAR(21) DEFAULT NULL Status of the query.
Table 3-1 QUERY_STATS_VW2 Field Definitions (continued) Field Name Data Type Description QUERY_EXECUTION_STATE CHAR(25) DEFAULT NULL What the executor is doing. Possible query states include: INITIAL OPEN EOF CLOSE DEALLOCATED FETCH CLOSE_TABLES PROCESS_ENDED UNKNOWN NULL WARN_LEVEL CHAR(7) DEFAULT NULL Warning level from WMS. Possible levels include: LOW MEDIUM HIGH NONE STATS_ERROR_CODE INTEGER DEFAULT NULL SQL error code, if any, returned while obtaining statistics for the query.
Table 3-1 QUERY_STATS_VW2 Field Definitions (continued) Field Name Data Type Description SQL_PROCESS_BUSY_TIME LARGEINT DEFAULT NULL An approximation, in microseconds, of the total node time spent in the Master Executor process and all ESPs involved in the query. This is a cumulative value, across all ESPs across all segments. Example: 31.
Table 3-1 QUERY_STATS_VW2 Field Definitions (continued) Field Name Data Type Description EXECUTOR_IN_DISK_MEMORY_ SPACE_ALLOCATED LARGEINT DEFAULT NULL Amount of “space” type (static) memory, in kilobytes, allocated (reserved) for the Executor in Disk (EID) disk processes involved in query execution. This process is separate from master and ESPs, and all the EID memory values are in addition to the SQL memory values. This is the value at the end of query execution.
Table 3-1 QUERY_STATS_VW2 Field Definitions (continued) Field Name Data Type Description PARENT_QUERY_ID CHAR(160) DEFAULT NULL Query ID for the immediate parent of this query ID. If the parent query ID is not present, value returned is NONE. TIP: You can use the parent query ID to relate a child SQL statement to an immediate parent, then trace the relationship to find the original SQL statement.
Table 3-1 QUERY_STATS_VW2 Field Definitions (continued) Field Name Data Type Description LOCK_ESCALATIONS LARGEINT DEFAULT NULL Cumulative number of times record (row) locks were escalated to file (table) locks during query execution. LOCK_WAITS LARGEINT DEFAULT NULL Number of times the statement waited for a lock request (concurrency cost). Access to the table is delayed due to conflicting locks. Ideally, this number is zero or very small.
VIEW NEO.HP_METRICS.SQL_TEXT_VW1 This view allows access to the complete SQL text for a query. When the SQL text for a query is 254 characters or fewer, the text is written to the QUERY_STATS_VW1 view and the SQL_TEXT_OVERFLOW_INDICATOR field is set to 0. If the text is longer, the first 254 characters are written to QUERY_STATS_VW1 and SQL_TEXT_OVERFLOW_INDICATOR is set to 1, indicating that the complete SQL text is found in the SQL_TEXT field of SQL_TEXT_VW1.
Table 3-2 SQL_TEXT_VW1 Field Definitions (continued) Field Name Data Type Description PLATFORM_NAME CHAR(64) DEFAULT NULL Neoview system name, made up of the first three letters of the system name, followed by 01 for the first platform at your site, 02 for the second platform at your site, and so on. For example, NEO01. SQL_TEXT VARCHAR(16000) DEFAULT NULL Complete SQL Text for a query. 16000 characters are written per row.
Table 3-3 ODBC_SESSION_STATS_V1 Field Definitions (continued) 34 Field Name Data Type Description SESSION_EVENT_DATETIME_UTC TIMESTAMP(6) ANSI SQL Timestamp, in Coordinated Universal Time, indicating when the statistics were recorded in the Repository. SESSION_EVENT_DATE_UTC DATE ANSI SQL date, in Coordinated Universal Time, when the statistics were recorded in the Repository.
Table 3-3 ODBC_SESSION_STATS_V1 Field Definitions (continued) Field Name Data Type Description TOTAL_INSERT_STMTS_EXECUTED LARGEINT Total number of SQL INSERT statements sent by the HP ODBC driver to the NDCS SQL server. Does not include internally generated SQL statements. TOTAL_ODBC_ELAPSED_TIME LARGEINT Total NDCS server real time, in microseconds, from the time the connection started to the time it was closed.
Table 3-3 ODBC_SESSION_STATS_V1 Field Definitions (continued) Field Name Data Type Description SEGMENT_NAME CHAR(10) Name of the Neoview segment used by the data source. PROCESS_NAME CHAR(18) Process name of the NDCS server. USER_ID CHAR(8) A deprecated internal representation of a Neoview role. USER_NAME CHAR(20) The name, used at time of connection, to identify a Neoview user. One or more such names can map internally to the same Neoview role.
The primary key for ODBC_SESSION_STATS_V2 field definition is SEGMENT_ID, NODE_ID, and SESSION_EVENT_DATETIME_UTC.
Table 3-4 ODBC_SESSION_STATS_V2 Fields (continued) 38 Source and Notes Complete Sessions Incomplete Sessions Data Type SESSION_END_DATE_UTC SESSION_END_DATE_UTC DATE END, or NULL for an incomplete session SESSION_ END _TIME_UTC SESSION_ END _TIME_UTC TIME END, or NULL for an incomplete session SESSION_STATE SESSION_STATE CHAR(14) 'INCOMPLETE' or 'COMPLETE' TOTAL_CATALOG_STMTS LARGEINT END, or NULL for an incomplete session TOTAL_CLOSES LARGEINT END, or NULL for an incomplete session T
Table 3-4 ODBC_SESSION_STATS_V2 Fields (continued) Complete Sessions Incomplete Sessions Data Type Source and Notes TOTAL_PREPARES LARGEINT END, or NULL for an incomplete session TOTAL_SELECT_STMTS_EXECUTED LARGEINT END, or NULL for an incomplete session TOTAL_UPDATE_STMTS_EXECUTED LARGEINT END, or NULL for an incomplete session TOTAL_WARNINGS LARGEINT END, or NULL for an incomplete session TOTAL_LOGIN_ELAPSED_TIME_MCSEC LARGEINT Time in microseconds spent on login, including processing st
Table 3-4 ODBC_SESSION_STATS_V2 Fields (continued) Complete Sessions Incomplete Sessions Data Type Source and Notes LDAP_LOGIN_ELAPSED_TIME_MCSEC LARGEINT Time in microseconds spent on communication with the external authentication server, including time waiting for replies from the server. For platform users, such as super-user or SecurityAdmin, the value is 0. CLIENT_USER_NAME CHAR(128) or NCHAR(128) The LDAP name of the user who originated the session by logging in to a workstation.
Table 3-5 NODE_STATS_V1 Field Definitions (continued) Field Name Data Type Description MEASURE_END_DATETIME TIMESTAMP(6) ANSI SQL timestamp, in Local Civil Time, indicating when the measurement interval ended. MEASURE_END_DATE DATE ANSI SQL date, in Local Civil Time, indicating when the measurement interval ended. MEASURE_END _TIME TIME(6) ANSI SQL time, in Local Civil Time, indicating when the measurement interval ended.
Table 3-5 NODE_STATS_V1 Field Definitions (continued) Field Name Data Type Description DISPATCHES LARGEINT Number of times a process was selected from the ready list and executed by the processing node. SWAPS LARGEINT Number of swap operations, both into and out of memory, performed by the memory manager. INTR_BUSY_TIME LARGEINT Time, in microseconds, that the processing node spent executing interrupt handlers.
Table 3-6 DISK_STATS_V1 Field Definitions Field Name Data Type Description SUBMIT_TS TIMESTAMP(6) Timestamp, in Local Civil Time, indicating when the measurement was submitted to the Repository SEGMENT_ID SMALLINT UNSIGNED Neoview platform segment number VOLUME_NAME CHAR(8) Logical disk volume name NODE_ID SMALLINT UNSIGNED Primary processing node number of the disk process for this volume PIN SMALLINT UNSIGNED Process identification number of the disk process for this volume SEGMENT_NAME
Table 3-6 DISK_STATS_V1 Field Definitions (continued) Field Name Data Type Description IO_REQS_PER_SEC DECIMAL(9,1) Number of disk input/output requests per second for this interval BLOCKED_REQS_PER_SEC SMALLINT UNSIGNED Number of disk requests blocked per second for this interval PAGE_SWAPS_PER_SEC DECIMAL(9,1) Number of disk page swaps per second for this interval STATUS_TEXT CHAR(12) Availability or performance status of the disk, reflecting objectives defined in the Neoview Management Dash
Table 3-6 DISK_STATS_V1 Field Definitions (continued) Field Name Data Type Description OUTPUT_KB_PER_SEC DECIMAL(9,1) Output kilobytes per second during this interval for the primary disk READS_PER_SEC_MIR DECIMAL(9,1) Number of disk process read operations per second for this interval, for the mirror disk WRITES_PER_SEC_MIR DECIMAL(9,1) Number of disk process write operations per second for this interval, for the mirror disk OPS_PER_SEC_MIR DECIMAL(9,1) Total number of disk process reads, wri
Table 3-7 EVENTS_VW1 Field Definitions Field Name Data Type Description LOG_TIMESTAMP_LCT TIMESTAMP(6) Primary Key Timestamp, in Local Civil Time, indicating when the event was placed in the EMS log. SEGMENT CHAR(8) Primary Key Name of the segment where this event was logged. This column always uses the ISO88591 character set. COLLECTOR CHAR(8) Primary Key The name of the EMS collector that logged this event. This column always uses the ISO88591 character set.
Table 3-7 EVENTS_VW1 Field Definitions (continued) Field Name Data Type Description PROCESS_ID_INSTANCE INTEGER UNSIGNED Instance number of the process that generated the event. NODE_ID INTEGER UNSIGNED Processing node number of the process that generated the event. PIN INTEGER UNSIGNED Process identification number of the process that generated the event. SEGMENT_ID INTEGER UNSIGNED Segment number of the process that generated the event.
Table 3-8 SPACE_PARTITION_DETAIL_VW1 Field Definitions Field Name Data Type Description LAST_UPDATE_LCT_JTS LARGEINT Timestamp, in Local Civil Time, indicating when this row was updated in the Repository Example: 2010-01-26 07:47:28.795392 LAST_CHECK_NOUPDATE_LCT_JTS LARGEINT Timestamp, in Local Civil Time, indicating when information in this row was checked against the system values but did not need any Repository update Example: 2010-01-26 07:48:09.
Table 3-8 SPACE_PARTITION_DETAIL_VW1 Field Definitions (continued) Field Name Data Type Description CRASH_OPEN CHAR(1) Crash open. For disk objects other than SQL shorthand views: 1 = if the object was open with write access when a system failure occurred and the object has not been opened since 0 = otherwise INCOMPLETE_SQL_DDL CHAR(1) Incomplete SQL DDL operation.
Table 3-8 SPACE_PARTITION_DETAIL_VW1 Field Definitions (continued) Field Name Data Type Description LAST_OPEN_LCT_JTS LARGEINT Timestamp, in Local Civil Time, when the partition was last opened LAST_OPEN_UTC_JTS LARGEINT Timestamp, in Coordinated Universal Time, when the partition was last opened DROP_TIME_LCT_JTS LARGEINT Timestamp, in Local Civil Time, when the partition was dropped DROP_TIME_UTC_JTS LARGEINT Timestamp, in Coordinated Universal Time, when the partition was dropped FILE_CODE
Table 3-9 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions (continued) Field Name Data Type Description PLATFORM_NAME CHAR(64) Neoview system name, made up of the first three letters of the system name, followed by 01 for the first platform at your site, 02 for the second platform at your site, and so on.
Table 3-9 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions (continued) Field Name Data Type Description INCOMPLETE_SQL_DDL_CURR CHAR(1) Incomplete SQL DDL operation. For SQL tables and indexes: 1 = if the object has the D flag set 0 = otherwise The D flag is an internal setting for incomplete SQL DDL operations. UNRECLAIMED_FREE_SPACE_CURR CHAR(1) Unreclaimed free space.
Table 3-9 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions (continued) Field Name Data Type Description ALLOC_EXTENT_MIN LARGEINT Minimum value of ALLOC_EXTENT_CURR ALLOC_EXTENT_MAX LARGEINT Maximum value of ALLOC_EXTENT_CURR RECORD_LEN_CURR_BYTES LARGEINT Record length in bytes RECORD_LEN_AVG_BYTES LARGEINT Average value of RECORD_LEN_CURR_BYTES RECORD_LEN_MIN_BYTES LARGEINT Minimum value of RECORD_LEN_CURR_BYTES RECORD_LEN_MAX_BYTES LARGEINT Maximum value of RECORD_LEN_CURR_BYTES
Table 3-9 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions (continued) Field Name Data Type Description LAST_MODIFIED_LCT_JTS LARGEINT Timestamp, in Local Civil Time, when the partition was last modified LAST_OPEN_LCT_JTS LARGEINT Timestamp, in Local Civil Time, when the partition was last opened DROP_TIME_LCT_JTS LARGEINT Timestamp, in Local Civil Time, when the partition was dropped FILE_CODE INT UNSIGNED The application-defined code FILE_OWNER_NAME CHAR(64) Owner of the file CATEGOR
SPACE_Q_DISK_FULL_VW1 Table 3-11 SPACE_Q_DISK_FULL_VW1 Description: This report provides disk fullness information, including the percentage of disk space that is currently in use from biggest to smallest and the percentage of user-allocated space that is currently full. Columns Included: LAST_CHECK_NOUPDATE_LCT_TS LAST_CHECK_NOUPDATE_UTC_TS SEGMENT_NAME DISK_NAME DISK_FULL_PCT USER_FULL_PCT Views Used: NEO.HP_METRICS.DISK_STATS_V1 NEO.HP_METRICS.
SPACE_Q_PARTITION_FULL_VW1 Table 3-14 SPACE_Q_PARTITION_FULL_VW1 Description: This report provides partition fullness information, including the percentage of partition space that is currently full for each partition, from biggest to smallest. Columns Included: LAST_CHECK_NOUPDATE_LCT_TS LAST_CHECK_NOUPDATE_UTC_TS SEGMENT_NAME PARTITION_NAME PARTITION_FULL_PCT Views Used: NEO.HP_METRICS.
SPACE_Q_TABLE_FULL_VW1 Table 3-18 SPACE_Q_TABLE_FULL_VW1 Description: This report provides table fullness information, including the percentage of table space that is currently full for each table, from biggest to smallest. Columns Included: LAST_CHECK_NOUPDATE_LCT_TS LAST_CHECK_NOUPDATE_UTC_TS TABLE_NAME TABLE_FULL_PCT Views Used: NEO.HP_METRICS.
SPACE_Q_TOP_10_PARTITION_DETAIL_VW1 Table 3-22 SPACE_Q_TOP_10_PARTITION_DETAIL_VW1 Description: This report lists the partitioned objects with the top 10 sizes, in megabytes, from biggest to smallest. Columns Included: LAST_CHECK_NOUPDATE_LCT_TS LAST_CHECK_NOUPDATE_UTC_TS CATALOG_NAME SCHEMA_NAME OBJECT_NAME OBJECT_SIZE_MB Views Used: NEO.HP_METRICS.
4 Examples and Guidelines for Creating Repository Queries Overview This section provides some simple queries against the available Repository views and provides a few suggestions for writing Repository queries. NOTE: More realistic examples of Repository queries are included in the Metric Miner of the HP Database Manager (HPDM) product. Queries in this section are simple examples provided as an introduction to the Repository.
from NEO.HP_METRICS.QUERY_STATS_VW2 ORDER BY EXEC_START_LCT_TS DESC FOR READ UNCOMMITTED ACCESS; Retrieve Statistics for Completed Queries This query lists information for the ten most recent queries that completed normally. You could enhance this query to filter for time ranges, user IDs, or other attributes. The list following the query shows values that might be returned in a single record.
select [first 10] (EXEC_START_LCT_TS) AS EXEC_START_LCT_TS ,(SEGMENT_ID) AS SEGMENT_ID ,(QUERY_STATUS) AS QUERY_STATUS ,(cLIENT_NAME) AS CLIENT_NAME from NEO.HP_METRICS.QUERY_STATS_VW2 WHERE QUERY_STATUS in ('REJECTED', 'INIT') ORDER BY EXEC_START_LCT_TS DESC FOR READ UNCOMMITTED ACCESS; List Statement Counts for a Selected Time Period This query returns statement counts by user name for a given time period that you provide. The output following the query shows a single row of the output.
Count Completed Queries, According to Specified Criteria The following queries all return the number of completed queries, but they break down the number in different ways. Completed Queries Executed on a System in the Last 24 Hours SELECT DISTINCT (CAST(EXEC_START_LCT_TS AS DATE)) AS START_DATE, COUNT(*) AS NUM_OF_QUERIES, MAX(CAST(EXEC_START_LCT_TS AS TIME(6))) AS MAX_START_TIME, MIN(CAST(EXEC_START_LCT_TS AS TIME(6))) AS MIN_START_TIME FROM NEO.HP_METRICS.
Queries within the Past 24 Hours, Grouped by Data Source This example indicates how many queries within the 24-hour period were associated with each data source. SELECT datasource, COUNT(*) AS NUM_OF_QUERIES FROM NEO.HP_METRICS.
count(*) as query_count from NEO.HP_METRICS.
The TRANSLATE function is another option for specifying a UCS2 literal.
A Sample Queries for Event Information These are examples of queries you can run against the error event data on the Neoview platform using HPDM SQL Whiteboard, HPDM Metric Miner, or Neoview Command Interface on Repository view EVENTS_VW1. For details about fields from the EVENTS_VW1 view used in these queries, refer to “VIEW NEO.HP_METRICS.EVENTS_VW1” (page 45).
Example A-1 Generate Update Stats Command List This report generates an executable command list to resolve missing table statistics, based on two event numbers: SELECT COUNT(*) AS CNT, MIN(LOG_TIMESTAMP_LCT) AS FIRST_TIME, MAX(LOG_TIMESTAMP_LCT ) AS LAST_TIME, UPDATE_STATS_COMMAND || ';' as UPDATE_STATS_COMMAND, EVENT_NUMBER FROM ( SELECT log_timestamp_lct, trim(trailing '.
Example A-3 Search Event Logs For a Specific Event Number This report lists all occurrences of a specified event number within a given time period. SELECT EVENT_NUMBER AS EVENT_NUMBER, GENERATED_TIMESTAMP_LCT AS GEN_TIME, SUBSTRING(SUBSTRING(SSID FROM CHAR_LENGTH('TANDEM. '))FOR LOCATE('.' ,SUBSTRING(SSID FROM CHAR_LENGTH('TANDEM. ')) )-1) AS SUB_SYS, trim(substring(Text FROM CHAR_LENGTH('00:00 01FEB09 001.01.
Example A-5 Search Event Logs for Missing Statistics This report searches for all occurrences of missing SQL statistics messages 6007, 6008, 6010 and 6011 during a given time period. SELECT EVENT_NUMBER AS EVENT_NUMBER, GENERATED_TIMESTAMP_LCT AS GEN_TIME, SUBSTRING(SUBSTRING(SSID FROM CHAR_LENGTH('TANDEM. '))FOR LOCATE('.' ,SUBSTRING(SSID FROM CHAR_LENGTH('TANDEM. ')) )-1) AS SUB_SYS, trim(substring(Text FROM CHAR_LENGTH('00:00 01FEB09 001.01.
Example A-7 Search Event Logs for a Query ID This report searches event text for a full or partial query_id during a given time period. SELECT EVENT_NUMBER AS EVENT_NUMBER, GENERATED_TIMESTAMP_LCT AS GEN_TIME, SUBSTRING(SUBSTRING(SSID FROM CHAR_LENGTH('TANDEM. '))FOR LOCATE('.' ,SUBSTRING(SSID FROM CHAR_LENGTH('TANDEM. ')) )-1) AS SUB_SYS, trim(substring(Text FROM CHAR_LENGTH('00:00 01FEB09 001.01.
Example A-9 Summary of all Events This report summarizes all events for a given time period and returns results grouped by SSID and event number. SELECT COUNT(*) AS CNT, EVENT_NUMBER AS EVENT_NUMBER, CASE WHEN LEFT(SSID,6) = 'TANDEM' THEN SUBSTRING(SUBSTRING(SSID FROM CHAR_LENGTH('TANDEM. '))FOR LOCATE('.' ,SUBSTRING(SSID FROM CHAR_LENGTH('TANDEM.
B STATEMENT_TYPE Field The following table provides the compiler-provided values for the STATEMENT_TYPE field of the NEO.HP_METRICS.ODBC_QUERY_STATS_VW2 Repository view.
C Repository Views Disabled by Default This appendix contains the following views that have been disabled by default: • “VIEW NEO.HP_METRICS.PROCESS_STATS_V1” (page 75) • “VIEW NEO.HP_METRICS.PROCESS_AGGR_LEVEL1_STATS_V1” (page 78) • “VIEW NEO.HP_METRICS.TABLE_STATS_V2 and VIEW NEO.HP_METRICS.
Table C-1 PROCESS_STATS_V1 Field Definitions (continued) 76 Field Name Data Type Description MEASURE_END _TIME TIME(6) ANSI SQL time, in Local Civil Time, indicating when the measurement interval ended MEASURE_END_DATETIME_UTC TIMESTAMP(6) ANSI SQL timestamp, in Coordinated Universal Time, indicating when the measurement interval ended MEASURE_END _DATE_UTC DATE ANSI SQL date, in Coordinated Universal Time, indicating when the measurement interval ended MEASURE_END _TIME_UTC TIME(6) ANSI SQL
Table C-1 PROCESS_STATS_V1 Field Definitions (continued) Field Name Data Type Description ANCESTOR_NODE_ID SMALLINT UNSIGNED Processing node of the parent process (the process that started the process being measured). If this information is unavailable, the field contains the value “unknown.” ANCESTOR_PIN SMALLINT UNSIGNED Unique number associated with a running instance of a process, in this case the parent process, or “unknown.
Table C-1 PROCESS_STATS_V1 Field Definitions (continued) Field Name Data Type Description FILE_OPEN_CALLS LARGEINT Number of calls to open regular files, pipes, FIFOs, AF_INET sockets, AF_UNIX sockets, directories, and other files, including implicit opens due to process creation APIs BEGIN_TRANS LARGEINT Number of times the process began a transaction ABORT_TRANS LARGEINT Number of times the process stopped a transaction in progress, causing the transaction to be rolled back PRES_PAGES_START L
Table C-2 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) Field Name Data Type Description SAMPLE_TIME TIME(6) ANSI SQL time of the aggregation, in Local Civil Time. SAMPLE_DATETIME_UTC TIMESTAMP(6) ANSI SQL timestamp of the aggregation, in Coordinated Universal Time. SAMPLE_DATE_UTC DATE ANSI SQL date of the aggregation, in Coordinated Universal Time. SAMPLE_TIME_UTC TIME(6) ANSI SQL time of the aggregation, in Coordinated Universal Time.
Table C-2 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) 80 Field Name Data Type Description CHILD_SYSTEM_BUSY_PCT DECIMAL(6,2) Total child busy time, in microseconds, divided by the number of processing nodes in the cluster MIN_CHILD_ BUSY_PCT DECIMAL(6,2) Minimum CPU busy time since the last aggregation, for all processes described by this record AVG_CHILD_ BUSY_PCT DECIMAL(6,2) Average CPU busy time since the last aggregation, for all processes described by this record MAX_CHILD
Table C-2 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) Field Name Data Type Description STDDEV_HISTOGRAM_1 LARGEINT Standard deviation of percent busy for processes with a CPU busy percentage less than 1 STDDEV _HISTOGRAM_1_25 LARGEINT Standard deviation of percent busy for processes with a CPU busy percentage between 1 and 25 STDDEV _HISTOGRAM_25_50 LARGEINT Standard deviation of percent busy for processes with a CPU busy percentage between 25 and 50 STDDEV _HISTOGRAM_50-75 LARGE
Table C-2 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) Field Name Data Type Description NUMBER_OF_CHILDREN_W_PAGE_FAULTS INTEGER UNSIGNED Count of processes with page faults MIN_NUMBER_OF_PAGE_FAULTS INTEGER UNSIGNED Among processes that had page faults, the smallest number of page faults AVG_NUMBER_OF_ PAGE_FAULTS INTEGER UNSIGNED Among processes that had page faults, the average number of page faults MAX_NUMBER_OF_ PAGE_FAULTS INTEGER UNSIGNED Among processes that had page faul
• TABLE_STATS_V2 provides the most recently collected data for each monitored object. Thus, the view includes only one row per monitored table or materialized view. • TABLE_STATS_DETAIL_V2 presents all the data collected for the table or materialized view since it began to be monitored. The view provides a row for each condition reported by the table monitoring process. Thus, the view includes one row for every data collection interval.
Table C-3 TABLE_STATS_V2 and TABLE_STATS_DETAIL_V2 Field Definitions (continued) 84 Field Name Data Type Description OBJECT_NAME_SPACE CHAR(22) Namespace in which the object is visible, currently: ‘TABLE' OBJECT_TYPE CHAR(22) Kind of object, one of: ‘TABLE’ ‘MAT VIEW’ or ‘_’ MONITOR_STATUS_TXT CHAR(16) “Up” means the table or materialized view is being monitored. “Dropped” means that the table or materialized view was dropped or renamed, and no other object with the same name was created.
Table C-3 TABLE_STATS_V2 and TABLE_STATS_DETAIL_V2 Field Definitions (continued) Field Name Data Type Description MISSING_STATS VARCHAR(1740) Cumulative list of columns for which statistics are missing, or “-” if the data is not the result of a runtime warning. Value is a comma-separated list in which each member has the form column(number) or (column, column)(number), where column is a column name, and number is the corresponding number of missing statistics warnings.
D History of New and Changed Information in Previous Releases of the Repository New and Changed Information in Previous Editions The Release 2.4 Service Pack 2 (SP2) edition of this manual includes the following new and changed information: • New fields in QUERY_STATS_VW1: DISK_PROCESS_BUSY_TIME_SEC and MASTER_EXECUTION_TIME_SEC. See “VIEW NEO.HP_METRICS.QUERY_STATS_VW1” (page 89). • New fields in SQL_TEXT_VW1: EXEC_START_LCT_TS and EXEC_START_UTC_TS. See “VIEW NEO.HP_METRICS.SQL_TEXT_VW1” (page 32).
The Release 2.4 edition of this manual included the following new and changed information: • Clarify and correct text related to space and heap. • Miscellaneous other corrections and clarifications. The Release 2.3 edition of this manual included the following new and changed information: • Description of Query Runtime Statistics as a standard feature, and enhancements to the view that provides access to the data. • Description of the Disk entity and views that provide metric information for Disks.
E Pre-R2.5 Query Statistics Views Query Statistics for R2.4 SP2 VIEW NEO.HP_METRICS.QUERY_STATS_VW1 Repository collects compilation and execution query statistics. This view provides access to all query information. The SQL text for the query is also included, up to 254 characters. If the SQL text is longer than 254 characters, the SQL_TEXT_OVERFLOW_INDICATOR field is set to 1 to indicate that only part of the SQL text fits in this view, and the complete SQL text is placed in the SQL_TEXT_VW1 view.
Table E-1 QUERY_STATS_VW1 Field Definitions (continued) Field Name Data Type Description ENTRY_ID_LCT_TS TIMESTAMP(6) NO DEFAULT Repository-generated ANSI Timestamp, in Local Civil Time, showing when this row was last updated. Example: 2009-08-17 07:48:09.967095 ENTRY_ID_UTC_TS TIMESTAMP(6) NO DEFAULT Repository-generated ANSI Timestamp, in Coordinated Universal Time, showing when this row was last updated. Example: 2009-08-17 15:48:09.
Table E-1 QUERY_STATS_VW1 Field Definitions (continued) Field Name Data Type Description DATASOURCE CHARACTER(128) DEFAULT NULL Datasource name on the Neoview platform to which the client connected. Example: Admin_Load_DataSource STATEMENT_ID CHARACTER(160) DEFAULT NULL Unique statement ID generated by the NDCS SQL server at the time the Prepare command was received.
Table E-1 QUERY_STATS_VW1 Field Definitions (continued) Field Name Data Type Description SUBMIT_UTC_TS TIMESTAMP(6) DEFAULT NULL ANSI Timestamp, in Coordinated Universal Time, indicating when the query first entered Workload Management Services (WMS). Example: 2009-08-17 15:47:28.795755 COMPILE_START_LCT_TS TIMESTAMP(6) NO DEFAULT ANSI Timestamp, in Local Civil Time, indicating when query compilation began. That is, when a Prepare statement was issued for this query. Example: 2009-08-17 07:47:28.
Table E-1 QUERY_STATS_VW1 Field Definitions (continued) Field Name Data Type Description COMPILE_TRANSACTION_NEEDED INTEGER UNSIGNED DEFAULT NULL Indicates whether this query is required to execute under a transaction. All SQL statements require a transaction except SELECT statements that use the READ UNCOMMITTED isolation level. Values: • 1 = True • 0 = False COMPILE_MANDATORY_CROSS_PRODUCT INTEGER UNSIGNED DEFAULT NULL Flags queries in which a join has been specified with no predicates.
Table E-1 QUERY_STATS_VW1 Field Definitions (continued) Field Name Data Type Description EST_COST DOUBLE PRECISION DEFAULT NULL Estimated total cost (time in seconds) of the SQL operations for this query in the Master Executor. The estimate occurs at the time the query is prepared. Example: 1.74125E-6 EST_CARDINALITY DOUBLE PRECISION NO DEFAULT Estimated number of rows that will be returned. The estimate occurs at the time the query is prepared. Example: 10.
Table E-1 QUERY_STATS_VW1 Field Definitions (continued) Field Name Data Type Description EST_RESOURCE_USAGE INTEGER DEFAULT NULL Estimate of the number of seconds of processor time it might take to execute the query. The estimate occurs at the time the query is prepared. NOTE: QUERY_STATUS QUERY_SUB_STATUS For future use. Currently zero (0). CHAR(21) DEFAULT NULL Status of the query.
Table E-1 QUERY_STATS_VW1 Field Definitions (continued) Field Name Data Type Description QUERY_EXECUTION_STATE CHAR(25) DEFAULT NULL What the executor is doing. Possible query states include: INITIAL OPEN EOF CLOSE DEALLOCATED FETCH CLOSE_TABLES PROCESS_ENDED UNKNOWN NULL WARN_LEVEL CHAR(7) DEFAULT NULL Warning level from WMS. Possible levels include: LOW MEDIUM HIGH NONE STATS_ERROR_CODE INTEGER DEFAULT NULL SQL error code, if any, returned while obtaining statistics for the query.
Table E-1 QUERY_STATS_VW1 Field Definitions (continued) Field Name Data Type Description SQL_PROCESS_BUSY_TIME LARGEINT DEFAULT NULL An approximation, in microseconds, of the total node time spent in the Master Executor process and all ESPs involved in the query. This is a cumulative value, across all ESPs across all segments. Example: 31.
Table E-1 QUERY_STATS_VW1 Field Definitions (continued) 98 Field Name Data Type Description EXECUTOR_IN_DISK_MEMORY_ SPACE_ALLOCATED INTEGER DEFAULT NULL Amount of “space” type (static) memory, in kilobytes, allocated (reserved) for the Executor in Disk (EID) disk processes involved in query execution. This process is separate from master and ESPs, and all the EID memory values are in addition to the SQL memory values. This is the value at the end of query execution.
Table E-1 QUERY_STATS_VW1 Field Definitions (continued) Field Name Data Type Description PARENT_QUERY_ID CHAR(160) DEFAULT NULL Query ID for the immediate parent of this query ID. If the parent query ID is not present, value returned is NONE. TIP: You can use the parent query ID to relate a child SQL statement to an immediate parent, then trace the relationship to find the original SQL statement.
Table E-1 QUERY_STATS_VW1 Field Definitions (continued) 100 Field Name Data Type Description LOCK_ESCALATIONS LARGEINT DEFAULT NULL Cumulative number of times record (row) locks were escalated to file (table) locks during query execution. LOCK_WAITS LARGEINT DEFAULT NULL Number of times the statement waited for a lock request (concurrency cost). Access to the table is delayed due to conflicting locks. Ideally, this number is zero or very small.
Query Statistics for R2.4 SP1 Three new Repository views improve the collection of the Repository statistics: • Query statistics: Two new Repository views allow you to access compilation and execution (runtime) statistics in one place without having to join rows or use multiple views: • “VIEW NEO.HP_METRICS.QUERY_STATS_VW1” (page 89) provides access to all query statistics, including new query statistics metrics • “VIEW NEO.HP_METRICS.
NOTE: The old data displayed in those views will not be transferred to the new Repository table and cleaned up. Also, the old Repository tables will no longer collect new data after Release 2.4 SP1 is installed. To remove old Repository data (and the tables that contain it), contact your HP service provider. NOTE: Although data collected in Release 2.4 is preserved by the installation script, no tools are provided for migrating existing data to Release 2.4 SP1 tables.
Table E-3 ODBC_QUERY_STATS_V1_2400 Field Definitions (continued) Field Name Data Type Description QUERY_EVENT_DATE_UTC DATE ANSI SQL date in Coordinated Universal Time when the statistics were recorded in the Repository. QUERY_EVENT_TIME_UTC TIME(6) ANSI SQL time in Coordinated Universal Time when the statistics were recorded in the Repository. SESSION_ID CHARACTER(108) Unique session ID generated by the NDCS server when the ODBC/JDBC connection was established.
Table E-3 ODBC_QUERY_STATS_V1_2400 Field Definitions (continued) 104 Field Name Data Type Description STATEMENT_STATUS CHARACTER(5) START if this record contains information collected at the start of the query, or END if it contains statistics collected at the end of the query. CANCELLED for queries killed through Neoview Query Viewer. START_PRIORITY SMALLINT UNSIGNED Execution priority of the NDCS process at the time the connection request is received.
Table E-3 ODBC_QUERY_STATS_V1_2400 Field Definitions (continued) Field Name Data Type Description STATEMENT_TYPE CHARACTER(36) Compiler-provided value, one of: SQL_SELECT_UNIQUE, SQL_SELECT_NON_UNIQUE SQL_INSERT_UNIQUE, SQL_INSERT_NON_UNIQUE, SQL_UPDATE_UNIQUE, SQL_UPDATE_NON_UNIQUE, SQL_DELETE_UNIQUE, SQL_DELETE_NON_UNIQUE, SQL_CONTROL, SQL_SET_TRANSACTION, SQL_SET_CATALOG, SQL_SET_SCHEMA, SQL_OTHER, SQL_UNKNOWN, SQL_CALL_NO_RESULT_SETS, SQL_CALL_WITH_RESULT_SETS, SQL_SP_RESULT_SET, SQL_NOT_SUPPORTED.
This table indicates which fields are present for completed and incomplete queries. The source of data fields indicates whether the value is collected at the start of the query or at the end. Unless indicated here, field descriptions are those provided above for ODBC_QUERY_STATS_V1. Character columns use ISO88591 or UCS2 encoding, as appropriate to the Neoview platform configuration. If the platform is UCS2 or SJIS, all character columns are created with CHARACTER SET UCS2.
Table E-4 ODBC_QUERY_STATS_V2_2400 Fields (continued) Complete Queries Incomplete Queries Data Type Source and Notes QUERY_START_DATETIME QUERY_START_DATETIME TIMESTAMP START QUERY_START_DATE QUERY_START_DATE DATE START QUERY_START_TIME QUERY_START_TIME TIME START QUERY_START_DATETIME_UTC QUERY_START_DATETIME_UTC TIMESTAMP START QUERY_START_DATE_UTC QUERY_START_DATE_UTC DATE START QUERY_START_TIME_UTC QUERY_START_TIME_UTC TIME START QUERY_END_DATETIME QUERY_START_DATETIME TIMEST
NOTE: In both ODBC_QUERY_STATS_V1_2400 and ODBC_QUERY_STATS_V2_2400, the QUERY_ID field contains the unique ID of the query unless an error occurs before the query is launched. In this case, no query ID is assigned, and the corresponding field is populated with the value . The rest of the information pertaining to the query, including the error code, is stored in the appropriate columns. VIEW NEO.HP_METRICS.
Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description START_PRIORITY SMALLINT UNSIGNED NO DEFAULT Starting priority of the Master Executor process. MASTER_BUSY* SMALLINT UNSIGNED NO DEFAULT Cumulative node utilization percentage for the Master Executor process during the elapsed time of this query.
Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type 110 Description METRIC_CLASS_ID INTEGER UNSIGNED NO DEFAULT Repository internal. Identification number of the Query Runtime Statistics metric class (measurement) in the Metric Class Registry. METRIC_CLASS_VERSION INTEGER UNSIGNED NO DEFAULT Repository internal. Internal version number of the Query Runtime Statistics metric (measurement) in the Metric Class Registry.
Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type SQL_HEAP_ALLOCATED* 1 Description INTEGER SIGNED NO DEFAULT Amount of “heap” type (dynamic) memory, in kilobytes, allocated (reserved) for query processes (master executor and ESPs) at the beginning of query execution. The processes that execute the query request this memory during execution, thus the amount can change as the query runs.
Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type 112 Description DISK_IOS* LARGEINT SIGNED NO DEFAULT Number of physical disk I/O operations performed for this statement. LOCK_ESCALATIONS LARGEINT SIGNED NO DEFAULT Cumulative number of times record locks were escalated to file locks. LOCK_WAITS LARGEINT SIGNED NO DEFAULT Number of times the statement waited for a lock request (concurrency cost).
Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description EST_IO_TIME* DOUBLE PRECISION NO DEFAULT Estimate of the number of seconds of I/O time (seeks plus data transfer) to perform the I/O for this query. The estimate occurs at the time the query is prepared.
Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type 114 Description OPEN_BUSY_TIME* INTERVAL SECOND(12,6) NO DEFAULT Time this process spent doing file OPENs (in microseconds). PROCESS_CREATE_BUSY_TIME* INTERVAL SECOND(12,6) NO DEFAULT Time the Executor spent creating new processes (in microseconds).
Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description ROLE_NAME CHAR(18) NO DEFAULT Neoview user role name. QUERY_STATUS* CHAR(14) NO DEFAULT What the executor is doing (INITIAL, OPEN, EOF, CLOSE, DEALLOCATED, FETCH, CLOSE_TABLES, PREPARE, PROCESS_ENDED, UNKNOWN). For queries with actual end statistics, this value will be CLOSE or DEALLOCATED.
Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description COMPILE_END_TS TIMESTAMP(6) NO DEFAULT ANSI Timestamp, in Local Civil Time, indicating when query compilation ended. If this information is initially unavailable, it is assigned the value NULL and later updated with the correct value. This field is updated whenever the Repository updates the statistics.
Table E-6 ODBC_QUERY_STATS_V1 Field Definitions Field Name Data Type Description SEGMENT_ID INTEGER UNSIGNED Segment number of the NDCS server that reported the statistics. NODE_ID INTEGER UNSIGNED Processing node number of the NDCS server which reported the statistics. This is the node where the master executor process which executed the query was running. QUERY_EVENT_DATETIME TIMESTAMP(6) ANSI SQL timestamp in Local Civil Time when the statistics were recorded in the Repository.
Table E-6 ODBC_QUERY_STATS_V1 Field Definitions (continued) 118 Field Name Data Type Description ROWS_ACCESSED LARGEINT Cumulative number of rows accessed. ROWS_RETRIEVED LARGEINT Cumulative number of rows retrieved. NUM_ROWS_IUD LARGEINT Number of rows inserted, updated, deleted. QUERY_ELAPSED_TIME LARGEINT Total NDCS server real time, in microseconds, from the time the execute or execdirect started to the time the results were sent back.
Table E-6 ODBC_QUERY_STATS_V1 Field Definitions (continued) Field Name Data Type Description STATEMENT_TYPE CHARACTER(36) Compiler-provided value, one of: SQL_SELECT_UNIQUE, SQL_SELECT_NON_UNIQUE SQL_INSERT_UNIQUE, SQL_INSERT_NON_UNIQUE, SQL_UPDATE_UNIQUE, SQL_UPDATE_NON_UNIQUE, SQL_DELETE_UNIQUE, SQL_DELETE_NON_UNIQUE, SQL_CONTROL, SQL_SET_TRANSACTION, SQL_SET_CATALOG, SQL_SET_SCHEMA, SQL_OTHER, SQL_UNKNOWN, SQL_CALL_NO_RESULT_SETS, SQL_CALL_WITH_RESULT_SETS, SQL_SP_RESULT_SET, SQL_NOT_SUPPORTED.
This table indicates which fields are present for completed and incomplete queries. The source of data fields indicates whether the value is collected at the start of the query or at the end. Unless indicated here, field descriptions are those provided above for ODBC_QUERY_STATS_V1. Character columns use ISO88591 or UCS2 encoding, as appropriate to the Neoview platform configuration. If the platform is UCS2 or SJIS, all character columns are created with CHARACTER SET UCS2.
Table E-7 ODBC_QUERY_STATS_V2 Fields (continued) Complete Queries Incomplete Queries Data Type Source and Notes QUERY_START_DATETIME QUERY_START_DATETIME TIMESTAMP START QUERY_START_DATE QUERY_START_DATE DATE START QUERY_START_TIME QUERY_START_TIME TIME START QUERY_START_DATETIME_UTC QUERY_START_DATETIME_UTC TIMESTAMP START QUERY_START_DATE_UTC QUERY_START_DATE_UTC DATE START QUERY_START_TIME_UTC QUERY_START_TIME_UTC TIME START QUERY_END_DATETIME QUERY_START_DATETIME TIMESTAMP EN
Table E-7 ODBC_QUERY_STATS_V2 Fields (continued) Complete Queries Incomplete Queries Data Type Source and Notes ROWS_ACCESSED LARGEINT END, or NULL for an incomplete query ROWS_RETRIEVED LARGEINT END, or NULL for an incomplete query NUM_ROWS_IUD LARGEINT END, or NULL for an incomplete query TOTAL_EXECUTES LARGEINT END, or NULL for an incomplete query SQL_TEXT VARCHAR(3200) START NOTE: In both ODBC_QUERY_STATS_V1 and ODBC_QUERY_STATS_V2, the QUERY_ID field contains the unique ID of the quer
Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions Field Name (* means available only in this view) Data Type Description SUBMIT_TS TIMESTAMP(6) NO DEFAULT ANSI Timestamp indicating when the query first entered Workload Management Services. SEGMENT_ID SMALLINT UNSIGNED NO DEFAULT Segment number of the NDCS (connectivity) server that reported the statistics. This is the segment where the master executor process which executed the query was running.
Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 124 Description MASTER_BUSY_FOR_INTERVAL* SMALLINT UNSIGNED NO DEFAULT Node utilization for the Master Executor for the last interval. MIN_PRIORITY_ESP* SMALLINT UNSIGNED NO DEFAULT Minimum priority this Master Executor has had while executing.
Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type SQL_SPACE_ALLOCATED* 1 Description INTEGER SIGNED NO DEFAULT Amount of “space” type (static) memory, in kilobytes, allocated (reserved) for query processes (master executor and ESPs) at the beginning of query execution. Maximum value of SQL_SPACE_ALLOCATED reached over the life of the query.
Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type EID_HEAP_USED* 126 1 Description INTEGER SIGNED NO DEFAULT Amount of “heap” type (dynamic) memory, in kilobytes, actually used for the Executor in Disk (EID) processes involved in query execution. As the query executes, this value can increase or decrease. Maximum value of EID_HEAP_USED reached over the life of the query.
Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description EST_ACCESSED_ROWS* DOUBLE PRECISION NO DEFAULT Estimated number of rows to be accessed from Disk Process at runtime by EID (Executor in Disk Process). This value is meaningful for SELECT, not for Insert/Update/Delete. For Insert/Update/Delete, the value is reported as 0.0.
Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 128 Description MASTER_EXECUTOR_BUSY_TIME* INTERVAL SECOND(12,6) NO DEFAULT Calculated number of elapsed microseconds that the master executor spent on behalf of the query since the query began execution, computed as query execution end time minus query execution start time.
Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description STATEMENT_TYPE CHAR(21) NO DEFAULT The SQL statement as returned by the Neoview SQL compiler, one of SELECT_UNIQUE, SELECT_NON_UNIQUE, INSERT_UNIQUE, INSERT_NON_UNIQUE, UPDATE_UNIQUE, UPDATE_NON_UNIQUE, DELETE_UNIQUE, DELETE_NON_UNIQUE, CONTROL, SET_TRANSACTION, SET_CATALOG, SET_SCHEMA, CALL_NO_RESULT_SETS, CALL_WITH_RESULT_SETS, SP_RESULT_SET, OTHER, UNKNOWN.
Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type REFRESH_TS TIMESTAMP(6) NO DEFAULT ANSI Timestamp indicating when the Workload Management Services received the most recent runtime statistics from the Query Executor. LAST_UPDATE_TS TIMESTAMP(6) NO DEFAULT Repository-generated ANSI Timestamp showing when this row was last updated. You can use this value to monitor whether the query is still running.
F History of Field Changes for Repository This is a chronological history of changes to Repository. • “Summary of Changes for Repository 2.4 SP2” (page 131) • “Summary of Changes For Repository 2.4 SP1” (page 132) • “Summary of Changes for Repository 2.4” (page 133) • “Summary of Field Definition Changes for Repository 2.3” (page 133) • “Summary of Field Definition Changes for Repository 2.2” (page 135) Summary of Changes for Repository 2.4 SP2 These changes have been made to views between Repository 2.
Summary of Changes For Repository 2.4 SP1 These changes have been made to views between Repository 2.4 and Repository 2.4 SP1. If you have already modified queries to reflect the new views in Repository 2.4 SP1, ignore this section. New Repository View For Accessing Error Event Information EVENTS_VW1 is a new Repository view to access error event information. Use SQL to query this information and easily create reports that show you how these events are occurring across the platform.
Table F-1 Fields Not Available in Repository 2.
The following table shows how fields defined in the view QUERY_RUNTIME_STATS_V1 were renamed in Repository 2.3. Table F-2 Field Definition Changes for QUERY_RUNTIME_STATS_V1 from Repository 2.2 to Repository 2.3 Field Name in Repository 2.2 Change in Repository 2.3 SQL_PROCESS_BUSY_TIME Now has data type INTERVAL SECOND(12,6). QUE_TIME Now has data type INTERVAL SECOND(12,6). MASTER_EXECUTOR_BUSY_TIME Value is now precise to the microsecond, or NULL if data is unavailable.
Summary of Field Definition Changes for Repository 2.2 The following field names changed between Repository 2.1 and Repository 2.2. If you have already modified queries to reflect field names in Repository 2.2, you can ignore this section. The following table shows how fields defined in the view ACCESS_ODBCMX_QUERY_STATS_V1 were renamed in the view ODBC_QUERY_STATS_V1.
Table F-5 Field Name Mapping from ACCESS_ODBCMX_QUERY_STATS_V2 to ODBC_QUERY_STATS_V2 (continued) Field Name in ACCESS_ODBCMX_QUERY_STATS_V2 Field Name in ODBC_QUERY_STATS_V2 END_ENTRY_ID_LCT QUERY_END_DATETIME, QUERY_END_DATE, QUERY_END_TIME ESTIMATED_COST EST_COST ODBC_ELAPSED_TIME QUERY_ELAPSED_TIME ODBC_EXECUTION_TIME MASTER_EXEC_EXECUTION_TIME DISC_READS DISK_READS LOCKESCALATIONS LOCK_ESCALATIONS LOCKWAITS LOCK_WAITS MSGSBYTESTODISC MESSAGE_BYTES_TO_DISK MSGSTODISC MESSAGES_TO_DISK
Table F-7 Field Name Mapping from ACCESS_ODBCMX_SESSION_DATA_V2 to ODBC_SESSION_STATS_V2 (continued) Field Name in ACCESS_ODBCMX_SESSION_DATA_V2 Field Name in ODBC_SESSION_STATS_V2 END_ENTRY_ID_LCT SESSION_END_DATETIME, SESSION_END_DATE, SESSION_END_TIME START_TIME Column Removed - Duplicate of SESSION_START_TIME END_TIME Column Removed - Duplicate of SESSION_END_TIME TOTAL_ODBC_EXECUTION_TIME TOTAL_MASTER_EXEC_EXECUTION_TIME Summary of Field Definition Changes for Repository 2.
Glossary Collector process A Repository process that collects metrics for a monitored entity. Execdirect A SQL statement or call that executes a query directly, as opposed to using a previously prepared statement. Manageability Repository A database and related components for collecting metric data about resources on a Neoview platform and making that data available to consumers, by means of APIs.
Index A Architecture, Repository, 16 C Character set support, 17 CPU statistics (see Processing node statistics) D Default configuration, 17 Disk statistics NEO.HP_METRICS.DISK_STATS_V1, 42 Documents, related information, 12 E EMS Events NEO.HP_METRICS.
