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'.

See https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.trb.doc/doc/p0059372.html

(2) Tracing load EDUs

Similarly you can find APPID of the loading application from db2pd -load, then use "db2trc on -appid ***"

See https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.trb.doc/doc/p0059372.html