stack overflow locking sysprocplan

We are experiencing the following problem and have yet to resolve it completely even after an IBM support call. When users run reports in Cognos's Impromptu that use views with stored procedures calls with them, the user will lock sysprocplan and prevent other users from doing anything. We are running IDS 9.40 HC3 on a HP-UX box running 11.11 v1 and using mostly CSDK.2.81.TC3 as a communication ODBC/SDK between Impromptu and IDS. We have some people on older versions of the CSDK, but no older then 2.70. IBM support said the cause of the locking of sysprocplan we are experiencing is due to running out of space in the stack. Now we have 128K for our stack as configured in STACKSIZE in the onconf file. (below is our onconf file). IBM said it has seen this when the procedures are recursive in nature and you get too many levels of calls. Which makes sense, but we have not found any recursive procedure calls. We do have a few procedures that call other procedures, but very few. The deepest we can come up with is six levels, a view that calls a proc the calls a proc ... until we have 5 procs calls and the view. Now this doesn't seem too deep and with 128K that seems like more then enough for the stack. If I do an onstat -g ses on average we have about 180-200 threads active at any give time. Now it is my understanding that each thread would have 128k and if we go with the high-end 200 threads that would be 25600K needed and we have SHMVIRTSIZE 524304 which should mean we have enough memory for 200 stacks. I have a couple of questions that might help in resolving this situation. 1 - Does anyone know how much stack space is used for each level of proc calls? Trying to figure out how many levels we can go before we run out of space, this is assuming IBM Support is correct that this is the cause of our problem. 2 - Does anyone know if the SDK/ODBC connection between Impromptu and the IDS database uses one thread for everyone that running Impromptu or does each report have its own thread? If the first then having 10-15 people all running reports with multiple levels of proc calls could easily exceed the 128K stack size. If however the latter then 128K seems like a lot of space for a lot of levels. 3 - Can anyone explain how the stack works when using stored procedures? 4 - Does anyone else have any ideas what we can do to resolve sysprocplan being locked when running Impromptu reports that use views with stored procedures calls in them? #************************************************************************** # # IBM Corporation # # Title: onconfig.std # Description: IBM Informix Dynamic Server Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME root # Root dbspace nameROOTPATH /opt/informix/dev/root.1 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 1048576 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 1 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH /opt/informix/dev/root.1-m # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS physlog_dbs # Location (dbspace) of physical log PHYSFILE 135138 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 50 # Number of logical log files LOGSIZE 8192 # Logical log size (Kbytes) # Diagnostics MSGPATH /opt/informix/Logs/cars.log # System message log file path CONSOLE /dev/console # System console message path # To automatically backup logical logs, edit alarmprogram.sh and set # BACKUPLOGS=Y ALARMPROGRAM /opt/informix/etc/log_full.sh # Alarm program path TBLSPACE_STATS 1 # Maintain tblspace statistics # System Archive Tape Device TAPEDEV /dev/rmt/c8t2d0BEST # Tape device path TAPEBLK 16 # Tape block size (Kbytes) TAPESIZE 24000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /opt/informix/tlog/current_trans_log # Log tape device path LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 24000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server staging area # System Configuration SERVERNUM 0 # Unique id corresponding to a OnLine instance DBSERVERNAME leto # Name of default database server DBSERVERALIASES carsitcp # List of alternate dbservernames NETTYPE ipcshm,3,400,CPU # Configure poll thread(s) for nettype NETTYPE soctcp,5,100,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 3 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 700000 # Maximum number of locks BUFFERS 200000 # Maximum number of shared buffers NUMAIOVPS 24 # Number of IO vps PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 100 # Maximum number of logical log files CLEANERS 127 # Number of buffer cleaner processes SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 524304 # initial virtual shared memory segment size SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 900 # Check point interval (in sec) LRUS 127 # Number of LRU queues LRU_MAX_DIRTY 4 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 2 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 128 # Stack size (Kbytes) # Dynamic Logging # DYNAMIC_LOGS: # 2 : server automatically add a new logical log when necessary. (ON) # 1 : notify DBA to add new logical logs when necessary. (ON) # 0 : cannot add logical log on the fly. (OFF) # # When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM, # because the server can add new logical logs during long transaction rollback. # However, to limit the number of new logical logs being added, LTXHWM/LTXEHWM # can be set to smaller values. # # If dynamic logging is off, LTXHWM/LTXEHWM need to be set to smaller values # to avoid long transaction rollback hanging the server due to lack of logical # log space, i.e. 50/60 or lower. DYNAMIC_LOGS 0 LTXHWM 50 LTXEHWM 60 # System Page Size # BUFFSIZE - OnLine no longer supports this configuration parameter. # To determine the page size used by OnLine on your platform # see the last line of output from the command, 'onstat -b'.@@NL@

jda wrote: > We are experiencing the following problem and have yet to resolve it > completely even after an IBM support call. When users run reports in > Cognos's Impromptu that use views with stored procedures calls with > them, the user will lock sysprocplan and prevent other users from doing > anything. > > We are running IDS 9.40 HC3 on a HP-UX box running 11.11 v1 and using > mostly CSDK.2.81.TC3 as a communication ODBC/SDK between Impromptu and > IDS. We have some people on older versions of the CSDK, but no older > then 2.70. > > IBM support said the cause of the locking of sysprocplan we are > experiencing is due to running out of space in the stack. Now we have > 128K for our stack as configured in STACKSIZE in the onconf file. > (below is our onconf file). IBM said it has seen this when the > procedures are recursive in nature and you get too many levels of > calls. Which makes sense, but we have not found any recursive > procedure calls. We do have a few procedures that call other > procedures, but very few. The deepest we can come up with is six > levels, a view that calls a proc the calls a proc ... until we have 5 > procs calls and the view. Now this doesn't seem too deep and with > 128K that seems like more then enough for the stack. > > If I do an onstat -g ses on average we have about 180-200 threads > active at any give time. Now it is my understanding that each thread > would have 128k and if we go with the high-end 200 threads that would > be 25600K needed and we have SHMVIRTSIZE 524304 which should mean we > have enough memory for 200 stacks. > > I have a couple of questions that might help in resolving this > situation. > > 1 - Does anyone know how much stack space is used for each level of > proc calls? Trying to figure out how many levels we can go before we > run out of space, this is assuming IBM Support is correct that this is > the cause of our problem. > > 2 - Does anyone know if the SDK/ODBC connection between Impromptu and > the IDS database uses one thread for everyone that running Impromptu or > does each report have its own thread? If the first then having 10-15 > people all running reports with multiple levels of proc calls could > easily exceed the 128K stack size. If however the latter then 128K > seems like a lot of space for a lot of levels. > > 3 - Can anyone explain how the stack works when using stored > procedures? > > 4 - Does anyone else have any ideas what we can do to resolve > sysprocplan being locked when running Impromptu reports that use views > with stored procedures calls in them? > > <snip> This sounds like two completely different things. 1. "Running out of STACKSIZE" Well, IDS will generally grow STACKSIZE if required, if it doesn't (i.e. there is a true IDS stack overflow), then really wierd and wonderful things happen - generally (hopefully!) and engine crash for a start. To prove the point why not just increase STACKSIZE to a "large" value for a while - say 512. 200 * 512K is 100 Mb - still not an issue - if needed another virtual segment will be added. I would suggest that this is a "red herring" :P 2. "SYSPROCPLAN getting locked" This suggests that you are getting something like a 244 error (without the error I can only guess), which would suggest that there is a re-optimisation occurring on some of your stored procedures during the execution of your stored procudure. What is the logging on your database? What do you do in your procedures - update statistics on tables used in stored procedures would cause problems (I have had a beer :p) - drop / create tables possibly. 9.40.HC7 is the latest version Still ...

jda wrote: > We are experiencing the following problem and have yet to resolve it > completely even after an IBM support call. When users run reports in > Cognos's Impromptu that use views with stored procedures calls with > them, the user will lock sysprocplan and prevent other users from doing > anything. > <SNIP> Is it possible that the stored procedure accesses some table(s) through the view that are being dropped and recreated or are being altered periodically, or that the view itself is being dropped and recreated? If so, that would invalidate the query plans stored with the compiled SPL and cause the procedure to be recompiled the first time it is executed afterwards which would indeed lock the sysprocplan record for the procedure. If the proc is a big one and many users are running the proc frequently it's possible that it takes long enough to recompile that several users' sessions decide they need to recompile it and queue up (if you have lock mode wait set) or error out on the lock. Similarly, running update stats on the underlying tables, if their data distributions change significantly that can also invalidate the store query plans. That means that any update stats jobs you run must also update stats for the stored procedures (my dostats utility does this for you automatically for example when it processes at the database level) after finishing the tables/database. Art S. Kagel

The other issue in terms of the "long enough" is if the re was a BEGIN WORK before the stored procedure was executed as the locks would not be freed from sysprocplan until after a COMMIT or a ROLLBACK has been executed.

I'm beginning to believe that the stacksize is a red herring, too. We have 5 tables involved: id_rec - id and default name and address aa_rec - alternative addresses (summer, work, home, campus, solicitation , etc.) addree_rec - alternative names (formal, greeting, informal, nickname, etc.) adre_table - priority of names aa_table - priority of addresses The view that is involved is one that gets the name and address and formats it for a given id. The procs is where the formatting actually happens. We only get this problem when we use Impromptu which is using an SDK/ODBC connection to communicate with the database. We have taken the exact same sql from the Impromptu report and run in dbaccess and never get the locks. We have been unable at this point to see any errors on Impromptu so do not know what IDS is returning to Impromptu when we get the lock problem. We run weekly update stats (high, medium, low) every Sunday morning on all tables and procedures. About 12 months ago per IBM suggestion I added daily update stats on all procedures. This did not help. All tables should be row level locking and we use Unbuffered logging. Now we do have people using our 3rd party software (Jenzabar's CX) to add/update names and address on a daily basis, and at times I'm sure while an Impromptu report is running. Can't say how many, but would expect a dozen or two, top three dozen names and/or addresses are added or changed each day. So the tables ending in '_rec' are not static, but do not have major changes daily. I hope this further detail might trigger a eureka moment. :-) John > >9.40.HC7 is the latest version > >Still ... We are waiting for our 3rd party software provider to release a IDS 10.0 version in 4Q and will be migrating to 10.0 then. Until then the only version they will support in 9.4 is the one we are on.

Hope this doesn't double post as having a little trouble with email right now. Art, The tables the view and procedures use are not dropped/recreated, new records may be added or old ones changed, but the 5 tables involved are key tables to the system are not rebuilt during normal work hours. To my knowledge the views & procs are not being recreated during normal business hours, as they are used by most reports to get proper name and addresses. I'm guessing that on an average day 10-40 records are being added/changed so a small percentage of the total record count for the tables involved. I'm sure there are peak days 100s are changed, but average is 10-40 per day. Myself thing it has something to do with the way we are connecting to the database via SDK/ODBC and I have something mis-configured with upstats or something else. Maybe some how Impromptu is not closing the query when done or IDS is sending an error that Impromptu ignores or something. Thanks for the suggestion and insight. John