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 or Version 10.5 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
Example
- 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.