Oracle7 Server Utilities

Using the DB2 RESUME Option

You can use the DB2 syntax for RESUME, but you may prefer to use SQL*Loader's equivalent keywords. See "Loading into Non-Empty Database Tables"

for more details about the SQL*Loader options summarized below.

DB2 SQL*Loader Options Result
RESUME NO or no RESUME clause INSERT Data loaded only if table is empty. Otherwise an error is returned.
RESUME YES APPEND New data is appended to existing data in the table, if any.
RESUME NO REPLACE REPLACE New data replaces existing table data, if any.

Table C - 1. DB2 Functions and Equivalent SQL*Loader Operations

A description of the DB2 syntax follows.

If the tables you are loading already contain data, you have three choices for the disposition of that data. Indicate your choice using the RESUME clause. The argument to RESUME can be enclosed in parentheses.

RESUME  { YES | NO [ REPLACE ] }

where:

YES Appends the new rows to rows already in the table.
NO Requires the table to be empty before loading. An error message results if the table contains rows and the run is terminated. This is the default.
NO REPLACE Deletes any data in the table before loading new data. Thus, the new data will replace the old. This argument requires that the username invoking SQL*Loader have DELETE privilege on the table. You cannot recover the data that was in the table before the load, unless you saved it using Export or something comparable.

In SQL*Loader you can use one RESUME clause to apply to all loaded tables by placing the RESUME clause before any INTO TABLE clauses. Alternatively, you can specify your RESUME options on a table-by-table basis by putting a RESUME clause after the INTO TABLE specification. The RESUME option following a table name will override one placed earlier in the file. The earlier RESUME applies to all tables that do not have their own RESUME clause.

DB2	*SQLLoader Options**	Result
RESUME NO or no RESUME clause	INSERT	Data loaded only if table is empty. Otherwise an error is returned.
RESUME YES	APPEND	New data is appended to existing data in the table, if any.
RESUME NO REPLACE	REPLACE	New data replaces existing table data, if any.

YES	Appends the new rows to rows already in the table.
NO	Requires the table to be empty before loading. An error message results if the table contains rows and the run is terminated. This is the default.
NO REPLACE	Deletes any data in the table before loading new data. Thus, the new data will replace the old. This argument requires that the username invoking SQL*Loader have DELETE privilege on the table. You cannot recover the data that was in the table before the load, unless you saved it using Export or something comparable.