By default, the COPY FROM command aborts on the first record that cannot be correctly parsed. However, in some cases, you may want the load process to continue and then later review rows that cannot be loaded. You can use the Reject File functionality to accomplish this.
Reject File is disabled, by default. To enable it, specify reject_file_path. This is the path to a file that will contain the rejected rows after load. You can set the number of records that can be rejected prior to the load being aborted and rolled back. To accomplish this, set the abort_on_count or abort_on_threshold parameter. If only reject_file_path is set, the processor will fail (terminate) on the first error and the row that was incorrect will be output to the reject file.
Usage example:
copy from '<path to file with data>' with (format txt_variable, …, reject_file_path '<path to reject file>', abort_on_count 3)
The above command would fail and the load would be terminated if there were more than three incorrect rows in the input file. All rejected rows will be added to <path to reject file>.
HYPERSTAGE LOADER REJECT FILE OPTIONS | |
|---|---|
OPTION | DESCRIPTION |
reject_file_path | Path to the file where rejected rows are stored. Rejected rows are placed into the reject file in the order they are rejected. The original format is preserved to allow the operator to correct and rerun the load for only the rejected rows. Note: If reject_file_path is set, abort_on_count or abort_on_threshold must be set, as well. |
abort_on_count | Abort and rollback the load if the number of rejected rows exceeds this value. If this value is not set, the load will be rolled back to the first bad record if the load fails. A value of -1 means never abort. A value of 0 means abort on first rejected row. There is no upper limit on this value. Note: abort_on_count and abort_on_threshold are mutually exclusive. |
abort_on_threshold | Abort and rollback the load if the relative number of rejected rows to total processed rows exceeds this value (threshold test starts after one packrow row has been processed). Value must be in the range (0,1). This is an open interval. For example: set @ abort_on_threshold=0.01 / 0.5 / 0.99 means that 1% / 50% / 99% of all processed lines corrupted will terminate the Hyperstage Loader and save the problematic rows in the reject file. Note: abort_on_count and abort_on_threshold are mutually exclusive. |
| WebFOCUS |