Informix DBA Survival Guide, Second Edition

Using The Violations Tables

Problem: Using During Loads

I'd like to be able to use dbaccess to load from a flatfile, but it may have duplicate records. I know I can use dbload and get around this, but is there any way I can have the "load" command work the same way?

Answer: Start Violations

Beginning with IDS 7.1 you can use the violations table facility to do this. Run the following SQL from dbaccess:

START VIOLATIONS TABLE FOR tablename;
SET INDEXES, CONSTRAINTS FOR tablename FILTERING WITHOUT ERROR;
LOAD FROM "tablename.unl" INSERT INTO tablename;
STOP VIOLATIONS TABLE FOR tablename;

This will create two tables in your database, tablename_vio and tablename_dia. The rows that violate the constraints or indexes will go into tablename_vio, ...