HP Neoview Repository User Guide HP Part Number: 587392-001 Published: September 2009 Edition: HP Neoview Release 2.
© Copyright 2009 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....................................75 Overview...............................................................................................................................................75 Get the Number of Rows in a View......................................................................................................75 Retrieve a Specified Number of Rows from a View.............................................................................
List of Figures 1-1 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 B-1 B-2 B-3 C-1 C-2 C-3 C-4 C-5 C-6 C-7 6 QUERY_STATS_VW1 Field Definitions........................................................................................23 SQL_TEXT_VW1 Field Definitions...............................................................................................35 Comparison of ODBC Query Statistics and Query Runtime Statistics.........................................
List of Examples A-1 A-2 A-3 A-4 A-5 A-6 A-7 A-8 A-9 Generate Update Stats Command List..........................................................................................88 Search Event Logs for Abnormal Program Termination...............................................................89 Search Event Logs For a Specific Event Number..........................................................................90 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.04 SP1and subsequent N-series RVUs until otherwise indicated in a replacement publication.
• • Added Appendix C (page 115) and moved the Repository history from Chapter 3 (page 21) to this appendix. Removed new and changed manual information for previous editions to its own section, leaving “New and Changed Information in This Edition” (page 9) for the current edition only. New and Changed Information in Previous Editions The R2.4 edition of this manual included the following new and changed information: • Clarify and correct text related to space and heap.
Chapter 3: Repository Views Use this chapter to learn about the available Repository views, including the meanings of statistics and other data available through the views. Chapter 4: Examples and Guidelines for Creating Repository Queries Use this chapter to see several simple examples of Repository queries and to learn about guidelines for creating your own queries. Appendix A (page 87) Use this chapter to see sample Repository queries of error and event information on the Neoview platform.
A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: DROP SCHEMA schema [CASCADE] [RESTRICT] DROP SCHEMA schema [ CASCADE | RESTRICT ] { } Braces Braces enclose required syntax items. For example: FROM { grantee[, grantee]...
DAY (datetime-expression) DAY(datetime-expression) If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items: myfile.sh 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.
Neoview DB Admin Online Help Context-sensitive help topics that describe how to use the HP Neoview DB Admin management interface. Neoview Management Dashboard Information on using the Dashboard Client, including how to install the Client, Client Guide for Database start and configure the Client Server Gateway (CSG), use the Client windows Administrators and property sheets, interpret entity screen information, and use Command and Control to manage queries from the Client.
Neoview Messages Manual Cause, effect, and recovery information for error messages. README for HP Neoview Release 2.4 Service Pack 1 Information about new features for the current release, including where to download software and obtain documentation. Publishing History Part Number Product Version Publication Date NA Release 2.0 February 2007 NA Release 2.1 May 2007 544570–001 Release 2.2 August 2007 544811–001 Release 2.3 April 2008 546262-001 Release 2.
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.
The Repository is related to another Neoview manageability product, called the Neoview Management Dashboard. That product is described in the Neoview Management Dashboard Client Guide for Database Administrators.
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. 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. The upgrade from Release 2.4 to Release 2.4 SP1 moves the R2.4 data to a different table.
By default, statistics are collected for queries, connectivity sessions, disks, processing nodes, and system events. Table data is not available by default. NOTE: For support of query statistics, Neoview Workload Management Services (WMS) must also be running. It is installed and started by default. For more information, see the Neoview Workload Management Services Guide. To change your configuration and the retention times for query statistics, 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 22) “VIEW NEO.HP_METRICS.SQL_TEXT_VW1” (page 34) “VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V1” (page 99) “VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V2” (page 102) “VIEW NEO.HP_METRICS.QUERY_RUNTIME_STATS_V1” (page 105) “VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V1_2400” (page 36) “VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V2_2400” (page 39) “VIEW NEO.HP_METRICS.
Query Statistics NOTE: As of Neoview Release 2.4 SP1, these three views should no longer be used to retrieve query information: • ODBC_QUERY_STATS_V1 • ODBC_QUERY_STATS_V2 • QUERY_RUNTIME_STATS_V1 These views access current query statistics, so your pre-SP1 queries continue to work. However, adjust your applications as soon as possible to use the QUERY_STATS_VW1 and SQL_TEXT_VW1 views. CAUTION: Even though your pre-R2.
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. Example: 2009-08-17 07:47:28.795392 EXEC_START_UTC_TS TIMESTAMP(6) NO DEFAULT Primary Key ANSI Timestamp, in Coordinated Universal Time, indicating when query execution began. Example:2009-08-17 15:47:28.
Table 3-1 QUERY_STATS_VW1 Field Definitions (continued) Field Name Data Type Description SESSION_ID CHARACTER(108) DEFAULT NULL Unique session ID generated by the NDCS server when the ODBC/JDBC connection was established. Example: MXID010010905082121170905693062730000000 26312DEV.USER2400 TIP: The session ID is fully contained within the query ID. To find all queries executed within a specific session, join the session statistics views and the query statistics view on the session ID.
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) 32 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 MESSAGE_BYTES_TO_DISK LARGEINT DEFAULT NULL Size, in bytes, of the total number of messages sent to the Encapsulated SQL Access Manager (ESAM). MESSAGES_TO_DISK LARGEINT DEFAULT NULL Number of messages sent to ESAM. That is, the number of messages exchanged between the file system and the disk process.
up to 16000 characters. If the complete query text is written across multiple rows, FRAGMENT_NUMBER is incremented for each row, starting with 0. 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. If the platform is ISO88591, all CHAR columns are created with CHARACTER SET ISO88591. The primary key for SQL_TEXT_VW1 field definition is QUERY_ID and FRAGMENT_NUMBER.
NOTE: Many of the differences reflect features of Neoview Workload Management Services (WMS). For information about that product, see the Neoview Workload Management Services Guide. Table 3-3 Comparison of ODBC Query Statistics and Query Runtime Statistics ODBC Query Statistics Query Runtime Statistics Monitors queries from ODBC and JDBC. Monitors queries managed by WMS. Execution metrics become available at the end of the query. Execution metrics are updated while the query is running.
Table 3-4 ODBC_QUERY_STATS_V1_2400 Field Definitions (continued) Field Name Data Type Description QUERY_EVENT_DATE DATE ANSI SQL date in Local Civil Time when the statistics were recorded in the Repository. QUERY_EVENT_TIME TIME(6) ANSI SQL time in Local Civil Time when the statistics were recorded in the Repository. QUERY_EVENT_DATETIME_UTC TIMESTAMP(6) ANSI SQL timestamp in Coordinated Universal Time when the statistics were recorded in the Repository.
Table 3-4 ODBC_QUERY_STATS_V1_2400 Field Definitions (continued) 38 Field Name Data Type Description MASTER_EXEC_EXECUTION_TIME LARGEINT Node processing time. TOTAL_EXECUTES LARGEINT Total number of executes performed on this statement. PIN INTEGER UNSIGNED Process identification number of the NDCS server. 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.
Table 3-4 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.
whose length exceeds 3200 bytes. The QUERY_TEXT_IS_MULTIROW column also designates multirow queries. 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.
Table 3-5 ODBC_QUERY_STATS_V2_2400 Fields (continued) Complete Queries Incomplete Queries Data Type Source and Notes SEQUENCE_SIZE SEQUENCE_SIZE SMALLINT UNSIGNED START 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
Table 3-5 ODBC_QUERY_STATS_V2_2400 Fields (continued) Complete Queries Incomplete Queries Data Type Source and Notes TOTAL_EXECUTES LARGEINT END, or NULL for an incomplete query SQL_TEXT VARCHAR(3200) START 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 .
Table 3-6 QUERY_RUNTIME_STATS_V1_2400 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 3-6 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type 44 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 3-6 QUERY_RUNTIME_STATS_V1_2400 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 3-6 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type EID_HEAP_USED* 46 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 3-6 QUERY_RUNTIME_STATS_V1_2400 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 3-6 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type 48 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 3-6 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description APPLICATION_ID CHAR(16) NO DEFAULT Name of the application used to connect to the database and send the query; e.g., ODBC, FASTJDBC.
Table 3-6 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.
SET UCS2. If the platform is ISO88591, all CHAR columns are created with CHARACTER SET ISO88591. The primary key for ODBC_SESSION_STATS_V1 field definition is SEGMENT_ID, NODE_ID, and SESSION_EVENT_DATETIME_UTC. Table 3-7 ODBC_SESSION_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 that generated the session.
Table 3-7 ODBC_SESSION_STATS_V1 Field Definitions (continued) 52 Field Name Data Type Description TOTAL_CLOSES LARGEINT Total number of SQL CLOSE calls sent by the HP ODBC driver to the NDCS SQL server. TOTAL_DELETE_STMTS_EXECUTED LARGEINT Total number of SQL DELETE statements sent by the HP ODBC driver to the NDCS SQL server. Does not include internally generated SQL statements. TOTAL_ERRORS LARGEINT Total number of SQL statement execution errors detected by the NDCS SQL server.
Table 3-7 ODBC_SESSION_STATS_V1 Field Definitions (continued) Field Name Data Type Description START_PRIORITY SMALLINT UNSIGNED Execution priority of the NDCS process at the time the connection request was received. SESSION_STATUS CHAR(5) START if this record contains information collected at the start of the session, or END if it contains statistics collected at the end of the session. APPLICATION_ID CHAR(130) Client application name or main window caption.
Table 3-8 ODBC_SESSION_STATS_V2 Fields Complete Sessions Incomplete Sessions Data Type Source and Notes SEGMENT_NAME SEGMENT_NAME CHAR(10) START SEGMENT_ID SEGMENT_ID INTEGER UNSIGNED START NODE_ID NODE_ID INTEGER UNSIGNED START PIN PIN INTEGER UNSIGNED START START_PRIORITY START_PRIORITY SMALLINT UNSIGNED END, or NULL for an incomplete session PROCESS_NAME PROCESS_NAME CHAR(18) START SESSION_ID SESSION_ID CHAR(108) START USER_ID USER_ID CHAR(8) START USER_NAME USER_NAME
Table 3-8 ODBC_SESSION_STATS_V2 Fields (continued) 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 TOTAL
Table 3-8 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-9 NODE_STATS_V1 Field Definitions (continued) Field Name Data Type Description 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 time, in Coordinated Universal Time, indicating when the measurement interval ended.
Table 3-9 NODE_STATS_V1 Field Definitions (continued) Field Name Data Type Description DISC_IOS LARGEINT Number of I/O disk transfers performed by disk processes in this processing node. CACHE_HITS LARGEINT Number of times the required block was found in cache during an I/O operation. STARTING_FREE_MEM LARGEINT Number of free memory frames (physical pages) when the processing node was loaded.
In this view, all CHARACTER columns use ISO88591 encoding, regardless of the underlying Neoview platform configuration. The primary key for PROCESS_STATS_V1 field definition is SEGMENT_ID, NODE_ID, PIN, and METRIC_CLASS_ID.
Table 3-10 PROCESS_STATS_V1 Field Definitions (continued) 60 Field Name Data Type Description MEASURE_START_TIME_UTC TIME(6) ANSI SQL time, in Coordinated Universal Time, indicating when the measurement interval began DELTA_TIME LARGEINT Duration of the measurement in microseconds SEGMENT_NAME CHARACTER(8) Neoview segment name 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 p
Table 3-10 PROCESS_STATS_V1 Field Definitions (continued) Field Name Data Type Description RECV_QUE_TIME LARGEINT Time, in microseconds, that messages spent waiting on a process message input queue MESSAGES_SENT LARGEINT Number of messages sent by the process (associated with I/O operations and some system procedure calls) SENT_BYTES LARGEINT Number of bytes sent by the process RETURNED_BYTES LARGEINT Number of message bytes received by the process MESSAGES_RECEIVED LARGEINT Number of messa
In the case of an NDCS server process or a Neoview SQL compiler process, statistics are not really aggregates because each record represents only one process. In this view, all CHARACTER columns use ISO88591 encoding, regardless of the underlying Neoview platform configuration. The primary key for PROCESS_AGGR_LEVEL1_STATS_V1 field definition is SEGMENT_ID, NODE_ID, PIN, and SAMPLE_DATETIME_UTC.
Table 3-11 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) Field Name Data Type Description TOTAL_CHILD_ELAPSED_TIME LARGEINT Sum of elapsed times, in microseconds, between the end of the last aggregation period and the end of this one, for all processes described by this record 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 microsec
Table 3-11 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued) 64 Field Name Data Type Description AVG_HISTOGRAM_75_95 LARGEINT Average percent busy of all processes with a CPU busy percentage between 75 and 95 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 p
Table 3-11 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-12 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-12 TABLE_STATS_V2 and TABLE_STATS_DETAIL_V2 Field Definitions (continued) Field Name Data Type Description LAST_REORG_TS TIMESTAMP(6) Time at which the last reorganization of the table occurred. REORG_STATUS CHAR(11) COMPLETED, IN_PROGRESS, or NONE MISSING_STATS_COUNT INTEGER Total count of missing statistics events for this object. 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.
Table 3-13 DISK_STATS_V1 Field Definitions (continued) Field Name Data Type Description PIN SMALLINT UNSIGNED Process identification number of the disk process for this volume SEGMENT_NAME CHAR(8) Neoview platform segment name SAMPLE_ELAPSED_TIME INTERVAL SECOND(12,6) Duration of the sample interval FULL_PCT DECIMAL(6,1) Percentage of disk space currently in use AVAILABLE_SPACE DECIMAL(12,1) Available disk space, in megabytes CAPACITY INTEGER Total capacity of this volume, in megabytes
Table 3-13 DISK_STATS_V1 Field Definitions (continued) Field Name Data Type Description 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 Dashboard configuration:: 'Exists' 'Up' 'Low' 'Medium' 'High' 'Warning' 'Critical' 'Down' 'Questionab
Table 3-13 DISK_STATS_V1 Field Definitions (continued) Field Name Data Type Description 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, writes, and seeks per second for this interval, for the mirror disk BUSY_PCT_MIR DECIMAL(6,1) Percentag
Table 3-14 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-14 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.
4 Examples and Guidelines for Creating Repository Queries Overview This section lists 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.
ORDER BY EXEC_START_LCT_TS DESC FOR READ UNCOMMITTED ACCESS; Field Value “EXEC_START_LCT_TS” 2009-08-19 04:56:20.264828 “EXEC_START_UTC_TS” 2009-08-19 11:56:20.264828 “QUERY_ID” “MXID01003030787212117386140307130000000009614SUPER.SERVICES00_254_SQL_CUR_61” “ENTRY_ID_LCT_TS” 2009-08-19 04:56:27.544543 “ENTRY_ID_UTC_TS” 2009-08-19 11:56:27.544543 “SEGMENT_NAME” \NEO0102 “PLATFORM_NAME” NEO01 “USER_NAME” “joesmith ”1 “ROLE_NAME” “ROLE.DBA” “START_PRIORITY” 148 “PROCESS_NAME” \NEO0102.
Field Value “COMPILE_ROWS_ACCESSED_FULL_SCAN” 1046.0 “COMPILE_DISK_PROCESS_ROWS_ACCESSED” 1046.0 “COMPILE_DISK_PROCESS_ROWS_USED” 1046.0 “EST_COST” 0.0338225 “EST_CARDINALITY” 52.0 “EST_ACCESSED_ROWS” 1046.0 “EST_USED_ROWS” 1046.0 “EST_IO_TIME” 0.01144 “EST_MSG_TIME” 0.0146622 “EST_IDLE_TIME” 0.5530 “EST_NODE_TIME” 0.00772028 “EST_TOTAL_TIME” 0.0338225 “EST_TOTAL_MEM” 587.
Field Value “MAX_MEMORY_EVER_USED” 33328 “TRANSACTION_ID” NULL “NUM_REQUEST_MSGS” 321 “NUM_REQUEST_MSG_BYTES” 733160 “NUM_REPLY_MSGS” 221 “NUM_REPLY_MSG_BYTES” 445664 “FIRST_RESULT_RETURN_LCT_TS” NULL “FIRST_RESULT_RETURN_UTC_TS” NULL “ROWS_RETURNED_TO_MASTER” 11 “PARENT_QUERY_ID” NULL “EXEC_END_LCT_TS” 2009-08-19 04:56:20.585779 “EXEC_END_UTC_TS” 2009-08-19 11:56:20.
Retrieve Statistics for Incomplete Queries This query lists information about queries that have started but not ended. The result set is ordered in descending chronological order (most recent first). The result set represents queries that are active, queries that terminated abnormally, or queries where the user chose not to finish fetching the data. If a query did not complete, fields that are not populated unless the query completes have the value NULL.
Field Value “COMPILE_AFFINITY_NUM” 679458816 “COMPILE_DEGREE_OF_PARALLELISM” 12 “COMPILE_TRANSACTION_NEEDED” 0 “COMPILE_MANDATORY_CROSS_PRODUCT” 0 “COMPILE_MISSING_STATS” 0 “COMPILE_NUM_JOINS” 0 “COMPILE_FULL_SCAN_ON_TABLE” 1 “COMPILE_ROWS_ACCESSED_FULL_SCAN” 1046.0 “COMPILE_DISK_PROCESS_ROWS_ACCESSED” 1046.0 “COMPILE_DISK_PROCESS_ROWS_USED” 1046.0 “EST_COST” 0.0338225 “EST_CARDINALITY” 52.0 “EST_ACCESSED_ROWS” NULL “EST_USED_ROWS” NULL “EST_IO_TIME” 0.01144 “EST_MSG_TIME” 0.
Field Value “SQL_MEMORY_HEAP_ALLOCATED” NULL “SQL_MEMORY_HEAP_USED” NULL “EXECUTOR_IN_DISK_MEMORY_SPACE_ALLOCATED” NULL “EXECUTOR_IN_DISK_MEMORY_SPACE_USED” NULL “EXECUTOR_IN_DISK_MEMORY_HEAP_ALLOCATED” NULL “EXECUTOR_IN_DISK_MEMORY_HEAP_USED” NULL “TOTAL_MEMORY_ALLOCATED” NULL “MAX_MEMORY_EVER_USED” NULL “TRANSACTION_ID” NULL “NUM_REQUEST_MSGS” NULL “NUM_REQUEST_MSG_BYTES” NULL “NUM_REPLY_MSGS” NULL “NUM_REPLY_MSG_BYTES” NULL “FIRST_RESULT_RETURN_LCT_TS” NULL “FIRST_RESULT_RETURN_UTC_TS
Field Value “PROCESS_CREATE_BUSY_TIME” NULL “PROCESS_CREATE_BUSY_TIME_SEC” NULL “SQL_TEXT” “SELECT AVG(CPU) CPU, AVG(MEMORY) MEMORY,…” “SQL_TEXT_OVERFLOW_INDICATOR” 0 1 The USER_NAME value is not restricted for something as simple as “joesmith.” The value might take another form, for example, it could be the user's email address or include a domain name. List Statement Counts for a Selected Time Period This query returns statement counts by user name for a given time period that you provide.
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.
TDM_Default_DataSource TDM_Default_DataSource TDM_Default_DataSource TDM_Default_DataSource TDM_Default_DataSource smart_msaccess watanabe.fargo. rstein-xp saxonyw-dc76c tanakas.fargo.c darius-d530 REVSTADTBR 9994 31 102 825 22 98 Retrieving UCS2 Columns From a Workstation Configured for ISO8859-1 As indicated in Chapter 2 (page 19), if the Neoview platform is configured for the SJIS or Unicode configuration, certain Repository views contain UCS2–encoded data.
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, using 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 71).
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 01FEB09 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.
Sample Query Result 97
B Pre-R2.4 SP1 Query Statistics Views Before Repository R2.4 SP1, these three views were used to collect ODBC/JDBC query statistics and runtime statistics. These views access current Repository data, but they use the pre-R2.4 SP1 "two rows per query" semantics of query statistics. Your existing queries will continue to work; however, update your applications as soon as possible to use the new query statistics views QUERY_STATS_VW1 and SQL_TEXT_VW1 so that you can obtain query statistics in a single row.
Table B-1 ODBC_QUERY_STATS_V1 Field Definitions (continued) 100 Field Name Data Type Description 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) or NCHAR(108) Unique session ID generated by the NDCS server when the ODBC/JDBC connection was established. SEQUENCE_NUM SMALLINT UNSIGNED Message sequence number generated by NDCS. Numbering starts at 0 and is incremented by 1 for each new record.
Table B-1 ODBC_QUERY_STATS_V1 Field Definitions (continued) Field Name Data Type Description START_PRIORITY SMALLINT UNSIGNED Execution priority of the NDCS process at the time the connection request is received. ERROR_CODE CHARACTER(6) Prepare error code. A value of 0 indicates that the prepare was successful. 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.
Table B-1 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 B-2 ODBC_QUERY_STATS_V2 Fields (continued) 104 Complete Queries Incomplete Queries Data Type Source and Notes SEQUENCE_SIZE SEQUENCE_SIZE SMALLINT UNSIGNED START 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
Table B-2 ODBC_QUERY_STATS_V2 Fields (continued) Complete Queries Incomplete Queries Data Type Source and Notes 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 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 .
Table B-3 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 106 Description NODE_ID SMALLINT UNSIGNED NO DEFAULT 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. PIN SMALLINT UNSIGNED NO DEFAULT Process identification number of the NDCS server which reported the statistics.
Table B-3 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 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 B-3 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type SQL_SPACE_USED* 108 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 B-3 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type Description STATS_ERROR_CODE* INTEGER SIGNED NO DEFAULT Usually the warning number that is returned to the stats collector while obtaining statistics from RMS. A warning of 8922 means that one or more nodes did not report the statistics for the given query. DISK_IOS* LARGEINT SIGNED NO DEFAULT Number of physical disk I/O operations performed for this statement.
Table B-3 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 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. The estimate occurs at the time the query is prepared.
Table B-3 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 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 B-3 QUERY_RUNTIME_STATS_V1 Field Definitions (continued) Field Name (* means available only in this view) Data Type 112 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 B-3 QUERY_RUNTIME_STATS_V1 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.
C History of Changes for Repository This is a chronological history of changes to Repository, beginning with Release 2.2. • “Summary of Field Definition Changes for Repository 2.2” (page 115) • “Summary of Field Definition Changes for Repository 2.3” (page 117) • “Summary of Changes For Repository 2.4 SP1” (page 118) Summary of Field Definition Changes for Repository 2.2 The following field names changed between Repository 2.1 and Repository 2.2.
Table C-2 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 START_ENTRY_ID QUERY_START_DATETIME_UTC, QUERY_START_DATE_UTC, QUERY_START_TIME_UTC START_ENTRY_ID_LCT QUERY_START_DATETIME, QUERY_START_DATE, QUERY_START_TIME END_ENTRY_ID QUERY_END_DATETIME_UTC, QUERY_END_DATE_UTC, QUERY_END_TIME_UTC END_ENTRY_ID_LCT QUERY_END_DATETIME, QUERY_END_DATE, QUERY_END_TIME ESTIMATED_COST EST_COS
Table C-4 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 START_ENTRY_ID SESSION_START_DATETIME_UTC, SESSION_START_DATE_UTC, SESSION_START_TIME_UTC START_ENTRY_ID_LCT SESSION_START_DATETIME, SESSION_START_DATE, SESSION_START_TIME END_ENTRY_ID SESSION_END_DATETIME_UTC, SESSION_END_DATE_UTC, SESSION_END_TIME_UTC END_ENTRY_ID_LCT SESSION_END_DATETIME, SESSION_END_DATE, SESSION_END
Table C-6 Field Name Mapping from TABLE_STATS_V1 to TABLE_STATS_V2 (continued) Field Name in TABLE_STATS_V1 Change in TABLE_STATS_V2 UPDATE_STATS_RUNNING_DATE and UPDATE_STATS_RUNNING_TIME UPDATE_STATS_RUNNING_TS ENTRY_DATE_UTC and ENTRY_TIME_UTC METRIC_SUBMITTED_TS_UTC UPDATE_DATE and UPDATE_TIME UPDATE_TS MISSING_STATS Now has datatype VARCHAR(1740) and includes a cumulative list of columns that have missing statistics REPAIR_ACTION Now defined as VARCHAR(1024) MISSING_STATS_SEQ_NUM No longer
Table C-7 Fields Not Available in 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, 42, 105 comparison of ODBC and query runtime statistics, 36 ODBC pre Release 2.4 Sp1, 39, 102 separate rows for start and end of query, 22 ODBC, separate rows for start and end of query, 99 ODBC, Pre– Release 2.
