SQR limitation on SQL statements compiled

[Andy Schepel <atsml@YAHOO.COM>]

Any assistance on this matter would be greatly appreciated:

We are running batch SQR V4.1 on a MVS DB2 V5 server for a PeopleSoft
HR 7.5 installation.

 Several of our SQR interfaces are quite large and one,in particular,
is written with 24 separate SQL statements. When adding the PeopleSoft
and in-house Includes (SQCs), however, the total number of SQLs to be
compiled balloons to over 40. Some of the SQL statements in the
Includes are never executed.

The problem is that in our testing of the SQR interface with 300+
employee cases, the CPU total time is 5 minutes. After re-executing
the SQR routine with the '-S' flag, it seems that SQR re-compiles the
majority of SQL statements every time it executes them. For the DB2
users, a SQR 'COMPILE' is equivalent to a PREPARE. The re-compiles
seem to be introducing a massive amount of overhead into the execution
of this interface.

Researching, it seems that SQRIBE put out a little blurb into their
User's Guide that states that the maximum amount of SQL statements in
a SQR is 30. If the SQR program exceeds that then ALL SQL statements
are put into a buffer pool and are open to being re-compiled every
time they are executed.

It sounds like they haven't modified that portion of the Compiler
since the days of the 386!

Either way, does anyone know of a workaround for this problem? Is
their a pecking order or a set of rules by which certain SQL
statements are not swapped out for subsequent re-compiling?

Again, we are thankful for any help on resolving this problem.



_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com