Hp Neoview Release 2.5 Software Manuale Utente

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

• Created new appendix for STATEMENT_TYPE field of QUERY_STATS_VW2. SeeAppendix B: “STATEMENT_TYPE Field” (page 73).• Moved Processing statistics (inc

Table E-1 QUERY_STATS_VW1 Field Definitions (continued)DescriptionData TypeField NameCumulative number of times record (row)locks were escalated to fi

Query Statistics for R2.4 SP1Three new Repository views improve the collection of the Repository statistics:Two new Repository views allow you to acce

NOTE: The old data displayed in those views will not be transferred to the new Repositorytable and cleaned up. Also, the old Repository tables will no

Table E-3 ODBC_QUERY_STATS_V1_2400 Field Definitions (continued)DescriptionData TypeField NameANSI SQL date in CoordinatedUniversal Time when the stat

Table E-3 ODBC_QUERY_STATS_V1_2400 Field Definitions (continued)DescriptionData TypeField NameSTART if this record containsinformation collected at th

Table E-3 ODBC_QUERY_STATS_V1_2400 Field Definitions (continued)DescriptionData TypeField NameCompiler-provided value, one of:SQL_SELECT_UNIQUE,SQL_SE

This table indicates which fields are present for completed and incomplete queries. The sourceof data fields indicates whether the value is collected

Table E-4 ODBC_QUERY_STATS_V2_2400 Fields (continued)Source and NotesData TypeIncomplete QueriesComplete QueriesSTARTTIMESTAMPQUERY_START_DATETIMEQUER

NOTE: In both ODBC_QUERY_STATS_V1_2400 and ODBC_QUERY_STATS_V2_2400, theQUERY_ID field contains the unique ID of the query unless an error occurs befo

Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Starting priorit

UPPERCASE LETTERSUppercase letters indicate keywords and reserved words. Type these items exactly as shown.Items not enclosed in brackets are required

Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Repository inter

Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Amount of “heap”

Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Number of physic

Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Estimate of the

Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Time this proces

Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Neoview user rol

Table E-5 QUERY_RUNTIME_STATS_V1_2400 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)ANSI Timestamp,

Table E-6 ODBC_QUERY_STATS_V1 Field DefinitionsDescriptionData TypeField NameSegment number of the NDCS serverthat reported the statistics.INTEGER UNS

Table E-6 ODBC_QUERY_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameCumulative number of rows accessed.LARGEINTROWS_ACCESSEDCumul

Table E-6 ODBC_QUERY_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameCompiler-provided value, one of:SQL_SELECT_UNIQUE,SQL_SELECT_

… EllipsisAn ellipsis immediately following a pair of brackets or braces indicates that you can repeatthe enclosed sequence of syntax items any number

This table indicates which fields are present for completed and incomplete queries. The sourceof data fields indicates whether the value is collected

Table E-7 ODBC_QUERY_STATS_V2 Fields (continued)Source and NotesData TypeIncomplete QueriesComplete QueriesSTARTTIMESTAMPQUERY_START_DATETIMEQUERY_STA

Table E-7 ODBC_QUERY_STATS_V2 Fields (continued)Source and NotesData TypeIncomplete QueriesComplete QueriesEND, or NULL foran incompletequeryLARGEINTR

Table E-8 QUERY_RUNTIME_STATS_V1 Field DefinitionsDescriptionData TypeField Name (* means available only inthis view)ANSI Timestamp indicating when th

Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Node utilization for

Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Amount of “space” typ

Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Amount of “heap” type

Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Estimated number of r

Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)Calculated number of

Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)The SQL statement as

Neoview Customer LibraryThe manuals in the Neoview customer library are listed here for your convenience.• AdministrationInformation about security fe

Table E-8 QUERY_RUNTIME_STATS_V1 Field Definitions (continued)DescriptionData TypeField Name (* means available only inthis view)ANSI Timestamp indica

