We are using cli bulk loader to load data into db2. In some cases the rows
get rejected and this is flagged as a warning
allowing the program to continue.

Is there any way to trap this warning, I read the use of "EXCEPTION TABLE"
but that is limited to only unique index
violations and not constraint related errors.
How do we handle or capture the rejected records in an elegant way?

Thanks in advance,
Sumanth

I don't know how "elegant" you want the capture to be but an easy way is
to pipe the output of LOAD to a file and parse the errors out of it.
These errors refer to records in a file which are not something handled
in the database. Most of the errors I've seen like this are formatting
errors - alphabetic data in a numeric column. A frequent cause of this
is a character column that contains an apostrophe - the same character
used as a delimiter for character data.

Constraint related errors are a different issue. A table that contains
constraints that is the target of a LOAD operation will be placed in
"check pending" state. You need to run the SET INTEGRITY command against
the table to locate and handle these errors. Set integrity has an option
to delete the rows failing constraint checks and move them to a work
table. There's no reason this couldn't be the same table that you used
for the "exception table" during LOAD processing.

Unfortunately, when there are multiple check constraints, there's no way
to tell which one caused the row to be deleted from the LOAD target
table. I usually try to scrub data before loading rather than have LOAD
catch all of the errors.


Phil Sherman



Sumanth wrote:
> We are using cli bulk loader to load data into db2. In some cases the rows
> get rejected and this is flagged as a warning
> allowing the program to continue.
>
> Is there any way to trap this warning, I read the use of "EXCEPTION TABLE"
> but that is limited to only unique index
> violations and not constraint related errors.
> How do we handle or capture the rejected records in an elegant way?
>
> Thanks in advance,
> Sumanth
>
>