HP Neoview Repository User Guide HP Part Number: 621366-001 Published: May 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.............................................
Examples and Guidelines for Creating Repository Queries....................................67 Overview...............................................................................................................................................67 Get the Number of Rows in a View......................................................................................................67 Retrieve a Specified Number of Rows from a View.............................................................................
Index...............................................................................................................................
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 3-26 3-27 3-28 C-1 C-2 C-3 C-4 C-5 C-6 C-7 D-1 D-2 D-3 D-4 D-5 D-6 D-7 QUERY_STATS_VW1 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..........................................................................................74 Search Event Logs for Abnormal Program Termination...............................................................75 Search Event Logs For a Specific Event Number..........................................................................76 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 N02.
Appendix B (page 83) Use this appendix to learn about the history of new and changed information in the Repository. Appendix C (page 85) Use this appendix to learn about the query statistics views before R2.4 SP2. Current data is available through these views. However, some fields now return NULL values and you must use the “two rows per query” semantics for query statistics.
{ } Braces Braces enclose required syntax items. For example: FROM { grantee[, grantee]...} A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.
Line Spacing If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections. For example: match-value [NOT] LIKE pattern [ESCAPE esc-char-expression] Related Documentation This manual is part of the HP Neoview customer library.
Neoview Performance Analysis Tools Online Help Context-sensitive help topics that describe how to use the Neoview Performance Analysis Tools to analyze and troubleshoot query-related issues on the Neoview data warehousing platform. Neoview Performance Analysis Tools User Guide Information about how to use the Neoview Performance Analysis Tools to analyze and troubleshoot query-related issues on the Neoview data warehousing platform.
Publishing History Part Number Product Version Publication Date 544570–001 Release 2.2 August 2007 544811–001 Release 2.3 April 2008 546262-001 Release 2.4 April 2009 587392-001 Release 2.4 Service Pack 1 (SP1) September 2009 621366–001 Release 2.4 Service Pack 2 (SP2) May 2010 HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs.
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 Process statistics, if enabled 30 days Process aggregation Level1 statistics, if enabled 30 days Table statistics 30 days Disk statistics 30 days Event data 30 days To modify these retention times, contact your HP Support representative.
3 Repository Views Overview You can use the following views for access to the Repository: • • • • • • • • • • • • “VIEW NEO.HP_METRICS.QUERY_STATS_VW1” (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 38) “VIEW NEO.HP_METRICS.PROCESS_STATS_V1” (page 40) “VIEW NEO.HP_METRICS.PROCESS_AGGR_LEVEL1_STATS_V1” (page 43) “VIEW NEO.HP_METRICS.
Table 3-1 QUERY_STATS_VW1 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_VW1 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_VW1 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_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_
Table 3-1 QUERY_STATS_VW1 Field Definitions (continued) Field Name Data Type Description 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.791758 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.
Table 3-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 3-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 3-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 3-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 3-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 3-1 QUERY_STATS_VW1 Field Definitions (continued) 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 3-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 3-1 QUERY_STATS_VW1 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. VIEW NEO.HP_METRICS.
Table 3-4 ODBC_SESSION_STATS_V2 Fields (continued) Complete Sessions Incomplete Sessions Data Type Source and Notes APPLICATION_ID APPLICATION_ID CHAR(130) START COMPONENT_NAME COMPONENT_NAME CHAR(20)\ START DATASOURCE DATASOURCE CHAR(128) START SEQUENCE_NUM SEQUENCE_NUM SMALLINT START UNSIGNED SESSION_START_DATETIME SESSION_START_DATETIME TIMESTAMP START SESSION_START_DATE SESSION_START_DATE DATE START SESSION_START_TIME SESSION_START_TIME TIME START SESSION_START_DATETIME_UTC SES
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 Processing Node Statistics The Repository stores one row for each probe of a processing node (CPU).
Table 3-5 NODE_STATS_V1 Field Definitions (continued) Field Name Data Type Description MEASURE_END _TIME_UTC TIME(6) ANSI SQL time, in Coordinated Universal Time, indicating when the measurement interval ended. MEASURE_START_DATETIME TIMESTAMP(6) ANSI SQL timestamp, in Local Civil Time, indicating when the measurement interval began. MEASURE_START_DATE DATE ANSI SQL date, in Local Civil Time, indicating when the measurement interval began.
Table 3-5 NODE_STATS_V1 Field Definitions (continued) Field Name Data Type Description STARTING_FREE_MEM LARGEINT Number of free memory frames (physical pages) when the processing node was loaded. ENDING_FREE_MEM LARGEINT Number of free memory frames (physical pages) at the end of the measurement interval. SVNET_READ_BYTES1 LARGEINT Number of bytes read by this processing node, if NODE_ID is 1. Othewise, this field is not populated.
Table 3-6 PROCESS_STATS_V1 Field Definitions Field Name Data Type Description SEGMENT_ID INTEGER UNSIGNED NO DEFAULT Segment number of the process NODE_ID INTEGER UNSIGNED NO DEFAULT Processing node number of the process being measured PIN INTEGER UNSIGNED NO DEFAULT Unique number.
Table 3-6 PROCESS_STATS_V1 Field Definitions (continued) 42 Field Name Data Type Description PROCESS_NAME CHARACTER(8) Name of the process being measured PROGRAM_FILE_NAME CHARACTER(24) Object file name of the process PRIORITY INTEGER UNSIGNED Creation priority of the measured process USERID NUMERIC(9, 0) Internal representation of a Neoview role USERID_GROUP SMALLINT UNSIGNED Part of the internal representation of a Neoview role USERID_USER SMALLINT UNSIGNED Part of an internal repres
Table 3-6 PROCESS_STATS_V1 Field Definitions (continued) Field Name Data Type Description RETURNED_BYTES LARGEINT Number of message bytes received by the process MESSAGES_RECEIVED LARGEINT Number of messages that the process has read from its message input queue RECEIVED_BYTES LARGEINT Number of message bytes received by the process REPLY_BYTES LARGEINT Number of message bytes sent by this process in reply to a request from another process FILE_OPEN_CALLS LARGEINT Number of calls to open re
Table 3-7 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions 44 Field Name Data Type SEGMENT_ID SMALLINT UNSIGNED Segment number of the parent process SAMPLE_DATETIME TIMESTAMP(6) ANSI SQL timestamp of the aggregation, in Local Civil Time. SAMPLE_DATE DATE ANSI SQL date of the aggregation, in Local Civil Time. 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.
Table 3-7 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) Field Name Data Type Description TOTAL_CHILD_BUSY_TIME LARGEINT Sum of CPU busy time, in microseconds, for all processes described by this record, since the last aggregation 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
Table 3-7 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) 46 Field Name Data Type Description AVG_HISTOGRAM_95 LARGEINT Average percent busy of all processes with a CPU busy percentage greater than 95 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 d
Table 3-7 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) Field Name Data Type Description MAX_NUMBER_OF_DISPATCHES INTEGER UNSIGNED Among processes that had more than 30 dispatches, the greatest number of dispatches for any process 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 pr
• • 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 in condition reported by the table monitoring process. Thus, the view includes one row for every data collection interval.
Table 3-8 TABLE_STATS_V2 and TABLE_STATS_DETAIL_V2 Field Definitions (continued) Field Name Data Type Description SCHEMA_NAME CHAR(128) Name of applicable schema for the object (e.g., the table). OBJECT_NAME CHAR(128) Name of the table, index, or materialized view.
Table 3-8 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.
Table 3-9 DISK_STATS_V1 Field Definitions (continued) Field Name Data Type Description PRIMARY_STATE CHAR(12) State of the primary disk, one of: 'Up' 'Down' 'Special' 'Mounted' 'Reviving' 'Testing' 'Exclusive' 'Hard down' 'Formatting' 'No processor' MIRROR_STATE CHAR(12) State of the mirror disk (see PRIMARY_STATE for possible values) PRIMARY_PATH CHAR(2) Path in use for communication with the primary disk, one of: • 'P' — Primary path in use • 'B' — Backup path in use • '-' — Neither path in use
Table 3-9 DISK_STATS_V1 Field Definitions (continued) Field Name Data Type Description STATUS SMALLINT UNSIGNED Numerical equivalent of the status text: 1 means 'Exists' 2 means 'Up' 3 means 'Low' 4 means 'Medium' 5 means 'High' 6 means 'Warning' 7 means 'Critical' 8 means 'Down' 9 means 'Questionable' 52 LARGEST_FRAGMENT DECIMAL(12,1) Size of the largest disk fragment available, in megabytes READS_PER_SEC DECIMAL(9,1) Number of disk process read operations per second for this interval, for the
Table 3-9 DISK_STATS_V1 Field Definitions (continued) Field Name Data Type Description INPUT_KB_PER_SEC_MIR DECIMAL(9,1) Input kilobytes per second during this interval for the mirror disk OUTPUT_KB_PER_SEC_MIR DECIMAL(9,1) Output kilobytes per second during this interval for the mirror disk SUBMIT_TS_UTC TIMESTAMP(6) Timestamp, in Universal Coordinated Time, when the measurement was submitted to the Repository UPDATE_TS TIMESTAMP(6) Timestamp, in Local Civil Time, when the measurement was sto
Table 3-10 EVENTS_VW1 Field Definitions (continued) Field Name Data Type Description SEQ_NUMBER INTEGER UNSIGNED Primary Key A sequence number used to differentiate between two events that have identical LOG and GENERATED timestamps. LOG_TIMESTAMP_UTC TIMESTAMP(6) Timestamp, in Coordinated Universal Time, indicating when the event was recorded in the EMS log. GENERATED_TIMESTAMP_LCT TIMESTAMP(6) Timestamp, in Local Civil Time, indicating when the event was generated.
Table 3-10 EVENTS_VW1 Field Definitions (continued) Field Name Data Type Description FSERROR INTEGER, nullable File system error, if related to an SQL error. Null for any other event. TEXT VARCHAR(5000) Text description of the EMS event. If the Neoview platform is configured for UTF8 or SJIS character sets, this column uses UCS2. Otherwise, this column uses the ISO88591 character set.
Table 3-11 SPACE_PARTITION_DETAIL_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-11 SPACE_PARTITION_DETAIL_VW1 Field Definitions (continued) Field Name Data Type Description SIZE_CURR_BYTES LARGEINT Current data size, in bytes, of the partition, determined by the End of File (EOF) indicator MAX_SIZE_CURR_BYTES LARGEINT Maximum size, in bytes, of the partition PRIMARY_EXTENT_CURR LARGEINT Primary extent size in bytes SECONDARY_EXTENT_CURR LARGEINT Secondary extent size in bytes MAX_EXTENT_CURR LARGEINT Maximum extent size in bytes ALLOC_EXTENT_CURR LARGEINT Al
Table 3-11 SPACE_PARTITION_DETAIL_VW1 Field Definitions (continued) Field Name Data Type Description ALLOCATED_SIZE_BYTES LARGEINT Size allocated by the file system FULL_PCT NUMERIC(128, 2) Percentage of disk space currently in use VIEW NEO.HP_METRICS.SPACE_PARTITION_DETAIL_HISTORY_VW1 The Space Management Client in HPDM accesses this view to obtain aging historical data about the table partitions. You can also run queries directly against this view.
Table 3-12 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions (continued) Field Name Data Type Description OBJECT_TYPE CHAR(2) Object type, which can be: BT (Base Table) MV (Materialized View) IX (Index) ROW_TYPE CHAR(1) Type of aggregation in the row: H (Hourly Aggregation Row) D (Daily Aggregation Row) W (Weekly Aggregation Row) M (Monthly Aggregation Row) Q (Quarterly Aggregation Row) Y (Yearly Aggregation Row) MOST_GRANULAR CHAR(1) Indicates whether the row is the most granular row for any
Table 3-12 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions (continued) 60 Field Name Data Type Description MAX_SIZE_CURR_BYTES LARGEINT Maximum size, in bytes, of the partition MAX_SIZE_AVG_BYTES LARGEINT Average value of MAX_SIZE_CURR_BYTES MAX_SIZE_MIN_BYTES LARGEINT Minimum value of MAX_SIZE_CURR_BYTES MAX_SIZE_MAX_BYTES LARGEINT Maximum value of MAX_SIZE_CURR_BYTES PRIMARY_EXTENT_CURR LARGEINT Primary extent size in bytes PRIMARY_EXTENT_AVG LARGEINT Average value of PRIMARY_EX
Table 3-12 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions (continued) Field Name Data Type Description STATS_CURR_UTC_JTS LARGEINT Timestamp, in Coordinated Universal Time, when UPDATE STATISTICS was last run STATS_CURR_LCT_JTS LARGEINT Timestamp, in Local Civil Time, when UPDATE STATISTICS was last run INDEX_LEVELS_CURR LARGEINT Index level of the last UPDATE STATISTICS operation INDEX_LEVELS_AVG LARGEINT Average value of INDEX_LEVELS_CURR INDEX_LEVELS_MIN LARGEINT Minimum value of I
Sample Query Views for Space Management You can use these sample query views to obtain various reports from the Repository views for space management. These sample queries are provided as views named SPACE_Q_query-name in the NEO.HP_METRICS schema. To return space management data from one of the sample query views, run a SELECT statement like this one: SELECT * FROM NEO.HP_METRICS.SPACE_Q_query-name; For example, to see the SPACE_Q_CATALOG_SIZE_VW1 view, use this query: SELECT * FROM NEO.HP_METRICS.
SPACE_Q_OBJECT_GROWTH_VW1 Table 3-16 SPACE_Q_OBJECT_GROWTH_VW1 Description: This report provides historical data of object growth for a given time period, from the earliest to the most recent time, and includes the current, average, maximum, and minimum sizes, in megabytes, of the objects on the Neoview platform.
SPACE_Q_TABLES_BY_SCHEMA_VW1 Table 3-20 SPACE_Q_TABLES_BY_SCHEMA_VW1 Description: This report lists the tables in each schema on the Neoview platform. Columns Included: SCHEMA_NAME OBJECT_NAME Views Used: NEO.HP_METRICS.SPACE_PARTITION_DETAIL_VW1 SPACE_Q_TABLE_FULL_VW1 Table 3-21 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.
SPACE_Q_TOP_10_NON_PARTITION_DETAIL_VW1 Table 3-24 SPACE_Q_TOP_10_NON_PARTITION_DETAIL_VW1 Description: This report lists the non-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.
SPACE_Q_TOP_10_USER_SPACE_SIZE_VW1 Table 3-28 SPACE_Q_TOP_10_USER_SPACE_SIZE_VW1 66 Description: This report lists the top 10 users who are consuming the most space, in megabytes, on the Neoview system. Columns Included: FILE_OWNER USER_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 Neoview Reports product. Queries in this section are simple examples provided as an introduction to the Repository.
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 ,(QUERY_ELAPSED_TIME) AS QUERY_ELAPSED_TIME ,(cLIENT_NAME) AS CLIENT_NAME from NEO.
SUM(MESSAGE_BYTES_TO_DISK) as TTL_MESSAGE_BYTES_TO_DISK, SUM(NUM_ROWS_IUD) as TTL_NUM_ROWS_IUD, SUM(ROWS_ACCESSED) as TTL_ROWS_ACCESSED, SUM(ROWS_RETRIEVED) as TTL_ROWS_RETRIEVED, SUM(LOCK_ESCALATIONS) as TTL_LOCK_ESCALATIONS, SUM(LOCK_WAITS) as TTL_LOCK_WAITS FROM NEO.HP_METRICS.
Completed Queries Executed on a System in the Previous Two Days To count queries that executed from one midnight to the next, instead of within a 24 hour period relative to the current time, the following query uses the Neoview DATE_TRUNC function in the SQL WHERE clause. SELECT DISTINCT (CAST(EXEC_START_LCT_TS AS COUNT(*) AS NUM_OF_QUERIES, MAX(CAST(EXEC_START_LCT_TS AS TIME(6))) AS MIN(CAST(EXEC_START_LCT_TS AS TIME(6))) AS FROM NEO.HP_METRICS.
Queries within the Past 24 Hours, Grouped by Client Name and Including Average and Maximum Elapsed Time SELECT client_name, COUNT(*) AS num_of_queries, AVG(query_elapsed_time)/1000 AS avg_qry_elapsed_msec, MAX(query_elapsed_time)/1000 AS max_qry_elapsed_msec FROM NEO.HP_METRICS.
Minor formatting of the columns can yield a more readable result: select [first 10] left (datasource, 25) as datasource1, client_name, count(*) as query_count from NEO.HP_METRICS.QUERY_STATS_VW1 where query_status = 'INIT' group by 1, client_name order by 1, client_name; DATASOURCE1 CLIENT_NAME QUERY_COUNT ------------------------- ----------- ---- -------------------ARSQLF exedl1-1.
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 or Neoview Reports 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 53).
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-2 Search Event Logs for Abnormal Program Termination This report performs a case-insensitive search for all events in which the event text contains the string 'ABEND' 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 01FEB10 001.01.
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-4 Search Event Logs for Specific Event Text This report performs a case-insensitive search for events in which the event text contains the entered text string and 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-6 Search Event Logs for a Process Name This report lists all events for a full or partial process name and 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-8 Search Event Logs for Subsystem This report searches for all events for full or partial subsystem name and 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 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 1 (SP1) edition of this manual includes the following new and changed information: • Data retention times were changed. See “Configuring Retention Times for Data” (page 17). • Removed references to NCHAR and NCHAR VARYING. • Added a new view for accessing data from error event logs: NEO.HP_METRICS.
• Naming changes to reflect the fact that the product formerly called Neoview Script is now called Neoview Command Interface. • Deletion of text and descriptions of prepackaged queries, which are no longer part of the Repository product. (The Neoview Reports product includes prepackaged queries. For more information, see the Neoview Reports Online Help.) • Removal of references to the table census utility, which is superseded by new functions in the Table data collector. The R2.
C Pre-R2.4 SP2 Query Statistics Views 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 19) provides access to all query statistics, including new query statistics metrics • “VIEW NEO.HP_METRICS.
These three views provide access to R2.4 query and runtime statistics information. This information is maintained on the Neoview platform for your convenience, but it will not change, nor are the tables maintained. These three views use the pre-R2.4 SP1 "two rows per query" semantics of query statistics. 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.
Table C-2 ODBC_QUERY_STATS_V1_2400 Field Definitions (continued) Field Name Data Type Description QUERY_EVENT_DATETIME_UTC TIMESTAMP(6) ANSI SQL timestamp in Coordinated Universal Time when the statistics were recorded in the Repository. 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.
Table C-2 ODBC_QUERY_STATS_V1_2400 Field Definitions (continued) 88 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 C-2 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 C-3 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 C-4 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description PIN SMALLINT UNSIGNED NO DEFAULT Process identification number of the NDCS server which reported the statistics. This is the node where the master executor process which executed the query was running. START_PRIORITY SMALLINT UNSIGNED NO DEFAULT Starting priority of the Master Executor process.
Table C-4 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type 94 Description LAG_ELAPSED_TIME* INTERVAL MINUTE(6) NO DEFAULT Difference in minutes between the time when WMS last refreshed statistics from RMS. (REFRESH_TS value) and the time when the Repository last wrote this row (LAST_UPDATE_TS). Indicates lag time, if any, between WMS statistics and Repository statistics.
Table C-4 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type SQL_SPACE_USED* 1 Description INTEGER SIGNED NO DEFAULT Amount of “space” type (static) memory, in kilobytes, actually used for query processes (master executor and ESPs) during query execution. It should be less than or equal to the SQL_SPACE_ALLOCATED value. Maximum value of SQL_SPACE_USED reached over the life of the query.
Table C-4 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type 96 Description STATS_ERROR_CODE* INTEGER SIGNED NO DEFAULT 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.
Table C-4 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description EST_COST* DOUBLE PRECISION NO DEFAULT Estimated cost (time in seconds) of the SQL operations in the Master Executor. The estimate occurs at the time the query is prepared. 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.
Table C-4 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type 98 Description COMPILE_ELAPSED_TIME* INTERVAL SECOND(12,6) NO DEFAULT Calculated compile time in microseconds, computed as compilation end time minus compilation start time. If compilation start time (COMPILE_START_TS), end time (COMPILE_END_TS), or both are NULL, this value is also NULL; when those values are updated, this value is also updated.
Table C-4 QUERY_RUNTIME_STATS_V1_2400 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 C-4 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description 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.
QUERY_STATS_VW1 and SQL_TEXT_VW1 so that you can obtain query statistics in a single row. CAUTION: Although your pre-R2.4 SP1 queries will not fail due to view changes, some fields are no longer available in ODBC_QUERY_STATS_V1, ODBC_QUERY_STATS_V2, and QUERY_RUNTIME_STATS_V1. These fields now return NULL values when you run queries using these views. For details, see Table D-1 (page 118). VIEW NEO.HP_METRICS.
Table C-5 ODBC_QUERY_STATS_V1 Field Definitions (continued) 102 Field Name Data Type Description SEQUENCE_NUM SMALLINT UNSIGNED Message sequence number generated by NDCS. Numbering starts at 0 and is incremented by 1 for each new record. METRIC_CLASS_ID INTEGER UNSIGNED Repository assigned value. Used internally. METRIC_CLASS_VERSION INTEGER UNSIGNED Repository assigned value. Used internally.
Table C-5 ODBC_QUERY_STATS_V1 Field Definitions (continued) Field Name Data Type Description DATASOURCE CHARACTER(128) Client data source name used at the time of the connection. APPLICATION_ID CHARACTER(130) Client application name or main window caption. CLIENT_ID CHARACTER(15) Client workstation TCP/IP or NetBios/network name. SEGMENT_NAME CHARACTER(10) Name of the Neoview segment used by the data source.
Table C-5 ODBC_QUERY_STATS_V1 Field Definitions (continued) Field Name Data Type Description QUERY_ID VARCHAR(160) A unique ID for the query, beginning with the string “MXID” and including, among other elements a unique query number, delimited by underscores, and an ODBC-generated statement ID. If an error occurred before the query launched, the value in this field is the text . SQL_TEXT VARCHAR(3200) Text of the query.
Table C-6 ODBC_QUERY_STATS_V2 Fields (continued) Complete Queries Incomplete Queries Data Type Source and Notes USER_ID USER_ID CHAR(8) START USER_NAME USER_NAME CHAR(128) START CLIENT_ID CLIENT_ID CHAR(15) START APPLICATION_ID APPLICATION_ID CHAR(130) START DATASOURCE DATASOURCE CHAR(128) START QUERY_ID QUERY_ID when available VARCHAR(160) END STATEMENT_ID STATEMENT_ID CHAR(30) START STATEMENT_TYPE STATEMENT_TYPE CHAR(36) END STATEMENT_STATE STATEMENT_STATE CHAR(14) 'C
Table C-6 ODBC_QUERY_STATS_V2 Fields (continued) Complete Queries Incomplete Queries Data Type Source and Notes QUERY_END_TIME_UTC QUERY_START_TIME_UTC TIME END, or NULL for an incomplete query EST_COST DOUBLE PRECISION START, else END, else NULL QUERY_ELAPSED_TIME LARGEINT END, or NULL for an incomplete query MASTER_EXECUTION_TIME LARGEINT END, or NULL for an incomplete query DISK_READS LARGEINT END, or NULL for an incomplete query LOCK_ESCALATIONS LARGEINT END, or NULL for an incompl
Because statistics are updated as the query executes, you can use this view to monitor running queries. With regard to when query statistics will be present, a single row is written per query execution. Thus, if the same query is executed 3 times, 3 rows will be present. If a query runs for a long time (that is, longer than the configured data collection interval), the statistics for that query will be updated at each data collection interval.
Table C-7 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 108 Description PCT_BUSY_ALL_PROCESSES* SMALLINT UNSIGNED NO DEFAULT Percentage of available processing node resources (for the whole Neoview platform) used by the Master Executor process and all its child processes for this query. BUSIEST_ESP_NODE* SMALLINT UNSIGNED NO DEFAULT Node of the busiest Executor Server Process involved in the query.
Table C-7 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description ERROR_CODE INTEGER SIGNED NO DEFAULT SQL error code, if any, returned in response to this query. The value is 100 for completed SELECT, UPDATE, or DELETE statements with actual end statistics. The value is 0 for completed INSERT queries or for uncompleted queries. A negative number indicates an execution-type error.
Table C-7 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type SQL_HEAP_USED* 110 1 Description INTEGER SIGNED NO DEFAULT Amount of “heap” type (dynamic) memory, in kilobytes, actually used in for query processes (master executor and ESPs) at the beginning of query execution. As the query executes, this value can increase or decrease. Maximum value of SQL_HEAP_USED reached over the life of the query.
Table C-7 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description ROWS_ACCESSED LARGEINT SIGNED NO DEFAULT Number of records accessed by the disk process or the file system to evaluate the statement. ROWS_RETRIEVED LARGEINT SIGNED NO DEFAULT Cumulative number of rows retrieved. NUM_ROWS_UID LARGEINT SIGNED NO DEFAULT Number of rows updated/inserted/deleted.
Table C-7 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 112 Description EST_IDLE_TIME* DOUBLE PRECISION NO DEFAULT Estimate of the number of seconds to wait for an operation to complete, (e.g., opening a table or starting a process). The estimate occurs at the time the query is prepared. EST_NODE_TIME* DOUBLE PRECISION NO DEFAULT Estimate of the number of seconds of processor time it might take to execute the query.
Table C-7 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description QUERY_ELAPSED_TIME INTERVAL MINUTE(18) NO DEFAULT Elapsed clock time, in minutes, since the query started executing, including compile time and time spent in the WMS queue. Sum of MASTER_EXECUTOR_BUSY_TIME, COMPILE_ELAPSED_TIME, and QUE_TIME. SESSION_ID CHAR(104) NO DEFAULT Unique session ID generated by the NDCS SQL server at the time the connection is established.
Table C-7 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 114 Description 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 C-7 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description EXEC_START_TS TIMESTAMP(6) NO DEFAULT ANSI Timestamp, in Local Civil Time, indicating when query execution began. If the query has not yet begun execution, the value is NULL. EXEC_END_TS TIMESTAMP(6) NO DEFAULT ANSI Timestamp indicating when query execution ended, converted to LCT from UTC. If the query has not yet begun execution, the value is NULL.
D History of Field Changes for Repository This is a chronological history of changes to Repository. • “Summary of Changes for Repository 2.4 SP2” (page 117) • “Summary of Changes For Repository 2.4 SP1” (page 118) • “Summary of Changes for Repository 2.4” (page 119) • “Summary of Field Definition Changes for Repository 2.3” (page 119) • “Summary of Field Definition Changes for Repository 2.2” (page 121) 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 D-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 D-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 D-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 D-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 collected during query execution, 92, 106 comparison of ODBC and query runtime statistics, 85 ODBC pre Release 2.4 Sp1, 89, 104 separate rows for start and end of query, 19 ODBC, separate rows for start and end of query, 101 ODBC, Pre– Release 2.
