Generating Log Archive Activity Histograms

You may also like...

8 Responses

  1. Greg says:

    Useful script, I’ve done this the hard way when I’ve previously needed it!

    The TRANSLATE is clever, but you don’t need it – you can use TO_CHAR to format the output and also get rid of the CTE for ‘format_ts’ :

    WITH gen_ts (ts, ymdh ) AS (
    VALUES (CURRENT TIMESTAMP – 7 DAYS, TO_CHAR(CURRENT TIMESTAMP – 7 DAYS, ‘YYYYMMDD HH24′)||’h’ )
    UNION ALL
    SELECT ts + 1 hour, TO_CHAR(ts + 1 hour, ‘YYYYMMDD HH24′)||’h’
    FROM gen_ts
    WHERE ts <= CURRENT TIMESTAMP
    ),
    log_archives (yyyymmddhh, archive_count) AS (
    SELECT TO_CHAR(TIMESTAMP(start_time), 'YYYYMMDD HH24')||'h' as YYYYMMDDhh, count(*)
    FROM sysibmadm.db_history
    WHERE operation = 'X'
    GROUP BY TO_CHAR(TIMESTAMP(start_time), 'YYYYMMDD HH24')||'h'
    )
    SELECT
    g.ymdh as hour
    ,coalesce(a.archive_count,0) AS logs_archived
    FROM
    gen_ts g
    LEFT OUTER JOIN log_archives a
    ON g.ymdh = a.yyyymmddhh;

    • I prefer the `format_ts` CTE if only because it separates the logic of formatting the timestamp from the recursive query – I think it’s perhaps a little easier to understand – but using `TO_CHAR` in the final query would certainly be a better solution than `TRANSLATE`. Thanks!

  2. Joachim says:

    Hi Ian,

    Looks like my query, but much smarter with your calendar and translate. But sometimes you have to know the amount of logs and the log size…

    SELECT H.DATE AS HOUR, H.DBPARTITIONNUM, H.NUMBER_OF_LOGS_PER_HOUR, H.NUMBER_OF_LOGS_PER_HOUR * L.LOGSIZE_MB AS AMOUNT_LOGS_HOUR_MB, LOGSIZE_MB
    FROM (SELECT (VALUE * 4096) / 1024 / 1024 AS LOGSIZE_MB, DBPARTITIONNUM
    FROM SYSIBMADM.DBCFG AS DBCFG
    WHERE NAME IN (‘logfilsiz’)) AS L, (
    SELECT SUBSTR(END_TIME, 1, 10) AS DATE, DBPARTITIONNUM,
    COUNT AS NUMBER_OF_LOGS_PER_HOUR
    FROM SYSIBMADM.DB_HISTORY
    WHERE OPERATION = ‘X’ AND OPERATIONTYPE = ‘1’
    AND TIMESTAMP(END_TIME) > CURRENT_DATE – 14 DAYS
    GROUP BY SUBSTR(END_TIME, 1, 10), DBPARTITIONNUM) AS H
    WHERE L.DBPARTITIONNUM = H.DBPARTITIONNUM
    ORDER BY 1, 2;

    Best regards
    Joachim

  3. Philip Louw says:

    Hi.

    I have been using the amount of logs archived as a proxy for activity too. However due to some constrains of our archiving solution we have to increase the log file sizes to keep things running, This has thrown my statistics out the window.
    Is there a table that contains the actual size of every log file.

    Regards

    Philip.

  4. Marian says:

    Hello,

    Great select but I’m wondering how to advance the script to see logs generated per day, not per hour.
    Can anybody advice???

    Thanks a lot!
    Marian

    • Marian,

      You can use substr(start_time, 1, 8) as YYYYMMDD in the query (i.e., shortening the substring by 2 characters) against sysibmadm.db_history to give you a summary by date.

      If you have at least 1 log file archived per day, you will probably not even need to use a calendar table.

  1. May 19, 2015

    […] Generating Log Archive Activity Histograms […]

Leave a Reply

Your email address will not be published. Required fields are marked *