Dataloader/MP Reference Manual

Recovery Strategies
DataLoader/MP Reference Manual—424148-003
7-5
Restarting From An Unknown State
system, the date column will not necessarily be a reliable indicator of when the table or
record was last updated.
You can set up a system in which data is segregated by date into separate loads so
that you can tell when the table or record was last updated. For example, on Sunday, if
the posting of data from previous weeks has been delayed, a separate load run is done
for each of the delayed weeks’ work, followed by the load run for the data of the week
just completed. If this loading can be done reliably, you can use restarting from an
unknown state based upon the business data.
However, in most cases, a table will not have business data that can be used to
identify the load run that last altered it. In this case, you have the option of adding a
column for this purpose. Give this column a name, such as LOAD_ID, and make it a
32-bit integer. LOAD_ID starts at zero and is used by the load program for comparison
purposes. If the load run finishes successfully, the load run will increment the LOAD_ID
column’s value. If the load run fails, the load run will not increment LOAD_ID.
Following is an example of this algorithm:
At initialization, the load program reads the current LOAD_ID from a parameter file or
table.
The updating of each row depends upon that row not having its LOAD_ID equal to the
current LOAD_ID:
UPDATE
=SALESTABLE
SET
LOAD_ID = :CurLoadID,
SALES_THIS_YEAR = SALES_THIS_YEAR + :SalesThisWeek
WHERE
LOAD_ID < :CurLoadID
AND
PART_NUM = :PartNum
At successful completion of the load job, the program increments the LOAD_ID in the
parameter file or table.
The LOAD_ID field can serve another purpose. It makes it possible to query the
database to determine which rows were and were not altered in the latest load run.
Generally, restarting from an unknown state is not the best recovery strategy when
multiple updates to a record can occur in a single load job. The above code could be
extended to accommodate multiple updates by adding another column called
RECORD_NUM, also a 32-bit integer, and having the DataLoader/MP process count
records, noting the number of the input record that the update came from: