How to get history of reorg on a particular table
SELECT
smallint(DBPARTITIONNUM) as PARTITION,
CASE OPERATIONTYPE
WHEN 'F' THEN 'OFFLINE'
WHEN 'N' THEN 'ONLINE'
END as REORG_TYPE,
substr(char(TIMESTAMP(START_TIME)),1,19) AS START_TIME,
substr(char(TIMESTAMP(END_TIME)),1,19) AS END_TIME,
TIMESTAMPDIFF(4,CHAR(TIMESTAMP(END_TIME)-TIMESTAMP(START_TIME))) as "ELAPSED(MIN)",
coalesce(SQLCODE,0) as SQLCODE,
CASE WHEN SQLCODE < 0 THEN 'FAILED' ELSE 'SUCCEEDED' END as STATUS
FROM
sysibmadm.db_history as a
WHERE OPERATION = 'G'
AND (TIMESTAMP(START_TIME) + 5 DAYS) >
CURRENT TIMESTAMP
AND TABSCHEMA = 'SCHEMA'
AND TABNAME = 'TAB'
ORDER BY START_TIME, PARTITION
for fetch only with ur;
SELECT
smallint(DBPARTITIONNUM) as PARTITION,
CASE OPERATIONTYPE
WHEN 'F' THEN 'OFFLINE'
WHEN 'N' THEN 'ONLINE'
END as REORG_TYPE,
substr(char(TIMESTAMP(START_TIME)),1,19) AS START_TIME,
substr(char(TIMESTAMP(END_TIME)),1,19) AS END_TIME,
TIMESTAMPDIFF(4,CHAR(TIMESTAMP(END_TIME)-TIMESTAMP(START_TIME))) as "ELAPSED(MIN)",
coalesce(SQLCODE,0) as SQLCODE,
CASE WHEN SQLCODE < 0 THEN 'FAILED' ELSE 'SUCCEEDED' END as STATUS
FROM
sysibmadm.db_history as a
WHERE OPERATION = 'G'
AND (TIMESTAMP(START_TIME) + 5 DAYS) >
CURRENT TIMESTAMP
AND TABSCHEMA = 'SCHEMA'
AND TABNAME = 'TAB'
ORDER BY START_TIME, PARTITION
for fetch only with ur;
No comments:
Post a Comment