HELP with "Stored procedure and memory problem"

Hi all, We run the following stored procedure, the vitual part of memory keeps growing until we get thousands of "out of virtual shared memory" and after a while On-Line crashes. We have: Informix 7.31UC2 HP-UX 10.20. 1 Gig. of Ram ------------------------------------------------------------------------ onstat -m08:53:40 Assert Failed: No Exception Handler 08:53:40 Informix Dynamic Server Version 7.31.UC2 08:53:40 Who: Session(1509, informix@blxcpm, 4293, -459175672) Thread(1551, sqlexec, e49f8d98, 3) File: mtex.c Line: 446 08:53:40 Results: Exception Caught. Type: MT_EX_BE, Context: mt_ex_setup: no mem 08:53:40 Action: Please notify Informix Technical Support. 08:53:40 stack trace for pid 25047 written to /usagers/informix/af.9f706d4 08:53:57 See Also: /usagers/informix/af.9f706d4, shmem.9f706d4.0 08:56:11 mtex.c, line 446, thread 1548, proc id 25048, No Exception Handler. 08:56:11 size of resident + virtual segments 600383488 + 169967616 >768606208 total allowed by configuration parameter SHMTOTAL ------------------------------------------------------------------------ CREATE PROCEDURE "informix".maj_cc_factservom() RETURNING VARCHAR(255); DEFINE l_code_client LIKE fact_serv_om.code_client; DEFINE l_jour LIKE fact_serv_om.jour; DEFINE l_tela LIKE fact_serv_om.tela; DEFINE l_no_fact LIKE fact_serv_om.no_fact; DEFINE l_alt_fact LIKE fact_serv_om.alt_fact; DEFINE sql_err INTEGER; DEFINE isam_err INTEGER; DEFINE msg_err VARCHAR(80); DEFINE err_buff VARCHAR(255); ON EXCEPTION SET sql_err, isam_err, msg_err BEGIN LET err_buff = "-- SQL erreur : " || sql_err || " isam erreur : " || isam_err || " "|| msg_err ; RETURN err_buff; END; END EXCEPTION; SET LOCK MODE TO WAIT 120; FOREACH curseur WITH HOLD FOR SELECT jour, tela, no_fact, alt_fact INTO l_jour, l_tela, l_no_fact,l_alt_fact FROM fact_serv_om WHERE code_client = 0 BEGIN ON EXCEPTION SET sql_err, isam_err, msg_err INSERT INTO pour_maj_codeclien ( nom_table, tela, jour, no_fact, alt_fact, sql_err, isam_err,msg_err) VALUES ("fact_serv_om", l_tela, l_jour, l_no_fact, l_alt_fact, sql_err, isam_err,msg_err); LET l_code_client = null; END EXCEPTION WITH RESUME; BEGIN WORK; IF l_alt_fact != " " and l_no_fact != " " THEN SELECT code_client INTO l_code_client FROM client WHERE tel = l_no_fact[1,3] || '-' || l_no_fact [4,6] || "-"|| l_no_fact[7,10] AND l_jour >= date_deb AND l_jour <= date_fin; ELSE SELECT code_client INTO l_code_client FROM client WHERE l_tela = tel AND l_jour >= date_deb AND l_jour <= date_fin; END IF; UPDATE fact_serv_om SET code_client = l_code_client WHERE CURRENT OF curseur; COMMIT; END; END FOREACH; END PROCEDURE; ------------------------------------------------------------------------- onstat -g seg Informix Dynamic Server Version 7.31.UC2 -- On-Line -- Up 2 days 21:24:00 -- 733776 Kbytes Segment Summary: id key addr size ovhd class blkused blkfree 4 1381451777 c0d0f000 600383488 11356 R* 73283 6 (shared) 1381451777 e49a1000 83894272 1892 V 9374 867 5 1381451778 e99a3000 2187264 644 M 260 7 99590 1381451779 e9fff000 16777216 864 V 1944 104 23047 1381451780 eafff000 16777216 864 V 1964 84 8 1381451781 ebfff000 16777216 864 V 1702 346 9 1381451782 ecfff000 16777216 864 V 1988 60 Total: - - 753573888 - - 90515 1474 (* segment locked in memory) ------------------------------------------------------------------------ onstat -g ses Informix Dynamic Server Version 7.31.UC2 -- On-Line -- Up 2 days 21:24:22 -- 733776 Kbytes session #RSAM total used id user tty pid hostname threads memory memory 6551 informix - 0 - 0 8192 5176 6541 informix 10 1275 blxcpm 1 49438720 48541512 6538 informix - 0 - 0 8192 5176 6522 informix 9 1155 blxcpm 1 67854336 66147104 6516 informix 0 1115 blxcpm 1 352256 247688 2473 informix 2 23026 blxcpm 1 65536 45984 12 informix - 0 - 0 16384 13664 10 informix - 0 - 0 8192 5176 9 informix - 0 - 0 8192 5176 8 informix - 0 - 0 8192 5176 7 informix - 0 - 0 16384 13664 6 informix - 0 - 0 16384 13664 5 informix - 0 - 0 16384 13664 4 informix - 0 - 0 8192 5720 3 informix - 0 - 0 8192 5176 2 informix - 0 - 0 8192 5176 ------------------------------------------------------------------------ onconfig file #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std # Description: Informix Dynamic Server Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace name ROOTPATH /chunk/root # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 512000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 1 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH /chunk/root_m # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS dbsphys # Location (dbspace) of physical log PHYSFILE 100000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 12 # Number of logical log files LOGSIZE 50000 # Logical log size (Kbytes) # Diagnostics MSGPATH /usagers/informi

The problem seems to be the SHMTOTAL ONCONFIG parameter. You have it set to a hard limit and the engine needs to allocate additional segments which would violate this limit. You can set SHMTOTAL to zero and it will permit shared memory to grow indefinitely. BEWARE you are running on an HP PA-RISC system. The CPU has only 4 special purpose registers for shared memory handles and if you have more than 4 it must swap the handles in and out of those four which will slow the engine and your applications to a crawl. You should fold the total of the normally allocated additional virtual segments into the initial virtual segment by increasing the value of SHMVIRTSIZE so that you only have the resident, the initial virtual, and the communication shared memory segments attached. This is a good idea on any platform but on HP it is mandatory for decent performance. You have dynamically added 4 16MB additional virtual segments and failed to add a fifth segment so you need to AT LEAST double SHMVIRTSIZE to 163840 or more and increase SHMTOTAL accordingly or set it to zero. Since you will now have >75% of main memory dedicated to Informix I'd also recommend adding memory to the server to bring it up to 1.5 or 2GB. As to why the stored procedure is causing the additional segments, if indeed it is, I would point to the exception that does the insert if the row is not found. BTW I cannot see this exception EVER firing since if the row is not found by the select there is not error only a NOTFOUND condition which is not an error. In this case the FOREACH loop will simply exit taking the exception out of scope anyway. I do not think that you can do what you want to do with an exception but may have to set a flag in the loop when the row is updated and after the loop if the flag is still clear then the row was not found so insert it. Art S. Kagel "BEDARD, NORMAND" wrote: > > Hi all, > We run the following stored procedure, the vitual part of memory keeps > growing until we get thousands of "out of virtual shared memory" and > after a while On-Line crashes. > > We have: > Informix 7.31UC2 > HP-UX 10.20. > 1 Gig. of Ram [Lots of good stuff SNIPPED]