F History of Field Changes for RepositoryThis is a chronological history of changes to Repository.• “Summary of Changes for Repository 2.4 SP2” (page

Summary of Changes For Repository 2.4 SP1These changes have been made to views between Repository 2.4 and Repository 2.4 SP1. If youhave already modif

Table F-1 Fields Not Available in Repository 2.4 SP1 Views ODBC_QUERY_STATS_V1,ODBC_QUERY_STATS_V2, and QUERY_RUNTIME_STATS_V1 (continued)Field Unavai

The following table shows how fields defined in the view QUERY_RUNTIME_STATS_V1 wererenamed in Repository 2.3.Table F-2 Field Definition Changes for Q

Summary of Field Definition Changes for Repository 2.2The following field names changed between Repository 2.1 and Repository 2.2. If you havealready

Table F-5 Field Name Mapping from ACCESS_ODBCMX_QUERY_STATS_V2 toODBC_QUERY_STATS_V2 (continued)Field Name in ODBC_QUERY_STATS_V2Field Name in ACCESS_

Table F-7 Field Name Mapping from ACCESS_ODBCMX_SESSION_DATA_V2 toODBC_SESSION_STATS_V2 (continued)Field Name in ODBC_SESSION_STATS_V2Field Name in AC

138

GlossaryCollector process A Repository process that collects metrics for a monitored entity.Execdirect A SQL statement or call that executes a query d

• ConnectivityReference information about the HP Neoview JDBC Type 4 Driver API.Neoview JDBC Type 4 Driver APIReferenceInformation about using the HP

140

IndexAArchitecture, Repository, 16CCharacter set support, 17CPU statistics (see Processing node statistics)DDefault configuration, 17Disk statisticsNE

1 IntroductionRepository Features and InterfacesThe Neoview Manageability Repository is a Neoview SQL database and set of programs thatautomatically c

Use the Neoview Performance Analysis Tools (NPA Tools) client to monitor queries and systemresources on the Neoview platform and to reveal, as soon as

2 Getting Started with RepositoryInstallationThe Repository is initially installed on the Neoview platform by HP Manufacturing. Repositoryupgrades are

Retention TimeRepository Metric90 days90 daysThese statistics are static and will never be updated. Toremove these statistics from your platform or ge

3 Repository ViewsOverviewYou can use the following views for access to the Repository:• “VIEW NEO.HP_METRICS.QUERY_STATS_VW2” (page 19)• “VIEW NEO.HP

© Copyright 2010 Hewlett-Packard Development Company, L.P.Legal NoticeConfidential computer software. Valid license from HP required for possession, u

Table 3-1 QUERY_STATS_VW2 Field DefinitionsDescriptionData TypeField NameANSI Timestamp, in Local Civil Time,indicating when query execution began. Th

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)DescriptionData TypeField NameNeoview user role name. Multiple users canhave the same role name

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)DescriptionData TypeField NameCompiler-provided value, one of:• SQL_SELECT_UNIQUE• SQL_SELECT_N

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)DescriptionData TypeField NameANSI Timestamp, in Coordinated UniversalTime, indicating when que

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)DescriptionData TypeField NameFlags queries in which a join has beenspecified with no predicate

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)DescriptionData TypeField NameEstimated number of rows to be accessed bySELECT statements from

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)DescriptionData TypeField NameConnection rule.CHAR(84) DEFAULTNULLCONN_RULECompilation rule.CHA

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)DescriptionData TypeField NameWhat the executor is doing. Possible querystates include:INITIALO

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)DescriptionData TypeField NameAn approximation, in microseconds, of thetotal node time spent in

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)DescriptionData TypeField NameAmount of “space” type (static) memory, inkilobytes, allocated (r

Table of ContentsAbout This Document...9Supporte

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)DescriptionData TypeField NameQuery ID for the immediate parent of thisquery ID. If the parent

Table 3-1 QUERY_STATS_VW2 Field Definitions (continued)DescriptionData TypeField NameCumulative number of times record (row)locks were escalated to fi

VIEW NEO.HP_METRICS.SQL_TEXT_VW1This view allows access to the complete SQL text for a query.When the SQL text for a query is 254 characters or fewer,

Table 3-2 SQL_TEXT_VW1 Field Definitions (continued)DescriptionData TypeField NameNeoview system name, made up of thefirst three letters of the system

Table 3-3 ODBC_SESSION_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameANSI SQL Timestamp, inCoordinated Universal Time,indicating

Table 3-3 ODBC_SESSION_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameTotal number of SQL INSERTstatements sent by the HPODBC dri

Table 3-3 ODBC_SESSION_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameName of the Neoview segmentused by the data source.CHAR(10)

The primary key for ODBC_SESSION_STATS_V2 field definition is SEGMENT_ID, NODE_ID,and SESSION_EVENT_DATETIME_UTC.Table 3-4 ODBC_SESSION_STATS_V2 Field

Table 3-4 ODBC_SESSION_STATS_V2 Fields (continued)Source andNotesData TypeIncomplete SessionsComplete SessionsEND, orNULL for anincompletesessionDATES

Table 3-4 ODBC_SESSION_STATS_V2 Fields (continued)Source andNotesData TypeIncomplete SessionsComplete SessionsEND, orNULL for anincompletesessionLARGE

