WITH Clause Options for COPY
Valid WITH clause options for the COPY statement are as follows:
COMPRESSION
Creates LZH compressed data files for COPY INTO.
CHARSET=characterset
Defines the character set of the data file. COPY INTO transliterates columns, if necessary, when writing to file and similarly for COPY FROM when reading.
Valid values are UTF8 or the installation character set.
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.
Note: ON_ERROR and ERROR_COUNT cannot be in the same statement.
FILESIZE=size
Splits the output file into multiple parts, where size is the number of megabytes per part.
LOG = 'filename'
Stores to the specified file any rows that COPY cannot process. This option can be used only if ON_ERROR CONTINUE is specified. For COPY INTO, the logged rows are in database (binary) format; for COPY FROM, the logged rows are in file format.
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).
Linux: 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).
The LOG option is not available if the table contains any LONG columns, whether they are part of the COPY list or not.
If copying from a data file that contains duplicate rows (or rows that duplicate rows already in the table) to a table that was created WITH NODUPLICATES and has a HASH, ISAM or BTREE storage structure, 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.
If copying from a data file that contains duplicate keys (or keys that duplicate keys already in the table) to a table that enforces the unique key, COPY displays a warning message and does not add the rows containing the duplicate keys. This operation is sequential so that the first row is copied to the table and a second row with the same key fails.
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 between table format and 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.
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 the COPY cannot be rolled back. Database server errors that indicate data corruption will always roll back the COPY statement. In addition, the user may decide to roll back the entire transaction rather than committing it.
Default: ENABLED
When copying to a file, the WITH ROLLBACK clause has no effect.
ROW_ESTIMATE
Specifies the estimated number of rows to be copied from a file to a table during a bulk copy operation. The DBMS Server uses the specified value to allocate memory for sorting rows before inserting them into the table. An accurate estimate can enhance the performance of the copy operation.
The estimated number of rows must be no less than 0 and no greater than 2,147,483,647. If this parameter is omitted, the default value is 0, in which case the DBMS Server makes its own estimates for disk and memory requirements.
SKIP = n
Directs COPY to skip n rows (where n is a positive integer) from the beginning of the file being read. This is useful when the file contains, for example, a header record which is not to be written to the table.
Default: 0 (skip no rows)
STATISTICS
X100 only. For COPY...FROM, creates statistics on the table loaded. Histograms are built for all columns in the loaded table.
Last modified date: 04/26/2024