script etiquette -load-unload

Greetings: Background: I inherited a school system database that uses HPUX/Informix. We have many customized scripts to collect and export data to the state. I have noticed a pattern in my predecessors scripts: Instead of a single unload to file, they use multiple unload /load / unload/ loads, ... and so I am wondering if I missed something in school. Typically I see: 1. UNLOAD to file1, selecting just a list of IDs. 2. LOAD file1 back into an informix temptable. 3. UNLOAD from temptable to file2, joining additional columns to the IDs 4. LOAD file2 into the target informix table. Problem: I'm worried that somewhere I missed something in my education, because if presented with this problem, I would have just done a single INSERT statement to move the IDs, along with the additional JOINED information directly into the target table. It is very thorough, but makes for difficult troubleshooting, and hard to make changes, because there are so many details to keep straight. Is there a reason I should follow the lead of my predecessors, or can I clean up the code and just make it simple? Many thanks to anyone who takes time to consider this for me, I appreciate it! Peter

Maybe to get around long TXs issues Cheers Paul > Greetings: > > Background: > I inherited a school system database that uses HPUX/Informix. > We have many customized scripts to collect and export data to the state. > > I have noticed a pattern in my predecessors scripts: > Instead of a single unload to file, they use multiple unload /load / > unload/ > loads, ... and so I am wondering if I missed something in school. > > Typically I see: > 1. UNLOAD to file1, selecting just a list of IDs. > 2. LOAD file1 back into an informix temptable. > 3. UNLOAD from temptable to file2, joining additional columns to the IDs > 4. LOAD file2 into the target informix table. > > Problem: > I'm worried that somewhere I missed something in my education, because if > presented with this problem, I would have just done a single INSERT > statement > to move the IDs, along with the additional JOINED information directly > into > the target table. > > It is very thorough, but makes for difficult troubleshooting, and hard to > make > changes, because there are so many details to keep straight. > > Is there a reason I should follow the lead of my predecessors, or can I > clean > up the code and just make it simple? > > Many thanks to anyone who takes time to consider this for me, I appreciate > it! > > Peter > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid.

I would clean it up. Test it thoroughly in a test environment before = pulling the plug on the old one. j. On Mar 1, 2012, at 11:43 AM, PETER JOHNSON wrote: > Greetings:=20 >=20 > Background:=20 > I inherited a school system database that uses HPUX/Informix.=20 > We have many customized scripts to collect and export data to the = state.=20 >=20 > I have noticed a pattern in my predecessors scripts:=20 > Instead of a single unload to file, they use multiple unload /load / = unload/=20 > loads, ... and so I am wondering if I missed something in school.=20 >=20 > Typically I see:=20 > 1. UNLOAD to file1, selecting just a list of IDs.=20 > 2. LOAD file1 back into an informix temptable.=20 > 3. UNLOAD from temptable to file2, joining additional columns to the = IDs=20 > 4. LOAD file2 into the target informix table.=20 >=20 > Problem:=20 > I'm worried that somewhere I missed something in my education, because = if=20 > presented with this problem, I would have just done a single INSERT = statement=20 > to move the IDs, along with the additional JOINED information directly = into=20 > the target table.=20 >=20 > It is very thorough, but makes for difficult troubleshooting, and hard = to make=20 > changes, because there are so many details to keep straight.=20 >=20 > Is there a reason I should follow the lead of my predecessors, or can = I clean=20 > up the code and just make it simple?=20 >=20 > Many thanks to anyone who takes time to consider this for me, I = appreciate it!=20 >=20 > Peter=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

