HP Neoview Release 2.5 Software User Manual

HP Neoview Repository User Guide

HP Part Number: 611086-001 Published: July 2010 Edition: HP Neoview Release 2.5

Legal Notice

Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR12.211and 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. The only warranties for HP products and services are set forth in the express

warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. HP

shall not be liable for technical or editorial errors or omissions contained herein.

Export of the information contained in this publication may require authorization from the U.S. Department of Commerce.

Microsoft, Windows, and Windows NT are U.S. registered trademarks of Microsoft Corporation.

Intel, Pentium, and Celeron are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other

countries.

Java is a U.S. trademark of Sun Microsystems, Inc.

Motif, OSF/1, UNIX, X/Open, and the "X" device are registered trademarks, and IT DialTone and The Open Group are trademarks of The Open

Group in the U.S. and other countries.

Open Software Foundation, OSF, the OSF logo, OSF/1, OSF/Motif, and Motif are trademarks of the Open Software Foundation, Inc.

OSF MAKES NO WARRANTY OF ANY KIND WITH REGARD TO THE OSF MATERIAL PROVIDED HEREIN, INCLUDING, BUT NOT

LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

OSF shall not be liable for errors contained herein or for incidental consequential damages in connection with the furnishing, performance, or

use of this material.

from materials supplied by the following:

OSF software and documentation are based in part on the Fourth Berkeley Software Distribution under license from The Regents of the University

of California. OSF acknowledges the following individuals and institutions for their role in its development: Kenneth C.R.C. Arnold, Gregory S.

the University of California.

About This Document.........................................................................................................9

Supported Release Version Updates (RVUs)..........................................................................................9

Intended Audience.................................................................................................................................9

New and Changed Information in This Edition.....................................................................................9

Document Organization.......................................................................................................................10

Notation Conventions...........................................................................................................................10

General Syntax Notation.................................................................................................................10

Related Documentation........................................................................................................................12

Neoview Customer Library.............................................................................................................13

Publishing History................................................................................................................................14

HP Encourages Your Comments..........................................................................................................14

1 Introduction...................................................................................................................15

Repository Features and Interfaces.......................................................................................................15

2 Getting Started with Repository..................................................................................17

Installation............................................................................................................................................17

Character Set Support in Repository Views.........................................................................................17

Obtaining Prepackaged Queries...........................................................................................................17

Starting the Repository.........................................................................................................................17

Configuring Retention Times for Data.................................................................................................17

3 Repository Views..........................................................................................................19

Overview...............................................................................................................................................19

Query Statistics.....................................................................................................................................19

VIEW NEO.HP_METRICS.QUERY_STATS_VW2..........................................................................19

VIEW NEO.HP_METRICS.SQL_TEXT_VW1.................................................................................32

ODBC Session Data...............................................................................................................................33

VIEW NEO.HP_METRICS.ODBC_SESSION_STATS_V1...............................................................33

VIEW NEO.HP_METRICS.ODBC_SESSION_STATS_V2...............................................................36

Processing Node Statistics....................................................................................................................40

VIEW NEO.HP_METRICS.NODE_STATS_V1................................................................................40

Disk Statistics........................................................................................................................................42

VIEW NEO.HP_METRICS.DISK_STATS_V1..................................................................................42

EMS Events...........................................................................................................................................45

VIEW NEO.HP_METRICS.EVENTS_VW1.....................................................................................45

Space Management Views and Sample Query Views for Space Management....................................47

VIEW NEO.HP_METRICS.SPACE_PARTITION_DETAIL_VW1...................................................47

VIEW NEO.HP_METRICS.SPACE_PARTITION_DETAIL_HISTORY_VW1.................................50

Sample Query Views for Space Management.................................................................................54

SPACE_Q_CATALOG_SIZE_VW1............................................................................................54

SPACE_Q_DISK_FULL_VW1....................................................................................................55

SPACE_Q_NUM_GUARDIAN_FILES_VW1............................................................................55

SPACE_Q_OBJECT_GROWTH_VW1........................................................................................55

SPACE_Q_PARTITION_FULL_VW1.........................................................................................56

SPACE_Q_SYSTEM_DETAILS_VW1.........................................................................................56

SPACE_Q_SYSTEM_TOTAL_SIZE_VW1..................................................................................56

SPACE_Q_TABLES_BY_SCHEMA_VW1..................................................................................56

Table of Contents 3

SPACE_Q_TABLE_FULL_VW1.................................................................................................57

SPACE_Q_TABLE_SIZE_VW1...................................................................................................57

SPACE_Q_TOP_10_CATEGORY_SIZE_VW1...........................................................................57

SPACE_Q_TOP_10_NON_PARTITION_DETAIL_VW1...........................................................57

SPACE_Q_TOP_10_PARTITION_DETAIL_VW1......................................................................58

SPACE_Q_TOP_10_SCHEMA_SIZE_VW1................................................................................58

SPACE_Q_TOP_10_TABLE_SIZE_VW1....................................................................................58

SPACE_Q_TOP_10_USER_SPACE_SIZE_VW1.........................................................................58

4 Examples and Guidelines for Creating Repository Queries....................................59

Overview...............................................................................................................................................59

Get the Number of Rows in a View......................................................................................................59

Retrieve a Specified Number of Rows from a View.............................................................................59

Retrieve Statistics for Completed Queries............................................................................................60

Retrieve Statistics for Incomplete Queries............................................................................................60

List Statement Counts for a Selected Time Period................................................................................61

Count Completed Queries, According to Specified Criteria................................................................62

Completed Queries Executed on a System in the Last 24 Hours....................................................62

Completed Queries Executed on a System in the Previous Two Days...........................................62

Queries within the Past 24 Hours, Grouped by Client Name.........................................................62

Queries within the Past 24 Hours, Grouped by Data Source..........................................................63

Queries within the Past 24 Hours, Grouped by Client Name and Including Average and

