HP Neoview Release 2.3 Software - Manual (Page 61)

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.
In general, each row in a Repository view includes two kinds of data:
• Observations or statistics pertaining to a query, session, or other object. Examples of columns
that contain this kind of information are MESSAGE_BYTES_TO_DISK, MESSAGES_TO_DISK,
ROWS_ACCESSED, ROWS_RETRIEVED, NUM_ROWS_IUD, LOCK_ESCALATIONS, and
LOCK_WAITS.
• Identifying information about a query, session, or other object, for instance who initiated it
or when it occurred. Examples of columns that contains this kind of information are
USER_NAME, CLIENT_ID, DATASOURCE, STATEMENT_TYPE, QUERY_START_DATE,
QUERY_START_TIME, QUERY_END_DATE, and QUERY_END_TIME.
To make the examples as simple as possible, most of these queries select all columns, using the
asterisk (*) as a wildcard. In practice, selecting all columns is not desirable because
• It is easier to extract meaningful information by focusing on fields relevant to the question
being asked.
• The number and definitions of columns in a view might change in future versions.
To increase database concurrency, submit queries using the “FOR READ UNCOMMITTED
ACCESS” option.
Queries against views that provide process or process aggregation data will return data only if
process data collection is enabled.
Get the Number of Rows in a View
Each of these queries gets the number of rows in a specific view:
select count(*) from NEO.HP_METRICS.ODBC_QUERY_STATS_V1 FOR READ UNCOMMITTED ACCESS;
select count(*) from NEO.HP_METRICS.ODBC_SESSION_STATS_V1 FOR READ UNCOMMITTED ACCESS;
select count(*) from NEO.HP_METRICS.PROCESS_AGGR_LEVEL1_STATS_V1 FOR READ UNCOMMITTED ACCESS;
Retrieve a Specified Number of Rows from a View
Each of these queries retrieves ten rows from a specific view. The first two queries retrieve the
ten rows most recently added to this view. The other three queries retrieve rows representing
the ten most recent queries, sessions, or aggregations.
select [first 10] * from NEO.HP_METRICS.ODBC_QUERY_STATS_V1
ORDER BY QUERY_EVENT_DATETIME DESC FOR READ UNCOMMITTED ACCESS;
select [first 10] * from NEO.HP_METRICS.ODBC_SESSION_STATS_V1
ORDER BY SESSION_EVENT_DATETIME DESC FOR READ UNCOMMITTED ACCESS;
select [first 10] * from NEO.HP_METRICS.ODBC_QUERY_STATS_V2
ORDER BY QUERY_START_DATETIME DESC FOR READ UNCOMMITTED ACCESS;
select [first 10] * from NEO.HP_METRICS.ODBC_SESSION_STATS_V2
ORDER BY SESSION_START_DATETIME DESC FOR READ UNCOMMITTED ACCESS;
select [first 10] * from NEO.HP_METRICS.PROCESS_AGGR_LEVEL1_STATS_V1
ORDER BY SAMPLE_DATE DESC, SAMPLE_TIME DESC FOR READ UNCOMMITTED ACCESS;
Retrieve Statistics for Completed Queries
This query lists information for the ten most recent queries that terminated normally. You could
enhance this query to filter for time ranges, user IDs, or other attributes.
Overview 61