Re: stack overflow locking sysprocplan

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 ...
.

Relevant Pages

  • Re: Identity Column
    ... the end use is using Crystal Reports - where is the magic? ... The user will connect using Active Directory from their desktop to the database server or do please explain the magic.... ... Using stored procedures you permission on the stored proc only; the user can only execute the stored procedure with the parameters defined, there is no select * from to get all the information in your database. ...
    (microsoft.public.sqlserver.programming)
  • Re: Debating Views vs Stored Procedures for reports and applications
    ... I choose usp's (user stored procedures) because I can send back multiple ... ProductName from dbo.Products p where exists (select null ... Now I use nested repeaters alot with basic reports. ... Since I have a list of all Customers, I'm able to display all Customers ...
    (microsoft.public.sqlserver.programming)
  • Re: Deadlock Issues When performing reports on subscriber
    ... If its a one to one, replicating the execution of stored procedures ... Looking for a SQL Server replication book? ... All of our reports are run off of our ... were even lucky enough to capture a deadlock victim error, ...
    (microsoft.public.sqlserver.replication)
  • Re: Reporting - Design and Implementation
    ... procedures to produce reports. ... you don't need OO techniques to format a fixed report. ... It is also fine to use stored procedures for doing grunt work related to the specific storage mechanisms in the DB and to enforce universal data integrity rules. ... Where one gets in trouble with stored procedures is when they contain complex business rules and policies. ...
    (comp.object)