Restarting LOADPLUS

In general, when you encounter a failure, you can correct the problem and restart the load with either the RESTART or RESTART(PHASE) parameter on your EXEC statement. LOADPLUS issues messages as it loads and rebuilds each Db2 object. The BMCSYNC table contains an entry for each Db2 object that is involved in the load and its current status. The BMCUTIL table contains an entry for each load job and its current status.

LOADPLUS relies on the object structure and input data to remain unchanged between the original run and the restarted run:

• Input data: 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, do not remove them before you restart. Data that is missing or that is in a different order in the restarted job than in the original job produces unpredictable results.
• Tables: You must load the same table that you specified in the original run. The table structure must not be changed (as with an ALTER) before restarting the load.
• Indexes: The same indexes must participate in the restarted run that participated in the original run. Additionally, the structure of those indexes must not be changed before restarting the load.

The following topics describe information that you need to know when restarting or recovering from specific situations or when a particular environment, type of load job, or object is involved. Use the following table to help locate this information:

You should not specify DISP=MOD for image copy data sets because LOADPLUS does not reset the data set to empty but appends to any data already present in the file. 

Data sharing environment

On restart in a data sharing environment, LOADPLUS can use either the same member that was used in the original load or any other member in the group. 

DEFINE NO objects

For objects that are defined with DEFINE NO, if LOADPLUS is unable to materialize a data set that is associated with the tables that you are loading, LOADPLUS attempts to materialize the underlying data sets for all of the tables before terminating.

If you are loading a table whose table space or index spaces are defined with DEFINE NO and there is a check constraint or VALIDPROC on the table, LOADPLUS attempts to materialize the underlying data sets. However, if LOADPLUS cannot resolve the constraint or if the VALIDPROC prevents LOADPLUS from materializing the data sets, LOADPLUS terminates. To load the table in this situation, you must manually materialize the associated data sets before you restart the job. 

DELETEFILES

To restart during DELETEFILES processing, specify RESTART without (PHASE). 

DSNUTILB load

After the UTILINIT phase, LOADPLUS passes the restart parameter to DSNUTILB. Restart processing is handled by the IBM Db2 LOAD utility. 

Duplicate unique keys

If you are restarting because the job terminated during discard processing while handling duplicate unique keys, LOADPLUS issues message BMC51449W if any indexes are left in a RECOVER pending status. In this case, you can recover the affected indexes by running BMC AMI Recover or the IBM RECOVER utility. 

Dynamic allocation

On restart, LOADPLUS automatically reallocates dynamically allocated data sets.

Changing any dynamic data set allocation option on restart such that it results in different ddnames or a different number of DDs than the original option can produce an error. If you need to change the number of SORTOUT or SYSUT1 work files, resubmit the job with a parameter of NEW.

In addition, you cannot change the value for the following options, which are related to dynamic allocation, on any restart:

• ACTIVE
• ENUMROWS

To change the value of other dynamic data set allocation options, specify RESTART(PHASE).

If you run out of space when dynamically allocating copy data sets, you can perform one of the following procedures:

ENFORCE option

You cannot change the value of the ENFORCE option on restart.

ENUMROWS option

You cannot change the value of the ENUMROWS option on restart.

FASTSWITCH option

You cannot change the value of the FASTSWITCH option on restart.

Field specification

You cannot change your field specification on restart.

IDCAMS DEFINE process

If you are restarting because a failure occurred during IDCAMS DEFINE processing, you must manually define the data set. 

Inadequate space failure on a Db2 object during a LOAD REPLACE job

Failure during the LOAD phase can result in an unusable table space. If a failure occurs on a LOAD REPLACE or LOAD RESUME YES PART n REPLACE job due to inadequate space for a Db2 object, complete the actions that the following table describes to help eliminate the cause of the failure:

The following restrictions apply when reallocating after an inadequate space failure:

• After the PRELOAD phase, LOADPLUS does not use changes to FREEPAGE, PCTFREE, or PIECESIZE values. LOADPLUS uses other values, such as storage group definitions and data set passwords, if they have changed.
• You cannot correct this problem by altering the primary and secondary quantity values to -1 (thus requesting that secondary extents be created by using a sliding-scale method). LOADPLUS ignores this change on restart.

After correcting the problem that caused the inadequate space failure, restart the load job with the RESTART option. LOADPLUS does not reload or rebuild Db2 objects that were already loaded or built successfully. However, if you reallocate any data sets that were successfully loaded or built, restart the load process at the beginning of the LOAD phase with RESTART(PHASE).

Inadequate space failure on SYSERR data set

When allocating a SYSERR data set, underestimating its size can result in an x37 abend, indicating an out-of-space condition. Perform one of the following set of steps to recover and restart your job.

INDEX UPDATE

If you specified INDEX UPDATE and a failure occurred during index update processing, you must recover your table space and indexes. If you attempt to restart, LOADPLUS terminates and issues message BMC51435S.

When you specify INDEX UPDATE and index update processing completes, but the job fails in the LOAD or COMBINED phase, you must restart the job by specifying RESTART without PHASE. If you specify RESTART(PHASE), LOADPLUS terminates and issues message BMC51436E. 

Inline image copies

If you want an inline image copy and you restart your two-phase job after the load job terminates in the LOAD phase, you must specify RESTART(PHASE).

If you are restarting a two-phase load job and do not specify RESTART(PHASE) or you are restarting a single-phase load job, LOADPLUS changes the value of the INLINE command option to NO, regardless of the value that you specified for the INLINE command or the INLINECP installation option.

