Issue about constraint data checking
Alexandre asked whether Informix has an equivalent of Oracle's "ENABLE NOVALIDATE" to add constraints without validating existing data. Art Kagel said constraint checking can't be skipped; others suggested SET CONSTRAINTS ALL DEFERRED within a transaction, or adding the constraint with the DISABLED keyword (then cleaning up offending rows and re-enabling). The real goal was migrating/replicating large tables with foreign keys from 11.50 to 11.70 on another architecture; Madison Pruet recommended creating replicates via a template and using 'cdr check' with repair (which honours RI ordering) rather than sync, or loading via HPL/external tables with constraints added afterwards. No single confirmed outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi my friends. Informix 11.70.FC4X4, on a linux_64 machine. I am facing some troubles here regarding constraint creations, already looked for something that could tell informix "not to do the check, and really trust my data is ok". In oracle we have the option "ENABLE NOVALIDATE" to ignore the constraint checking during creation, is there any similar way to do it in Informix? (obs: I´ve already think about disabling constraints before loading data, and enabling them after, but informix would just postpone the data checking job, right?) Thanks, anyway.
There is no way to disable the constraint checking. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jan 5, 2012 at 12:17 PM, ALEXANDRE MARINI <alexandre@briug.org>wrote: > Hi my friends. > Informix 11.70.FC4X4, on a linux_64 machine. > I am facing some troubles here regarding constraint creations, already > looked > for something that could tell informix "not to do the check, and really > trust > my data is ok". > In oracle we have the option "ENABLE NOVALIDATE" to ignore the constraint > checking during creation, is there any similar way to do it in Informix? > (obs: Ie already think about disabling constraints before loading data, > and > enabling them after, but informix would just postpone the data checking > job, > right?) > > Thanks, anyway. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e897408758004b5cb2986
Alexandre: Try this: Begin work; Set constraints all deferred; <Your SQL DML code> Commit work; You gonna disable the constraints only at the transaction scope. After commit, your constraints will enable automatically. Regards, Alberto. -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Art Kagel Enviada em: quinta-feira, 5 de janeiro de 2012 15:21 Para: ids@iiug.org Assunto: Re: Issue about constraint data checking [25823] There is no way to disable the constraint checking. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jan 5, 2012 at 12:17 PM, ALEXANDRE MARINI <alexandre@briug.org>wrote: > Hi my friends. > Informix 11.70.FC4X4, on a linux_64 machine. > I am facing some troubles here regarding constraint creations, already > looked > for something that could tell informix "not to do the check, and really > trust > my data is ok". > In oracle we have the option "ENABLE NOVALIDATE" to ignore the constraint > checking during creation, is there any similar way to do it in Informix? > (obs: Ie already think about disabling constraints before loading data, > and > enabling them after, but informix would just postpone the data checking > job, > right?) > > Thanks, anyway. > > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e897408758004b5cb2986 ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
Page 2-101 of the manual (ids_sqs_bookmap.pdf) says:
"The new constraint is enabled by default. To add a constraint that is not
enabled,
you can include the DISABLED keyword after the name of the constraint:
ALTER TABLE customerADD CONSTRAINT UNIQUE (lname, fname) CONSTRAINT u_cust DISABLED;
Before you perform subsequent DML operations in which you want the
constraint
to be enforced. you can use the SET Database Object Mode statement to enable
the
disabled constraint. "
You may just want to create the index (detached) first and the index on the
parent table column.
Then write a script to query for rows that would break the constraint (
"where not in (select blah) into temp t" ) .
Then fix them or delete them as appropriate by selecting from t;
Then add the constraint without the DISABLED keyword.
-----Original Message-----
From: ALEXANDRE MARINI
Sent: Thursday, January 05, 2012 11:17 AM
To: ids@iiug.org
Subject: Issue about constraint data checking [25821]
Hi my friends.
Informix 11.70.FC4X4, on a linux_64 machine.
I am facing some troubles here regarding constraint creations, already
looked
for something that could tell informix "not to do the check, and really
trust
my data is ok".
In oracle we have the option "ENABLE NOVALIDATE" to ignore the constraint
checking during creation, is there any similar way to do it in Informix?
(obs: I´ve already think about disabling constraints before loading data,
and
enabling them after, but informix would just postpone the data checking job,
right?)
Thanks, anyway.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
We NEVER trust the user :) I understand this can be a limitation, but you'd be amazed if I told you the number of times I saw Oracle DBA laughing at "users" because they shoot themselves in the foot (used novalidate, messed up, and voilá - You have data you'll never find without using special clauses to force full scans). Although personally I see value in the feature, I'd say it can do more harm than good... In any case, what are you trying to do? Why do you need to avoid the constraints? Are they causing bad performance? I had a similar need in the past while working with fragmented tables... An attachment needs to validate that the table you're attaching only contains rows that follow the expression... But that's a case when a constraint will help... (it can consume a bit of time creating that constraint, but usually the idea is to minimize the attach time) Regards. On Thu, Jan 5, 2012 at 5:17 PM, ALEXANDRE MARINI <alexandre@briug.org>wrote: > Hi my friends. > Informix 11.70.FC4X4, on a linux_64 machine. > I am facing some troubles here regarding constraint creations, already > looked > for something that could tell informix "not to do the check, and really > trust > my data is ok". > In oracle we have the option "ENABLE NOVALIDATE" to ignore the constraint > checking during creation, is there any similar way to do it in Informix? > (obs: Ie already think about disabling constraints before loading data, > and > enabling them after, but informix would just postpone the data checking > job, > right?) > > Thanks, anyway. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf303b40ff48109f04b5cf39d1
Hello Fernando! I see your points, of course. Let me explain: We will replicate some very huge tables from one arch to another, and from 11.50 to 11.70. That should be done with minimum downtime, of course. We are trying to do it via ER (just simple, uhn?) but almost all of the source tables have foreign keys, and we have a very low bandwidth here. I could include all my tables with constraints into one or more replicateset(s), using row scope, start the sync process, and let Informix do the job (and mainly don´t worry about my constraints? Would that be the fastest way to do it? I heard about creating one replicate (for each table - duh), and do one sync at a time, but disabling the constraints. The only (and biggest) problem would be creating (or activating) these constraints, later, these are heavily used tables. Thanks Fernando for your explanations. Best Regards!
You can define replicates on all of the tables with a single create template command and then realize that template on both systems. Then instead of doing a sync, do a cdr check with repair. The reason is tha= t 1) cdr check automatically orders the operation using RI rules. 2) If th= ere are recursive RI rules in place, cdr check will automatically switch to= a vertical set mode of operation so that the entire RI complex for that r= ow is replicated as a unit. The fastest way would be to take a backup on the source and restore on = the target. Then upgrade the system. If changing systems (i.e. HP to Linu= x), then you would need to copy the data using either HPL or external table= s. I'd initially not have the RI rules on the target system in that case because your data extraction won't be consistent. Then I'd reestablish= the constraints after the data was synchronized. = From: "ALEXANDRE MARINI" <alexandre@briug.org> = = To: ids@iiug.org = = Date: 01/06/2012 10:16 AM = = Subject: Re: Issue about constraint data checking [25839] = = Sent by: ids-bounces@iiug.org = = Hello Fernando! I see your points, of course. Let me explain: We will replicate some very huge tables from one arch to another, and f= rom 11.50 to 11.70. That should be done with minimum downtime, of course. We are trying to do it via ER (just simple, uhn?) but almost all of the= source tables have foreign keys, and we have a very low bandwidth here. I could include all my tables with constraints into one or more replicateset(s), using row scope, start the sync process, and let Infor= mix do the job (and mainly don=B4t worry about my constraints? Would that be the fastest way to do it? I heard about creating one replicate (for each table - duh), and do one= sync at a time, but disabling the constraints. The only (and biggest) problem would be creating (or activating) these constraints, later, these are heavily used tables. Thanks Fernando for your explanations. Best Regards! ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =