Saving Load Exceptions (EXCEPTIONS)

COPY exceptions consist of informational messages describing why a row of data could not be parsed. The optional EXCEPTIONS parameter lets you specify a file to which COPY writes exceptions. If you omit this parameter, COPY saves exception files to the following default location:

catalog‑dir/CopyErrorLogs/tablename-sourcefilename-copy-from-exceptions
catalog-dir Database catalog files directory
table-sourcefile Names of the target table and source data file
-copy-from-exceptions File suffix appended to table and source file name

Using REJECTED DATA AS TABLE r_table is mutually exclusive with using the EXCEPTIONS filename parameter. The rejected data table includes a column with the exceptions messages. COPY does not permit both parameters. Trying to do so results in this error:

ERROR 0:  Cannot specify both an exceptions file and a rejected table in the same statement

The optional EXCEPTIONS parameter lets you specify a file of your choice to which COPY writes load exceptions. The EXCEPTIONS file indicates the input line number and the reason for each data record exception in this format: 

COPY: Input record number in pathofinputfile has been rejected (reason). Please see pathtorejectfile, record recordnum for the rejected record.

If copying from STDIN, the filename-of-source is STDIN.

You can use specific rejected data and exceptions files with one or more of the files you are loading. Separate consecutive rejected data and exception file names with a comma (,) in the COPY statement.

You must specify a filename in the path to load multiple input files. Keep in mind that long table names combined with long data file names can exceed the operating system's maximum length (typically 255 characters). To work around file names exceeding the maximum length, use a path for the exceptions file that differs from the default path; for example, \tmp\<shorter-file-name>.

For all data loads (except for COPY LOCAL), COPY behaves as follows:

No exceptions file specified
  • For one data source file (pathToData or STDIN), all information stored as one file in the default directory.
  • For multiple data files, all information stored as separate files, one for each data file in default directory.
Exceptions file specified
  • For one data file, the path is treated as a file, and COPY stores all information in this file. If the path is not a file, COPY returns an error.
  • For multiple data files, the path is treated as a directory and COPY stores all information in separate files, one for each data file. If path is not a directory, COPY returns an error.
  • Exceptions files are not stored on the initiator node.
  • You can specify only one path per node. If you specify more than one path per node, COPY returns an error.