DB2 Version 10.1 for Linux, UNIX, and Windows

Creating the restart table

By default, failed INGEST commands are restartable from the last commit point; however you first need to create a restart table, which stores the information needed to resume an INGEST command.

About this task

You have to create the restart table only once, and that table will be used by all INGEST commands in the database.

The ingest utility will use this table to store information needed to resume an incomplete INGEST command from the last commit point.

Note: The restart table does not contain copies of the input rows, only some counters to indicate which rows have been committed.

Restrictions

It is recommended that you place the restart table in the same tablespace as the target tables that the ingest utility updates. If this is not possible, you must ensure that the tablespace containing the restart table is at the same level as the tablespace containing the target table. For example, if you restore or roll forward one of the table spaces, you must restore or roll forward the other to the same level. If the table spaces are at different levels and you run an INGEST command with the RESTART CONTINUE option, the ingest utility could fail or ingest incorrect data.
If your disaster recovery strategy includes replicating the target tables of ingest operations, you must also replicate the restart table so it is kept in sync with the target tables.

Procedure

To create the restart table:

If you are using a Version 10.1 server, call the SYSPROC.SYSINSTALLOBJECTS stored procedure:
```
db2 "CALL SYSPROC.SYSINSTALLOBJECTS('INGEST', 'C', tablespace-name, NULL)"
```

If you are using a Version 9.5, Version 9.7, or Version 9.8 server, issue the following SQL statements:

CREATE TABLE SYSTOOLS.INGESTRESTART ( 
  JOBID             VARCHAR(256) NOT NULL, 
  APPLICATIONID     VARCHAR(256) NOT NULL, 
  FLUSHERID         INT          NOT NULL, 
  FLUSHERDISTID     INT          NOT NULL, 
  TRANSPORTERID     INT          NOT NULL, 
  BUFFERID          BIGINT       NOT NULL, 
  BYTEPOS           BIGINT       NOT NULL, 
  ROWSPROCESSED     INT          NOT NULL, 
    PRIMARY KEY (JOBID, FLUSHERID, TRANSPORTERID, FLUSHERDISTID)) 
  IN <tablespace-name>
  DISTRIBUTE BY (FLUSHERDISTID);

GRANT SELECT, INSERT, UPDATE, DELETE
   ON TABLE SYSTOOLS.INGESTRESTART TO PUBLIC;

Results

The restart table, SYSTOOLS.INGESTRESTART, should now be created in the specified table space, and you can now run restartable INGEST commands.

Example

A DBA intends to run all INGEST commands as restartable, so the DBA needs to first create a restart table:

The DBA connects to the database:
```
db2 CONNECT TO sample
```

The DBA calls the stored procedure:

db2 "CALL SYSPROC.SYSINSTALLOBJECTS('INGEST', 'C', NULL, NULL)"

What to do next

Ensure that any user who will modify the restart table has the appropriate authorization:

If the INGEST command specifies RESTART NEW, the user must have SELECT, INSERT, UPDATE, and DELETE privilege on the restart table.
If the INGEST command specifies RESTART TERMINATE, the user must have SELECT and DELETE privilege on the restart table.