How to know what causes constraint failure during inserting enrties to a table using a DAT file?

Question

I'm inserting multiple entries to a table using a DAT file. How will I know what records are causing constraint failures (eg. duplicate constraint)? I'm using informix dbaccess to load the entries.

Here is a sample code of how I load it:

load from 'table.dat'
insert into table (
    col1,
    col2,
    col3
);

This is the error I'm getting. It shows where from the sql code it fails but it doesn't tell me which specific row failed.

  268: Unique constraint (test.tableconstraint) violated.

  100: ISAM error:  duplicate value for a record with unique key.
Error in line 1
Near character position 0

Answer 1

You have a couple of options:

1. Use DB-Load (dbload) instead of DB-Access to load the data.

   DBLOAD Load Utility           INFORMIX-SQL Version 12.10.FC6
   
   Usage: 
   
   dbload [-d dbname] [-c cfilname] [-l logfile] [-e errnum] [-n nnum]
       [-i inum] [-s] [-p] [-r | -k] [-X]
   
       -d  database name
       -c  command file name
       -l  bad row(s) log file
       -e  bad row(s) # before abort
       -s  syntax error check only
       -n  # of row(s) before commit
       -p  prompt to commit or not on abort
       -i  # of row(s) to ignore before starting
       -r  loading without locking table
       -X  recognize HEX escapes in character fields
       -k  loading with exclusive lock on table(s)
   

2. Use START VIOLATIONS TABLE and STOP VIOLATIONS TABLE. This will record the data that violates constraints in one table and give diagnostic information in another. You can specify the auxilliary table names if you wish, but the default is the base table name with the suffixes _vio and _dia.

Try both — I suspect that violations tables are the better choice, but both work.