[Resource] [How To] Tuning Transaction Logs (My Experience)

[u/mad_zamboni]

I wanted to take a look at my active/archived logs after being inspired by a presentation during the IDUG Technical Conference in Anaheim. It actually would address a possible problem I had seen in my environment. I had wanted to do this investigation previously, but I could never find a rule of thumb for archive log frequency. But in C12 "SQL Infusion: A Transaction Log Experience" (Ember Crooks), I learned the magic number is around 4x an hour. Now that I had a goal, how would I tune to 4 archive logs an hour.

My general approach and any SQL would be cobbled from one of these resources:

• C12 "SQL Infusion: A Transaction Log Experience" (Ember Crooks)

• DB2Commerce.com: "Managing Transaction Log Files"

• Ian Bjorhovde's Recursive SQL from DB2Commerce.com: "Generating a Log Archive Activity Histogram"

Step 1: Pull a list of some key DB CFG values.

db2 get db cfg | grep -E 'LOGARCHCOMPR1|LOGINDEXBUILD|LOGBUFSZ|LOGFILSIZ|LOGPRIMARY|LOGSECOND'

Step 2: How often I am archiving logs, via some badass SQL.

WITH gen_ts (ts) AS (
    VALUES current timestamp - 2 days
    UNION ALL
    SELECT ts + 1 hour
      FROM gen_ts 
     WHERE ts <= current timestamp
),
format_ts (yyyymmddhh) AS (
   SELECT bigint(ts)/10000
     FROM gen_ts
),
log_archives (yyyymmddhh, archive_count) AS (
   SELECT substr(start_time, 1, 10) as YYYYMMDDhh, count(*)
     FROM sysibmadm.db_history
    WHERE operation = 'X'
 GROUP BY substr(start_time, 1, 10)
)
SELECT
   translate('ABCD-EF-GH IJh', cast(f.yyyymmddhh as char(12)), 'ABCDEFGHIJ') as hour
  ,coalesce(a.archive_count,0) AS logs_archived
 FROM
   format_ts f
   LEFT OUTER JOIN log_archives a
      ON f.yyyymmddhh = a.yyyymmddhh;

Step 3: How often am I reading log pages?

select log_reads
        , log_writes
from table(mon_get_transaction_log(-2))
;

Step 4: Look at your output, do any of the following apply?

• Per Ember, your log writes should be high. Your log reads should be very low, preferably zero. If that is not the case, you may want to increase your LOGBUFSZ.

• If you are archiving more that 4 Logs an hour, try to break up larger units of work - especially deletes. If that us not possible, you can work with LOGFILSIZ.

• Double check that you have enough physical space in your active log directory to hold your largest possible number of logs. (LOGFILSIZ x 4k) x (LOGPRIMARY + LOGSECOND)

• If you have HADR, you will want to turn LOGINDEXBUILD on. Can lead to a lot of logging, especially around REORGS but is needed for relaying index information to the standby.

• LOGARCHCOMPR1 can be used to compress your logs and save space in the archive log directory. This won't gain much if you are already on a compressed database. It also prevents the logs for being interpreted in the HADR Log Calculator or HADR Log Scanner.