RE: SPL using cursor not closed or freed.

> -----Original Message----- > From: owner-informix-list@iiug.org [SMTP:owner-informix-list@iiug.org] > On Behalf Of Roy Mercer > Sent: Friday, March 25, 2005 2:10 PM > To: informix-list@iiug.org > Subject: SPL using cursor not closed or freed. > > See the output from onstat -g stm <sesid> below. We have recently > upgraded from 7.30 to 9.40.FC5 and using the onstat -g stm command can > see all of the users keep adding to the onstat -g stm. The app was > developed using PowerBuilder and uses stored procedures. Why don't > the stored procedures either close or free the implicit cursor? How > can I modify this behavior? > We never noticed this on the earlier version but the onstat -g stm > brought this to light. This user actually has over 200 in the > listing. > > My next question would be for ESQL/c apps. I guess our development > team never closes and /or free a cursor. I also see the same sql > statement repeated over and over for the C apps. They are going to > have to close and or free the cursor. The sessions keep using more > and > more memory. [Bill Dare] I've seen the same behavior recently in 9.40.FC3. I've got a 4gl app that is a background job. It wakes up every 5 minutes and does some work and then goes back to sleep. That work involves running the same queries every 5 minutes. The cursors were prepared/declared when the program started. The cursors were closed after each run at 5 minute intervals. The heap size for some of the queries would keep growing every time the cursor was reopened. After a few days the session's memory would get up to 30 MB and I'd restart the job. I fixed it by closing and freeing the cursors after each run. But, if you FREE the cursor you have to prepare/declare it again. I'm not sure if this is the expected behavior but that's the way it works in FC3. Regards, Bill > Should they be closing or freeing or both? > > Thanks for your help. > > > output for onstat -g stm 18332 > IBM Informix Dynamic Server Version 9.40.FC5 -- On-Line -- Up 4 > days 09:50:27 -- 3401344 Kbytes> > session 18332 > --------------------------------------------------------------- > sdblock heapsz statement ('*' = Open cursor) > c59a0218 6144 <SPL statement> > c59a0408 4096 <SPL statement> > c59a05f8 7168 <SPL statement> > c59a07e8 12120 <SPL statement> > c59a09d8 6144 <SPL statement> > c59a0bc8 12768 <SPL statement> > c59a0db8 9456 <SPL statement> > c59a0fa8 14088 <SPL statement> > c59a1198 7848 <SPL statement> > c59a1388 7864 <SPL statement> > ..... cut ........... > > > session 17500 > --------------------------------------------------------------- > sdblock heapsz statement ('*' = Open cursor) > c2123028 28920 SELECT t.tabname, > c.colname, c.coltype, > c.collength, c.colno > FROM > informix.systables t, informix.syscolumns c > WHERE ( 1 = ? OR t.owner = > ? > ) AND t.tabname IN ( ? ) AND ( t.tabid = > c.tabid ) > ORDER BY 1, 5 > c2123218 28928 SELECT t.tabname, > c.colname, c.coltype, > c.collength, c.colno > FROM > informix.systables t, informix.syscolumns c > WHERE ( 1 = ? OR t.owner = > ? > ) AND t.tabname IN ( ? ) AND ( t.tabid = > c.tabid ) > ORDER BY 1, 5 > c2123408 29008 SELECT t.tabname, > c.colname, c.coltype, > c.collength, c.colno > FROM > informix.systables t, informix.syscolumns c > WHERE ( 1 = ? OR t.owner = > ? > ) AND t.tabname IN ( ? ) AND ( t.tabid = > c.tabid ) > ORDER BY 1, 5 > c21235f8 28912 SELECT t.tabname, > c.colname, c.coltype, > c.collength, c.colno > FROM > informix.systables t, informix.syscolumns c > WHERE ( 1 = ? OR t.owner = > ? > ) AND t.tabname IN ( ? ) AND ( t.tabid = > c.tabid ) > ORDER BY 1, 5 > c21237e8 28968 SELECT t.tabname, > c.colname, c.coltype, > c.collength, c.colno > FROM > informix.systables t, informix.syscolumns c > WHERE ( 1 = ? OR t.owner = > ? > ) AND t.tabname IN ( ? ) AND ( t.tabid = > c.tabid ) > ORDER BY 1, 5 > c21239d8 28928 SELECT t.tabname, > c.colname, c.coltype, > c.collength, c.colno > FROM > informix.systables t, informix.syscolumns c > WHERE ( 1 = ? OR t.owner = > ? > ) AND t.tabname IN ( ? ) AND ( t.tabid = > c.tabid ) > ORDER BY 1, 5 > c2123bc8 29032 SELECT t.tabname, > c.colname, c.coltype, > c.collength, c.colno > FROM > informix.systables t, informix.syscolumns c > WHERE ( 1 = ? OR t.owner = > ? > ) AND t.tabname IN ( ? ) AND ( t.tabid = > c.tabid ) > ORDER BY 1, 5 > sending to informix-list