Performance tuning by type of object

Use the following information to tune your jobs when loading specific types of objects.

Loading tables with DEFINE NO data sets

As required by Db2, if you are loading at least one partition of a table whose table space or index spaces are defined with DEFINE NO, LOADPLUS materializes all partitions. For a table space with many partitions, this materialization might affect the performance of your load job. Subsequent jobs on the same table space, however, are not affected.

Important

For LOB table spaces, LOADPLUS materializes the LOB spaces only for base table partitions that are participating in the load.

Loading tables that contain identity columns

If you are loading an identity column during an SQLAPPLY load job, LOADPLUS does not generate values but allows Db2 to assign values during insert processing. Loading an identity column by specifying LOAD RESUME YES SHRLEVEL CHANGE SQLAPPLY can reduce the performance of your load job.

Loading partitioned table spaces

Consider the following performance recommendations if you are loading partitioned table spaces (except partition-by-growth). If the objects have a large number of partitions, see also the recommendations in Loading objects with a large number of partitions.

Use multiple SYSREC data sets (and, if you must use two-phase load, multiple SORTOUT data sets).
If you are loading a majority of the partitions, specify a single INTO statement without the PART option. If you are loading a small number of partitions, specify all partitions on a single INTO statement (for example, INTO PART 1:3,5).
Specify PART for the COPYLVL installation or command option if you need to make copies.
Consider specifying ORDER YES to allow LOADPLUS to more efficiently build the data-sorting indexes and load the data.

Loading objects with a large number of partitions

If you are loading objects with a large number of partitions, consider the following performance recommendations. For more considerations, including a definition of the large number of partitions, see Considerations-when-loading-a-large-number-of-partitions.

If you are replacing only a few partitions and the other partitions are empty, specify LOAD RESUME YES PART n REPLACE instead of LOAD REPLACE.
If you are loading a large number of partitions, consider specifying REDEFINE NO, either in the installation options or on the LOAD command. This option creates additional data sets when needed and cleans up unused data sets.
If you are loading a large number of partitions, need to make inline copies, and are specifying ORDER NO, consider specifying COPYLVL FULL.
If you are loading a large number of partitions, ensure that you specify REGION=0M in the JOB or EXEC statement of the execution JCL to tell the system to allocate all available virtual storage to the LOADPLUS job.

Databases with many data sets and many tables

LOADPLUS offers features that provide advantages over competitors when loading these types of databases. For example, LOADPLUS allows you to load data into tables whose table spaces and index spaces are defined with DEFINE NO.

If you are using LOADPLUS in this type of environment, consider the following performance recommendations:

Consider specifying REDEFINE NO, either in the installation options or on the LOAD command. This option creates additional data sets when needed and cleans up unused data sets.
When replacing data in a segmented table space, specify LOAD RESUME YES TABLE tableName REPLACE to load only the tables in which you want to replace data.
Consider skipping processing on nonpartitioned indexes by using the SKIPIX option.