With Clause Options

Valid WITH clause options for the COPY statement are as follows:

• ON_ERROR = TERMINATE | CONTINUE

  Directs COPY to continue after encountering conversion errors.

  To direct copy to continue until a specified number of conversion errors have occurred, specify the ERROR_COUNT option instead.

  By default, COPY terminates when an error occurs while converting a table row into file format.

  When ON_ERROR is set to CONTINUE, COPY displays a warning whenever a conversion error occurs, skips the row that caused the error, and continues processing the remaining rows. At the end of the processing, COPY displays a message that indicates how many warnings were issued and how many rows were successfully copied.

  Setting ON_ERROR to CONTINUE does not affect how COPY responds to errors other than conversion errors. Any other error, such as an error writing the file, terminates the COPY operation.

• ERROR_COUNT = n

  Specifies how many errors can occur before processing terminates.

  Default: 1.

  If ON_ERROR is set to continue, setting ERROR_COUNT has no effect.

• LOG = 'filename'

  Stores to a file any rows that COPY cannot process. This option can be used only if ON_ERROR CONTINUE is specified. When specified with log, COPY places any rows that it cannot process into the specified log file. The rows in the log file are in the same format as the rows in the database.

Logging works as follows:

Windows: COPY opens the log file prior to the start of data transfer. If it cannot open the log file, copy halts. If an error occurs when writing to the log file, copy issues a warning, but continues. If the specified log file already exists, it is overwritten with the new values (or truncated if the copy operation encounters no bad rows).

UNIX: COPY opens the log file prior to the start of data transfer. If it cannot open the log file, copy halts. If an error occurs when writing to the log file, copy issues a warning, but continues. If the specified log file already exists, it is overwritten with the new values (or truncated if the copy operation encounters no bad rows).

VMS: COPY attempts to open the log file prior to the start of data transfer. If it cannot open the log file, copy halts. If an error occurs when writing to the log file, copy issues a warning, but continues. If the log file already exists, copy creates a new version.

If copying from a data file that contains duplicate rows (or rows that duplicate rows already in the table) to a table that has a unique key, copy displays a warning message and does not add the duplicate rows. If the WITH LOG option is specified, COPY does not write the duplicate rows to the log file.

• ROLLBACK = ENABLED | DISABLED

  Enables or disables rollback, as follows:

  • ENABLED

    Directs the DBMS Server to back out all rows appended by the copy if the copy is terminated due to an error.

  • DISABLED

    Retains the appended rows.

    The ROLLBACK=DISABLED option does not mean that a transaction cannot be rolled back. Database server errors that indicate data corruption still causes rollback, and rows are not committed until the transaction is complete.

    Default: ENABLED

    When copying to a file, the WITH ROLLBACK clause has no effect.