SPACE_Q_TABLE_FULL_VW1...57SPACE_Q_TABLE_SIZE_VW1...

Table 3-4 ODBC_SESSION_STATS_V2 Fields (continued)Source andNotesData TypeIncomplete SessionsComplete SessionsTime inmicrosecondsspent oncommunication

Table 3-5 NODE_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameANSI SQL timestamp, in LocalCivil Time, indicating when themeasurem

Table 3-5 NODE_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameNumber of times a process wasselected from the ready list andexecut

Table 3-6 DISK_STATS_V1 Field DefinitionsDescriptionData TypeField NameTimestamp, in Local Civil Time,indicating when the measurement wassubmitted to

Table 3-6 DISK_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameNumber of disk input/output requestsper second for this intervalDEC

Table 3-6 DISK_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameOutput kilobytes per second duringthis interval for the primary dis

Table 3-7 EVENTS_VW1 Field DefinitionsDescriptionData TypeField NamePrimary KeyTimestamp, in Local Civil Time,indicating when the event wasplaced in t

Table 3-7 EVENTS_VW1 Field Definitions (continued)DescriptionData TypeField NameInstance number of the processthat generated the event.INTEGER UNSIGNE

Table 3-8 SPACE_PARTITION_DETAIL_VW1 Field DefinitionsDescriptionData TypeField NameTimestamp, in Local Civil Time, indicatingwhen this row was update

Table 3-8 SPACE_PARTITION_DETAIL_VW1 Field Definitions (continued)DescriptionData TypeField NameCrash open. For disk objects other than SQLshorthand v

VIEW NEO.HP_METRICS.QUERY_RUNTIME_STATS_V1_2400...108Query Statistics for Release 2.4 ...

Table 3-8 SPACE_PARTITION_DETAIL_VW1 Field Definitions (continued)DescriptionData TypeField NameTimestamp, in Local Civil Time, when thepartition was

Table 3-9 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions (continued)DescriptionData TypeField NameNeoview system name, made up of the firstthree

Table 3-9 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions (continued)DescriptionData TypeField NameIncomplete SQL DDL operation. For SQLtables an

Table 3-9 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions (continued)DescriptionData TypeField NameMinimum value of ALLOC_EXTENT_CURRLARGEINTALLO

Table 3-9 SPACE_PARTITION_DETAIL_HISTORY_VW1 Field Definitions (continued)DescriptionData TypeField NameTimestamp, in Local Civil Time, when thepartit

SPACE_Q_DISK_FULL_VW1Table 3-11 SPACE_Q_DISK_FULL_VW1This report provides disk fullness information, including the percentage of disk space that iscur

SPACE_Q_PARTITION_FULL_VW1Table 3-14 SPACE_Q_PARTITION_FULL_VW1This report provides partition fullness information, including the percentage of partit

SPACE_Q_TABLE_FULL_VW1Table 3-18 SPACE_Q_TABLE_FULL_VW1This report provides table fullness information, including the percentage of table space thatis

SPACE_Q_TOP_10_PARTITION_DETAIL_VW1Table 3-22 SPACE_Q_TOP_10_PARTITION_DETAIL_VW1This report lists the partitioned objects with the top 10 sizes, in m

4 Examples and Guidelines for Creating Repository QueriesOverviewThis section provides some simple queries against the available Repository views and

List of Figures1-1 Neoview Repository and Related Products...166 List o

from NEO.HP_METRICS.QUERY_STATS_VW2ORDER BY EXEC_START_LCT_TS DESC FOR READ UNCOMMITTED ACCESS;Retrieve Statistics for Completed QueriesThis query lis

select [first 10] (EXEC_START_LCT_TS) AS EXEC_START_LCT_TS,(SEGMENT_ID) AS SEGMENT_ID,(QUERY_STATUS) AS QUERY_STATUS,(cLIENT_NAME) AS CLIENT_NAMEfrom

Count Completed Queries, According to Specified CriteriaThe following queries all return the number of completed queries, but they break down thenumbe

Queries within the Past 24 Hours, Grouped by Data SourceThis example indicates how many queries within the 24-hour period were associated with eachdat

count(*) as query_countfrom NEO.HP_METRICS.QUERY_STATS_VW2where query_status = 'COMPLETED'group by datasource, client_nameorder by datasou

The TRANSLATE function is another option for specifying a UCS2 literal. For example, thefollowing query uses the TRANSLATE function to translate an IS

66

A Sample Queries for Event InformationThese are examples of queries you can run against the error event data on the Neoview platformusing HPDM SQL Whi