Maximum Elapsed Time.................................................................................................................63

Formatting Techniques to Enhance Readability of Query Output.......................................................63

Retrieving UCS2 Columns From a Workstation Configured for ISO8859-1........................................64

A Sample Queries for Event Information.......................................................................67

Sample Query Result............................................................................................................................72

B STATEMENT_TYPE Field...............................................................................................73

C Repository Views Disabled by Default.......................................................................75

Process Statistics...................................................................................................................................75

VIEW NEO.HP_METRICS.PROCESS_STATS_V1..........................................................................75

VIEW NEO.HP_METRICS.PROCESS_AGGR_LEVEL1_STATS_V1..............................................78

Table Statistics.......................................................................................................................................82

VIEW NEO.HP_METRICS.TABLE_STATS_V2 and VIEW

NEO.HP_METRICS.TABLE_STATS_DETAIL_V2...........................................................................83

D History of New and Changed Information in Previous Releases of the

Repository.........................................................................................................................87

New and Changed Information in Previous Editions..........................................................................87

E Pre-R2.5 Query Statistics Views..................................................................................89

Query Statistics for R2.4 SP2.................................................................................................................89

VIEW NEO.HP_METRICS.QUERY_STATS_VW1..........................................................................89

Query Statistics for R2.4 SP1...............................................................................................................101

Query Statistics for Pre-R2.4 SP1........................................................................................................101

VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V1_2400......................................................102

VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V2_2400......................................................105

4 Table of Contents

VIEW NEO.HP_METRICS.QUERY_RUNTIME_STATS_V1_2400................................................108

Query Statistics for Release 2.4 ..........................................................................................................116

VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V1................................................................116

VIEW NEO.HP_METRICS.ODBC_QUERY_STATS_V2................................................................119

VIEW NEO.HP_METRICS.QUERY_RUNTIME_STATS_V1.........................................................122

F History of Field Changes for Repository..................................................................131

Summary of Changes for Repository 2.4 SP2.....................................................................................131

New Fields in QUERY_STATS_VW1 ............................................................................................131

New Fields in SQL_TEXT_VW1....................................................................................................131

New Space Management Views....................................................................................................131

Summary of Changes For Repository 2.4 SP1....................................................................................132

New Repository View For Accessing Error Event Information....................................................132

Repository View Changes For Easy Access to Query Statistics....................................................132

USER_NAME Field Increased to 128 Characters..........................................................................133

Summary of Changes for Repository 2.4............................................................................................133

Summary of Field Definition Changes for Repository 2.3..................................................................133

Summary of Field Definition Changes for Repository 2.2..................................................................135

Glossary.........................................................................................................................139

Index...............................................................................................................................141

Table of Contents 5

List of Figures

1-1 Neoview Repository and Related Products..................................................................................16

6 List of Figures

List of Tables

3-1 QUERY_STATS_VW2 Field Definitions........................................................................................20

3-2 SQL_TEXT_VW1 Field Definitions...............................................................................................32

3-3 ODBC_SESSION_STATS_V1 Field Definitions.............................................................................33

3-4 ODBC_SESSION_STATS_V2 Fields..............................................................................................37

3-5 NODE_STATS_V1 Field Definitions.............................................................................................40

3-6 DISK_STATS_V1 Field Definitions................................................................................................43

3-7 EVENTS_VW1 Field Definitions...................................................................................................46

3-8 SPACE_PARTITION_DETAIL_VW1 Field Definitions................................................................48

3-9 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions..............................................50

3-10 SPACE_Q_CATALOG_SIZE_VW1................................................................................................54

3-11 SPACE_Q_DISK_FULL_VW1.......................................................................................................55

3-12 SPACE_Q_NUM_GUARDIAN_FILES_VW1................................................................................55

3-13 SPACE_Q_OBJECT_GROWTH_VW1...........................................................................................55

3-14 SPACE_Q_PARTITION_FULL_VW1............................................................................................56

3-15 SPACE_Q_SYSTEM_DETAILS_VW1............................................................................................56

3-16 SPACE_Q_SYSTEM_TOTAL_SIZE_VW1.....................................................................................56

3-17 SPACE_Q_TABLES_BY_SCHEMA_VW1.....................................................................................56

3-18 SPACE_Q_TABLE_FULL_VW1....................................................................................................57

3-19 SPACE_Q_TABLE_SIZE_VW1......................................................................................................57

3-20 SPACE_Q_TOP_10_CATEGORY_SIZE_VW1...............................................................................57

3-21 SPACE_Q_TOP_10_NON_PARTITION_DETAIL_VW1..............................................................57

3-22 SPACE_Q_TOP_10_PARTITION_DETAIL_VW1.........................................................................58

3-23 SPACE_Q_TOP_10_SCHEMA_SIZE_VW1...................................................................................58

3-24 SPACE_Q_TOP_10_TABLE_SIZE_VW1.......................................................................................58

3-25 SPACE_Q_TOP_10_USER_SPACE_SIZE_VW1............................................................................58

C-1 PROCESS_STATS_V1 Field Definitions........................................................................................75

C-2 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions............................................................78

C-3 TABLE_STATS_V2 and TABLE_STATS_DETAIL_V2 Field Definitions.......................................83

E-1 QUERY_STATS_VW1 Field Definitions........................................................................................89

E-2 Comparison of ODBC Query Statistics and Query Runtime Statistics.......................................101

E-3 ODBC_QUERY_STATS_V1_2400 Field Definitions....................................................................102

E-4 ODBC_QUERY_STATS_V2_2400 Fields......................................................................................106

E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions.............................................................108

E-6 ODBC_QUERY_STATS_V1 Field Definitions.............................................................................117

E-7 ODBC_QUERY_STATS_V2 Fields...............................................................................................120

E-8 QUERY_RUNTIME_STATS_V1 Field Definitions......................................................................123

F-1 Fields Not Available in Repository 2.4 SP1 Views ODBC_QUERY_STATS_V1,

ODBC_QUERY_STATS_V2, and QUERY_RUNTIME_STATS_V1..............................................132

F-2 Field Definition Changes for QUERY_RUNTIME_STATS_V1 from Repository 2.2 to Repository

2.3.................................................................................................................................................134

F-3 Field Name Mapping from TABLE_STATS_V1 to TABLE_STATS_V2.......................................134

F-4 Field Name Mapping from ACCESS_ODBCMX_QUERY_STATS_V1 to

ODBC_QUERY_STATS_V1.........................................................................................................135

F-5 Field Name Mapping from ACCESS_ODBCMX_QUERY_STATS_V2 to

ODBC_QUERY_STATS_V2.........................................................................................................135

F-6 Field Name Mapping from ACCESS_ODBCMX_SESSION_DATA_V1 to

ODBC_SESSION_STATS_V1.......................................................................................................136

F-7 Field Name Mapping from ACCESS_ODBCMX_SESSION_DATA_V2 to

ODBC_SESSION_STATS_V2.......................................................................................................136

List of Examples

A-1 Generate Update Stats Command List..........................................................................................68

A-2 Search Event Logs for Abnormal Program Termination...............................................................68

A-3 Search Event Logs For a Specific Event Number..........................................................................69

A-4 Search Event Logs for Specific Event Text.....................................................................................69

A-5 Search Event Logs for Missing Statistics.......................................................................................70

A-6 Search Event Logs for a Process Name.........................................................................................70

A-7 Search Event Logs for a Query ID.................................................................................................71

A-8 Search Event Logs for Subsystem..................................................................................................71

A-9 Summary of all Events...................................................................................................................72

8 List of Examples

About This Document

This document describes the views supported by the Neoview Manageability Repository, a Neoview SQL database and set of programs that collect and store statistics and other information about queries, query sessions, tables, disks, processing nodes, and (optionally) selected processes involved in query execution on a Neoview platform.

Supported Release Version Updates (RVUs)

This manual supports Neoview Release 2.5 and subsequent Neoview releases until otherwise indicated in a replacement publication.

Intended Audience

This document is intended for Neoview platform administrators, database administrators, and other users interested in evaluating query performance and resource utilization on a Neoview platform. Familiarity with Neoview SQL is a prerequisite.

New and Changed Information in This Edition

• Added a new view for accessing query statistics: QUERY_STATS_VW2. This view differs

from QUERY_STATS_VW1 by the addition of 5 new fields for WMS query aggregation and changes to 8 existing field data types. See “VIEW NEO.HP_METRICS.QUERY_STATS_VW2”

(page 19). The new fields:

— QAG_OPTION — QAG_TOTAL — CONN_RULE — COMP_RULE — EXEC_RULE The fields with data type changes: — SQL_MEMORY_SPACE_ALLOCATED — SQL_MEMORY_SPACE_USED — SQL_MEMORY_HEAP_ALLOCATED — SQL_MEMORY_HEAP_USED — EXECUTOR_IN_DISK_MEMORY_SPACE_ALLOCATED — EXECUTOR_IN_DISK_MEMORY_SPACE_USED — EXECUTOR_IN_DISK_MEMORY_HEAP_ALLOCATED — EXECUTOR_IN_DISK_MEMORY_HEAP_USED

• Increased the retention times for the following views:

— Processing node statistics — Session data — ODBC/JDBC Query compilation and execution statistics — Table statistics — Disk statistics See Section : “Configuring Retention Times for Data” (page 17)

• Added 3 columns to ODBC_SESSION_STATS_V1 and ODBC_SESSION_STATS_V2:

— TOTAL_LOGIN_ELAPSED_TIME_MCSEC — LDAP_LOGIN_ELAPSED_TIME_MCSEC — CLIENT_USER_NAME

Supported Release Version Updates (RVUs) 9

• Created new appendix for STATEMENT_TYPE field of QUERY_STATS_VW2. See

Appendix B: “STATEMENT_TYPE Field” (page 73).

• Moved Processing statistics (including the PROCESS_STATS_V1 AND

PROCESS_AGGR_LEVEL1_STATS_V1 views) and Table statistics (including the TABLE_STATS_V2 and TABLE_STATS_DETAIL_V2 views) to new Appendix C: “Repository

Views Disabled by Default” (page 75).

• Moved QUERY_STATS_V1 view to Appendix E: “Pre-R2.5 Query Statistics Views” (page 89).

Document Organization

This document is organized as follows:

Chapter 1: Introduction

Use this chapter to learn about the Repository and related Neoview software products.

Chapter 2: Getting Started with Repository

Use this chapter to find how to obtain and install the Repository software, enable capture of session data, and configure retention times for data.

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: Sample Queries for Event Information

Use this appendix to see sample Repository queries of error and event information on the Neoview platform. You can use these sample queries or create your own queries following the format in the examples.

Appendix B: STATEMENT_TYPE Field

Use this appendix to see the possible values for the STATEMENT_TYPE fields in the QUERY_STATS_VW2 view.

Appendix C: Repository Views Disabled by Default

Use this appendix to learn about views that are disabled by default. These views include Process Statistics and Table Statistics.

Appendix D: History of New and Changed Information in Previous Releases of the Repository

Use this appendix to learn about the history of new and changed information in the Repository.

Appendix E: Pre-R2.5 Query Statistics Views

Use this appendix to learn about the query statistics views before R2.5. 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.

Appendix F: History of Field Changes for Repository

Use this appendix to review the changes that have been made to the fields in the Repository, beginning with Release 2.2 to the current release.

Glossary

Use the glossary to find the meanings of various terms used in this document.

Notation Conventions

General Syntax Notation

This list summarizes the notation conventions for syntax presentation in this manual.

UPPERCASE LETTERS

Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example:

SELECT

Italic Letters

Italic letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required. For example:

