Increased stored procedures

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.

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.