2023 Posted by Damir Wilder

Db2 Log Space Management in V11.5

Introduction

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): 

The current Db2 Log configuration was the following:

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:

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:
LOGPRIMARY=2-256
LOGSECOND=0-254  [-1 for infinite logging]
with a further restriction on the combined value:
LOGPRIMARY+LOGSECOND <= 256

Whereas on Db2 V11.5 the same limit has been extended to:
LOGPRIMARY=2-4096
LOGSECOND=0-4096   [-1 for infinite logging]
and the same restriction on the combined value to:
LOGPRIMARY+LOGSECOND <= 8192
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!):

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):

 

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:

Db2_ADVANCED_LOG_SPACE_MGMT=ON

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

 

Conclusion

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! 

 

 

---

« Previous | Next »

• Share