Get rocords rejected during an import sentence.

[Caesar001]

Hi folks!

I'm inserting recods into DB2 table from a text file with the IMPORT COMMAND (the file is a coma delimiter).
Example:
ID1,value1, value2
ID2,value1, value2
ID3,value1, value2
ID4,value1, value2

I using something like this:
import from file_name of del insert into table_name (field1, field2, field3)

My question is:
How can I get the recods rejected at the end import command?.
I want to do a report for my user in order to say:
There were some records rejected.
A show a list with the recods rejected.
ID1,value1, value2
ID3,value1, value2


Thanks in advance.

Cesar

 

[gregsimpson]

Cesar,

IMPORT
First, the table to which you want to add data should exist. So create it first, if you do not have it already. Prepare a file with the "tuples" that you want to import. For a file in "DEL" format, each line in the file is a tuple, and the attribute values are separated (delimited) by commas (","'s). The file is just an ASCII file.

import from file.del of del
messages import.msg
insert into table;


This imports the "tuples" from the file named file.del into the table named table. The import utility will attempt to insert each line from file.del into table just as if you had issued an insert command yourself (formatted correctly, of course). Note that the order of your attribute values on each line of file.del must be the same as the "order" of the attributes in the table. (The describe command will show you the "order" of your table's attributes. This is the same order as you specified them in your create command when you made the table.)
Of course, some of the inserts may succeed, others may fail. (Why?) Whatever the import utility accomplishes is recorded in the message file import.msg for you perusal.

You can replace "insert" in the command above with insert_update. Then any new tuple that clashes with a tuple already in the table will replace ("update&quot the older tuple. (How can two tuples clash? They have the same primary key values.) To use this option, your table must exist and have a primary key declared. And, of course, you must have update privilege on the table. Likewise, you can replace "insert" with replace. This first wipes all tuples from the table, and then adds the "tuples" from the file.

This link has more.

http://www.cs.yorku.ca/~seela/db2/import.html

Cheers
Greg

 

[Caesar001]

I have read yor information, but, it does not mention about my question.. (How can I get the rejected records ?).
In the log file defined in "messages" only you can find the error and the number record.
Can DB2 generate a file with the records rejected?.

Thanks in advance.

 

[gregsimpson]

Cesar,

you need to use "For Exception" and specify a table to write the error records to. I believe the table needs to be defined first.

Here's an example using load, but the concept is the same.

http://www.informatik.uni-stuttgart.de/ipvr/as/lehre/ausbildung/ibm_db2/Part3/exercise3_2.html

Cheers
Greg