Technical Blog Post
Abstract
How to monitor LOAD progress and improve its performance?
Body
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-12-14-03.31.20.222374
Utilities:
Address ID Type State Invoker Priority StartTime DBName NumPhases CurPhase Description
0x0780000002CFFCC0 488 LOAD 0 0 0 Wed Dec 14 03:03:19 PINP2 3 2 [LOADID: 217228.2016-12-14-03.03.19.577143.0 (65530;32773)] [*LOCAL.db2user1.161215182014] OFFLINE LOAD CURSOR AUTOMATIC INDEXING REPLACE NON-RECOVERABLE USR1 .T1
0x0780000002CDFE00 487 LOAD 0 0 0 Wed Dec 14 03:03:01 PINP2 3 2 [LOADID: 183739.2016-12-14-03.03.01.706021.0 (65530;32771)] [*LOCAL.db2user1.161215181437] OFFLINE LOAD CURSOR AUTOMATIC INDEXING REPLACE NON-RECOVERABLE USR1 .T2
Progress:
Address ID PhaseNum CompletedWork TotalWork StartTime Description
0x0780000002D0F108 488 1 0 bytes 0 bytes Wed Dec 14 03:03:19 SETUP
0x0780000002D0F2C0 488 2 12236239 rows 12236239 rows Wed Dec 14 03:03:20 LOAD
0x0780000002D0F448 488 3 0 indexes 24 indexes NotStarted BUILD
0x0780000002CEFAC8 487 1 0 bytes 0 bytes Wed Dec 14 03:03:01 SETUP
0x0780000002CFF500 487 2 147377583 rows 147377583 rows Wed Dec 14 03:03:02 LOAD
0x0780000002CFF688 487 3 0 indexes 24 indexes NotStarted BUILD
By comparing another round of "db2pd -util", we can find out the laod speed. For instance :
Load 487
147377583 - 36097665 rows / 21m 5s = 111279918 rows / 1265s = 87968 rows/s
Load 488
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:
...
LOADID: 233107.2016-12-08-18.03.56.775757.0 (65530;32773)
Load CPU parallelism is: 5, 0
or use db2pd -load:
$ grep "^LOADID: 183739" db2pd_load.2016-12-14-03.10.15|grep db2lfr|wc -l
5
$ grep "^LOADID: 217228" db2pd_load.2016-12-14-03.10.15|grep db2lfr|wc -l
1
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 ***"
UID
ibm13286593