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 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 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
- 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.
If you can fix the problems that caused the failed load to occur, choose a LOAD RESTART. This saves time because if a load restart operation is initiated, the load operation continues from where it left off on all database partitions.
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.
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 must 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 are undone for the partitions that failed during the load stage.
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