Why does Load command seem to hang after load is completed?

Answer

Load command on DB2 may spent time to purge all the pages out of buffer pool after load with build phase, or load phase if there is no index build pahase after load phase.

Following is sample from db2diag.log showing almost 3 minutes time gap after load completed build phase till load commit

2016-04-18-11.42.12.112951+540 I96462697A572 LEVEL: Warning
PID : 123456 TID : 123456 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : mydb
AUTHID : DB2INST1 HOSTNAME: SAMPLE
EDUID : 123456 EDUNAME: db2lrid0 0
FUNCTION: DB2 UDB, database utilities, sqlulPrintPhaseMsg, probe:315
DATA #1 : String, 112 bytes
LOADID: 123456.2016-04-18-11.39.58.951645.0 (65530;32769)
Completed BUILD phase at 2016-04-18 11:42:12.111735.


2016-04-18-11.44.56.141057+540 I96464927A460 LEVEL: Error
PID : 123456 TID : 123456 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : mydb
AUTHID : DB2INST1 HOSTNAME: SAMPLE
EDUID : 123456 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, database utilities, sqlu_register_table_load, probe:1479
MESSAGE : commit

The reason is, DB2's load will need to repeatedly purge all the pages of target table out of buffer pool to make database level consistency during this time.

This problem will show higher time spent when one or more following conditions met.
- Large local buffer pool.
- Large global buffer pool ( for pureScale architecture product )
- Load into partitioned table under DPF or pureScale architecture.

To avoid this problem, reducing local and/or global buffer pool prior to load job start may help.
And for partitioned table, avoid loading to the whole partitioned table, but detach the target partition to single table, load or insert into that partition, then attach the partition can help reducing buffer pool purge time.