HP Neoview Release 2.4 Software - Manual (Page 67)

4 Examples and Guidelines for Creating Repository Queries
Overview
This section provides 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.
With regard to creating Repository queries, here are a few tips:
• Using SELECT (*) in a programmatic query (embedded in a program) is not guaranteed to
return columns in the same order all the time. The following list applies to cases when the
result of the query is parsed programmatically or used in an automated calculation. Although
a few of the sample queries use the asterisk wildcard (*) to select all columns in the view for
simplicity, generally using the asterisk (*) wildcard is not desirable because:
— It is easier to extract meaningful information by selecting individual fields relevant to
the question being asked.
— The number and definitions of columns in a view might change in future versions.
— Column name changes can cause problems in programs.
If you are not parsing the result data, selecting all columns by using the asterisk (*) is
acceptable.
• To increase database concurrency, views use the “FOR READ UNCOMMITTED ACCESS”
option. If you are creating your own views, please include this option in the view creation
statements.
• 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] (EXEC_START_LCT_TS) AS EXEC_START_LCT_TS
,(SEGMENT_ID) AS SEGMENT_ID
,(QUERY_STATUS) AS QUERY_STATUS
,(QUERY_ELAPSED_TIME) AS QUERY_ELAPSED_TIME
,(cLIENT_NAME) AS CLIENT_NAME
from NEO.HP_METRICS.QUERY_STATS_VW1
ORDER BY EXEC_START_LCT_TS DESC FOR READ UNCOMMITTED ACCESS;
Overview 67