Resolving Performance Degradation During the Load Process

When executing the EIM load process, performance might degrade noticeably after a number of batch loads are executed. This performance degradation often results when outdated statistics are loaded on the tables. To resolve the problem, update statistics on the target base tables.

You can also improve performance during the load process by updating the S_LST_OF_VAL table. The BU_ID column in the S_LST_OF_VAL base table can have only one or very few distinct values. When this happens, the DB2 optimizer often needlessly performs a table scan through all rows in the S_LST_OF_VAL table.

To avoid this problem and speed up the query, modify the statistics data by running the following SQL statements:

update sysibm.sysindexes set firstkeycardf=1000 where
name='S_LST_OF_VAL_M2';
update sysibm.syscolumns set colcardf = 1000 where
tbname='S_LST_OF_VAL' and name='BU_ID';

Note: Depending on the data you are working with, you might have to run other SQL statements first.