HP Neoview Release 2.4 Software - Manual (Page 70)

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 (CAST(EXEC_START_LCT_TS AS DATE)) AS START_DATE,
COUNT(*) AS NUM_OF_QUERIES,
MAX(CAST(EXEC_START_LCT_TS AS TIME(6))) AS MAX_START_TIME,
MIN(CAST(EXEC_START_LCT_TS AS TIME(6))) AS MIN_START_TIME
FROM NEO.HP_METRICS.QUERY_STATS_VW1
WHERE EXEC_START_LCT_TS <= CURRENT
AND EXEC_START_LCT_TS >= DATE_TRUNC('DAY', CURRENT - INTERVAL '1' DAY)
AND QUERY_STATUS = 'COMPLETED'
GROUP BY 1
FOR READ UNCOMMITTED ACCESS;
MIN_START_TIME MAX_START_TIME NUM_OF_QUERIES START_DATE
15:15:34.092368 16:16:30.572346 20 2010-04-25
04:20:09.297520 10:56:20.568352 1072 2010-04-26
Queries within the Past 24 Hours, Grouped by Client Name
This example indicates how many queries different clients ran within a 24-hour period.
SELECT client_name, COUNT(*) AS NUM_OF_QUERIES
FROM NEO.HP_METRICS.QUERY_STATS_VW1
WHERE EXEC_START_LCT_TS <= CURRENT
AND EXEC_START_LCT_TS >= CURRENT - INTERVAL '1' DAY
AND QUERY_STATUS = 'COMPLETED'
GROUP BY client_name
ORDER BY client_name
FOR READ UNCOMMITTED ACCESS;
NUM_OF_QUERIES CLIENT_NAME
52 SJUMT1
980 exedl1-1
23 scott1
38 sqdev8
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.QUERY_STATS_VW1
WHERE EXEC_START_LCT_TS <= CURRENT
AND EXEC_START_LCT_TS >= CURRENT - INTERVAL '1' DAY
AND QUERY_STATUS = 'COMPLETED'
GROUP BY datasource
ORDER BY datasource
FOR READ UNCOMMITTED ACCESS;
NUM_OF_QUERIES DATASOURCE
996 Admin_Load_DataSource
98 TDM_Default_DataSource
70 Examples and Guidelines for Creating Repository Queries