Neoview Repository User Guide (R2.4 SP1)

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:
• Metrics 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, QUERY_ID, DATASOURCE, STATEMENT_TYPE, and QUERY_STATUS.
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 using the asterisk (*) wildcard 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.QUERY_STATS_VW1 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
This query retrieves the ten rows most recently added to a view.
select [first 10] * from NEO.HP_METRICS.QUERY_STATS_VW1
ORDER BY EXEC_START_LCT_TS DESC FOR READ UNCOMMITTED ACCESS;
Retrieve Statistics for Completed Queries
This query lists information for the ten most recent queries that completed normally. You could
enhance this query to filter for time ranges, user IDs, or other attributes.
The list following the query shows values that might be returned in a single record.
SELECT [FIRST 10] *
FROM NEO.HP_METRICS.QUERY_STATS_VW1
WHERE QUERY_STATUS = 'COMPLETED'
Overview 75