HP Neoview Release 2.5 Software User Manual

Page 62

Count Completed Queries, According to Specified Criteria

The following queries all return the number of completed queries, but they break down the
number in different ways.

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_VW2
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;

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_VW2
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;

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_VW2
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;

Examples and Guidelines for Creating Repository Queries