Slow LOAD jobs in a DPF DB2LUW setup? Don’t forget to check DIAGLEVEL setting
Biswarup(Bis)Mukherjee 120000HKTY Visits (1043)
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.
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.