How to monitor LOAD progress and improve its performance?
db2scope 310002R9XW Visits (9590)
This Blog explains how you can monitor the progress of your load commands and in case there is a performance concern
on the load commands, where you can check the cause of the slowness.
1. Monitor Load Progress
You can use db2pd -util command to monitor load progress. For instance, the following output has two load commands ongoing:
Database Member 0 -- Active -- Up 6 days 05:15:41 -- Date 2016
Address ID Type
0x0780000002CFFCC0 488 LOAD
0x0780000002CDFE00 487 LOAD
Address ID PhaseNum Comp
0x0780000002D0F108 488 1 0 byte
0x0780000002D0F2C0 488 2 12236239 rows 12236239 rows Wed Dec 14 03:03:20 LOAD
0x0780000002D0F448 488 3 0 inde
0x0780000002CEFAC8 487 1 0 byte
0x0780000002CFF500 487 2 147377583 rows 147377583 rows Wed Dec 14 03:03:02 LOAD
0x0780000002CFF688 487 3 0 inde
By comparing another round of "db2pd -util", we can find out the laod speed. For instance :
147377583 - 36097665 rows / 21m 5s = 111279918 rows / 1265s = 87968 rows/s
12236239 - 2967388 rows / 21m 5s = 9268851 rows / 1265s = 7327 rows/s
2. Load Performance
If the load performance is not what you expected, what should you check ? Firstly check what is the CPU parallelism of the load.
You can check this in db2diag.log, eg:
or use db2pd -load:
$ grep "^LOADID: 183739" db2p
$ grep "^LOADID: 217228" db2p
Next, verify what is your UTIL_HEAP_SZ setting. Also check your db2diag.log, if you see following message,
MDCRPPERF: insufficient memory to cache all units of clustering 0, 0
That may be an indication of insufficient memory in UTIL_HEAP_SZ. You can increase the memory to load by using "DATA BUFFER" option.
You can start with 1/4 of UTIL_HEAP_SZ assigned to DATA BUFFER. As an example, if UTIL_HEAP_SZ is 10000, add "DATA BUFFER 2500"
to your load command.
3. Further Troubleshooting
The above should be enough to solve most problems, but if the issue still exists, you can take the following:
(1) Stack dump of load EDUs
Essentially you get the load ID from "db2pd -load -alldbs", and use 'db2pd -db DbName -load loadID="LoadID" stacks'.
(2) Tracing load EDUs
Similarly you can find APPID of the loading application from db2pd -load, then use "db2trc on -appid ***"