Restarting a failed ingest operation

If an INGEST command fails before completing and you want to restart it, reissue the INGEST command with the RESTART CONTINUE option. This second INGEST command starts from the last commit point and is also restartable.

Before you begin

The userid restarting the failed INGEST command must have SELECT, INSERT, UPDATE, and DELETE privilege on the restart log table.

About this task

The INGEST utility considers a command to be complete when it reaches the end of the file or pipe. Under any other conditions, the INGEST utility considers the command incomplete. These can include:
  • The INGEST command gets an I/O error while reading the input file or pipe.
  • The INGEST command gets a critical system error from the Db2® database system.
  • The INGEST command gets a Db2 database system error that is likely to prevent any further SQL statements in the INGEST command from succeeding (for example, if the table no longer exists).
  • The INGEST command is killed or terminates abnormally.


Restrictions

  1. If the target table and the restart table are in different table spaces, the two table spaces must be at the same level in terms of rollforward or restore operations.
  2. You cannot modify the contents of the restart table, other than restoring the entire table to keep it in sync with the target table.
  3. The num_flushers_per_partition configuration parameter must be the same as on the original command.
  4. If the input is from files or pipes, the number of input files or pipes must be the same as on the original command.
  5. The input file or pipes must provide the same records and in the same order as on the original command.
  6. The following INGEST command parameters must be the same as on the original command:
    • input type (file or pipe)
    • the SQL statement
    • the field definition list, including the number of fields and all field attributes
  7. The target table columns that the SQL command updates must have the same definition as they had at the time of the original command.
  8. In a partitioned database environment, you cannot have added or removed database partitions.
  9. In a partitioned database environment, you cannot have redistributed data across the partitions.
  10. If an INGEST command specifies the DUMPFILE (BADFILE) parameter, the dump file is guaranteed to be complete only if the INGEST command completes normally in a single run. If an INGEST command fails and the restarted command succeeds, the combination of dump files from the two commands might be missing some records or might contain duplicate records.
If the third, fourth, fifth, or ninth restriction is violated, the ingest utility issues an error and ends the INGEST command. In the case of the other restrictions, the ingest utility does not issue an error, but the restarted INGEST command might produce different output rows than the original would have if it had completed.

Procedure

To restart a failed INGEST operation, do the following:

  1. Use the available information to diagnose and correct the problem that caused the failure
  2. Reissue the INGEST command, specifying the RESTART CONTINUE option with the appropriate job-id.

Results

Once the restarted INGEST command completes, you can reuse the job-id on a later INGEST command.

Example

The following INGEST command failed:
INGEST FROM FILE <source_file> 
   FORMAT DELIMITED 
   (
      $field1 INTEGER EXTERNAL,
      $field2 DATE 'mm/dd/yyyy',
      $field3 CHAR(32)
   )
   RESTART NEW 'ingestjob001'
   INSERT INTO <table-name> 
      VALUES($field1, $field2, $field3);
The DBA corrects the problem that cause the failure and restarts the INGEST command (which starts from the last commit point) with the following command:
INGEST FROM FILE <source_file> 
   FORMAT DELIMITED 
   (
      $field1 INTEGER EXTERNAL,
      $field2 DATE 'mm/dd/yyyy',
      $field3 CHAR(32)
   )
   RESTART CONTINUE 'ingestjob001'
   INSERT INTO <table-name> 
      VALUES($field1, $field2, $field3);