And if the old one is not broke why bother fixing it Cheers Paul > I would clean it up. Test it thoroughly in a test environment before = > pulling the plug on the old one. > > j. > On Mar 1, 2012, at 11:43 AM, PETER JOHNSON wrote: > >> Greetings:=20 >>=20 >> Background:=20 >> I inherited a school system database that uses HPUX/Informix.=20 >> We have many customized scripts to collect and export data to the = > state.=20 >>=20 >> I have noticed a pattern in my predecessors scripts:=20 >> Instead of a single unload to file, they use multiple unload /load / = > unload/=20 >> loads, ... and so I am wondering if I missed something in school.=20 >>=20 >> Typically I see:=20 >> 1. UNLOAD to file1, selecting just a list of IDs.=20 >> 2. LOAD file1 back into an informix temptable.=20 >> 3. UNLOAD from temptable to file2, joining additional columns to the = > IDs=20 >> 4. LOAD file2 into the target informix table.=20 >>=20 >> Problem:=20 >> I'm worried that somewhere I missed something in my education, because = > if=20 >> presented with this problem, I would have just done a single INSERT = > statement=20 >> to move the IDs, along with the additional JOINED information directly = > into=20 >> the target table.=20 >>=20 >> It is very thorough, but makes for difficult troubleshooting, and hard = > to make=20 >> changes, because there are so many details to keep straight.=20 >>=20 >> Is there a reason I should follow the lead of my predecessors, or can = > I clean=20 >> up the code and just make it simple?=20 >>=20 >> Many thanks to anyone who takes time to consider this for me, I = > appreciate it!=20 >>=20 >> Peter=20 >>=20 >>=20 >> = > **************************************************************************= > *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > >>=20 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid.

Then they would have to be using dbload instead of load. Is that the case? j. On Mar 1, 2012, at 11:47 AM, Paul Watson wrote: > Maybe to get around long TXs issues=20 >=20 > Cheers=20 > Paul=20 >=20 >> Greetings:=20 >>=20 >> Background:=20 >> I inherited a school system database that uses HPUX/Informix.=20 >> We have many customized scripts to collect and export data to the = state.=20 >>=20 >> I have noticed a pattern in my predecessors scripts:=20 >> Instead of a single unload to file, they use multiple unload /load /=20= >> unload/=20 >> loads, ... and so I am wondering if I missed something in school.=20 >>=20 >> Typically I see:=20 >> 1. UNLOAD to file1, selecting just a list of IDs.=20 >> 2. LOAD file1 back into an informix temptable.=20 >> 3. UNLOAD from temptable to file2, joining additional columns to the = IDs=20 >> 4. LOAD file2 into the target informix table.=20 >>=20 >> Problem:=20 >> I'm worried that somewhere I missed something in my education, = because if=20 >> presented with this problem, I would have just done a single INSERT=20= >> statement=20 >> to move the IDs, along with the additional JOINED information = directly=20 >> into=20 >> the target table.=20 >>=20 >> It is very thorough, but makes for difficult troubleshooting, and = hard to=20 >> make=20 >> changes, because there are so many details to keep straight.=20 >>=20 >> Is there a reason I should follow the lead of my predecessors, or can = I=20 >> clean=20 >> up the code and just make it simple?=20 >>=20 >> Many thanks to anyone who takes time to consider this for me, I = appreciate=20 >> it!=20 >>=20 >> Peter=20 >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >=20 > --=20 > Paul Watson=20 > Tel: +1 913-674-0360=20 > Mob: +1 913-387-7529=20 > Web: www.oninit.com=20 >=20 > www.advancedatatools.com=20 >=20 > Failure is not as frightening as regret.=20 > If you want to improve, be content to be thought foolish and stupid.=20= >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

There are temp tables with no log to get around long tx problems. Either way, at the last step they load into a standard table. So this should not be the cause. But for the initial question: Maybe sombody wanted to have a sort of log what the scripts did in the filesystem for some reason. But I would keep the log in the database in a separate table or mark the records which were transferred instead of producing unload files. Marcus ----- Ursprüngliche Mail ----- Von: "Paul Watson" <paul@oninit.com> An: ids@iiug.org Gesendet: Donnerstag, 1. März 2012 17:47:28 Betreff: Re: script etiquette -load-unload [26421] Maybe to get around long TXs issues Cheers Paul > Greetings: > > Background: > I inherited a school system database that uses HPUX/Informix. > We have many customized scripts to collect and export data to the state. > > I have noticed a pattern in my predecessors scripts: > Instead of a single unload to file, they use multiple unload /load / > unload/ > loads, ... and so I am wondering if I missed something in school. > > Typically I see: > 1. UNLOAD to file1, selecting just a list of IDs. > 2. LOAD file1 back into an informix temptable. > 3. UNLOAD from temptable to file2, joining additional columns to the IDs > 4. LOAD file2 into the target informix table. > > Problem: > I'm worried that somewhere I missed something in my education, because if > presented with this problem, I would have just done a single INSERT > statement > to move the IDs, along with the additional JOINED information directly > into > the target table. > > It is very thorough, but makes for difficult troubleshooting, and hard to > make > changes, because there are so many details to keep straight. > > Is there a reason I should follow the lead of my predecessors, or can I > clean > up the code and just make it simple? > > Many thanks to anyone who takes time to consider this for me, I appreciate > it! > > Peter > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

So, I would make three alternate suggestions, all better than what your predecessor left you: 1. Your idea to just make the join select and insert the results directly into the target table. If the tables are resident on the same server this is likely to be the fastest option (but not necessarily - if the join is complex of filter columns are not indexed). 2. Make the initial select of keys into a temp table directly (skip the unload/load) then make the second select directly into the target table. If the joins are complex or join or filter columns are not indexed, this may be fastest. 3. If you are using Informix 11.50 or later, replace the unload/load to files using dbaccess with inserts into external tables directly from the server followed by selects from the external tables. External tables are VERY fast, much faster than the dbaccess UNLOAD verb because data never leaves the server to go to dbaccess, and if you create the external tables in 'INFORMIX' mode, the files are binary so smaller than an unload file, and you eliminate the overhead of converting data to text and back to internal format. 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, Mar 1, 2012 at 11:43 AM, PETER JOHNSON <pjohnson@fwps.org> wrote: > Greetings: > > Background: > I inherited a school system database that uses HPUX/Informix. > We have many customized scripts to collect and export data to the state. > > I have noticed a pattern in my predecessors scripts: > Instead of a single unload to file, they use multiple unload /load / > unload/ > loads, ... and so I am wondering if I missed something in school. > > Typically I see: > 1. UNLOAD to file1, selecting just a list of IDs. > 2. LOAD file1 back into an informix temptable. > 3. UNLOAD from temptable to file2, joining additional columns to the IDs > 4. LOAD file2 into the target informix table. > > Problem: > I'm worried that somewhere I missed something in my education, because if > presented with this problem, I would have just done a single INSERT > statement > to move the IDs, along with the additional JOINED information directly into > the target table. > > It is very thorough, but makes for difficult troubleshooting, and hard to > make > changes, because there are so many details to keep straight. > > Is there a reason I should follow the lead of my predecessors, or can I > clean > up the code and just make it simple? > > Many thanks to anyone who takes time to consider this for me, I appreciate > it! > > Peter > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba9315b060304ba333035

Well THANK YOU, to everyone who responded. To those of you who said "if it is not broke, don't fix it": I agree, but... I need to make new scripts, and so do I need to follow the older examples for a reason?, OR, can I take a shorter path that makes more sense to me and uses fewer steps and fewer temp files, which I find hard to keep track of? What is the measure of a better solution? that's probably a too loaded question to be fair. After thinking about it some more, it might have been seen as a solution in face of not being allowed to do a JOIN in an INSERT statement - the script is running on an older IDS 9.* box. (Joins are not allowed in an INSERT, correct?) Well, from all your replies, I don't hear any major concerns - or a reason why unload / loads are necessary or considered 'proper'. so I will backup, test, and proceed! Peter A.R. Johnson

Thanks Art for your lengthy reply, I really appreciate it. It is comforting to know that at least someone agrees that I'm headed in a reasonable direction. Peter

You CAN do a join in a SELECT that is being used by an insert to acquire data, even in 9.xx or 7.xx for that matter. So: insert into targetselect a.*, b.* from a, b; is legal. 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 Fri, Mar 2, 2012 at 12:05 PM, PETER JOHNSON <pjohnson@fwps.org> wrote: > Well THANK YOU, to everyone who responded. > > To those of you who said "if it is not broke, don't fix it": > I agree, but... > I need to make new scripts, and so do I need to follow the older examples > for > a reason?, OR, can I take a shorter path that makes more sense to me and > uses > fewer steps and fewer temp files, which I find hard to keep track of? > > What is the measure of a better solution? that's probably a too loaded > question to be fair. > > After thinking about it some more, it might have been seen as a solution in > face of not being allowed to do a JOIN in an INSERT statement - the script > is > running on an older IDS 9.* box. (Joins are not allowed in an INSERT, > correct?) > > Well, from all your replies, I don't hear any major concerns - or a reason > why > unload / loads are necessary or considered 'proper'. > > so I will backup, test, and proceed! > > Peter A.R. Johnson > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f646abf3469a404ba45df03

oops! that's embarassing.... my 'grew up MSSQL, too young to know old-school' is showing. you're right. I'm just not as comfortable with WHERE clause joins, I've never used them as much.