HP Neoview Release 2.4 Software User 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

SJUMT1

3981

980

exedl1-1

355007

19722

scott1

1376

122

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