file-name

Computer Type

Computer type letters within text indicate case-sensitive keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example:

myfile.sh

Bold Text

Bold text in an example indicates user input typed at the terminal. For example:

ENTER RUN CODE

?123 CODE RECEIVED: 123.00

The user must press the Return key after typing the input.

[ ] Brackets

Brackets enclose optional syntax items. For example:

DATETIME [start-field TO] end-field

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]...}

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. For example:

INTERVAL { start-field TO end-field } { single-field }

INTERVAL { start-field TO end-field | single-field }

| Vertical Line

A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. For example:

{expression | NULL}

Notation Conventions 11

… Ellipsis

An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example:

ATTRIBUTE[S] attribute [, attribute]...

{, sql-expression}...

An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example:

expression-n…

Punctuation

Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. For example:

DAY (datetime-expression)

@script-file

Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example:

"{" module-name [, module-name]... "}"

Item Spacing

Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example:

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. For example:

match-value [NOT] LIKE pattern

[ESCAPE esc-char-expression]

Publishing History

HP Encourages Your Comments

HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found or suggestions for improvement to docsfeedback@hp.com.

Include the document title and part number, shown below:

Document title: HP Neoview Repository User Guide Part Number: 611086-001

Publication DateProduct VersionPart Number

April 2008Release 2.3544811–001

April 2009Release 2.4546262-001

September 2009Release 2.4 Service Pack 1 (SP1)587392-001

May 2010Release 2.4 Service Pack 2 (SP2)621366–001

July 2010Release 2.5611086–001

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. Certain data about queries is available from the time the query starts; other data becomes available only after the query is complete.

• Processing nodes (CPUs). Information about processing nodes is collected from the Neoview

platform every five minutes.

• Table statistics. This feature is off by default and no data is returned. Use the Neoview

Command Interface (NCI) automated Update Statistics feature to update histogram statistics for tables.

• Disks. Information about disk availability and performance is collected from the Neoview

platform and sent to the Repository every 5 minutes, by default.

• Error and other events that are tracked in the Neoview event logs. A Repository view provides

access to the event data and you can use SQL queries to obtain information about a variety of events occurring on the Neoview platform.

• Space management. Repository views provide access to inventory data about table partitions

and aging historical data about table partitions.

• Optional processes involved in query execution. Process statistics gathering is disabled by

default, but if you enable this feature, the information is collected from the Neoview platform processes started by a specific query (NDCS connectivity server process). Different views provide access to information about individual processes and sets of processes of the same type, for example all query execution processes started by a specific query (NDCS connectivity server process). The Repository data collector process is also monitored. Aggregates for sets of related processes are computed every five minutes with a 15-minute delay; thus, aggregate data becomes available from the Repository 15 minutes after the end of the sampling interval.

Process collectors should not be turned on and left on for a long time because they place a load on the platform. They should be turned off after the measurement period has completed.

NOTE: As part of the retirement of the process statistics collectors, the collectors are not

installed nor configured on systems configured with Unicode characters sets.

The Repository is used to store historical information that the DBA can access to see patterns of queries which were run on the Neoview platform. Some examples of the resource information that can be obtained from the Repository includes:

• Platform CPU (Node) usage by time (hourly, daily, and distribution by segment)

• Platform disk space usage

• Information on queries run by specific users, applications, client PCs, type of queries run at

which time, the number of queries run

More information and sample queries are available from the Metric Miner within the HP Database Manager product.

Any database administrator (ROLE.DBA) can issue queries against the Repository,using Neoview Command Interface or another standard SQL interface, and can grant other users access to the Repository.

Repository Features and Interfaces 15

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.

Use the Neoview Performance Analysis Tools to monitor running queries on the Neoview platform.

Figure 1-1: “Neoview Repository and Related Products” (page 16) illustrates, at a very high

level, the architectural relationship and interfaces of the Repository and the products with which it interacts. This manual describes only the user interface of the Repository, which allows you to create and display the results of Repository queries on any standard SQL interface. HP Support has access to additional tools for managing the Repository configuration.

Figure 1-1 Neoview Repository and Related Products

16 Introduction

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. If the Neoview platform is configured for the ISO88591 configuration, then character fields in all Repository tables and views will contain ISO8859-1 data. If the platform is configured for the SJIS or Unicode configuration, character fields in some, but not all, Repository views contain double-wide UCS2–encoded data. The description of each view in Chapter 3: “Repository Views” (page 19) indicates whether the view supports UCS2–encoded data. Section : “Retrieving UCS2 Columns

From a Workstation Configured for ISO8859-1” (page 64) discusses how to retrieve UCS2 data

from an ISO8859-1 workstation or “from a workstation configured for the ISO8859-1 character set”).

Regardless of the character set used by the Neoview platform, column names in Repository views are encoded using ISO8859-1, and segment names are not internationalized.

Obtaining Prepackaged Queries

Prepackaged queries against the Repository are distributed as part of the Metric Miner product within HPDM.

Starting the Repository

During installation, the Repository is configured to come up automatically when the Neoview platform starts.

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.

Configuring Retention Times for Data

By default, the retention times for Repository data are:

Retention TimeRepository Metric

90 daysProcessing node statistics

90 daysSession data

Installation 17

Retention TimeRepository Metric

ODBC/JDBC Query compilation and execution statistics

ODBC query statistics and Query runtime statistics

(Release 2.4 statistics)

90 days

These statistics are static and will never be updated. To remove these statistics from your platform or get help with migration to new tables, contact your HP service provider.

30 daysProcess statistics, if enabled

30 daysProcess aggregation Level 1 statistics, if enabled

20 daysSQL Space status

90 daysTable statistics

90 daysDisk statistics

30 daysEvent data

To modify these retention times, contact your HP Support representative.

18 Getting Started with Repository

3 Repository Views

Overview

You can use the following views for access to the Repository:

