HP Neoview Release 2.4 Software - Manual (Page 65)

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.
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.
• Information identifying a query, session, or other object, for instance who initiated it or
when it occurred. Examples of columns that contain 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.
Thus, using SELECT (*) in a programmatic query (embedded in a program) is not guaranteed
to return columns in the same order all the time.
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. Queries against views that provide table data will return data
only if the table statistics data collection is enabled in Dashboard.
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;
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;
Overview 65