Tuesday, July 9, 2013

Reorg History

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;

No comments:

Post a Comment