Managing db2 transaction log files

You may also like...

30 Responses

  1. Priyank says:

    Hi Ember,

    We have a DB2 warehouse database in Tivoli environment. Lately the database has been generating heavy logs. The Transaction Log directory is the same as for the database. And i am not able to find a proper way to archive/delete the logs.
    we have configured online backup for the database and it runs 3 time a week, does the backup include the Transaction logs also ?

    Also if you could help on how to archive/delete the logs, that would be great.

    Any help in this regard is appreciated 🙂

    Thanks in advance.

    Priyank Arora

    • Ember Crooks says:

      Backups will only include the log files used during the backup itself, assuming you’re on DB2 9 or higher OR you specify INCLUDE LOGS when you take the backup.

      Do you have TSM? If so, it may be best to archive log files to TSM using the LOGARCHMETH1 parameter. The other option is to set LOGARCHMETH1 to a directory on the server. Then you know it is safe to compress anything in that archive directory – and you can also back up that archive log directory to TSM or TAPE.

      Once the logs are in a separate archive log directory, you have to decide how long you would like to keep them. You can delete them from that archive log directory whenever you want to, keeping in mind that you want to retain log files as long as you need to be able to restore the database to a point in time. So in my case, I have to be able to restore some databases to any given time in the last two weeks. So I compress the logs in my archive log directory when they’re older than 3 days, and delete them when they’re older than 14 days – just using normal OS-level compress and delete commands. I never touch a log file in the active log directory.

      You could also use AUTO_DEL_REC_OBJ registry parameter along with REC_HIST_RETENTN and NUM_DB_BACKUPS – if you want to manage log files (and database backup files) through DB2, and you don’t care to keep anything in your history file longer than it is on disk.

      Some Tivoli databases, you don’t really care about the data, but just getting the structure of the database back, and you only retain log files so you can take online backups. If that’s the case here, then you could even set LOGARCHMETH1 and then delete log files older than a day or so, and rely on the INCLUDE LOGS functionality of the backup to ensure each backup can be restored itself, but you would be unable to roll forward past the end of the backup.

  2. Priyank says:

    Backups will only include the log files used during the backup itself, assuming you’re on DB2 9 or higher OR you specify INCLUDE LOGS when you take the backup.

    From the above, you mean that only the active logs will be backed up or the older log files also will be backed up ?

    we have LOGARCHMETH1 set to Logretain. can you please explain how can we set it to a directory on the server and the archive/compress the logs ? And which log files it will archive to that directory ? ( any conditions on which files should be archived for e.g older than 3 days)

    unfortunately we dnt have TSM. we have 9.7 FP2 version of DB2.
    /opt/IBM/ITM/db2/db2inst1/NODE0000/SQL00001/SQLOGDIR – this is the log directoy in our env Log Directory.

    Can we have a separate directory for active logs and the old ones ?

    By which factor we can decide that how long should be the log files retained ?? because the database data we need is for 3 months atleast.

    Also i am not sure wether the online backup we are taking is with the includes log option.

    Thanks Again.

  3. harihara says:

    query : How can i analyse the db2 database archive log file ? How to verify the log file is related to particular database ? How to view the db2 database id ?
    Because i am facing the following issue as :

    RETCODE : ZRC=0x071000D6=118489302=SQLP_EXT_DBID_INCORR
    “Database ID does not match Extent probably for another database.”

    Here i had recreated the database with same name and during restore some how older log file came into picture.

    • Ember Crooks says:

      I can’t find a way to do that – either to query or compare the database seed or to get the database seed from the log file. I’m asking around and will let you know if I find a way to do it.

  4. sanchayan says:

    Hi Ember,

    we are a data warehouse team and we are using DB2 which is running on ISAS box.
    As part of daily data load in some of our facts, we are deleting data from 3 core tables and reloading latest data.
    The number of rows marked for daily delete is approx close to 4,00,000 rows . We are using a Stored procedure to perform this delete and the commit point set is 1,60,000.
    Of late we are witnessing a considerable delay in the delete operations for 3,50,000 or above rows. When we abort the query and restart it, the delete gets completed in normal time.
    This is happening more frequent of late and we are trying to identify the ways to manage these delete operations.

    We have started with fine tuning the delete query , but also wondering if the commit size of 1,60,000 rows is a bit too many which might clog up the log.
    what is your thoughts on this?

    PS : At the time of delete operations, there are multiple sessions are running in parallel, which are reading/inserting data into the same database schema.

  5. chaitanya says:

    Hi, Ember.. we have db2 V9.7.7 installed .. we have separate filesystem for archive logs.. and its getting filled up. we take daily online backups.. right now the current active lognumber is 1556, so my question here is can I delete my old archived logs starting from lognumber 1 ,as I mentioned about filesystem space issue.. as we take daily online backups or do we need full offline backup then delete archive logs., please advise..

    • Ember Crooks says:

      You need to keep the logs to restore with whatever your recovery requirements are. For most systems, I like to be able to restore to any time in the last two weeks. This requires that I keep two weeks worth of log files. This obviously depends on your recovery requirements and how/if the log files are copied to system backups or to tape. Each backup lists the earliest log file it needs for rollforward recovery – you can also use that to make sure you keep what you like. There’s also an automated methodology to delete both logs and backups using these three parameters:

      number of database backups to retain
      number of days recovery history will store information on recovery objects, including transaction logs and backups
      if set to “yes” or “on”, means recovery objects will be actually deleted when they expire using REC_HIS_RETENTN and NUM_DB_BACKUPS

  6. Phil says:

    Hi Ember, thanks for the site/blog.

    Anyways, are you doing any log shipping for offsite backup? I see you mention some scripts for handling log files. I was wondering, ur, if you had already done the work. I was thinking of a cron job script that would
    1) Close active log files
    2) FTP closed log files to the DR server that werent already over there.
    and of course
    3) A cron job on the DR server to roll forward to the end of logs every X hours or so.

    I am searching away today, learning about log files etc. I will check some of the links you have here also.

    Thanks again


    • Ember Crooks says:

      Why would you use this form of log shipping instead of HADR?

      • Phil says:

        Actually, I am just starting to get a handle on HADR, and looking into that. Its more a case of discovering what options are out there.

        • Ember Crooks says:

          I would use HADR long before I would use such a method. I haven’t seen that kind of manual log shipping in years. I’ve seen mirroring at the disk level, but not that.

  7. Srini says:

    Hi Ember,

    Is there a way to prune archive logs from out of the DISK:/Archive_log_path upto a certain number of days and at same time this pruning of just the archive logs alone needs to reflect in the history or metadata with the related archive log entries removed and this too without removing the other backup, recovery and other load entries from out of the history file around that duration of days or time…

    Looks like the db2 prune history command with DELETE’ parm can clean up the the metadata in the recovery history file without touching physical log or backup files.

    Do you or has anyone tested this and can show a small example of the something similar being done…

    • Ember Crooks says:

      I don’t have an example, but if you set the parameter AUTO_DEL_REC_OBJ (see and then issue the prune history command, it should do what you are asking. Keep in mind it will ALSO prune backup images if they are purged from history with this parameter. I tend to be a fan of scripting it myself – deleting files older than a certain number of days as long as their number is not after the first log file I want to keep based on my recovery history.

      • Srini says:

        I will try what you mentioned, but I would need to prune the archive log history alone out of the recovery history file, along with the archive logs in the DISK path,
        and in the process, leave out the backup, restore and load related history entries intact within….

        Similarly need to know if its possible to purge the backup entries or load entries from history file individually as well thru db2 prune history or any other similar command….

        • Ember Crooks says:

          That setting does log files and backups as one – though it respects NUM_DB_BACKUPS. If you want to do them individually, you’ll have do do them manually with a script.

  8. Mario says:

    Nice post, this one and all your others, very informative. I don’t fully agree with your comment on LOGRETAIN and circ /archive logging. But that is not my question.

    You provided a link under LOGARCHMETH1 that I’d like to take a look at but IBM changed, again, and the link no longer works. Where/how might I find the info in that link.


  9. Suresh says:

    at what point, Transactions logs will be moved to archive log path. My understanding is after commit, transaction logs moved to archive log path.. am I right? if not clear me out, Thanks in advance

    • Ember Crooks says:

      After commit, transaction logs CAN be moved to archive path. But remember that there are many transactions of varying durations happening at once that use only pieces of a transaction log file. The only times that all transaction logs will be archived are on backup, on database deactivation/activation (I think), and on the archive log command. You can see attempts to archive transaction logs in the DB2 diagnostic log, and also in the database history.

  10. Himanshu Goyal says:

    i have run below purn command successfully but logs are not purn.
    Any configuration needed to run this command??

    db2 prune logfile prior to

  1. February 15, 2012

    […] can find more information here in detail and here in short. Like this:LikeBe the first to like this […]

  2. October 22, 2013

    […] long idle connection causing logfile saturation – causing the database to be unavailable. See “Managing db2 transaction log files” for more information on log file […]

  3. June 1, 2015

    […] can get a brief overview of Managing DB2 Transaction Log Files in Ember Crook’s post. You can understand different parameters, settings and the potential […]

Leave a Reply

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