Home > Articles > Data > DB2

  • Print
  • + Share This
This chapter is from the book

Transaction Logging

So just what is transaction logging and how does it work? Transaction logging is simply a process that is used to keep track of changes made to a database (by transactions), as they are made. Each time an update or a delete operation is performed, the page containing the record to be updated/deleted is retrieved from storage and copied to the appropriate buffer pool, where it is then modified by the update/delete operation (if a new record is created by an insert operation, that record is created directly in the appropriate buffer pool). Once the record has been modified (or inserted), a record reflecting the modification/insertion is written to the log buffer, which is simply another designated storage area in memory. (The actual amount of memory that is reserved for the log buffer is controlled by the logbufsiz database configuration parameter.) If an insert operation is performed, a record containing the new row is written to the log buffer; if a delete operation is performed, a record containing the row's original values is written to the log buffer; and if an update operation is performed, a record containing the row's original values, combined with the row's new values, is written to the log buffer. These kinds of records, along with records that indicate whether the transactions that were responsible for making changes were committed or rolled back, make up the majority of the records stored in the log buffer.

Whenever buffer pool I/O page cleaners are activated, the log buffer becomes full, or a transaction is terminated (by being committed or rolled back), all records stored in the log buffer are immediately written to one or more log files stored on disk. (This is done to minimize the number of log records that might get lost in the event a system failure occurs.) This process is referred to as write-ahead logging and it ensures that log records are always flushed to log files before data changes are recorded in the database (i.e., copied to the appropriate tablespace containers for permanent storage). Eventually, all changes made in the buffer pool are recorded in the database, but only after the corresponding log records have been externalized to one or more log files. The modified data pages themselves remain in memory, where they can be quickly accessed if necessary; eventually they will be overwritten. The transaction logging process is illustrated in Figure 7-4.

07fig04.gifFigure 7-4. The transaction logging process.

Because multiple transactions may be working with a database at any given point in time, a single log file may contain log records that belong to several different transactions. Therefore, to keep track of which log records belong to which transactions, every log record is assigned a special "transaction identifier" that ties it to the transaction that created it. By using transaction IDs, log records associated with a particular transaction can be written to one or more log files at any time, without impacting data consistency. Eventually, the execution of the COMMIT or ROLLBACK statement that terminates the transaction will be logged as well.

Since log records are externalized frequently and since changes made by a particular transaction are only externalized to the database after all log records associated with the transaction have been recorded in one or more log files, the ability to return a database to a consistent state after a failure occurs is guaranteed. When the database is restarted, log records are analyzed and each record that has a corresponding COMMIT record is reapplied to the database; every record that does not have a corresponding COMMIT record is either ignored or backed out (which is why "before" and "after" information is recorded for all update operations).

Logging Strategies

When a database is first created, three log files, known as primary log files, are allocated as part of the creation process. On Linux and UNIX platforms, these log files are 1,000 4K (kilobyte) pages in size; on Windows platforms, these log files are 250 4K pages in size. However, the number of primary log files used, along with the amount of data each is capable of holding, is controlled by the logprimary and logfilsiz parameters in the database's configuration file. The way in which all primary log files created are used is determined by the logging strategy chosen for the database. Two very different strategies, known as circular logging and archival logging, are available.

Circular Logging

When circular logging is used, records stored in the log buffer are written to primary log files in a circular sequence. Log records are written to the current "active" log file and when that log file becomes full, it is marked as being "unavailable". At that point, DB2 makes the next log file in the sequence the active log file, and begins writing log records to it. And when that log file becomes full, the process is repeated. In the meantime, as transactions are terminated and their effects are externalized to the database, their corresponding log records are released because they are no longer needed. When all records stored in an individual log file are released, that file is marked as being "reusable" and the next time it becomes the active log file, its contents are overwritten with new log records.

Although primary log files are not marked reusable in any particular order (they are marked reusable when they are no longer needed), they must be written to in sequence. So what happens when the logging cycle gets back to a primary log file that is still marked "unavailable"? When this occurs, the DB2 Database Manager will allocate what is known as a secondary log file and begin writing records to it. As soon as this secondary log file becomes full, the DB2 Database Manager will poll the primary log file again and if its status is still "unavailable", another secondary log file is allocated and filled. This process will continue until either the desired primary log file becomes "reusable" or the number of secondary log files created matches the number of secondary log files allowed. If the former occurs, the DB2 Database Manager will begin writing log records to the appropriate primary log file and logging will pick up where it left off in the logging sequence. In the meantime, the records stored in the secondary log files are eventually released, and when all connections to the database have been terminated and a new connection is established, all secondary log files are destroyed. On the other hand, if the latter happens, all database activity will stop and the following message will be generated:

SQL0964C The transaction log for the database is full.

By default, up to two secondary log files will be created, if necessary, and their size will be the same as that of each primary log file used. However, the total number of secondary log files allowed is controlled by the logsecond parameter in the database configuration file. Circular logging is illustrated in Figure 7-5.

07fig05.gifFigure 7-5. Circular logging.

By default, when a new database is first created, circular logging is the logging strategy used.

Archival Logging

Like circular logging, when archival logging (also known as log retention logging) is used, log records stored in the log buffer are written to the primary log files that have been pre-allocated. However, unlike with circular logging, these log files are never reused. Instead, when all records stored in an individual log file are released, that file is marked as being "archived" rather than as being "reusable" and the only time it is used again is if it is needed to support a roll-forward recovery operation. Each time a primary log file becomes full, another primary log file is allocated so that the desired number of primary log files (as specified by the logprimary database configuration parameter) are always available for use. This process continues as long as there is disk space available.

By default, all log records associated with a single transaction must fit within the active log space available (which is determined by the maximum number of primary and secondary log files allowed and the log file size used). Thus, in the event a long running transaction requires more log space than the primary log files provide, one or more secondary log files may be allocated and filled as well. If such a transaction causes the active log space to become full, all database activity will stop and the SQL0964C message we saw earlier will be produced.

Because any number of primary log files can exist when archival logging is used, they are classified according to their current state and location. Log files containing records associated with transactions that have not yet been committed or rolled back that reside in the active log directory (or device) are known as active log files; log files containing records associated with completed transactions (i.e., transactions that have been externalized to the database) that reside in the active log directory are known as online archive log files; and log files containing records that are associated with completed transactions that have been moved to a storage location other than the active log directory are known as offline archive log files. Offline archive files can be moved to their storage location either manually or automatically with a user exit program. Archival logging is illustrated in Figure 7-6.

07fig06.gifFigure 7-6. Archival logging.

Infinite Active Logging. You would think that you could avoid running out of log space simply by configuring a database to use a large number of primary and/or secondary log files if needed. However, the maximum number of log files allowed (primary and secondary combined) is 256 and if the size of your log files is relatively small, you can still run out of log space quickly when transaction workloads become heavy or when transactions run for an inordinate amount of time. Furthermore, you want to avoid allocating a large number of secondary log files if possible because performance is affected each time a log file has to be allocated. Ideally, you want to allocate enough primary log files to handle most situations and you want to use just enough secondary log files to handle peaks in transaction workloads.

If you are concerned about running out of log space and you want to avoid allocating a large number of secondary log files, you can configure a database to perform what is known as infinite active logging or infinite logging. Infinite active logging allows an active transaction to span all primary logs and one or more archive logs, effectively allowing a transaction to use an infinite number of log files. To enable infinite active logging, you simply set the database configuration parameters userexit and logsecond to YES and –1, respectively. It is important to note that when the userexit database configuration parameter is set to YES, a user-supplied userexit program will be invoked each time a log file is closed and this program can move unneeded log files to another location for permanent storage (thus the risk of running out of log storage space on the server is eliminated).

When the logsecond database configuration parameter is set to -1, the logprimary and logfilsiz configuration parameters are still used to specify how many primary log files DB2 should keep in the active log path as well as with how big each file should be. If DB2 needs to read log data from a log file, but the file is not in the active log path, DB2 will invoke the userexit program provided to retrieve the log file from the archive and copy it to the active log location so that other reads of log data from the same file will be fast. DB2 manages the retrieval, copying, and removal of these log files as required.


Although infinite active logging can be used to support environments with large jobs that require more log space than you would normally allocate to the primary logs, it does have its tradeoffs. Specifically, rollback operations (both at the savepoint level and at the transaction level) could be very slow due to the need to retrieve log files from the archive storage location. Likewise, crash recovery could be very slow for the same reason.

Log mirroring. With DB2 UDB Version 8.1, you have the ability to configure a database such that the DB2 Database Manager will create and update active log files in two different locations (which is sometimes referred to as dual logging). By storing active log files in one location and mirroring them in another, separate location, database activity can continue if a disk failure or human error causes log files in one location to be destroyed. (Mirroring log files may also aid in database recovery.) To enable log file mirroring, you simply assign the fully qualified name of the mirror log location to the mirrorlogpath database configuration parameter. It is important to note that if log mirroring is used, the primary log file location used must be a directory and not a raw device. And ideally, the mirror log file storage location used should be on a physical disk that is separate from the disk used to store primary log files and that does not have a large amount of I/O.

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020