Transaction Logging Details

Transaction log files are one of the core strengths of a DBMS, if you ask me. The ‘D’ in ACID refers to ‘Durability’. What Durability means is that once a transaction is committed, it is not lost, even if the database crashes or the server it is on has to be restarted.

Transaction Logging in DB2

DB2 writes transactions to the tranacation log files while they are still in progress. Every logged action is written to the log buffer. The log buffer is then written out to disk whenever either it becomes full or a transaction is committed (or a couple of other special situations). With many transactions in progress, this means that the active transaction logs on disk contain both committed and uncommitted changes. Part of the information in the transaction logs is what transaction various changes are a part of and whether or not they are committed.

Active Logs

Any uncommitted transactions can only be in the active log files. The active log files are defined in number by the database configuration parameters LOGPRIMARY and LOGSECOND, and in size by the database configuration parameter LOGFILSIZ. On database activation (or first connect), the number of log files defined by LOGPRIMARY are created. If DB2 runs out of room in these log files, then it will allocate additional logfiles up to the number specified by LOGSECOND. This is true for either circular or archive logging.

It is generally a good idea to keep LOGPRIMARY on the smaller side to speed up database activation time, but to also have room there for your normal day-in and day-out transaction logging purposes. Usually LOGPRIMARY on my databases is 14 or fewer, with the LOGFILSZ for midsize IBM Websphere Commerce database working well somewhere between 10000 and 60000. Obviously larger, more active databases need more. I like LOGSECOND somewhere between 50 and 240 to handle those unexpected spikes. Of course all of this has to take into account the size of your active log filesystem, too.

A single transaction cannot use more log space than LOGFILSZ(LOGPRIMARY + LOGSECOND). Large deletes fail when they fill up the available log files. Commit frequently to avoid this. You can also end up with a long idle connection causing logfile saturation – causing the database to be unavailable. See “Managing db2 transaction log files” for more information on log file saturation.

Archive Logs

All OLTP and many other databases should use archive logging.

When archive logging is enabled, you should always choose a location for LOGARCHMETH1, even if it is just a different directory. A different filesystem and even a vendor archive system such as TSM is vastly preferred. But even if you are only using a single filesystem, set it to a different directory. What this does is give you a location where you know you can compress, archive, or delete archived transaction log files without crashing the database. Because if you (or anyone) mess with any active log file, you are pretty likely to crash the database, probably in a pretty nasty way.

Side note: LOGARCHMETH1 was a great parameter when it was added. Before that we had to compile and manage userexit programs, and that was a pain.

An active log is archived whenever it is full, even if it contains transactions that are still being used. However, db2 does not ‘remove’ it from the active path until it contains no active transactions. I say ‘remove’ because it looks to us like it is removed, but actually the file is renamed and reused because that performs better than actually removing a files and creating a new one. Because of this scenario, you can see the same log file in the active and archive paths at the same time.

There are some scenarios, such as data replication or HADR that can cause an archived log file to be archived. Scenarios like this make it generally a bad idea to compress or move/remove every log file as soon as it it archived to disk. They can also require you to make retrieval of logs from a tape library or other location possible from more than one database server.

Log files, whether full or not, may be archived with an ARCHIVE LOG command, a DEACTIVATE DATABASE command or a BACKUP.

LOGBUFSZ

The log buffer size frequently needs to be tuned. The larger it is, the more partial transactions you may lose in a failure – though in most failures partial transactions are rolled back anyway. In a database snapshot, two numbers are reported like this:

$ db2 get snapshot for db on sample |grep  "Log pages" 
Log pages read                             = 23108
Log pages written                          = 20513325

If you want to get that through an administrative snapshot view, you can use:

db2 "select log_reads, log_writes from sysibmadm.snapdb with ur"

LOG_READS            LOG_WRITES          
-------------------- --------------------
               23108             20513325

  1 record(s) selected.

Interestingly enough, I’m not finding this in the mon_get table functions.

Generally, you want Log pages read to be at or near 0, and Log pages written to be a large number. If Log pages read is not 0, you may want to consider increasing your log buffer, but generally you don’t want it much over 1096 or so.

Basically during normal operations we want to mostly avoid log reads.

So, readers, please share with me other tuning that you do around log file size or log buffer size. Is there anything else on transaction log files that you want to learn about?

You may also like...

