IDS Forum

RE: Arrays in Informix Stored Procedures

Posted By: Jeff Glenn
Date: Thursday, 2 October 2003, at 8:15 p.m.

In Response To: Re: Arrays in Informix Stored Procedures (Jonathan Leffler )

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

Messages In This Thread

• Arrays in Informix Stored Procedures
  BRIAN HOUSE -- Thursday, 2 October 2003, at 12:55 p.m.
  • Re: Arrays in Informix Stored Procedures
    Jonathan Leffler -- Thursday, 2 October 2003, at 7:05 p.m.
    • RE: Arrays in Informix Stored Procedures
      Jeff Glenn -- Thursday, 2 October 2003, at 8:15 p.m.
    • RE: Arrays in Informix Stored Procedures
      Jonathan Leffler -- Thursday, 2 October 2003, at 8:56 p.m.
  • RE: Arrays in Informix Stored Procedures
    Colin Bull -- Friday, 3 October 2003, at 3:59 a.m.
    • RE: Arrays in Informix Stored Procedures
      John Carlson -- Friday, 3 October 2003, at 11:13 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.