Syntax Help
Michelle needed to update a table surrounded by many referential constraints; disabling constraints was too slow on a huge database, so she was writing a procedure to update child tables first and asked whether a FOREACH loop can iterate a list backwards. Respondents never answered the loop question directly, instead recommending deferred constraint checking: BEGIN WORK; SET CONSTRAINTS [name|ALL] DEFERRED; do the updates in any order; COMMIT WORK, so checks happen at commit time. The rest of the thread was bickering over how long Informix has supported deferred constraints; no confirmation from the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I don't know if this is the place to ask a question, but i saw several others pop up on a google search so I thought it might be okay. A simple 'this is not the place' will be enough for me to remove it. Explanation: I need to write a procedure to change data in a table that branches off into a whole mess of referential constraints. At first I tried just turning off the constraints and changing all the affected tables but due to the tremendous size of the database it was taking way too long. Anyhow, the new method involves changing the tables who's referential constraints stem the farthest out from the parent table first by using temporary table entries. Question: Is there a way to loop through a List backwards? I'm using foreach to run through the list, but need to access it from the bottom to top.
Sorry to top post, hotmail will mung this... I think it will be easier if you give a little bit of detail. Its never a good idea to disable constraints. (Although that sometimes you have to do it as a last resort.) You need to say more about your database. You said that you need to write a procedure. Did you mean stored procedure or did you mean that you needed to write a script/function that will be used once to clean up the database? I think someone already mentioned using an ORDER BY clause, but that may not be enough. I guess the easiest thing is for you to start from the beginning.... -G > From: shannon_csis@hotmail.com > Subject: Syntax Help > Date: Mon, 13 Oct 2008 16:51:24 -0700 > To: informix-list@iiug.org > > I don't know if this is the place to ask a question, but i saw several > others pop up on a google search so I thought it might be okay. A > simple 'this is not the place' will be enough for me to remove it. > > Explanation: I need to write a procedure to change data in a table > that branches off into a whole mess of referential constraints. At > first I tried just turning off the constraints and changing all the > affected tables but due to the tremendous size of the database it was > taking way too long. Anyhow, the new method involves changing the > tables who's referential constraints stem the farthest out from the > parent table first by using temporary table entries. > > Question: Is there a way to loop through a List backwards? I'm using > foreach to run through the list, but need to access it from the bottom > to > top. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ See how Windows Mobile brings your life together—at home, work, or on the go. http://clk.atdmt.com/MRT/go/msnnkwxp1020093182mrt/direct/01/
Michelle wrote: > I don't know if this is the place to ask a question, but i saw several > others pop up on a google search so I thought it might be okay. A > simple 'this is not the place' will be enough for me to remove it. > > Explanation: I need to write a procedure to change data in a table > that branches off into a whole mess of referential constraints. At > first I tried just turning off the constraints and changing all the > affected tables but due to the tremendous size of the database it was > taking way too long. Anyhow, the new method involves changing the > tables who's referential constraints stem the farthest out from the > parent table first by using temporary table entries. > > Question: Is there a way to loop through a List backwards? I'm using > foreach to run through the list, but need to access it from the bottom > to > top. Wish you'd posted this 24 hours earlier. I would have used this for my example. <g> With deferrable constraints and MVCC you wouldn't even need a procedure. Just simple DML in any order followed by a commit. SET CONSTRAINTS ALL DEFERRED; UPDATE ... UPDATE ... UPDATE ... SET CONSTRAINTS ALL IMMEDIATE; -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)
Michelle wrote: > I don't know if this is the place to ask a question, but i saw several > others pop up on a google search so I thought it might be okay. A > simple 'this is not the place' will be enough for me to remove it. > > Explanation: I need to write a procedure to change data in a table > that branches off into a whole mess of referential constraints. At > first I tried just turning off the constraints and changing all the > affected tables but due to the tremendous size of the database it was > taking way too long. Anyhow, the new method involves changing the > tables who's referential constraints stem the farthest out from the > parent table first by using temporary table entries. You could try using deferred constraint checking. This enables the constraint checking to be performed at commit time rather that as the rows are being updated. Basically do the following. BEGIN WORK; SET CONSTRAINTS [constraint_name/ALL] DEFERRED; .... COMMIT WORK; I can't remember when this syntax was first supported by IDS, so you'll need to check it out. > > Question: Is there a way to loop through a List backwards? I'm using > foreach to run through the list, but need to access it from the bottom > to > top.
Another marvelous example of Informix knowledge... It has deferred constraints for long, long time.... Congratulations, keep on the useful posting! On Tue, Oct 14, 2008 at 4:50 AM, DA Morgan <damorgan@psoug.org> wrote: > Michelle wrote: >> I don't know if this is the place to ask a question, but i saw several >> others pop up on a google search so I thought it might be okay. A >> simple 'this is not the place' will be enough for me to remove it. >> >> Explanation: I need to write a procedure to change data in a table >> that branches off into a whole mess of referential constraints. At >> first I tried just turning off the constraints and changing all the >> affected tables but due to the tremendous size of the database it was >> taking way too long. Anyhow, the new method involves changing the >> tables who's referential constraints stem the farthest out from the >> parent table first by using temporary table entries. >> >> Question: Is there a way to loop through a List backwards? I'm using >> foreach to run through the list, but need to access it from the bottom >> to >> top. > > Wish you'd posted this 24 hours earlier. I would have used this for my > example. <g> > > With deferrable constraints and MVCC you wouldn't even need a procedure. > Just simple DML in any order followed by a commit. > > SET CONSTRAINTS ALL DEFERRED; > UPDATE ... > UPDATE ... > UPDATE ... > SET CONSTRAINTS ALL IMMEDIATE; > -- > Daniel A. Morgan > University of Washington > damorgan@x.washington.edu (replace x with u to respond) > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
In article <mailman.124.1223983359.874.informix-list@iiug.org>, Fernando Nunes says... > >Another marvelous example of Informix knowledge... >It has deferred constraints for long, long time.... LOL. Daniel Moron is really funny, though not intentionally. I have used deferred constraints since OL 5.0 and I will not be surprised if it was in OL 4.0 itself.
4.0 didn't have foreign keys :P Regards, On Tue, Oct 14, 2008 at 12:54 PM, <dcruncher4@aim.com> wrote: > In article <mailman.124.1223983359.874.informix-list@iiug.org>, Fernando Nunes > says... >> >>Another marvelous example of Informix knowledge... >>It has deferred constraints for long, long time.... > > LOL. Daniel Moron is really funny, though not intentionally. > > I have used deferred constraints since OL 5.0 and I will > not be surprised if it was in OL 4.0 itself. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...