Nonparticipating nonpartitioned indexes (SKIPIX option)

If you change the SKIPIX option on restart after the load job terminated in any phase other than UTILINIT, LOADPLUS issues message BMC50113E and terminates.

ORDER option

You cannot change the value of the ORDER command option on restart.

Partition number

You cannot change the partition number on restart.

Partition-by-growth table spaces

The following restrictions apply when you restart a load of a partition-by-growth table space:

• If the value for MAXPARTITIONS changes between the time the job fails and the time you restart it, LOADPLUS issues message BMC50221U and terminates.
• If Db2 adds partitions before you restart a job, LOADPLUS issues message BMC50221U and terminates.
• If a SHRLEVEL NONE load job fails because insufficient space is available, BMC recommends that you perform an ALTER TABLESPACE to decrease the PCTFREE or FREEPAGE value before restarting the job. However, LOADPLUS only honors these changes in the following situations:
  • For a single-phase load, LOADPLUS honors these changes for those partitions to which it has not started loading.
  • For a two-phase load, LOADPLUS honors these changes only if the failure occurs during the PRELOAD phase. 

Real-time statistics

Unlike the IBM Db2 LOAD utility, LOADPLUS attempts to update Db2 real-time statistics tables any time you restart your load job. If LOADPLUS is unable to update one or more columns, LOADPLUS issues message BMC50290I and continues processing.

Referential constraint checking

If you restart a job that checks referential constraints, LOADPLUS might not be able to start at the beginning of the job and, therefore, might not be able to check all referential constraints. In this case, LOADPLUS sets CHKP status on the table space. 

RESUME YES SHRLEVEL NONE

If your load job failed in any phase other than UTILINIT or UTILTERM, and you specify LOAD RESUME YES with SHRLEVEL NONE in effect, ensure that the participating objects are stopped before you restart your job.

Secondary extents

On a restart, if you alter the primary and secondary quantity values to -1 (thus requesting that secondary extents be created by using a sliding-scale method), LOADPLUS ignores this change.

Single-phase load

In general, the restart parameter RESTART(PHASE) works the same as RESTART when you restart a single-phase load job. For more criteria that might affect which RESTART option to use, refer to the other topics in this section.

For a single-phase LOAD RESUME YES SHRLEVEL NONE load job, you cannot restart the job when either of the following conditions exists:

• You did not allocate a SYSUT1 data set (or specify dynamic allocation for SYSUT1) on the original job. This restriction does not apply if you are replacing a table (specifying TABLE tableName REPLACE).
• You specified ORDER YES and did not allocate a SORTOUT data set (or specify dynamic allocation for SORTOUT) on the original job. This restriction does not apply if you are replacing one or more partitions, or replacing a table (specifying PART n REPLACE or TABLE tableName REPLACE).

See Recovering-the-Db2-object-after-terminating-or-canceling-a-LOADPLUS-job for information about recovering your load job.

SORTOUT, SYSUT1, and SYSERR data sets

If you specified DISP=NEW for the SORTOUT, SYSUT1, or SYSERR data sets, you must change your JCL to DISP=OLD before restarting. Alternatively, you can allocate the data sets with DISP=MOD, or allocate the data sets in a preceding step and then specify DISP=OLD for the data sets in the load step. For more information, see Check for data set attributes.

Do not delete your SORTOUT, SYSUT1, or SYSERR data sets before restarting your job. Doing so produces unpredictable results.

SQLAPPLY load

As with other LOADPLUS jobs, if you restart an SQLAPPLY job, you must use the same input data that you used originally and it must be in the same order. This is particularly critical for an SQLAPPLY job because of the way that High-speed Apply handles restart processing.

In general, the restart parameter RESTART(PHASE) works the same as RESTART when you restart an SQLAPPLY job. For more criteria that might affect which RESTART option to use, refer to the other topics in this section.

Statistics

The following considerations apply to restarted jobs when you specify BMCSTATS YES or UPDATEDB2STATS YES:

• On restart, LOADPLUS does not update statistics if, in the original job, any participating table space partitions were completely loaded or any participating index partitions were completely updated or built.
• You can change the TSSAMPLEPCT option on restart.

SWITCHTIME option

If the SWITCHTIME process did not start before the load job failed, you can change the SWITCHTIME option on restart. If the SWITCHTIME process started before the load job failed, LOADPLUS immediately performs the rename or FASTSWITCH process on restart.

SYSDISC data sets

To prevent data loss, BMC makes the following recommendations regarding SYSDISC data sets and restart:

• Do not delete your SYSDISC data set before restarting your job.
• If you need to reallocate your SYSDISC data set due to an out-of-space condition (x37 abend), copy the contents of your original file to the new file before restarting your job.

Two-phase load

In general, the restart option that you specify to restart a two-phase load job depends on whether you want to restart from the last restart sync point or at the beginning of the last incomplete phase:

• To restart from the last restart sync point, specify RESTART without (PHASE). LOADPLUS takes restart sync points as each phase completes and as the processing of each Db2 object completes.

  Important

  The SYNC option (on the LOAD command) controls the frequency with which rows are updated in the BMCSYNC table. It does not control the sync points that LOADPLUS uses when restarting.

• To restart at the beginning of the last incomplete phase, specify RESTART(PHASE). The utility ID must exist in the BMCUTIL table.