2023 Posted by Damir Wilder

Db2 Log Space Management in V11.5


I have recently found myself in a situation where I needed to expand (quickly and without downtime) the Db2 Transaction Log space in a customer’s live production database, which had been steadily filling up (for several hours) by a very long and intensive transaction. Also contributing to the log space usage were numerous other (smaller) transactions that were executing during the same time and biting off their chunks of the log space.

The first check into the underlying file system – showed plenty of free space (about 90%) for further expansion of the Db2 Log space (here, /log02 is the MIRRORLOGPATH of /log01): 

Db2 Log space

The current Db2 Log configuration was the following:

Db2 Log configuration

Because the number of Primary and Secondary log files was already at the maximum allowed (40+216=256), there appeared to be no room for further expansion there.

On the other hand, the Log file size parameter already defined quite chunky physical files (400MB) and, anyway, changing the Log file size would require a database restart, so that was not an option here as well (no downtime allowed).

Since the Db2 Log file system had a lot of free space (only about 11% used at the time), configuring the “Infinite logging” was what I tried next, as it theoretically allows the Db2 Log space to keep growing until the file system becomes 100% full.

In this case the Db2 Log space would be able to grow to (about) 10x its current size – quite enough to save the day!

However, this attempt failed with the following error message:

Db2 Log file system

At this point, even though the situation looked hopeless, I still had a (hidden) Ace up my sleeve, one that I wasn’t immediately aware of…


Db2 Log Space Management in V11.5

Up to this point I haven’t intentionally specified the Db2 version at the customer’s site, but this proved to be the turning point in resolving the problem, as I will explain next.

The customer had been running Db2 V11.1 for years (nothing wrong with that!) and only recently has been upgraded to the latest Db2 version (at the time of this writing) – V11.5.7.

That upgrade proved to provide a crucial change in the Db2 Log space configuration:

The maximum allowed number of transaction log files (for the archive logging) on Db2 versions V11.1 and older is:
LOGSECOND=0-254  [-1 for infinite logging]
with a further restriction on the combined value:

Whereas on Db2 V11.5 the same limit has been extended to:
LOGSECOND=0-4096   [-1 for infinite logging]
and the same restriction on the combined value to:
Obviously, this gives much more “breathing” room to the DBA in need 😊


New Configuration

Therefore, the solution to the problem at hand ended up being a very simple one.

All I had to do was execute the following command (without incurring any downtime!):

Db2 Log Space Management in V11.5

This immediately extended the Transaction Log space in the live PROD database to a maximum allowed size of (about) 900GB, or about 9x of its previous value, providing ample room to support the current workload and letting all transactions (big and small) to run to completion
(and at the same time keeping a sizable chunk of unallotted spare room that can be added later, if ever required):

Db2 Transaction Log space


Alternative Solution(s)

Db2 V11.5 offers another option which helps you in resolving such situations where there are long-running transactions clogging up the Transaction Log space, which is called the ALSM (Advanced Log Space Management).

In short, the ALSM can move the long-running transactions out of the traditional Db2 Transaction Log space and into a separate space (which however shares the same disk storage with the Db2 Trans.Log space!) and keep them there for as long as they run, which can prevent clogging.

This feature is enabled by setting the Db2 registry variable:


However, as this requires downtime (the database must be deactivated and reactivated to pick up the change) it wasn’t applicable in this case but would have otherwise been considered.

If you are interested in learning more about this new feature, there is a lot of detailed information in the Db2 Knowledge Centre (on this page).



Allowing the number of Db2 Transaction Log files to grow well beyond the “traditional” limit of 256 files in Db2 v11.5 significantly improves the manageability of busy transactional Db2 databases, while largely avoiding the need for a downtime in times of crisis 😊

This is a welcome extension of the Db2 capabilities, very much in line with other “cloud” enhancements, that is contributing towards turning Db2 into a modern and cloud-ready database. 

For this reason only, I find it well worth upgrading to the latest Db2 version 11.5! 



Subscribe for updates

* indicates required

4 thoughts on “Db2 Log Space Management in V11.5”

  1. Ken Peterson says:

    An excellent tip I will add to my toolbox. I’m glad Db2 LUW “came to the rescue” when you needed help with a nasty UOW. Thanks very much!

    1. Damir Wilder says:

      Hi Ken,
      I’m glad you liked the article, always like to share my findings with others!
      Cheers, Damir

  2. CD says:

    Excellent article some great info there, so fail over to standby node and restart db2 was not an option here?

    1. Damir Wilder says:

      That is/was a valid opion as well, but as you said – it requires a Db2 restart (following a failover), so much less straightforward than a single Db2 command which did the trick just as well!
      Regards, Damir

Leave a Reply

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

« | »
Have a Question?

Get in touch with our expert team and see how we can help with your IT project, call us on +44(0) 870 2411 550 or use our contact form…