Transaction Logging Details

Posted by

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?

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

35 comments

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

    1. 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. 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)]

  3. 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

    1. 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.

  4. 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.

  5. 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

    1. 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.

      1. 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?

        1. 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.

          1. 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?

          2. 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?

  6. 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

  7. 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

    1. I haven’t used USEREXIT in at least 10 years – it used to be the only option and we hated it – so glad when they came up with LOGARCHMETH parameters instead. If you’re using userexit, you generally have to have a compiled C program to do the actual user exit process. Do you have that executable in place? It must be named properly and be in the right location. Check out this link on some of the details of USEREXIT: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.ha.doc/doc/c0024701.html

  8. 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.

    1. 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”

      1. 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 .

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

  9. 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

  10. Pingback: Db2 Udb V9.7 Architecture | Easy Architecture Fan
  11. 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

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

  12. 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

    1. 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?

  13. Hi Ember ..
    As you suggested we should keep LOGBUFSZ under 1024,
    we have Log buffer size (4KB) (LOGBUFSZ) = 4096
    still we are facing lost for log reads
    Log pages read = 31041539
    Log read time (sec.ns) = 63191.960901451
    Log pages written = 287942923
    Log write time (sec.ns) = 91488.767335623

    can we increase size of LOGBUFSZ to 8192

  14. Hi Ember,
    We have the current log file sizes. Does increasing the log file size or log buffer size be helpful ?
    How to come to an optimal number ? Like making my logbufsiz to 4096 or 8192 ?

    Log buffer size (4KB) (LOGBUFSZ) = 2048
    Log file size (4KB) (LOGFILSIZ) = 5120
    Number of primary log files (LOGPRIMARY) = 256

    Log pages read = 1260489
    Log read time (sec.ns) = 129.511013232
    Log pages written = 673672762
    Log write time (sec.ns) = 552544.818212560
    Number write log IOs = 461438463
    Number read log IOs = 160663
    Number partial page log IOs = 214285295
    Number log buffer full = 166000

    1. Generally you increase it slowly until the number of log pages read is where you want it. Given your numbers, I’d likely increase it. I’d probably try 3,072 first.

      A note on your log files – I like to have LOGPRIMARY+LOGSECOND add up to something a bit less than 256. This means I can increase LOGSECOND (the only online configurable log file size parameter) on the fly to temporarily and quickly get out of a log full situation. It is also unusual to have all 256 as primary files, as more primary files leads to a longer database activation time. You may want to consider increasing LOGFILSIZ a bit, reducing the size of LOGPRIMARY, and increasing the size of LOGSECOND. Not critical to performance overall – the only area this would help performance for is for database activation time.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.