Example A-1 Generate Update Stats Command ListThis report generates an executable command list to resolve missing table statistics, based ontwo event

Example A-3 Search Event Logs For a Specific Event NumberThis report lists all occurrences of a specified event number within a given time period.SELE

List of Tables3-1 QUERY_STATS_VW2 Field Definitions...203-2 SQL_T

Example A-5 Search Event Logs for Missing StatisticsThis report searches for all occurrences of missing SQL statistics messages 6007, 6008, 6010 and60

Example A-7 Search Event Logs for a Query IDThis report searches event text for a full or partial query_id during a given time period.SELECT EVEN

Example A-9 Summary of all EventsThis report summarizes all events for a given time period and returns results grouped by SSIDand event number.SELECT

B STATEMENT_TYPE FieldThe following table provides the compiler-provided values for the STATEMENT_TYPE field ofthe NEO.HP_METRICS.ODBC_QUERY_STATS_VW2

74

C Repository Views Disabled by DefaultThis appendix contains the following views that have been disabled by default:• “VIEW NEO.HP_METRICS.PROCESS_STA

Table C-1 PROCESS_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameANSI SQL time, in Local Civil Time,indicating when the measureme

Table C-1 PROCESS_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameProcessing node of the parent process(the process that started t

Table C-1 PROCESS_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameNumber of calls to open regular files,pipes, FIFOs, AF_INET sock

Table C-2 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameANSI SQL time of theaggregation, in Local Civil Time

List of ExamplesA-1 Generate Update Stats Command List...68A-2

Table C-2 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameTotal child busy time, inmicroseconds, divided by th

Table C-2 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameStandard deviation of percentbusy for processes with

Table C-2 PROCESS_AGGR_LEVEL1_STATS_V1 Field Definitions (continued)DescriptionData TypeField NameCount of processes with pagefaultsINTEGER UNSIGNEDNU

• TABLE_STATS_V2 provides the most recently collected data for each monitored object.Thus, the view includes only one row per monitored table or mater

Table C-3 TABLE_STATS_V2 and TABLE_STATS_DETAIL_V2 Field Definitions (continued)DescriptionData TypeField NameNamespace in which the object is visible

Table C-3 TABLE_STATS_V2 and TABLE_STATS_DETAIL_V2 Field Definitions (continued)DescriptionData TypeField NameCumulative list of columns for whichstat

86

D History of New and Changed Information in PreviousReleases of the RepositoryNew and Changed Information in Previous EditionsThe Release 2.4 Service

The Release 2.4 edition of this manual included the following new and changed information:• Clarify and correct text related to space and heap.• Misce

E Pre-R2.5 Query Statistics ViewsQuery Statistics for R2.4 SP2VIEW NEO.HP_METRICS.QUERY_STATS_VW1Repository collects compilation and execution query s

About This DocumentThis document describes the views supported by the Neoview Manageability Repository, aNeoview SQL database and set of programs that

Table E-1 QUERY_STATS_VW1 Field Definitions (continued)DescriptionData TypeField NameRepository-generated ANSI Timestamp, inLocal Civil Time, showing

Table E-1 QUERY_STATS_VW1 Field Definitions (continued)DescriptionData TypeField NameDatasource name on the Neoview platformto which the client connec

Table E-1 QUERY_STATS_VW1 Field Definitions (continued)DescriptionData TypeField NameANSI Timestamp, in Coordinated UniversalTime, indicating when the

Table E-1 QUERY_STATS_VW1 Field Definitions (continued)DescriptionData TypeField NameIndicates whether this query is required toexecute under a transa

Table E-1 QUERY_STATS_VW1 Field Definitions (continued)DescriptionData TypeField NameEstimated total cost (time in seconds) of theSQL operations for t

Table E-1 QUERY_STATS_VW1 Field Definitions (continued)DescriptionData TypeField NameEstimate of the number of seconds ofprocessor time it might take

Table E-1 QUERY_STATS_VW1 Field Definitions (continued)DescriptionData TypeField NameWhat the executor is doing. Possible querystates include:INITIALO

Table E-1 QUERY_STATS_VW1 Field Definitions (continued)DescriptionData TypeField NameAn approximation, in microseconds, of thetotal node time spent in

Table E-1 QUERY_STATS_VW1 Field Definitions (continued)DescriptionData TypeField NameAmount of “space” type (static) memory, inkilobytes, allocated (r

Table E-1 QUERY_STATS_VW1 Field Definitions (continued)DescriptionData TypeField NameQuery ID for the immediate parent of thisquery ID. If the parent