IBM Support

[Db2] How to maintain transaction log files

Question & Answer


Question

How can I maintain unnecessary archived log files?

Answer

There are three general methods to maintain unnecessary archived log files.

1) maintain archived log files before specific date by using PRUNE HISTORY command
You can delete archived log file along with corresponding entry in recovery history file. Run PRUNE HISTORY command with AND DELETE parameter, or invoke db2Prune() API with DB2PRUNE_OPTION_DELETE flag was set. The archived log entries before specified date will be deleted along with corresponding log file.
Note: If LOGARCHMETH1 database configuration parameter was set as LOGRETAIN, log file will not be deleted even though the corresponding entry in recovery log file was automatically deleted.
Example steps:
List the backup history from recovery history file. Determine which backup image needs to be kept (that means, older one can be removed).
In this case backup image taken at "20140613151650001" and later need to be kept, and remove older backup image and related objects. You have to keep log file "S0001039.LOG", which is the oldest log file to recover from the backup "20140613151650001". The log file "S0001038.LOG" or older one can be removed.
  • $ db2 "list history all for db DB01" | grep " B "
      B  D  20140613145851001   N    D  S0001033.LOG S0001035.LOG  
      B  D  20140613150712001   N    D  S0001036.LOG S0001038.LOG  
      B  D  20140613151650001   N    D  S0001039.LOG S0001041.LOG  
      B  D  20140613153005001   N    D  S0001042.LOG S0001044.LOG  
      B  D  20140613153424001   N    D  S0001045.LOG S0001047.LOG

Verify the archived log files exist in archived log path.
  • $ ls -la
    total 47904
    drwxr-x---.  2 db2inst1 db2iadm1    4096  Jun 13 15:54 2014 .
    drwxr-x---. 12 db2inst1 db2iadm1    4096  Apr 18 21:39 2014 ..
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 14:59 2014 S0001033.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:00 2014 S0001034.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 1548288  Jun 13 15:01 2014 S0001035.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:08 2014 S0001036.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:08 2014 S0001037.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 1466368  Jun 13 15:09 2014 S0001038.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:17 2014 S0001039.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:18 2014 S0001040.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 1396736  Jun 13 15:19 2014 S0001041.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:31 2014 S0001042.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:31 2014 S0001043.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 1200128  Jun 13 15:32 2014 S0001044.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:35 2014 S0001045.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:36 2014 S0001046.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 1409024  Jun 13 15:37 2014 S0001047.LOG
List the archived log file history from the recovery history file. Verify the timestamp of "S0001038.LOG" ("20140613150851" in sample below).
$ db2 "list history all for db DB01" | grep " X "    
  •   X  D  20140613145851      1    D  S0001033.LOG C0000009      
      X  D  20140613145951      1    D  S0001034.LOG C0000009      
      X  D  20140613150029      1    D  S0001035.LOG C0000009      
      X  D  20140613150712      1    D  S0001036.LOG C0000009      
      X  D  20140613150811      1    D  S0001037.LOG C0000009      
      X  D  20140613150851      1    D  S0001038.LOG C0000009      
      X  D  20140613151648      1    D  S0001039.LOG C0000009      
      X  D  20140613151747      1    D  S0001040.LOG C0000009      
      X  D  20140613151829      1    D  S0001041.LOG C0000009      
      X  D  20140613153005      1    D  S0001042.LOG C0000009      
      X  D  20140613153109      1    D  S0001043.LOG C0000009      
      X  D  20140613153149      1    D  S0001044.LOG C0000009      
      X  D  20140613153430      1    D  S0001045.LOG C0000009      
      X  D  20140613153532      1    D  S0001046.LOG C0000009      
      X  D  20140613153609      1    D  S0001047.LOG C0000009

Run PRUNE HISTORY command specified the timestamp found above ("20140613150851"), with AND DELETE parameter.
  • $ db2 prune history 20140613150851 and delete
    DB20000I  The PRUNE command completed successfully.

Verify the entries before the specified timestamp was removed from recovery history file as expected.
  • $ db2 "list history all for db DB01" | grep " X "
      X  D  20140613151648      1    D  S0001039.LOG C0000009      
      X  D  20140613151747      1    D  S0001040.LOG C0000009      
      X  D  20140613151829      1    D  S0001041.LOG C0000009      
      X  D  20140613153005      1    D  S0001042.LOG C0000009      
      X  D  20140613153109      1    D  S0001043.LOG C0000009      
      X  D  20140613153149      1    D  S0001044.LOG C0000009      
      X  D  20140613153430      1    D  S0001045.LOG C0000009      
      X  D  20140613153532      1    D  S0001046.LOG C0000009      
      X  D  20140613153609      1    D  S0001047.LOG C0000009

Verify the corresponding archived log file was removed from archived log path.
  • $ ls -la
    total 28544
    drwxr-x---.  2 db2inst1 db2iadm1    4096  Jun 13 16:04 2014 .
    drwxr-x---. 12 db2inst1 db2iadm1    4096  Apr 18 21:39 2014 ..
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:17 2014 S0001039.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:18 2014 S0001040.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 1396736  Jun 13 15:19 2014 S0001041.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:31 2014 S0001042.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:31 2014 S0001043.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 1200128  Jun 13 15:32 2014 S0001044.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:35 2014 S0001045.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 4202496  Jun 13 15:36 2014 S0001046.LOG
    -rw-r-----.  1 db2inst1 db2iadm1 1409024  Jun 13 15:37 2014 S0001047.LOG
 
