Too big to be REORGed (Db2 LUW)

Author:

Platform:

Published on:

Kacper Kubica

LUW

August 13, 2025

Recently I REORGed some big tables. Even though they were partitioned, it turned out that some partitions are too big to be reorganised in Db2 11.1 (and earlier). I had to upgrade to 11.5. Curious? Let me explain.

Bumping into the problem
The troublemaker partitions were a little over 1 GB. Doesn’t sound so bad, right? However, the REORG process generated hundreds of gigabytes of active transaction logs (or even 1TB) and I ended up with the following error: “ADM1823E The active log is full…” In the active logs filesystem I saw that indeed the number of active logs equals LOGPRIMARY+LOGSECOND, which is maximum number allowed by my config.

Trying to solve the problem
OK, so I decided to just significantly increase the number of secondary logs (LOGSECOND). However, to my surprise, the maximum possible number of active logs is 256!

Hmm… If you can’t increase the number of logs, you can increase its size, right? Yeah, but the size of a transaction log is not configurable online (you need to restart the database) and also has its upper limit (around 1 milion pages if I remember correctly).

So, what’s the maximum amount of transaction logs for a database?
It equals: maximum number of logs times log size times page size. Using the config keywords syntax: (LOGPRIMARY+LOGSECOND) * LOGFILSIZ * PAGESIZE. In my case the page size was 4 KB. So, my environment could deal with up to around: 256 * 1.000.000 * 4 KB ~= 1TB of logs. This is much, but not sufficient.

Solution
Then, I did what I usually do, when I feel trapped and I need some help with Db2. I searched through IBM Db2 technotes and I found this: [Db2] How to solve transaction log for the databases is full (SQL0964C). In this doc I found the following sentence:

Sum of LOGPRIMARY and LOGSECOND value cannot exceed 256 (in V11.5, the maximum total number of log files becomes 8192 in archived logging mode).

Number 8192 sounds much more reasonable. IBM decided to increase maximum number of log files 16 times! It means I can’t have been alone having this issue (yeah, otherwise there wouldn’t be such a technote).

When I took on the REORG task, I thought it would be short and easy, but it turned out I had to upgrade the database!

Yeah, it’s not so bad, because I had to do it anyway. I feel like IBM makes sure my Db2 level is up to date ;)

‍