31 Responses

  1. Arzvi says:

    Another important parameter is NUM_log_span, the number of logs a transaction can span. Important to tune acc to type of the database

    • Ember Crooks says:

      Yes, num_log_span and max_logs can be important parameters too. I think I (and most DBAs) could write a small book on transaction logging.

  2. isaac munoz says:

    DB2 v10 now compress log files so a lot of space is saved at all levels [active and archive log paths, Virtual Tape Manager (disk and tape levels)]

    • Ember Crooks says:

      Yes, and from what I understand, you can use that compression without purchasing the compression feature.

  3. Frederik Engelen says:

    For LOGBUFSZ, is your recommendation to not go over 1024 or 4096? 1096 looks like an odd number 🙂

  4. Chaitanya says:

    Super .. Now I get to know , when a active log gets archived.,one quick question,

    For DB2 instance we have dbm cfg parameter called ‘svcename’ that give the port number for TCP/ip for client applicatons,.

    lets say we have 2 active databases under a single instance .
    that means we have one ‘svcename’, applications getting connected to both active databases … so how is this possible applications getting connected to both databases using single port or ‘svcname’… is there any port sharing mechanism.. I mean single port utilized by both Active Db’s

    • Ember Crooks says:

      DB2 uses one port number for every instance. You cannot have separate port numbers per databases without separating them into separate instances. DB2 knows which database on that instance/port, because you must specify the database name when you connect.

  5. Naveed Shakur says:

    Hi, I have to delete bulk records from a table; currently i am using the default configuration for number of logs written on disk.
    Need some suggestions on setting the parameters so when i delete the records i am not getting any issues.
    I am using db2 10.1 on Linux.

  6. raju says:

    Hi, i am raju.

    I am new to DB2..
    I have a doubt that, Is archive logs are reused when the application in rollback state? If yes please explain clearly how it happens and whe

    • Ember Crooks says:

      a transaction log will not be archived until all transactions in it are committed. It is not possible to rollback a committed transaction. So assuming you’re not using data capture/dprop or HADR, Archived transaction log files will not be used by DB2 during normal operation.

      • raju says:

        Yesterday what happened is, in one of Windows congnos server one application is generating huge number of logs and I verified and found that one application is in rollback state and consuming more log space.. then I moved all the logs which are in archive log directory to get some space in the directory.. few hours later some app team call me and then said db2 is in stopping mode. My colleagues n TL said u moved all archive logs so rollback application hung and finally instance stopped. Is it correct?

        • Ember Crooks says:

          Do you keep archived and active transaction logs in separate directories using the LOGARCHMETH1 parameter? What you described sounds like what happens when you move a log that is still active.

          • raju says:

            Yes.. the archive & active logs path are separated using logarchmeth1.. In the current setup… logprimary value was set to 200, log secondary value was set infinite and blk_log_dsk_ful has set to NO….. I was moving bunch of logs to another location to get free some free space in archive log path since one application which is in rollback state generating huge number of logs in a minute. while i am moving the archive logs into another location, at the same time i am monitoring the rollback status by completed work in application snapshot output. few hour later i got ticket instance got down.. then I shocked..

            My queries…

            current set up is logprimary value is 200 & logsecond is infite.. and blk_log_dsk_ful has set to NO…

            1. if one very very big transaction is running and assume that transaction will generate 500 logs and even still that transaction is not completed then how many logs will be there in active log directory?

            2. If one transaction is in rollback sate then Is that transaction will look for required log in archive log directory to complete the rollback

            3. While application is in rollback, if that application could not find required log either in active log directory or archive log directory then db2 services will go into sopped mode?

          • Ember Crooks says:

            Ah, infinite logging, that’s a whole other beast. I haven’t done infinite logging – have stayed away from it. 200 is HUGE for LOGPRIMARY, in my experience – many of my clients run with far fewer. How big are your log files?

      • raju says:

        Thanks Ember

  7. pratik shah says:

    Dear Ember,

    When I am setting this LOGARCHMETH1 PARAMTER in db cfg as USEREXIT then it is showing following error in the diag log.

    From db2diag.log I can see following error messages –
    =====================================================================
    2012-01-16-17.16.54.175099+330 E109828489E435 LEVEL: Error
    PID : 28315 TID : 47130618095936PROC : db2sysc 0
    INSTANCE: db2inp NODE : 000
    EDUID : 25 EDUNAME: db2logmgr (INP) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3155
    DATA #1 :
    Failed to archive log file S0000000.LOG to USEREXIT from /db2/INP/log_dir/NODE0000/ return code 16.

    We are using
    Db2 10.5 (Work group edition)
    Redhat Linux (6.5) OS cluster
    Data Protector (Version 9)

    We have two nodes (active -passive) configuration for the same.

    When we use DB1 or DB2 (physical IP),
    Data protector can take online backup as well as archival log backup using USEREXIT parameter.

    When we are using virtual(floating IP),
    Data protector can take online backup but it wont be able to take archival log backup on tape.

    Coul you please help on this.
    Thanks in advance.

    Regards,
    Pratik Shah
    pratik.titu@gmail.com
    9428694150

  8. pratik shah says:

    Dear Ember,

    When I am setting this LOGARCHMETH1 PARAMTER in db cfg as USEREXIT then it is showing following error in the diag log.

    We are using
    Db2 10.5 (Work group edition)
    Redhat Linux (6.5) OS cluster
    Data Protector (Version 9)

    We have two nodes (active -passive) configuration for the same.

    When we use DB1 or DB2 (physical IP),
    Data protector can take online backup as well as archival log backup using USEREXIT parameter.

    When we are using virtual(floating IP),
    Data protector can take online backup but it wont be able to take archival log backup on tape.

    2012-01-16-17.16.54.175099+330 E109828489E435 LEVEL: Error
    PID : 28315 TID : 47130618095936PROC : db2sysc 0
    INSTANCE: db2inp NODE : 000
    EDUID : 25 EDUNAME: db2logmgr (INP) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3155
    DATA #1 :
    Failed to archive log file S0000000.LOG to USEREXIT from /db2/INP/log_dir/NODE0000/ return code 16.

    Coul you please help on this.
    Thanks in advance.

    Regards,
    Pratik Shah
    pratik.titu@gmail.com
    9428694150

  9. Suvradeep Sensarma says:

    Hi Ember,
    I wanted to know whether we can monitor which agentid is consuming the maximum number of logs. Often we face the situation of log full condition. During that time we see it in db2diag.log that which agentid we need to kill to resolve the issue.
    But we want to know beforehand only so that we can avoid the log full situation.
    We used to monitor log utilization through sysibmadm.log_utilization and check the percent of log usage but don’t get any information which application is using the most.

    • Ember Crooks says:

      I think that what you’re looking for is the application id holding the oldest transaction log (and log file saturation seems to be what you’re describing). This query should give you what you need (works on 10.5, let me know if it doesn’t on the version you’re on, and there are other ways to get it): db2 “select APPLID_HOLDING_OLDEST_XACT from table(mon_get_transaction_log(-2)) with ur”

      • Suvradeep Sensarma says:

        Thanks for the reply. Unfortunately it is not working in our environment which uses DB2 V9.7. I have searched this in Infocenter, but could not find anything. So, i think there must be some indirect way of finding it .

      • Suvradeep Sensarma says:

        I got the query for V9.7.. It is db2 “select appl_id_oldest_xact from sysibmadm.snapdb”. Thanks for your help.

  10. Nadir Doctor says:

    Hi Ember,

    In your prior reply which is dated a year later from creation of blog post, mon_get_transaction_log() is referenced – this table function is available in db2 10.x and contains information for snapshot elements log_reads and log_writes.

    Best Regards,
    Nadir

  11. reddy says:

    Hi Ember,

    Thanks for sharing your knowledge and experience to us.i have question how select query causes transaction log full issues.

    I really appreciate on sharing your knowledge to others

    • Ember Crooks says:

      Selects certainly can acquire locks, but a pure select alone should not cause transaction logs to fill.

  12. Naveed says:

    Hi Ember
    One of the database have been set as below:

    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300
    Log buffer size (4KB) (LOGBUFSZ) = 256
    Log file size (4KB) (LOGFILSIZ) = 185000
    Number of primary log files (LOGPRIMARY) = 20
    Number of secondary log files (LOGSECOND) = 200
    Changed path to log files (NEWLOGPATH) =
    Path to log files = D:\DB2\NODE0000\SQL00001\LOGSTREAM0000\
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file = S0000272.LOG
    Block log on disk full (BLK_LOG_DSK_FUL) = NO
    Block non logged operations (BLOCKNONLOGGED) = NO
    Percent max primary log space by transaction (MAX_LOG) = 0
    Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
    First log archive method (LOGARCHMETH1) = DISK:D:\tr_logs\

    I have noticed that the First active log file number remains same while on the location (D:\tr_logs) the number of log files are growing, i cannot run the prune command to free up space. What could be the reason?
    DB2 version is 10.5.8 on windows 2012
    Thanks

    • Ember Crooks says:

      You may have a connection holding on to pages in that first log file. You cannot prune the log until transactions are committed or rolled back. Is your application doing appropriate commits?

  1. March 20, 2016

    […] Transaction Logging Details – db2commerce.com – Super .. Now I get to know , when a active log gets archived.,one quick question, For DB2 instance we have dbm cfg parameter called ‘svcename’ that give the port … […]

Leave a Reply

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