Neoview Repository User Guide (R2.4 SP1)

Completed Queries Executed on a System in the Last 24 Hours
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 >= CURRENT - INTERVAL '1' DAY
AND QUERY_STATUS = 'COMPLETED'
GROUP BY 1
FOR READ UNCOMMITTED ACCESS;
MIN_START_TIMEMAX_START_TIMENUM_OF_QUERIESSTART_DATE
08:51:0112:48:46.8797742432009–05–23
13:15:1418:44:05.390777492009–05–22
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_TIMEMAX_START_TIMENUM_OF_QUERIESSTART_DATE
00:23:5912:48:46.8797744792009–05–23
13:15:1418:44:05.390777492009–05–22
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_QUERIESCLIENT_NAME
16EXPTC082205
51ORDROBERTSC-P2
162ORDHROTHGAR-P2
28ordbantam-p2
Count Completed Queries, According to Specified Criteria 83