IDS Forum

RE: Increased stored procedures

Posted By:
Date:

I think you should be OK now.
I also had to increase STACKSIZE as well for large SQL Queries; up to 192.
This also eliminated our AF's.
Try upping this if you start getting AF's.

Is there a reason not to move to 9.4, there are some great features in this release?

Carl W Berner - Team Lead Informix/Oracle DBA
IBM Global Services - Serving Lucent Technologies
Work : (770) 750 5225
Pager: 1-800-759-8888 Pin#1615039
Email: cberner@lucent.com



-----Original Message-----
From: rkusenet [mailto:rkusenet@sympatico.ca]
Sent: Tuesday, May 06, 2003 10:31 AM
To: ids@iiug.org
Subject: Increased stored procedures [1085]


We are increasing the number of stored procedures
in our system, because we are rewriting dynamic sql (using exec datablade)
into static SQL standard SPL. We have found unacceptable problem with
datablades. Informix crashes and it can be traced to bug # 146360.

Coding dynamic sql to a static sql is not easy and that's why we ended up
with a huge increase in the number of SPs. From 2 SPs which use to do our
task, we now have 120 SPS. They all are written in such a way as to not
change the client code. 60 of these SPs are around 270 lines long and
the other 60 are 340 lines long.

Yesterday I loaded the new 120 Stored procedures in our QA system. Immediately
informix started giving AF's. Looking at the AF file, it sure look like some
memory related problem. I just could not find any reason for it to crash. Finally
I changed the following values:-

DD_HASHMAX 20
DD_HASHSIZE 503
PC_HASHSIZE 53
PC_POOLSIZE 127

The new values are:-

DD_HASHMAX 50
DD_HASHSIZE 1999
PC_HASHSIZE 149
PC_POOLSIZE 127

So far it seems to be stable, though I am not relieved 100%.

Is my diagnosis correct. With large number of SPs, each pretty big, does it make sense
to change the above parameters.

IDS 9.21.UC4
Solaris 2.6

Looks like we have to upgrade to at least 9.21.UC7. PDQ mentions that bug # 146360
was fixed in 9.21.UC6. We are in the process of renewing our maintenance contract
and hopefully that would enable us to get 9.21.UC7.

TIA.

Messages In This Thread

• Increased stored procedures
  rkusenet -- Tuesday, 6 May 2003, at 10:30 a.m.
  • RE: Increased stored procedures
    Berner, Carl (Carl)** CTR ** -- Tuesday, 6 May 2003, at 12:09 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.