Stored procedures and blocking Sybase processes

[Steve Krim <SMKrim@TELEWEB.NET>]

Hi all,

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