Arrays in Informix Stored Procedures
Brian asked whether Informix SPL supports arrays (for keeping running totals by subscript) apart from collection types. Jonathan Leffler answered that standard SPL has no arrays; the usual database approach is a table holding subscript/value rows. He warned that temp tables inside stored procedures cause the SP plan to be re-optimized on every call (which can explain sporadic -211 errors on sysprocplan), and suggested using a permanent table keyed by a session identifier (e.g. session ID or timestamp plus username), cleaned out at session start, to avoid re-optimization — at the cost of managing session keys and cleanup. The rest of the thread is jokes.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I was wondering if it was possible to use arrays in Informix stored procedures or if there was an alternative to them besides collection objects. What I am trying to do is keep a running total for a subscript(item) and the way I have done it before in other languages is as follows: array is X assign initial value: x(subscript) = 1 increment value: x(subscript) = x(subscript) + 1 thanks, Brian
There is no array in standard SPL. Is the subscript a string or an integer or something else? The 'database way' is to do something like create a temp table, and insert rows in there as needed. OTOH, stored procedures and temp tables interact badly - the SP gets reoptimized every time it is used. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "BRIAN HOUSE" | | | <houseb@intelihea| | | lth.com> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 10/02/2003 09:55 | | | AM | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Arrays in Informix Stored Procedures [1977] | >------------------------------------------------------------------------------- --------------------------------------------------------------| I was wondering if it was possible to use arrays in Informix stored procedures or if there was an alternative to them besides collection objects. What I am trying to do is keep a running total for a subscript(item) and the way I have done it before in other languages is as follows: array is X assign initial value: x(subscript) = 1 increment value: x(subscript) = x(subscript) + 1 thanks, Brian
I was happily ignoring this until I read the part where if a temp table is involved, the SP gets optimized every time it is used. So if I have a procedure that selects rows into a temp table for processing, every time that SP is called it will be reoptimized? If I have multiple users executing the same SP, that would explain the sporadic -211 errors I've been getting on sysprocplan. (We do update stats for affected procedures if there is a schema change but sometimes we still see the -211 happening.) What a pain this will be to work around. We do this in more places than I care to think about right now. Jeff -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of Jonathan Le.... Sent: Thursday, October 02, 2003 4:06 PM To: ids@iiug.org Subject: Re: Arrays in Informix Stored Procedures [1981] There is no array in standard SPL. Is the subscript a string or an integer or something else? The 'database way' is to do something like create a temp table, and insert rows in there as needed. OTOH, stored procedures and temp tables interact badly - the SP gets reoptimized every time it is used. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "BRIAN HOUSE" | | | <houseb@intelihea| | | lth.com> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 10/02/2003 09:55 | | | AM | |---------+----------------------------> >--------------------------------------------------------------------------- ------------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Arrays in Informix Stored Procedures [1977] | >--------------------------------------------------------------------------- ------------------------------------------------------------------| I was wondering if it was possible to use arrays in Informix stored procedures or if there was an alternative to them besides collection objects. What I am trying to do is keep a running total for a subscript(item) and the way I have done it before in other languages is as follows: array is X assign initial value: x(subscript) = 1 increment value: x(subscript) = x(subscript) + 1 thanks, Brian
And the way to do it without using temp tables is to use permanent tables :-) Only, now you have to worry about how to identify rows from different sessions, and how to clean up after the session (especially if sessions ever abort). You could consider using a session ID, or a session time stamp plus user name, as the unique identifying columns (along with the subscript). When a session starts, it can clean out its bit of the table - just in case there is data left over from a previous use of the session ID (not material if the key is time stamp plus user name), and then do its stuff on that basis. Because the table is not temporary, the SPL does not get continuously reoptimized. But handling the session identifier is messier. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------------> | | "Jeff Glenn" | | | <jglenn@entertainmentpa| | | rtners.com> | | | | | | 10/02/2003 05:12 PM | | | Please respond to | | | jglenn | |---------+----------------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: Jonathan Leffler/Menlo Park/IBM@IBMUS, <ids@iiug.org> | | cc: | | Subject: RE: Arrays in Informix Stored Procedures [1981] | >------------------------------------------------------------------------------- --------------------------------------------------------------| I was happily ignoring this until I read the part where if a temp table is involved, the SP gets optimized every time it is used. So if I have a procedure that selects rows into a temp table for processing, every time that SP is called it will be reoptimized? If I have multiple users executing the same SP, that would explain the sporadic -211 errors I've been getting on sysprocplan. (We do update stats for affected procedures if there is a schema change but sometimes we still see the -211 happening.) What a pain this will be to work around. We do this in more places than I care to think about right now. Jeff -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of Jonathan Le.... Sent: Thursday, October 02, 2003 4:06 PM To: ids@iiug.org Subject: Re: Arrays in Informix Stored Procedures [1981] There is no array in standard SPL. Is the subscript a string or an integer or something else? The 'database way' is to do something like create a temp table, and insert rows in there as needed. OTOH, stored procedures and temp tables interact badly - the SP gets reoptimized every time it is used. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "BRIAN HOUSE" | | | <houseb@intelihea| | | lth.com> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 10/02/2003 09:55 | | | AM | |---------+----------------------------> >--------------------------------------------------------------------------- ------------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Arrays in Informix Stored Procedures [1977] | >--------------------------------------------------------------------------- ------------------------------------------------------------------| I was wondering if it was possible to use arrays in Informix stored procedures or if there was an alternative to them besides collection objects. What I am trying to do is keep a running total for a subscript(item) and the way I have done it before in other languages is as follows: array is X assign initial value: x(subscript) = 1 increment value: x(subscript) = x(subscript) + 1 thanks, Brian
BRIAN HOUSE wrote > > > I was wondering if it was possible to use arrays > in Informix stored procedures or if there was an alternative > to them besides collection objects. > > What I am trying to do is keep a running total for a > subscript(item) and the way I have done it before in other > languages is as follows: > > array is X > > assign initial value: > > x(subscript) = 1 > > increment value: > x(subscript) = x(subscript) + 1 > When I had problems with my sewers, the carpenter said hit the pipe with a hammer and it will free the blockage. The electrician said fit a pump to pump it through The sweet shop owner said throw dolly mixtures down the hole and see if that helps The DBA said "create table runn_tot( x char(10), subscr integer) ; " The builder said "tough sh*t, that is the way we do it. Suggestions please for the laywers and accountants responses. Colin Bull Up to my ears in it
. . . . the lawyers said "Sue the builder; pipes shouldn't clog". ....... the accountants said that they would need at least three itemized estimates before they'd even consider fixing the problem. <G> -----Original Message----- From: Colin Bull [mailto:c.bull@videonetworks.com] Sent: Friday, October 03, 2003 4:00 AM To: ids@iiug.org Subject: RE: Arrays in Informix Stored Procedures [1984] BRIAN HOUSE wrote > > > I was wondering if it was possible to use arrays > in Informix stored procedures or if there was an alternative to them > besides collection objects. > > What I am trying to do is keep a running total for a > subscript(item) and the way I have done it before in other languages > is as follows: > > array is X > > assign initial value: > > x(subscript) = 1 > > increment value: > x(subscript) = x(subscript) + 1 > When I had problems with my sewers, the carpenter said hit the pipe with a hammer and it will free the blockage. The electrician said fit a pump to pump it through The sweet shop owner said throw dolly mixtures down the hole and see if that helps The DBA said "create table runn_tot( x char(10), subscr integer) ; " The builder said "tough sh*t, that is the way we do it. Suggestions please for the laywers and accountants responses. Colin Bull Up to my ears in it "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."