RE: Disable Constraint

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