DB2 Version 9.7 for Linux, UNIX, and Windows

Resuming, restarting, or terminating load operations in a partitioned database environment

The steps you need to take following failed load operations in a partitioned database environment depend on when the failure occurred.

The load process in a multi-partition database consists of two stages:

The setup stage, during which database partition-level resources such as table locks on output database partitions are acquired
In general, if a failure occurs during the setup stage, restart and terminate operations are not necessary. What you need to do depends on the error isolation mode that was specified for the failed load operation.

If the load operation specified that setup stage errors were not to be isolated, the entire load operation is cancelled and the state of the table on each database partition is rolled back to the state it was in prior to the load operation.

If the load operation specified that setup stage errors were to be isolated, the load operation continues on the database partitions where the setup stage was successful, but the table on each of the failing database partitions is rolled back to the state it was in prior to the load operation. This means that a single load operation can fail at different stages if some partitions fail during the setup stage and others fail during the load stage
The load stage, during which data is formatted and loaded into tables on the database partitions
If a load operation fails on at least one database partition during the load stage of a multi-partition database load operation, a load RESTART or load TERMINATE command must be issued. This is necessary because loading data in a multi-partition database is done through a single transaction.

You should choose a load RESTART if you can fix the problems that caused the failed load to occur. This saves time because if a load restart operation is initiated, the load operation continues from where it left off on all database partitions.

You should choose a load TERMINATE if you want the table returned to the state it was in prior to the initial load operation.

Procedure:

Determining when a load failed

The first thing you need to do if your load operation in a partitioned environment fails is to determine on which partitions it failed and at what stage each of them failed. This is done by looking at the partition summary. If the load command was issued from the CLP, the partition summary is displayed at the end of the load (see example below). If the load command was issued from the db2Load API, the partition summary is contained in the poAgentInfoList field of the db2PartLoadOut structure.

If there is an entry of "LOAD" for "Agent Type", for a given partition, then that partition reached the load stage, otherwise a failure occurred during the setup stage. A negative SQL Code indicates that it failed. In the following example, the load failed on partition 1 during the load stage.

   Agent Type     Node     SQL Code     Result
   ________________________________________________________________
   LOAD           000      +00000000    Success.
   ________________________________________________________________
   LOAD           001      -00000289    Error. May require RESTART.
   ________________________________________________________________
   LOAD           002      +00000000    Success.
   ________________________________________________________________
   LOAD           003      +00000000    Success.
.
.
.

Resuming, restarting, or terminating a failed load

Only loads with the ISOLATE_PART_ERRS option specifying SETUP_ERRS_ONLY or SETUP_AND_LOAD_ERRS should fail during the setup stage. For loads that fail on at least one output database partition fail during this stage, you can issue a LOAD REPLACE or LOAD INSERT command. Use the OUTPUT_DBPARTNUMS option to specify only those database partitions on which it failed.

For loads that fail on at least one output database partition during the load stage, issue a load RESTART or load TERMINATE command.

For loads that fail on at least one output database partition during the setup stage and at least one output database partition during the load stage, you need to perform two load operations to resume the failed load-one for the setup stage failures and one for the load stage failures, as previously described. To effectively undo this type of failed load operation, issue a load TERMINATE command. However, after issuing the command, you have to account for all partitions because no changes were made to the table on the partitions that failed during the setup stage, and all the changes have been undone for the partitions that failed during the load stage.

For example, TABLE1 is defined on database partitions 0 through 3 in database WSDB. The following command is issued:

load from load.del of del insert into table1 partitioned db config
isolate_part_errs setup_and_load_errs

There is a failure on output database partition 1 during the setup stage. Since setup stage errors are isolated, the load operation continues, but there is a failure on partition 3 during the load stage. To resume the load operation, you would issue the following commands:

load from load.del of del replace into table1 partitioned db config
output_dbpartnums (1)

load from load.del of del restart into table1 partitioned db config
isolate_part_errs setup_and_load_errs

Note: For load restart operations, the options specified in the LOAD RESTART command will be honored, so it is important that they are identical to the ones specified in the original LOAD command.