Basis Errors and Solutions

Symptom

To better support DB2 row compression (also known as deep compression) and DB2 load, we have added new options for installing SAP products to the R3load. These options are delivered with DB2 Version 9 for Linux, UNIX and Windows.
Other terms

DB6, UDB, DB2UDB, LUW, DB2LUW
Reason and Prerequisites

R3load supports the generic option
-loadprocedure dbsl|fast [fastload argument]
Until now, you could transfer the LOAD and COMPRESS options in the argument. This is described in the related notes.
As of SAP Kernel Release 7.00, we support the additional options that are described below.

Solution

If you are operating a system with SAP Kernel Version 7.00 or 7.10, use a current version of R3load:
Patch text: “DB6: FULL_COMPRESS with 9.5 ADC”
List of the options:

LOAD
As before (see related notes).
If it is technically possibly, use DB2 LOAD instead of INSERT to insert the data.
Exceptions:
Tables that were exported in several parts, or tables that only contain a few entries.LOAD_FORCED
You should also use DB2 LOAD in the exceptional cases that are mentioned above.ANY_ORDER
The system uses “ANYORDER filetype modifier” to start DB2 LOAD in conjunction with LOAD (see DB” LOAD documentation).COMPRESS
As before (see related notes).
A table for which row compression is active is reorganized after a part of the data was inserted (DEFAULT 10000 rows). As a result, the system creates a compression dictionary and uses this dictionary to compress the following rows that were added. In DB2 versions higher than 9.1, the system ignores this option.
The option COMPRESS does not have any effect if you specified the option “-nolog” and if the tables is not loaded using the fastload module.COMPRESS_ALL
Tables are created with the option “COMPRESS YES”. The “COMPRESS” option is used in DB2 Version 9.1 when you load data.FULL_COMPRESS
The table is loaded with all of the data. The R3load then performs a reorganization to create an optimal compression dictionary. However, before the reorganization, the table increases to above the complete uncompressed size.SAMPLED
You can use this option to download only a part of the data. By default, the system inserts only one line from 100.
You can use the following environment variables to adjust the quantity of loaded data.
DB6LOAD_SAMPLING_FREQUENCY=N ( default: 100 )
specifies that only one line from N is inserted.
DB6LOAD_MAX_SAMPLE_SIZE=X ( default: infinity )
the system inserts a maximum of X MB of data.
In general, you attain a better compression rate for the entire table if you create a compression dictionary from a representative part of the data and you do not use the first data that is inserted into the table. The reason for this is that the exported data is mostly assigned according to the primary key of the table.
You can reach a very good compression rate by using R3load to load a table in two steps. Proceed as follows:
1. Step:
R3load … -loadprocedure fast SAMPLED_FULL_COMPRESS
loads a representative sample of the data and uses this to create a compression dictionary. Since not all of the data was loaded, R3load leaves the data loading step with the status “err”.
2. Step:
R3load … -loadprocedure fast
loads all of the data in the existing table and uses the compression dictionary that was just created to compress this data.DEF_CRT
activates “Deferred Table Creation”. Therefore, all the tables are first created as views and are converted into database tables only when the first INSERT is performed. For an exact description of “Deferred Table Creation” and for more information about the prerequisites of this option, see Note 1151343.
In the 7.00 kernel, to use the option SAMPLED, you require a current version of R3load.
Patch text: “DB6: added option SAMPLED”

Several of the options mentioned above can be combined, for example:
-loadprocedure fast LOAD:COMPRESS_ALL
or
-loadprocedure dbsl FULL_COMPRESS_ALL

Important note:

Refer to related Note 1156514.
On databases with several partitions (DPF), due to a DB2 error in Version 9.1 up to and including fix pack 4, the option COMPRESS is dangerous and may result in data loss. In DB2 V9.5, the same applies to the option FULL_COMPRESS.