Neoview Repository User Guide (R2.4 SP1)

ValueField
NULL“PROCESS_CREATE_BUSY_TIME”
NULL“PROCESS_CREATE_BUSY_TIME_SEC”
“SELECT AVG(CPU) CPU, AVG(MEMORY) MEMORY,…”“SQL_TEXT”
0“SQL_TEXT_OVERFLOW_INDICATOR”
1 The USER_NAME value is not restricted for something as simple as “joesmith.” The value might take another form,
for example, it could be the user's email address or include a domain name.
List Statement Counts for a Selected Time Period
This query returns statement counts by user name for a given time period that you provide.
SELECT
USER_NAME,
count(*) as TTL_STMTS,
cast(MIN((QUERY_EVENT_DATETIME)) as timestamp(0)) as FIRST_TIME,
cast(MAX((QUERY_EVENT_DATETIME)) as timestamp(0)) as LAST_TIME,
sum(case when ERROR_CODE not like '0%' AND error_code not like '-1 %' then 1 else 0 end ) as error_count,
SUM(DISK_READS) as TTL_DISK_READS,
SUM(MESSAGES_TO_DISK) as TTL_MESSAGES_TO_DISK,
SUM(MESSAGE_BYTES_TO_DISK) as TTL_MESSAGE_BYTES_TO_DISK,
SUM(NUM_ROWS_IUD) as TTL_NUM_ROWS_IUD,
SUM(ROWS_ACCESSED) as TTL_ROWS_ACCESSED,
SUM(ROWS_RETRIEVED) as TTL_ROWS_RETRIEVED,
SUM(LOCK_ESCALATIONS) as TTL_LOCK_ESCALATIONS,
SUM(LOCK_WAITS) as TTL_LOCK_WAITS
FROM ODBC_QUERY_STATS_V1
WHERE
(QUERY_EVENT_DATETIME) >= cast(cast(('2009-08-15') as DATE) as TIMESTAMP(0))
AND (QUERY_EVENT_DATETIME) < CURRENT_TIMESTAMP
AND STATEMENT_STATUS = 'END'
AND SEQUENCE_NUM = 0
GROUP BY
USER_NAME
ORDER BY
USER_NAME;
ValueField
joesmithUSER_NAME
33530TTL_STMTS
2009-08-23 08:23:33FIRST_TIME
2009-08-24 06:57:0LAST_TIME
2254ERROR_COUNT
2128TTL_DISK_READS
80702TTL_MESSAGES_TO_DISK
1955319932TTL_MESSAGE_BYTES_TO_DISK
16792820TTL_NUM_ROWS_IUD
33584132TTL_ROWS_ACCESSED
33584104TTL_ROWS_RETRIEVED
4TTL_LOCK_ESCALATIONS
29TTL_LOCK_WAITS
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.
82 Examples and Guidelines for Creating Repository Queries