RE: Disable Constraint
I used the correct method, I don't do hacks through the catalog, I just wanted to know what happens when executing SET CONSTRAINT <name> DISABLED Regards Colin There are 10 types of people in the world, those that understand binary and those that don't >From: "Urich Ann" <Uricha@mcao.maricopa.gov> >To: "Colin Dawson" <cjd_1955@hotmail.com>, <informix-list@iiug.org> >Subject: RE: Disable Constraint >Date: Thu, 14 Dec 2006 09:04:37 -0700 > >To your question.... Are you loading a table? >Updating/Inserting/Deleting from a table? > Are you asking if modifying the sysobjstate table, will Infmx >DYNAMICALLY disable the constraint 'after' a transaction has already >begun? > or ... > Are you asking if that is ALL you need to do to the CATALOG >tables (to disable constraints) is to modify one table via changing the >'state' column in sysobjstate from 'E' to 'D' with the 'objtype' of 'C' >? >(Note: When I run the 'SET CONSTRAINTS ....DISABLE' command it does >change the 'E' to a 'D' in the sysobjstate table. But, I don't know if >running the 'SET CONSTRAINTS...' command does anything ELSE to any other >catalog table.) > >*** I would use the 'SET CONSTRAINTS...' command to see if it works >better then modifying the catalog table. I believe the default mode >using this command is IMMEDIATEly... vs DEFFERED until a commit.*** > >Personally, I disable constraints when I load a series of tables and >don't want to worry about loading them in the "right" order >(parent/child). >I run: > SET CONSTRIANTS primarykeyorwhateverconstraintnamehere DISABLED. >... and this allows me to load the table without the constraints being >'checked'. > >Then after I am done loading I want to put the constraints back.... >I run: > SET CONSTRIANTS primarykeyorwhateverconstraintnamehere ENABLED. > >Note: The IBM IDS V10 manual says: > >Use the SET CONSTRAINTS statements to specify how some or all of the >constraints on a table are processed. Constraint-mode options include >these: >Whether constraints are checked at the statement level (IMMEDIATE) or at >the transaction level (DEFERRED) >Whether to enable or disable constraints >Whether to change the filtering mode of constraints. > >Use the SET Transaction Mode statement to specify whether constraints >are checked at the statement level or at the transaction level during >the current transaction. > >Use the SET Database Object Mode statement to change the filtering mode >of constraints of unique indexes, or to enable or disable constraints, >indexes, and triggers. > >http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/co >m.ibm.sqls.doc/sqls725.htm >-----Original Message----- >From: informix-list-bounces@iiug.org >[mailto:informix-list-bounces@iiug.org] On Behalf Of Colin Dawson >Sent: Thursday, December 14, 2006 7:29 AM >To: informix-list@iiug.org >Subject: Disable Constraint > > >What does Informix do when you disable a constraint? > >I'm running one on a 188M row table and it is doing a lot of reads (over >1M >so far) I though all it had to do was change the state column in >sysobjstate >to 'D' > > > > >Regards > >Colin > >There are 10 types of people in the world, those that understand binary >and >those that don't > >_________________________________________________________________ >Be the first to hear what's new at MSN - sign up to our free >newsletters! >http://www.msn.co.uk/newsletters > >_______________________________________________ >Informix-list mailing list >Informix-list@iiug.org >http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters