Software User guide

Completed Queries Executed on a System in the Previous Two Days
To count queries that executed from one midnight to the next, instead of within a 24 hour period
relative to the current time, the following query uses the Neoview DATE_TRUNC function in
the SQL WHERE clause.
SELECT DISTINCT(QUERY_START_DATE) AS START_DATE,
COUNT(*) AS NUM_OF_QUERIES,
MAX(QUERY_START_TIME) AS MAX_START_TIME,
MIN(QUERY_START_TIME) AS MIN_START_TIME
FROM NEO.HP_METRICS.ODBC_QUERY_STATS_V2
WHERE QUERY_START_DATETIME <= CURRENT
AND QUERY_START_DATETIME >= DATE_TRUNC('DAY', CURRENT - INTERVAL '1' DAY)
AND STATEMENT_STATE = 'COMPLETE'
GROUP BY 1
FOR READ UNCOMMITTED ACCESS;
MIN_START_TIMEMAX_START_TIMENUM_OF_QUERIESSTART_DATE
00:23:5912:48:46.8797744792007–05–23
13:15:1418:44:05.390777492007–05–22
Queries within the Past 24 Hours, Grouped by Client ID
This example indicates how many queries different clients ran within a 24-hour period.
SELECT client_id, COUNT(*) AS NUM_OF_QUERIES
FROM NEO.HP_METRICS.ODBC_QUERY_STATS_V2
WHERE QUERY_START_DATETIME <= CURRENT
AND QUERY_START_DATETIME >= CURRENT - INTERVAL '1' DAY
AND STATEMENT_STATE = 'COMPLETE'
GROUP BY client_id
ORDER BY client_id
FOR READ UNCOMMITTED ACCESS;
NUM_OF_QUERIESCLIENT_ID
16EXPTC082205
51ORDROBERTSC-P2
162ORDHROTHGAR-P2
28ordbantam-p2
Queries within the Past 24 Hours, Grouped by Data Source
This example indicates how many queries within the 24-hour period were associated with each
data source.
SELECT datasource, COUNT(*) AS NUM_OF_QUERIES
FROM NEO.HP_METRICS.ODBC_QUERY_STATS_V2
WHERE QUERY_START_DATETIME <= CURRENT
AND QUERY_START_DATETIME >= CURRENT - INTERVAL '1' DAY
AND STATEMENT_STATE = 'COMPLETE'
GROUP BY datasource
70 Examples and Guidelines for Creating Repository Queries