DB2 FOR LUW / DB2FORLUW / 970 - DB2

This APAR improves the performance of the LOAD utility's 
automatic-dictionary-creation feature (ADC) when the taget table 
is range-partitioned. 
The Load ADC feature is automatically used when the Load utility 
is invoke into a table that is compression enabled, but does not 
yet have a dictionary defined. 
This APAR improves memory usage, and if the table contains 
pre-existing data it reduces the amount of pre-existing data 
read from disk.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Range partitioned tables with compression enabled and either * 
* a number of empty ranges, a large amount of uncompressed     * 
* data, or both.                                               * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* When using row compression on partitioned tables in          * 
* DB2v9.5(up to fp4) and v9.7, a LOAD INSERT operation         * 
* triggers automatic dictionary creation (ADC). ADC involves   * 
* scanning existing data in order to create a compression      * 
* dictionary.Even if there is only one range without           * 
* compression dictionary, the Table Scanner needs to scan all  * 
* existing data (for all ranges) on the table. This            * 
* is,however, not necessary, since there might be some ranges  * 
* that already have a compression dictionary and also because  * 
* after a certain amount of data (usually 2MB) the ADC         * 
* routines have enough information to create the compression   * 
* dictionary for the corresponding range. This behavior is     * 
* perceived externally in that, the more data is stored in the * 
* table, the longer it takes for the next load insert          * 
* operation to finish, regardless of the size of its input     * 
* file. There is also an impact on memory usage. The ADC       * 
* routines need a certain amount of memory in order to sample  * 
* rows to create a dictionary. This memory is allocated        * 
* upfront, for all the existing ranges in the table. That      * 
* means that even empty ranges get an associated buffer, but   * 
* they are never used.                                         * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 9.5 fp5 or DB2 v9.7 fp1                       * 
****************************************************************

To workaround the Load ADC feature, you can disable compression 
on the table. Alternately you can use IMPORT instead of LOAD 
(however you should be familiar with the fundamental differences 
between Import and Load (ie. logging, performance)).

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Scanned data is minimized. Only ranges that lack a compression 
dictionary are scanned and the ADC table scanner stops once it 
has enough data to create a dictionary (usually 2 MB). This 
benefit LOAD INSERT into Range Partition tables that have a 
large amount of  existing data. 
The memory usage has also been decreased. Memory is allocated 
only for the duration of the table scanner, 
 
 
so no memory gets  allocated for empty ranges and, in fact, 
 
 
only the size of one  buffer is allocated at a time.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

22.10.2009
12.03.2010
12.03.2010

9.5.FP5,
9.7.FP1