HP Neoview Release 2.4 Software - Manual (Page 71)

Queries within the Past 24 Hours, Grouped by Client Name and Including Average
and Maximum Elapsed Time
SELECT client_name,
COUNT(*) AS num_of_queries,
AVG(query_elapsed_time)/1000 AS avg_qry_elapsed_msec,
MAX(query_elapsed_time)/1000 AS max_qry_elapsed_msec
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;
MAX_QRY_ELAPSED_TIME AVG_QRY_ELAPSED_MSEC NUM_OF_QUERIES CLIENT_NAME
16931 1316 52 SJUMT1
3981 26 980 exedl1-1
355007 19722 25 scott1
1376 122 38 sqdev8
Formatting Techniques to Enhance Readability of Query Output
Often the typical data in a column is much narrower than the maximum defined column size.
For example, the SESSION_ID column is defined as CHARACTER(108), and the DATASOURCE
column is defined as CHARACTER(128); actual values in those columns are usually much shorter.
Many query tools support automatic adjustment of column size for viewing results, but in some
cases, it is necessary to adjust the size of the columns returned from the query to improve
readability of the results.
Neoview SQL supports several string manipulation functions. Among these, the LEFT function
is useful for simple column size reduction. In addition to string functions, use of CAST is
sometimes helpful for modifying the appearance or size of numeric fields.
For example, the following query produces results that can be difficult to read:
select [first 10]
datasource,
client_name,
count(*) as query_count
from NEO.HP_METRICS.QUERY_STATS_VW1
where query_status = 'COMPLETED'
group by datasource, client_name
order by datasource, client_name;
DATASOURCE
CLIENT_NAME QUERY_COUNT
--------------------------------------------------------------------------------------------------------------------------------
----------------- --------------------
ARSQLF
exedl1-1.caclab 4454231
Admin_Load_DataSource
D-138202 2510
Admin_Load_DataSource
EC4T12HP135360 3
Admin_Load_DataSource
NEHAG 2753
Admin_Load_DataSource
NPATTIPATI 1495
Admin_Load_DataSource
THARAKNAT-DC76 11
Admin_Load_DataSource
TXNSNELLURU 58
Admin_Load_DataSource
WUZHO 36
Admin_Load_DataSource
YZHANG106 10
Admin_Load_DataSource
exedl1-1.caclab 49952
Formatting Techniques to Enhance Readability of Query Output 71