• “VIEW NEO.HP_METRICS.QUERY_STATS_VW2” (page 19)

• “VIEW NEO.HP_METRICS.SQL_TEXT_VW1” (page 32)

• “VIEW NEO.HP_METRICS.ODBC_SESSION_STATS_V1” (page 33)

• “VIEW NEO.HP_METRICS.ODBC_SESSION_STATS_V2” (page 36)

• “VIEW NEO.HP_METRICS.NODE_STATS_V1” (page 40)

• “VIEW NEO.HP_METRICS.DISK_STATS_V1” (page 42)

• “VIEW NEO.HP_METRICS.EVENTS_VW1” (page 45)

• “VIEW NEO.HP_METRICS.SPACE_PARTITION_DETAIL_VW1” (page 47)

• “VIEW NEO.HP_METRICS.SPACE_PARTITION_DETAIL_HISTORY_VW1” (page 50)

Views are reinstalled every time the Repository installation is run. Reinstalling the views picks up any view definition changes.

Query Statistics

The Repository defines these views for retrieval of query statistics information:

• QUERY_STATS_VW2

• SQL_TEXT_VW1

If you have applications that use views from previous releases, the queries will continue to work and access current query statistics. However, queries against views from previous releases cannot use the new and revised columns. Also, some fields may no longer be available in QUERY_STATS_VW2. If a field is no longer available, NULL is returned when you run queries using the view. It is recommended that you adjust your applications as soon as possible to use the QUERY_STATS_VW2 view. For query statistics information prior to R2.5, see “Pre-R2.5

Query Statistics Views” (page 89).

VIEW NEO.HP_METRICS.QUERY_STATS_VW2

The Repository collects compilation and execution query statistics.

This view provides access to all query information. The SQL text for the query is also included, up to 254 characters. If the SQL text is longer than 254 characters, the SQL_TEXT_OVERFLOW_INDICATOR field is set to 1 to indicate that only part of the SQL text fits in this view, and the complete SQL text is placed in the SQL_TEXT_VW1 view. In this case, QUERY_STATS_VW2 has the first 254 characters and SQL_TEXT_VW1 has the complete SQL text. The QUERY_STATS_VW2 view can be joined with the SQL_TEXT_VW1 view through the EXEC_START_LCT_TS (or EXEC_START_UTC_TS) and QUERY_ID fields to fetch the full SQL text for the query.

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 QUERY_STATS_VW2 field definition is EXEC_START_UTC_TS and QUERY_ID.

Overview 19

Table 3-1 QUERY_STATS_VW2 Field Definitions

DescriptionData TypeField Name

EXEC_START_LCT_TS

EXEC_START_UTC_TS

QUERY_ID

TIMESTAMP(6) NO DEFAULT

CHAR(160) 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. Columns SUBMIT_TS_LCT, WAIT_TIME and HOLD_TIME can be used to calculate the actual launch time of the query.

If the query is not under control of WMS, EXEC_START_LCT_TSrepresents the actual launch time.

Example: 2009-08-17 07:47:28.795392

Primary Key

ANSI Timestamp, in Coordinated Universal Time, indicating when query execution began.

Example: 2009-08-17 15:47:28.795392

Primary Key

A unique identifier for this SQL statement, generated by the Neoview SQL compiler.

Example:

MXID0100109050821211709056930627300 0000026312DEV.USER2400_15010_SQL_CUR_1

ENTRY_ID_LCT_TS

ENTRY_ID_UTC_TS

SEGMENT_NAME

PLATFORM_NAME

USER_NAME

TIMESTAMP(6) NO DEFAULT

CHAR(16) DEFAULT NULL

CHAR(64) DEFAULT NULL

CHAR(128) DEFAULT NULL

Repository-generated ANSI Timestamp, in Local Civil Time, showing when this row was last updated. Example: 2009-08-17 07:48:09.967095

Repository-generated ANSI Timestamp, in Coordinated Universal Time, showing when this row was last updated. Example: 2009-08-17 15:48:09.967095

Name of the Neoview platform segment used by the datasource to connect and start the query. The Master Executor process for the query is running in this segment.

Example: \NEO0101

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. Example: NEO01

The name or alias, used at time of connection, to identify a Neoview user. One or more users can have the same Neoview role. Example: dbmgr

20 Repository Views

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)

DescriptionData TypeField Name

ROLE_NAME

START_PRIORITY

PROCESS_NAME

SESSION_ID

CLIENT_NAME

CHAR(128) DEFAULT NULL

INTEGER UNSIGNED DEFAULT NULL

CHAR(64) DEFAULT NULL

CHARACTER(108) DEFAULT NULL

VARCHAR(1024) DEFAULT NULL

Neoview user role name. Multiple users can have the same role name.

Example: DBA

Starting priority of the Master Executor process for this query. Example: 148

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.$XYZ

Unique session ID generated by the NDCS server when the ODBC/JDBC connection was established. Example: :

MXID010010905082121170905693062 73000000026312DEV.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.

Name of the workstation that initiated the query. Example: testpc-8

APPLICATION_NAME

DATASOURCE

STATEMENT_ID

CHAR(130) DEFAULT NULL

CHARACTER(128) DEFAULT NULL

CHARACTER(160) DEFAULT NULL

Name of the application used to connect to the database and send the query. Example: NCI.

Datasource name on the Neoview platform to which the client connected. Example: Admin_Load_DataSource

Unique statement ID generated by the NDCS SQL server at the time the Prepare command was received. Example: SQL_CUR_1

Query Statistics 21

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)

DescriptionData TypeField Name

STATEMENT_TYPE

CHARACTER(36) DEFAULT NULL

Compiler-provided value, one of:

• SQL_SELECT_UNIQUE

• SQL_SELECT_NON_UNIQUE

• SQL_INSERT_UNIQUE

• SQL_INSERT_NON_UNIQUE

• SQL_UPDATE_UNIQUE

• SQL_UPDATE_NON_UNIQUE