When you set AUTO_DEL_REC_OBJ database configuration parameter to ON, related recovery object such as backup image, load copy image is also removed along with archived log file.

2) maintain through BACKUP DATABASE command and automated deletion of recovery objects
"Recovery Object" consists of physical log file, backup image, load copy image, etc.
When AUTO_DEL_REC_OBJ database parameter was set to ON, after every successful full database backup operation or full table space backup operation, the database manager prunes the recovery history file and recovery object according to the settings of the NUM_DB_BACKUP and REC_HIS_RETENTN configuration parameters.
In this case, all the recovery object exceeds both NUM_DB_BACKUP and REC_HIS_RETENTN value can be deleted automatically. You cannot specify specific date.
Note: If LOGARCHMETH1 database configuration parameter was set as LOGRETAIN, log file is not deleted even though the corresponding entry in recovery log file was automatically deleted.
For example, assumed AUTO_DEL_REC_OBJ = ON, NUM_DB_BACKUPS = 3, REC_HIS_RETENTN = 5 was set after BACKUP DATABASE command was executed for the database, recovery object satisfies both older than 3 last (includes taken at the time) backup, and more than 5 days has passed, it can be deleted automatically.
Example steps:
  • $ db2 get db cfg for DB01
     
     Number of database backups to retain   (NUM_DB_BACKUPS) = 3
     Recovery history retention (days)     (REC_HIS_RETENTN) = 5

List the backup history from recovery history file. Note there are 3 entries of backup image now.
  • $ db2 list history backup all for db DB01 | grep " B "
      B  D  20130709184408001   F    D  S0000041.LOG S0000041.LOG  
      B  D  20130709184805001   F    D  S0000043.LOG S0000043.LOG  
      B  D  20130709185352001   F    D  S0000045.LOG S0000045.LOG  

Verify the archived log files exist in archived log path.
  • $ ls -la
    total 39304
    drwxr-x---. 2 db2inst1 db2iadm1    4096  Jul  9 18:52 2013 .
    drwxr-x---. 3 db2inst1 db2iadm1    4096  May 23 14:28 2013 ..
    -rw-r-----. 1 db2inst1 db2iadm1 4202496  Jul  9 18:46 2013 S0000041.LOG
    -rw-r-----. 1 db2inst1 db2iadm1 3698688  Jul  9 18:47 2013 S0000042.LOG
    -rw-r-----. 1 db2inst1 db2iadm1 4202496  Jul  9 18:51 2013 S0000043.LOG
    -rw-r-----. 1 db2inst1 db2iadm1 4198400  Jul  9 18:52 2013 S0000044.LOG

Full database backup was taken by BACKUP DATABASE command
  • $ db2 backup db DB01 to /db2backup

Verify the entry for the oldest backup taken at "20130709184408001" was removed, and you can see new entry was added.
  • $ db2 list history backup all for db DB01 | grep " B "
      B  D  20130709184805001   F    D  S0000043.LOG S0000043.LOG  
      B  D  20130709185352001   F    D  S0000045.LOG S0000045.LOG  
      B  D  20130723120755001   F    D  S0000045.LOG S0000045.LOG  

Verify the correspond archived log files (S0000041.LOG and S0000042.LOG) was removed from archived log path.
  • $ ls -la
    total 8212
    drwxr-x---. 2 db2inst1 db2iadm1    4096  Jul 23 12:08 2013 .
    drwxr-x---. 3 db2inst1 db2iadm1    4096  May 23 14:28 2013 ..
    -rw-r-----. 1 db2inst1 db2iadm1 4202496  Jul  9 18:51 2013 S0000043.LOG
    -rw-r-----. 1 db2inst1 db2iadm1 4198400  Jul  9 18:52 2013 S0000044.LOG


3) maintain based on "First active log file" database configuration parameter value
The informational "First active log file" database configuration parameter has the oldest active log file name in the database. Log file prior to the "First active log file" was already archived from Db2 point of view (refer another technote [Db2] When will transaction log files get archived ? for detail).
The log file prior to the "First active log file" is no longer necessary for possible crash recovery. You can delete these log files unless you do not need them for rollforward recovery after the backup image was restored, etc.
PRUNE LOGFILE command can be used for the purpose, but the command was deprecated in V10.1. You can use operating system function, for example deleting them manually, or creating your own shell script to satisfy your requirement.

You can verify "First active log file" database configuration parameter value by any of below:
  • GET DATABASE CONFIGURATION command
    example:
    $ db2 get db cfg for tpcc | grep "First active log file"
    First active log file                        = S0000024.LOG
     
  • SNAPDETAILLOG administrative view
    example:
    $ db2 "select substr(DB_NAME,1,8), first_active_log from sysibmadm.snapdetaillog "

    1        FIRST_ACTIVE_LOG    
    -------- --------------------
    DB01                       24
     
  • MON_GET_TRANSACTION_LOG table function
    example:
    $ db2 "select first_active_log from table(mon_get_transaction_log(-1))"

    FIRST_ACTIVE_LOG
    --------------------
    　                24

Related information
auto_del_rec_obj - Automated deletion of recovery objects configuration parameter
Deleting database recovery objects using the PRUNE HISTORY command or the db2Prune API
Automating database recovery object management
Protecting recovery objects from being deleted

 

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"OTHER - Uncategorised","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;10.1;10.5;11.1;11.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
01 December 2020

UID

ibm11088290

Page Feedback