Slow LOAD jobs in a DPF DB2LUW setup? Don’t forget to check DIAGLEVEL setting

Body

In DB2LUW there are many reasons why loads might be slower. It might be a config issue at Db2 or, even OS level.  Or, might be a tuning need for the load command itself.

But, one thing commonly overlooked is what kind of diagnostic dumps are enabled when loads are running.

It’s observed when multiple Db2 partitions are sharing  a same physical host and sharing same  db2diag.log  file  there might be severe  contentions around db2diag.log file if large amount of messages are dumped into the db2diag.log using DIAGLEVEL 4.

It might even more prominent when multiple loads are running in parallel.

All the entries in db2diag.log are written in chronicle order (each entry has it own timestamp ). Usually, there is only one db2diag.log shared by all the threads within processes, and shared by various processes.  That's why the writing to db2diag.log buffer has to be synchronous for different writers. Otherwise, the entries will lose the order. The log contention may happen if a large number of EDUs and/or many processes writing to one db2diag.log at the same time.

Several ways to reduce log contention.

1. When db2 writing to db2diag.log intensely, it usually indicates there are some messages or, errors are happening. So, try to correct the messages and errors first.
2. If many members sharing one physical  host. Try to distribute those to different physical hosts.
3. If multiple hosts are used, make sure each host has its own DIAGPATH ( local ), so each host has its own db2diag.log
4.  If multiple members on each host, splitting DIAGPATH per member ( $n ), so each member has its own db2diag.log  
5. And, lastly the DIAGLEVEL setting.

DIAGLEVEL 4 is used to diagnose issues with Db2 setup and it should be turned ON for specific duration and should not be left that way for ever.

The  DIAGLEVEL could be changed on the fly without a need for recycle of the instance.  Just need to attach to instance before changing it.

db2 attach to <instance-hane>

db2 update dbm cfg using DIAGLEVEL 3

In  customer situations  reducing  DIAGLEVEL 4 to 3  significantly improved load performance.