Differences between LOADPLUS and the IBM Db2 LOAD utility

This topic describes LOADPLUS functions and operations that differ from functions and operations in the IBM Db2 LOAD utility.

Important

When LOADPLUS invokes DSNUTILB, you are using the IBM Db2 LOAD utility. Therefore, many of these differences do not apply to a DSNUTILB load.

Related topic

Product features

The following table describes the functional and operation differences between LOADPLUS and the IBM Db2 LOAD utility:

Functional or operational area

Differences

Authorization

LOADPLUS does not run as part of the Db2 subsystem. Therefore, you must have the system authorization that is similar to that required by Db2 unless you are using the IBM Resource Access Control Facility (RACF) or the Access Control Facility 2 (ACF2) components and LOADPLUS is installed with OPNDB2ID=YES.

Compression

  • If the object that you are loading has the COMPRESS YES attribute, LOADPLUS always reserves enough pages to hold the largest compression dictionary.
  • LOADPLUS optionally compresses XML data.

Tables

For segmented table spaces, you can specify LOAD RESUME YES INTO TABLE tableName REPLACE to load only the tables in which you want to replace data, leaving the other tables intact.

Indexes

  • Unless you specify INDEX UPDATE or SKIPIX, LOADPLUS reorganizes all indexes that participate in a LOAD RESUME YES job by unloading existing index entries, merging in new index entries, and then sorting and reloading them into the index spaces. If you specify PART n REPLACE or TABLE tableName REPLACE, LOADPLUS rebuilds the data-sorting indexes for the partitions or tables that are being replaced.
  • For partitioned table spaces, you can tell LOADPLUS to skip the index building process for nonpartitioned indexes that are associated with the table that you are loading. LOADPLUS supports this functionality for all SHRLEVEL values except SHRLEVEL CHANGE SQLAPPLY.

DEFINE NO objects

To enable loading data into tables whose table spaces or index spaces were defined with DEFINE NO, LOADPLUS can materialize the underlying data sets without actually loading data. LOADPLUS materializes the data sets by performing a Db2 INSERT and ROLLBACK on the table.

Important

For an SQLAPPLY load, DEFINE NO data sets are materialized by the Db2 INSERT processing that occurs during the COMBINED phase.

Identity columns

  • LOADPLUS allows you to set the size of the cache that LOADPLUS uses when loading generated identity column values.

  • LOADPLUS allows you to specify whether you want to reset the value of MAXASSIGNEDVAL in the SYSIBM.SYSSEQUENCES table when loading identity column values from an input file.

    When generating identity column values, LOADPLUS always updates MAXASSIGNEDVAL.

  • For most load types, you can tell LOADPLUS whether to discard or fail when a generated identity column value would fall outside the range that is defined on the column.
  • Unless you tell LOADPLUS not to enforce check constraints, LOADPLUS fails when attempting to generate identity column values if the identity column is referenced in the check condition of a Db2 check constraint.

Availability

  • LOADPLUS allows you to specify SHRLEVEL REFERENCE with a LOAD REPLACE or LOAD RESUME YES job. SHRLEVEL REFERENCE allows RO access to the table space that you are loading.
  • LOADPLUS allows you to specify SHRLEVEL CHANGE with a LOAD REPLACE job. SHRLEVEL CHANGE allows RW access to the table space that you are loading.

LOAD command options

  • LOADPLUS allows only one LOAD command in the command input data set (SYSIN).
  • LOADPLUS optimizes the processing of INTO PART statements by combining statements that are the same. For example, if you specify INTO PART 1 twice on the same LOAD command, LOADPLUS combines these statements into a single INTO statement and loads one set of rows. In contrast, the Db2 LOAD utility loads two sets of rows into partition 1 in this example.
  • For a field specification, LOADPLUS requires that you specify the position before the data type in the command statement.

WHEN, NULLIF, and DEFAULTIF processing

LOADPLUS provides the RULES installation option for specifying the set of comparison rules to be used in WHEN, NULLIF, and DEFAULTIF processing. Specifying RULES=STANDARD causes comparisons to be performed directly on the input record values rather than the values as they will be in the column. Specifying RULES=BMC causes comparisons to be performed on the values as they will be in the column.

Constraints and referential integrity

  • LOADPLUS optionally allows you to enforce informational referential constraints.
  • LOADPLUS does not support referential checking on self-referencing tables.
  • LOADPLUS does not support referential checking when loading the parent and child in the same load execution.
  • If check constraint conditions contain one or more of the following constant types, LOADPLUS does not enforce those check constraints:
    • Floating-point constants
    • Constants whose length is greater than 254 bytes

Data types and conversions

  • LOADPLUS always truncates digits for conversions of FLOAT to DECIMAL if digits are not needed in the precision. This action differs from the Db2 LOAD utility action of rounding in certain circumstances but is consistent with the LOADPLUS definition of numeric-to-numeric conversions. If rounding is required, use the ROUND keyword on your field specification.
  • LOADPLUS supports all floating-point formats except IEEE Binary Floating Point.

Multiple data sets and multitasking

LOADPLUS automatically multitasks when you specify multiple data sets. You can specify multiple SYSREC input data sets and use multiple SORTOUT data sets to load partitioned tables. You can also specify multiple SORTWK data sets to multitask sort processing and multiple SYSUT1 data sets to multitask index processing.

Work data sets

  • When allocating SORTOUT data sets, LOADPLUS requires that the total allocation be large enough to contain the Db2 row images that are created during the PRELOAD or COMBINED phase.
  • For most single-phase load jobs, LOADPLUS does not use the SORTOUT and SYSUT1 work data sets and does not require that you define them in your JCL. For some single-phase load jobs, however, these data sets are required if you want to be able to restart the job. For details about when these data sets are required for restart, see SORTOUT-data-sets-in-LOADPLUS and SYSUT1-data-sets-in-LOADPLUS.

Copies

  • You can create one or more image copies by using the LOADPLUS COPY YES option, regardless of whether you specify LOAD REPLACE, LOAD RESUME NO, or LOAD RESUME YES.

    You cannot create an image copy when performing an SQLAPPLY load.

  • LOADPLUS creates all image copies with system pages at the beginning of the data set (in the same way that the IBM COPY utility creates image copies when you specify SYSTEMPAGES YES). However, LOADPLUS does not provide a SYSTEMPAGES option.
  • LOADPLUS optionally creates an inline copy of a LOB table space.

Free space

When loading XML data, LOADPLUS applies FREEPAGE specifications to the XML table space and the base table.

Recoverability and restartability

  • LOADPLUS uses work files until it loads all of the data. Therefore, the work files should not be temporary data sets if you want to be able to restart the job.
  • LOADPLUS allows an image copy to be made after a table is loaded even if LOADPLUS leaves the table in CHECK pending status. Because the CHECK DATA utility logs any changes made to the table, data recovery is possible using the RECOVER utility. Recover uses the full image copy and applies log records as necessary, ensuring the integrity of the data in the table. A RECOVER TOCOPY utility execution places the affected table in CHECK pending status again. A subsequent invocation of the CHECK DATA utility restores referential integrity.

  • For all types of load jobs except an SQLAPPLY load, LOADPLUS does not write to the Db2 log, even when your table space is defined as LOGGED. Therefore, you must create a full image copy to ensure recoverability of the table space after loading. You can create a full image copy by using the LOADPLUS COPY YES option. You can also use

    Some content is unavailable due to permissions.

    or the IBM Db2 COPY utility.

    An SQLAPPLY load does write to the Db2 log unless your table space is defined as NOT LOGGED. In addition, you cannot create an image copy with this type of load in LOADPLUS.

COPY pending status

  • When you specify COPY YES, LOADPLUS resets COPY pending status after loading the data and starting the table space.
  • When you specify COPY NO, LOADPLUS places the table space in COPY pending status only if the value of COPYPEND is YES. If the value of COPYPEND is NO, LOADPLUS does not place the table space in COPY pending status.

SYSMAP data set

LOADPLUS uses an SYSMAP data set only when invoking DSNUTILB.

Multiple volumes

For multi-volume STOGROUP-defined table spaces and indexes, LOADPLUS attempts to reallocate the data set on the volume on which it currently resides if the volume is still defined in the storage group. After the current volume, the order of the volumes retrieved from the storage group is unpredictable.

Concurrent utilities

LOADPLUS uses the BMCSYNC table to control access to Db2 spaces by BMC utilities. This allows LOADPLUS and other BMC utilities to run concurrently on Db2 spaces.

Return codes

When the load job discards records (and no other conditions or options result in a greater return code), LOADPLUS completes with return code 0.

SQL Statements in the Dynamic Statement Cache

 

LOADPLUS invalidates the SQL Statements in the Dynamic Statement Cache when the LOAD utility is executed unless INVALIDATECACHE=NO is specified.

The Db2 LOAD utility invalidates the SQL Statements in the Dynamic Statement Cache when STATISTICS and INVALIDATECACHE YES are specified.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*