• SQL_DELETE_UNIQUE

• SQL_DELETE_NON_UNIQUE

• SQL_SET_TRANSACTION

• SQL_SET_SCHEMA

• SQL_OTHER

• SQL_CALL_NO_RESULT_SETS

• SQL_CALL_WITH_RESULT_SETS

• SQL_CAT_UTIL

• SQL_EXE_UTIL

• SQL_EXEC_INSERT_RWRS

For definitions of SQL statement types, see

Appendix B: “STATEMENT_TYPE Field” (page 73). The suffix _UNIQUE indicates

that the statement operates on a single row. The suffix _NON_UNIQUE indicates that the statement could operate on multiple rows.

SERVICE_NAME

SEGMENT_ID

NODE_ID

SUBMIT_LCT_TS

SUBMIT_UTC_TS

COMPILE_START_LCT_TS

CHAR(128) DEFAULT NULL

INTEGER UNSIGNED DEFAULT NULL

SMALLINT UNSIGNED DEFAULT NULL

TIMESTAMP(6) DEFAULT NULL

TIMESTAMP(6) NO DEFAULT

Name of service registered with Workload Management Services (WMS). Example: HP_DEFAULT_SERVICE

Segment number of the NDCS server that reported the statistics. The Master Executor process for the query runs in this segment.

Example: 1

Processing node number of the NDCS server that reported the statistics.

Example: 9

ANSI Timestamp, in Local Civil Time, indicating when the query first entered Workload Management Services (WMS).

Example: 2009-08-17 07:47:28.795755

ANSI Timestamp, in Coordinated Universal Time, indicating when the query first entered Workload Management Services (WMS).

Example: 2009-08-17 15:47:28.795755

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

22 Repository Views

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)

DescriptionData TypeField Name

COMPILE_START_UTC_TS

COMPILE_END_LCT_TS

COMPILE_END_UTC_TS

COMPILE_ELAPSED_TIME

COMPILE_ELAPSED_TIME_SEC

TIMESTAMP(6) DEFAULT NULL

LARGEINT DEFAULT NULL

FLOAT(54) DEFAULT NULL

ANSI Timestamp, in Coordinated Universal Time, indicating when query compilation began. That is, when a Prepare statement was issued for this query.

Example: 2009-08-17 15:47:28.791758

ANSI Timestamp, in Local Civil Time, indicating when query compilation ended. That is, when the Prepare statement completed for this query.

Example: 2009-08-17 07:47:28.795259

ANSI Timestamp, in Coordinated Universal Time, indicating when query compilation ended. That is, when the Prepare statement completed for this query.

Example: 2009-08-17 15:47:28.795259

Calculated compile time in microseconds, computed as compilation end time minus compilation start time. This is the amount of time needed to prepare the query. This value is obtained from the compiler.

Example: 3501

Compile time in seconds, computed as compilation end time minus compilation start time. This is the amount of time needed to prepare the query. This value is obtained from the compiler.

Example: 0.0035009999999999998

COMPILE_AFFINITY_NUM

COMPILE_DEGREE_OF_PARALLELISM

COMPILE_TRANSACTION_NEEDED

LARGEINT DEFAULT NULL

INTEGER UNSIGNED DEFAULT NULL

Specifies the choice of CPU Subsets to be used for placement of ESPs.. The value used by the compiler for ESP placement for this query. Example: 697115338

Degree of parallelism used by the query. The number of ESP operators that are reused to parallelize a single fragment of the query plan. If different fragments in the same plan are parallelized to different extents, then this counter reports the degree of parallelization of the fragment that is parallelized the most.

Example: 0

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

Query Statistics 23

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)

DescriptionData TypeField Name

COMPILE_MANDATORY_CROSS_PRODUCT

COMPILE_MISSING_STATS

COMPILE_NUM_JOINS

COMPILE_FULL_SCAN_ON_TABLE

INTEGER UNSIGNED DEFAULT NULL

DEFAULT NULL

INTEGER UNSIGNED DEFAULT NULL

Flags queries in which a join has been specified with no predicates. Joins with no predicates may result from predicate movement during optimization or simply because no predicate was specified for the join.

Values:

• 1 = True

• 0 = False

Indicates whether single column missing statistics warnings were raised during compilation (SQLCODE 6008 or 6011). Values:

• 1 = True

• 0 = False

Multi-column missing statistics warnings (SQLCODE 6007 or 6010) are not flagged in this field.

The number of joins in the query plan.INTEGER UNSIGNED

Indicates whether the query plan will perform a full scan on at least one table. Values:

• 1 = True

• 0 = False

COMPILE_ROWS_ACCESSED_FULL_SCAN

COMPILE_DISK_PROCESS_ROWS_ACCESSED

COMPILE_DISK_PROCESS_ROWS_USED

EST_COST

DOUBLE PRECISION DEFAULT NULL

The estimated number of rows accessed by a full scan of a table. Valid only if Full Scan On Table is set.

If the query plan contains more than one full scan operation, the largest number of rows accessed by all scans is reported here.

Values:

• Largest number of rows accessed

• -1.0 – Full Scan on Table is not set.

• Zero (0.0) – A full scan was performed on

an empty table.

Estimated number of rows to be accessed by all scan, insert, update and delete operations in the query plan. Example: 5.0

Estimated number of rows accessed by scan and insert operations in the query plan. Example: 2.0

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

24 Repository Views

DOUBLE PRECISION NO DEFAULT

Estimated number of rows that will be returned. The estimate occurs at the time the query is prepared. Example: 10.0

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)

DescriptionData TypeField Name

EST_ACCESSED_ROWS

EST_USED_ROWS

EST_IO_TIME

EST_MSG_TIME

EST_IDLE_TIME

DOUBLE PRECISION DEFAULT NULL

Estimated number of rows to be accessed by SELECT statements from Disk Process at runtime by EID (Executor in Disk Process). For Insert/Update/Delete statements, the value is reported as 0.0.

