Neoview Repository User Guide (R2.4 SP2)

Example A-1 Generate Update Stats Command List
This report generates an executable command list to resolve missing table statistics, based on
two event numbers:
SELECT
COUNT(*) AS CNT,
MIN(LOG_TIMESTAMP_LCT) AS FIRST_TIME,
MAX(LOG_TIMESTAMP_LCT ) AS LAST_TIME,
UPDATE_STATS_COMMAND || ';' as UPDATE_STATS_COMMAND,
EVENT_NUMBER
FROM
(
SELECT
log_timestamp_lct,
trim(trailing '.'
FROM
trim
(
CASE
WHEN locate( 'UPDATE STATISTICS FOR TABLE',TEXT) > 0
THEN
--'GOT A MATCH'
substring(text
FROM
LOCATE ('UPDATE STATISTICS FOR TABLE',TEXT) )
ELSE '/*Unable to create update statistics command. Invalid value in EMS event*/'
END
)
) as UPDATE_STATS_COMMAND,
EVENT_NUMBER
FROM
NEO.HP_METRICS.EVENTS_VW1
WHERE
log_timestamp_lct >= TIMESTAMP '$$_FROM_TIME$$'
AND log_timestamp_lct <= TIMESTAMP '$$_TO_TIME$$'
and SSID like '%SQLMX%'
and (event_number = 6010 or event_number = 6011)
) AS A
GROUP BY UPDATE_STATS_COMMAND, EVENT_NUMBER
ORDER BY
CNT DESC
for read uncommitted access in share mode;
/*QueryDocumentationStart
(C) COPYRIGHT 2010 HEWLETT-PACKARD DEVELOPMENT COMPANY, LP.
QueryTitle: Generate Update Stats Command List
Application: Neoview Reports, HPDM
QueryStyleRevNum: S2.0
ReposRevNum: R2.4 SP2
QueryRevNum:Q1
74 Sample Queries for Event Information