Disable Validation by creating constraings??

Hi, I'm new to Informix database. We port just now our application to informix. I discovered that Informix do validate the created constrains against the database contents. this can take several hours, when the database is not empty or has a huge amount of data. But I need a mechanism to disable the validation. In Oracle I use the "NOVALIDATE" option, is there a similar option can by used by informix? thanks in advance khamis

Khamis wrote: > Hi, > > I'm new to Informix database. We port just now our application to > informix. > I discovered that Informix do validate the created constrains against > the database contents. this can take several hours, when the database > is not empty or has a huge amount of data. > > But I need a mechanism to disable the validation. In Oracle I use the > "NOVALIDATE" option, is there a similar option can by used by > informix? SET <constraintname> DISABLED; -- "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche

khamis@web.de (Khamis) wrote in message news:<c3708908.0401190308.68b1e124@posting.google.com>... > Hi, > > I'm new to Informix database. We port just now our application to > informix. > I discovered that Informix do validate the created constrains against > the database contents. this can take several hours, when the database > is not empty or has a huge amount of data. > > But I need a mechanism to disable the validation. In Oracle I use the > "NOVALIDATE" option, is there a similar option can by used by > informix? > > thanks in advance > khamis Perhaps I was not clear enough, my gool is actually to not let the database validate existing data. this is achieved by oracle by using the "NOVALIDATE" option by creating the constraint (ALTER TABLE table_name ADD CONSTRAINT cont_name FOREIGN KEY (column_name) REFERENCES foreign_table(column_name) ENABLE NOVALIDATE) , in SQLServer the option "WITH NOCHECK" does the job. The constraint validation should actually only run for new data. but I begin to see, that this is not possible in informix. for me this sounds like a big problem, because we have automated our updates into a tool, that first removes the constrains and recreate those after the update is run. The process takes actually some few minutes. It also anchors, that new contraints are created and older are removed. Or the problem in other words, if I do "SET CONTRAINTS FOR table_name ENABLED", does informix check the whole contents of the table again??? best regards khamis

Khamis wrote: > khamis@web.de (Khamis) wrote in message > news:<c3708908.0401190308.68b1e124@posting.google.com>... >> Hi, >> >> I'm new to Informix database. We port just now our application to >> informix. >> I discovered that Informix do validate the created constrains against >> the database contents. this can take several hours, when the database >> is not empty or has a huge amount of data. >> >> But I need a mechanism to disable the validation. In Oracle I use the >> "NOVALIDATE" option, is there a similar option can by used by >> informix? >> >> thanks in advance >> khamis > > Perhaps I was not clear enough, > my gool is actually to not let the database validate existing data. > this is achieved by oracle by using the "NOVALIDATE" option by > creating the constraint (ALTER TABLE table_name ADD CONSTRAINT > cont_name FOREIGN KEY (column_name) REFERENCES > foreign_table(column_name) ENABLE NOVALIDATE) > , in SQLServer the option "WITH NOCHECK" does the job. > > The constraint validation should actually only run for new data. but I > begin to see, that this is not possible in informix. for me this > sounds like a big problem, because we have automated our updates into > a tool, that first removes the constrains and recreate those after the > update is run. The process takes actually some few minutes. It also > anchors, that new contraints are created and older are removed. > > Or the problem in other words, if I do "SET CONTRAINTS FOR table_name > ENABLED", does informix check the whole contents of the table again??? Yes it . You can improve the performance of this by doing the following: 1. Fragment the data across multiple dbspaces 2. Enable PDQPRIORITY 3. Enable the parallel sort package (PSORT_NPROCS and PSORT_DBTEMP) -- "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche