RE: stack overflow locking sysprocplan

you may need to open a case with cognos, we had somewhat similar problem when we upgraded cognos (in our case cognos was on windows server, csdk was 2.81 and ids 9.4 on hp ux), it turned out be a problem with the cognos driver for informix (i don't remember the name of the .dll file ). -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]On Behalf Of jda Sent: Wednesday, July 20, 2005 12:37 PM To: informix-list@iiug.org Subject: 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.