====== DB2 Log management ======

== Total logs used ==
<sxh bash>
db2 "select LOG_UTILIZATION_PERCENT, TOTAL_LOG_USED_KB/1024 MB_USED, TOTAL_LOG_USED_TOP_KB/1024 MB_TOP, TOTAL_LOG_AVAILABLE_KB/1024 MB_AVAILABLE from sysibmadm.log_utilization"
</sxh>

**Log used for connected apps :**
<sxh bash>
db2 "select APPLICATION_ID, UOW_START_TIME, NUM_LOCKS_HELD, UOW_LOG_SPACE_USED from table(MON_GET_UNIT_OF_WORK(NULL,-2)) as t with ur"
</sxh>


**Log paths for all DBs :**
<sxh bash>
for db in `db2 list db directory | grep -p Indirect | grep alias | awk '{print $NF}'`; do echo "${db} : `db2 get db cfg for ${db} | grep log | grep Path | awk '{print $NF}'`"; done
</sxh>

----



== Switch to archival logging ==

<sxh bash>
db2 "CONNECT TO HADR01";
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
UNQUIESCE DATABASE;
CONNECT RESET;
CONNECT TO HADR01;
UPDATE DATABASE CONFIGURATION USING logarchmeth1 "DISK:/db2logs/archive" newlogpath "/db2logs" logprimary 3 logsecond 2 logfilsiz 1000 LOGINDEXBUILD OFF ;
CONNECT RESET;
DEACTIVATE DATABASE HADR01;
BACKUP DATABASE HADR01 TO "/db2backup" EXCLUDE LOGS WITHOUT PROMPTING;
</sxh>

**Finding log file name for LSN (Log Sequence Number) :**
<sxh bash>
db2flsn
</sxh>

**Check if archived log is valid :**
<sxh bash>
db2cklog
</sxh>

**Log settings :**
<sxh bash>
db2 update db cfg for <DB> using logfilsiz XXXXX logprimary XXX logsecond XXX
</sxh>

**Transactional log archiving history :**
<sxh bash>
db2 "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"
</sxh>