Resuming and 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:
  1. 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, a terminate operation is 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 canceled and the state of the table on each database partition is rolled back to the state it was in before 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 before 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

  2. 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 TERMINATE command must be issued. This is necessary because loading data in a multi-partition database is done through a single transaction.

    If you want the table returned to the state it was in before the initial load operation, choose a LOAD TERMINATE.

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 following example). 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 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 TERMINATE command.