Estimated number of rows to be returned by EID (Executor in Disk Process) after applying selection predicates based on SELECT statements. For Insert/Update/Delete statements, the value is reported as 0.0.

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. Example: 0.0

Estimate of the number of seconds required for the messaging for this query, including local and remote messages, and the amount of data sent. The estimate occurs at the time the query is prepared. Example: 0.0

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. Example: 0.0

EST_NODE_TIME

EST_TOTAL_TIME

EST_TOTAL_MEM

EST_RESOURCE_USAGE

DOUBLE PRECISION DEFAULT NULL

INTEGER DEFAULT NULL

Estimate of the number of seconds of processor time it might take to execute instructions for this operator. The estimate occurs at the time the query is prepared. Example: 1.74125E-6

Estimated time in seconds for the SQL operations for this query. This estimate includes estimates of I/O time, Message time, and Node time, and does not include Idle time. The estimate occurs at the time the query is prepared.

Example: 1.74125E-6

Estimate, in kilobytes, of the memory that the query plan will use. The value is computed based on operators that could potentially consume large amounts of memory: Hash Join, Hash Groupby, Sort, Merge Join and Sequence. Example: 0.0

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: For future use. Currently zero (0).

QAG_OPTION

QAG_TOTAL

CHAR(3) DEFAULT NULL

NULL

Determines whether this is an aggregated record. Yes/No

Total number of queries aggregated.LARGEINT DEFAULT

Query Statistics 25

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)

DescriptionData TypeField Name

CONN_RULE

COMP_RULE

EXEC_RULE

QUERY_STATUS

QUERY_SUB_STATUS

NULL

CHAR(21) DEFAULT NULL

CHAR(30) DEFAULT NULL

Connection rule.CHAR(84) DEFAULT

Compilation rule.CHAR(84) DEFAULT

Execution rule.CHAR(84) DEFAULT

Status of the query. One of these:

INIT COMPLETED REJECTED CANCELLED

Substatus of the query, dependent on QUERY_STATUS When QUERY_STATUS = COMPLETED or CANCELLED, possible values for QUERY_SUB_STATUS are:

• HOLD_TIMEOUT

• EXEC_TIMEOUT

• CANCELLED_BY_ADMIN

• QUERY_NOT_FOUND

• NDCS_PROCESS_FAILED

• CPU_FAILED

• SEGMENT_FAILED

• BY_EXEC_RULE

• SERVICE_NOT_ACTIVE

• UNEXPECTED_STATE

• CLIENT_DISAPPEARED

• CONNECTION_FAILED

When QUERY_STATUS = REJECTED, possible values for QUERY_SUB_STATUS are:

• BY_ADMIN

• CONNECTION_FAILED

• HOLD_TIMEOUT

• WAIT_TIMEOUT

• NDCS_PROCESS_FAILED

• CPU_FAILED

• SEGMENT_FAILED

• QUEUE_MSG_CANCELLED

• VERSION_MISMATCH

• WMS_ON_HOLD

• MAX_QUERIES_REACHED

• SERVICE_NOT_FOUND

• SERVICE_ON_HOLD

• BY_COMP_RULE

• UNKNOWN_USER

• CLIENT_DISAPPEARED

• UNEXPECTED_STATE

For more information, refer to the Neoview Workload Management Services Guide.

26 Repository Views

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)

DescriptionData TypeField Name

QUERY_EXECUTION_STATE

WARN_LEVEL

STATS_ERROR_CODE

CHAR(25) DEFAULT NULL

CHAR(7) DEFAULT NULL

INTEGER DEFAULT NULL

What the executor is doing. Possible query states include:

INITIAL OPEN EOF CLOSE DEALLOCATED FETCH CLOSE_TABLES PROCESS_ENDED UNKNOWN NULL

Warning level from WMS. Possible levels include:

LOW MEDIUM HIGH NONE

SQL error code, if any, returned while obtaining statistics for the query. Indicates whether the query completed with warnings, errors or successfully:

• Positive number – Warning

For example, warning 8922 – one or more nodes did not report statistics for this query.

• Negative number – Error

• Zero (0) or 100 – Success

WAIT_TIME

HOLD_TIME

QUERY_ELAPSED_TIME

QUERY_ELAPSED_TIME_SEC

LARGEINT DEFAULT NULL

FLOAT(54) DEFAULT NULL

Query wait time in seconds. The amount of time a query waited in WMS for a resource (for example, CPU time) to become available for the query to start executing.

Query hold time in seconds. The amount of time the query spent in a WMS queue in a HOLDING or SUSPENDED state due to:

• Rules configured in WMS.

• An external attempt to hold the query,

such as a HOLD QUERY command issued from NCI by a WMS administrator.

This counter is updated during the time before the query starts executing.

Elapsed clock time in microseconds from the time the query started executing to the time the results were sent back. Example: 31169481

Elapsed clock time, in seconds, from the time the query started executing to the time the results were sent back. Example: 311694

Query Statistics 27

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)

DescriptionData TypeField Name

SQL_PROCESS_BUSY_TIME

SQL_PROCESS_BUSY_TIME_SEC

DISK_PROCESS_BUSY_TIME

DISK_PROCESS_BUSY_TIME_SEC

DISK_IOS

LARGEINT DEFAULT NULL

FLOAT(54) DEFAULT NULL

LARGEINT DEFAULT NULL

FLOAT(54) DEFAULT NULL

LARGEINT DEFAULT NULL

INTEGERNUM_SQL_PROCESSES

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.169480999999998

An approximation, in seconds, of the total node time spent in the Master Executor process and all ESPs involved in the query. This value is cumulative across all ESPs across all segments. Example: 39593

Cumulative node busy time in microseconds for all disk processes involved in executing the query.

Example: 0.039592999999999996

Cumulative node busy time, in seconds, for all disk processes involved in executing the query. Example: : 0.000000039592999999999996

Number of physical disk I/O operations (reads or writes) caused by accessing this table.

The number of SQL processes involved in execution of this query.

SQL_MEMORY_SPACE_ALLOCATED

SQL_MEMORY_SPACE_USED

SQL_MEMORY_HEAP_ALLOCATED

SQL_MEMORY_HEAP_USED

LARGEINT DEFAULT NULL

Amount of “space” type (static) memory, in kilobytes, allocated (reserved) for query processes (master executor and ESPs) at the beginning of query execution. This value stays constant for the duration of query execution. It should be less than or equal to the SQL_MEMORY_SPACE_ ALLOCATED value. This is the value at the end of query execution.

Amount of “heap” type (dynamic) memory, in kilobytes, allocated (reserved) for query processes (master executor and ESPs) at the beginning of query execution. The processes that execute the query request this memory during execution, thus the amount can change as the query runs. This is the value at the end of query execution.

Amount of “heap” type (dynamic) memory, in kilobytes, actually used for query processes (master executor and ESPs) during query execution. This is the value at the end of query execution.

28 Repository Views

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)

DescriptionData TypeField Name

EXECUTOR_IN_DISK_MEMORY_ SPACE_ALLOCATED

EXECUTOR_IN_DISK_MEMORY_ SPACE_USED

EXECUTOR_IN_DISK_MEMORY_ HEAP_ALLOCATED

EXECUTOR_IN-DISK_MEMORY_ HEAP_USED

TOTAL_MEMORY_ALLOCATED

LARGEINT DEFAULT NULL

Amount of “space” type (static) memory, in kilobytes, allocated (reserved) for the Executor in Disk (EID) disk processes involved in query execution. This process is separate from master and ESPs, and all the EID memory values are in addition to the SQL memory values. This is the value at the end of query execution.

Amount of “space” type (static) memory, in kilobytes, actually used by the Executor in Disk (EID) disk processes involved in query execution. This is the value at the end of query execution.

Amount of “heap” type (dynamic) memory, in kilobytes, allocated for the Executor in Disk (EID) disk processes involved in query execution. This is the value at the end of query execution.

Amount of “heap” type (dynamic) memory, in kilobytes, actually used for the Executor in Disk (EID) processes involved in query execution. This is the value at the end of query execution

Total memory allocated to the query. The sum of SQL_MEMORY_SPACE_USED and SQL_MEMORY_HEAP_USED.

MAX_MEMORY_EVER_USED

TRANSACTION_ID

NUM_REQUEST_MSGS

NUM_REQUEST_MSG_BYTES

NUM_REPLY_MSGS

NUM_REPLY_MSG_BYTES

FIRST_RESULT_RETURN_LCT_TS

FIRST_RESULT_RETURN_UTC_TS

ROWS_RETURNED_TO MASTER

LARGEINT DEFAULT NULL

CHAR(25) DEFAULT NULL

LARGEINT DEFAULT NULL

NULL

LARGEINT DEFAULT NULL

TIMESTAMP(6) DEFAULT NULL

LARGEINT DEFAULT NULL

Maximum memory, in kilobytes, ever used during the lifetime of the query.

Transaction identifier, in internal format, of the transaction involved in executing the query.

Count of messages that are initiated from the Master Executor process to ESPs or ESP to ESP. This count includes both control and data messages.

Number of message bytes sent as part of NUM_REQUEST_MSGS.

Count of reply messages from ESPs.LARGEINT DEFAULT

Number of bytes sent as part of the replies from ESPs.

RMS timestamp in Local Civil Time when SQL sends the first result row to the Master Executor process.

RMS timestamp in Coordinated Universal Time when SQL sends the first result row to the Master Executor process.

Number of rows returned from the root operator to the Master Executor process and to the application in both the regular fetch and rowset fetch.

Query Statistics 29

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)

DescriptionData TypeField Name

PARENT_QUERY_ID

EXEC_END_LCT_TS

EXEC_END_UTC_TS

MASTER_EXECUTION_TIME

CHAR(160) DEFAULT NULL

TIMESTAMP(6) DEFAULT NULL

LARGEINT 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.

ANSI Timestamp in Local Civil Time indicating when query execution ended. Example: 2009-08-17 07:47:59.964873

ANSI Timestamp in Coordinated Universal Time indicating when query execution ended. Example: 2009-08-17 15:47:59.964873

TIP: You can use this value to monitor

whether the query is still running.

Node processing time, in microseconds, from the time the execution starts to the time the results are returned. It does not include CPU time consumed by SQL executor child processes (ESPs) or disk processes for the query. Example: 40214

MASTER_EXECUTION_TIME_SEC

ERROR_CODE

SQL_ERROR_CODE

ERROR_TEXT

LAST_ERROR_BEFORE_AQR

FLOAT(54) DEFAULT NULL

INTEGER DEFAULT NULL

CHAR(200) DEFAULT NULL

INTEGER DEFAULT NULL

Node processing time, in seconds, from the time the execution starts to the time the results are returned. It does not include CPU time consumed by SQL executor child processes (ESPs) or disk processes for the query. Example: 0.040214

Query error code returned to the client. If the NDCS process stops or fails, this field is set to 201. In this case, the last statistics available from WMS are retrievable with this view.

Top–level error code returned by the query. Values:

• 100 – completed SELECT, UPDATE, or

DELETE statements with actual end statistics

• zero (0) – completed INSERT queries or

uncompleted queries

• negative number – an execution-type

error

Message text of the error returned by SQL. See SQL_ERROR_CODE.

Last error code that caused the query to be automatically retried.

DELAY_TIME_BEFORE_AQR_SEC

TOTAL_NUM_AQR_RETRIES

30 Repository Views

LARGEINT DEFAULT NULL

Delay time in seconds before the query was automatically retried.

Total number of times that a query was retried.

+ 112 hidden pages