Re: Stored procedures and blocking Sybase processes

[Tim Green <Tim_Green@MERCER.COM>]

I think there must be more going on here.  We use SQR and Sybase and have never
run into this problem.  I can't understand why the system tables would be locked
unless at least one of the temp stored procedures being used is being dropped
and recreated iterively.

Here's what I'd do:

1.  Run the process, noting the time you start it.

2.  Let it run for quite a while.  I suspect the program has a loop that it runs
in during the bulk of it's processing time.  Let it get in the loop and run for
a while.  Then kill it, again noting the time.  You may need to use a kill -9,
as the idea is to kill it and not let SQR clean up after itself.

3.  Look at the temp stored procedures left in the database from the run.  They
will have names that begin with 'SQR'.  Look at the crdate (Creation Date)
column in sysobjects for each of these and note the times.

4.  I suspect the culprit (or culprits) will have creation times very near to
the time you kill the process, while the other procedures will have times very
near to the time you started the process.

5.  Look at the text of the stored procedure and identify the SQL in the SQR
program that is the source of the procedure.

6.  Put a -XP on the Begin-SQL or Begin-Select line of the identified
query(ies), and cross your fingers.

I suspect what you have is a SQL statement that is called iterively, and one
that for some reason SQR feels must have it's stored procedure dropped and
recreated each time.  Perhaps it uses dynamic query variables (i.e. square
bracket variable references) or something else.  If you get lucky and this does
identify a bit of bad logic on SQR's part, using the -XP on the specific SQL
statement should fix the problem and avoids the need to use -XP on the SQR
command line.

Good luck and let us know what you find.

Tim Green                                           Tim_Green@mercer.com
Administrative Solutions Group
An ADP/Mercer Alliance



> I'm a long time lurker with a big time problem!!!
>
> Our platform info is as follows:
> Sybase 11.0.2
> Open client 10.0.4
> SQR 3.0.18.1
> Solaris 2.5.1
>
> We are a PeopleSoft customer and have recently upgraded from
> PeopleTools
> 5.12 to 7.02, thus going from SQR 3.012.x to 3.0.18 (DB-Lib
> to CT-Lib).
> When we run SQR and allow it to create stored procedures, the stored
> procedure that is created will put exclusive table locks on all the
> database system tables (syscolumns, sysindexes, etc..) thus
> locking every
> one out until the sqr finishes.  So, we are currently running
> in production
> with the -XP flag set, this prevents the locking / blocking
> problem, but
> the reports run far too slowly for a production system (a 45
> minute report
> went to 7 hours!!!)  In some benchmarking that I have done I find that
> sqrs' that create stored procedures run 8 to 10 times quicker
> than without
> stored procedures.  I have tried a couple of things such as
> setting the
> isolation level to 0, ensuring chained transaction is set to
> on, etc...
>
> So, has anyone one else had this problem?  Any ideas or
> suggestions to try
> and get around this???
>
> Thanks,
>
> Steve
>