DB2 Log management

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"

Log used for connected apps :

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"

Log paths for all DBs :

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

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;

Finding log file name for LSN (Log Sequence Number) :

db2flsn

Check if archived log is valid :

db2cklog

Log settings :

db2 update db cfg for <DB> using logfilsiz XXXXX logprimary XXX logsecond XXX

Transactional log archiving history :

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"