ignore duplicate errors on load from statements

[noober]

Hello all. I get this file full of new membership numbers abiout once a month that I have to "load from * insert into" a table. I get this file from a user whose attention to detail leaves something to be desired, and so I almost always end up with two or three member numbers from the month before intermingled with 1000's of other numbers. This of course makes my load/insert statement choke and sends me searching for the dupes, commenting out the numbers that did insert, etc...

Is there anyway to do something similar to VBs "on error resume next" with dbaccess? It would be nice if the query engine just through these bad numbers out for my review later and kept on going. I did try to set the violations table, but I dont know if I got the syntax wrong or I am just having trouble understanding IBMs manual.

Please help...this is making me pre-postal.

 

[vpshriyan]

Hi,

You may use dbload utility to accomplish this job. dbload transfers data from text files to base tables along with constraint checks, loggings, transaction batches etc.

If duplicate rows are your primary concern and you want to trap those, you have to enforce or create primary key or unique index in a target table. dbload will uphold this constraint at the time of data insert. The bad row will be diverted to a log file along with diagnostic messages.

Syntax and major options:
dbload [-d dbname] [-c cfilname] [-l logfile] [-e errnum] [-n nnum][-r | -k]
-d database name
-c command file name
-l bad row(s) log file
-e bad row(s) # before abort
-n # of row(s) before commit
-r loading without locking table
-k loading with exclusive lock on table(s)

Command file layout:
FILE 'datafile' DELIMITER '|' num_of_columns;
insert into table_name ;

dbload example:
dbload -d testdb -c cust.cmd -l cust.err -e 50 -n 100 -k

command file example:
FILE 'cust.dat' DELIMITER '|' 8;
insert into customer ;

Regards,
Shriyan
"When fate hands us a lemon, let's try to make lemonade."