Load may be slow due to a very large history file

Answer

The symptoms is
1) the load progress is quick
$ db2 load from test.del of del insert into test

SQL3500W The utility is beginning the "LOAD" phase at time "01/04/2016 11:23:52.975954".
......
SQL3515W The utility has finished the "LOAD" phase at time "01/04/2016 11:23:52.999516".

2) the load command take longer time to return the result

$ time db2 load from test.del of del insert into test

real 1m33.63s
user 0m0.02s
sys 0m0.01s

So, the load only take less than 1 second, while it take more than 1 minutes to return the result.

3) check the stack of db2agent which runs load, and find it is process the history file

<StackTrace>
-------Frame------ ------Function + Offset------
lseek64 + 0x20
lseek64@glue7B0 + 0x98
sqloseek + 0x128
sqluhReadEntry__F12SQLO_FHANDLEP11SQLUH_ENTRYP14SQLUH_WORKAREAPcN34PUiC19SQLUH_ENTRY_VERSION + 0x154
sqluhUpdate + 0xCCC
sqluCommitLoadEntryInHistoryFile__FP8sqeAgentP17SQLU_LOAD_HF_INFO + 0xF4
sqlu_process_pending_operation__FP8sqeAgentiPcP9SQLP_LSN8P12SQLP_PENLISTPUiP15SQLD_RECOV_INFO + 0x1024
sqlptppl__FP8sqeAgent + 0x490
sqlptppl__FP8sqeAgent@glue15F6 + 0x74
sqlpxcm1__FP8sqeAgentP15SQLXA_CALL_INFOi + 0x224
sqlrrcom_dps__FP8sqlrr_cbiT2P15SQLXA_CALL_INFO +
sqlrrcom__FP8sqlrr_cbiT2 + 0x1C8
sqlrr_commit__FP14db2UCinterface + 0xD8
sqluv_commit__FP8sqeAgentPiP5sqlcai + 0x450
sqlu_register_table_load__FP26sqluCLoadRequestDescriptorP24sqlusCLoadMPPCoordinatorP15sql_static_dataPb + 0x27C
......
</StackTrace>

It is because at the last phase of load, db2agent would update the history file, if the history file is too large, then it would take long time to return the result.

To solve the problem, prune the history file and better keep it less than 10M.
Refer to this link to prune the history file
http://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001992.html