SHRLEVEL considerations

SHRLEVEL CHANGE considerations

You can specify SHRLEVEL CHANGE with LOAD REPLACE, LOAD RESUME YES PART n REPLACE, or LOAD RESUME YES (without PART n REPLACE). The load process differs based on these options.

Restrictions on SHRLEVEL CHANGE LOAD REPLACE

The following restrictions apply when you specify SHRLEVEL CHANGE and you are replacing one or more partitions.

LOADPLUS terminates when any of the following conditions exists and you specify SHRLEVEL CHANGE with LOAD REPLACE:

• You are running a load job that invokes DSNUTILB. LOADPLUS issues message BMC50178E and terminates with return code 8.
• Partitions are added to a partition-by-growth table space (other than by LOADPLUS) while you are loading that table space. LOADPLUS issues messages BMC50225E and BMC50010U and terminates with return code 16.

LOADPLUS terminates when any of the following conditions exists and you specify SHRLEVEL CHANGE with LOAD RESUME YES PART n REPLACE:

• Nonpartitioning indexes exist on the table space.
• You are loading a partition-by-growth table space.
• You are running a load job that invokes DSNUTILB. LOADPLUS issues message BMC50178E and terminates with return code 8. 

Considerations for an SQLAPPLY load

The following considerations apply when you are running an SQLAPPLY load job (SHRLEVEL CHANGE LOAD RESUME YES). 

LOB and XML data

LOADPLUS invokes DSNUTILB when you use SQLAPPLY to load LOB or XML data.

DSNUTILB load jobs

The following table describes the restrictions that apply to certain options when invoking DSNUTILB for an SQLAPPLY load job:

DB2 logs

Because this type of load uses insert processing, it writes to your Db2 logs unless your table space is defined as NOT LOGGED. With this additional activity, you might need to increase the size of your active logs. Refer to your IBM Db2 installation guide for guidelines to use when sizing your active logs.

Processing phases

For this type of load, LOADPLUS uses its single-phase load architecture to pass your data to High-speed Apply.

Work data sets

For this type of load, LOADPLUS does not require the SORTOUT and SYSUT1 data sets, and does not use them if they are specified.

SNAP dumps

When High-speed Apply generates a SNAP dump in response to an error, High-speed Apply tells the dump to an APTDUMP data set.

To enable this capability, you must include the following DD statement in your LOADPLUS JCL:

Table space organization

High-speed Apply loads your data by using SQL INSERT statements. Therefore, following an SQLAPPLY load, your table functions as a table whose data was inserted, not loaded. Because insert processing does not preserve the table’s free space and free pages, this type of load can affect the organization of your table. Consider reorganizing your table space following this type of load.

ORDER YES can help maintain the order of your table space and reduce the need to reorganize the table space. Specify ORDER YES if both of the following conditions exist:

• You are adding new rows to an empty table space or to a table space with little or no free space.
• You want the new records inserted in clustering key or partitioning key order.

Multiple table loads

For performance reasons with this type of load, LOADPLUS sorts multiple table data only if you specify ORDER YES.

Referential integrity

If you are loading multiple tables that have foreign key relationships between them, and you want your parent tables to load before their child tables, specify ORDER YES. This causes LOADPLUS to assign a table number based on the parent-child relationships and to sort your data by this assigned table number.

If you are loading multiple tables that are self-referential or that have circular relationships, LOADPLUS cannot determine the correct order. To maintain referential integrity in this case, order the data before running your load job, and specify ORDER NO on your LOAD command.

Duplicate keys

When you run an SQLAPPLY load, LOADPLUS handles duplicates differently from other load types. For this type of load, if the input file contains duplicate rows, LOADPLUS loads the first one that it encounters and discards any subsequent ones.

This type of load uses Db2 insert processing, and Db2 rejects a duplicate row in the first index in which it detects that the row is a duplicate. Therefore, LOADPLUS might not detect all indexes in which a row is a duplicate.

For this type of load, LOADPLUS detects and discards duplicates during insert processing in the COMBINED phase.

Copies

For an SQLAPPLY load, your table space is always available. Therefore, for this type of load, LOADPLUS does not create an image copy or DSN1COPY-type copy. If you include COPY YES in your LOAD command, LOADPLUS terminates.

Restart

If you restart an SQLAPPLY load, you must use the same input data that you used originally and it must be in the same order. For example, if you are loading concatenated data sets, these data sets must be in the same order as they were originally, and you must not include any additional data sets. Similarly, if LOADPLUS encountered duplicates during the original job, you should not remove them before you restart.

Statistics

For an SQLAPPLY load, LOADPLUS does not update BMC statistics tables or Db2 catalog statistics. However, because this type of load uses SQL insert processing, Db2 updates the real-time statistics tables as it would for any other Db2 application that uses SQL insert processing.

For an SQLAPPLY load, LOADPLUS generates a report that indicates the number of loaded rows and the number of rows that were discarded due to SQL errors. LOADPLUS provides this information by table space, partition, and table.

Incompatible LOADPLUS command options

Some LOADPLUS command options are not available when you perform an SQLAPPLY load.

The following table describes the options that are not valid with this type of load and how LOADPLUS responds if your job contains these options: