1.31 COPY LOCAL Rejection and Exception Files

COPY LOCAL Rejection and Exception Files

1. Invoking COPY LOCAL (or COPY LOCAL FROM STDIN) does not automatically create rejected data and exceptions files.

This behavior differs from using COPY, which saves both files automatically, regardless of whether you use the optional REJECTED DATA and EXCEPTIONS parameters to specify either file explicitly.

2. We need to Use the REJECTED DATA and EXCEPTIONS parameters with COPY LOCAL and COPY LOCAL FROM STDIN to save the corresponding output files on the client.

If you do not use these options, rejected data parsing events (and the exceptions that describe them) are not retained, even if they occur.

3. Because COPY LOCAL (and COPY LOCAL FROM STDIN) must write any rejected rows and exceptions to the client, you cannot use the [ON nodename ] clause with either the rejected data or exceptions options.

4. Using COPY LOCAL does not support storing rejected data in a table, as you can when using the COPY statement.

5. With COPY LOCAL  If you also use theREJECTED DATA and EXCEPTIONS options, the statement writes rejected rows and exceptions and to separate files. The respective files contain all rejected rows and corresponding exceptions, respectively, regardless of how many input files were loaded. So ith COPY LOCAL the file names are actual file names and not directories.

6. When you invoke COPY LOCAL or COPY LOCAL FROM STDIN, the files you specify need not pre-exist. If they do, COPY LOCAL must be able to overwrite them.

You can specify the path and file names with vsql variables:

\set rejected ../except_reject/copyLocal.rejected

\set exceptions ../except_reject/copyLocal.exceptions

When you use the COPY LOCAL or COPY LOCAL FROM STDIN statement, specify the variable names for the files with their corresponding parameters:

=> COPY large_tbl FROM LOCAL rejected data :rejected exceptions :exceptions;

=> COPY large_tbl FROM LOCAL STDIN rejected data :rejected exceptions :exceptions;

Example:

sukul1=> COPY USER_30_DAY_TABLES.EMPTABLE

sukul1-> (FIRSTNAME,

sukul1(> LASTNAME FILLER VARCHAR(20),

sukul1(> INITIALS AS SUBSTR(LASTNAME,1,1),

sukul1(> DAILYWAGE,

sukul1(> CURRUSER AS CURRENT_USER(),

sukul1(> LOAD_DT AS CURRENT_DATE(),

sukul1(> LOAD_TM AS CURRENT_TIME

sukul1(> )

sukul1-> FROM LOCAL '/home/sukul1/empdet*.dat'

sukul1-> REJECTED DATA '/home/sukul1/rej'

sukul1-> EXCEPTIONS '/home/sukul1/exp'

sukul1-> DELIMITER '|'

sukul1-> DIRECT;

 Rows Loaded

-------------

           3

(1 row)

sukul1=> select get_num_rejected_rows();

ERROR 4106:  No single-source bulk loads have been executed in this session

sukul1=> select * from USER_30_DAY_TABLES.EMPTABLE;

 FIRSTNAME | INITIALS | DAILYWAGE | CURRUSER |  LOAD_DT   |     LOAD_TM

-----------+----------+-----------+----------+------------+-----------------

 RUTUJA    | K        |    234.11 | sukul1   | 2015-11-15 | 18:32:41.706638

 SACHIN    | T        |     56.23 | sukul1   | 2015-11-15 | 18:32:41.706638

 SUKUL     | M        |     23.54 | sukul1   | 2015-11-15 | 18:32:41.706638

(3 rows)

<> /home/sukul1 $ cat rej

SHANE|WARNE|yy

BHANU|SINGH|xxxxx

<> /home/sukul1 $ cat exp

COPY: Input record 2 has been rejected (Invalid numeric format 'yy' for column 3 (DAILYWAGE)).  Please see /home/sukul1/rej, record 1 for the rejected record. This record was read from empdet_1.dat

COPY: Input record 5 has been rejected (Invalid numeric format 'xxxxx' for column 3 (DAILYWAGE)).  Please see /home/sukul1/rej, record 2 for the rejected record. This record was read from empdet.dat

Note that the function get_num_rejected_rows() can be used only with multinode copy.

Does not work with copy local.