Re: SQR limitation on SQL statements compiled

[Tony DeLia <tdelia@EROLS.COM>]

Andy,
   24 seperate SQL statements shouldn't be considered large... If you're
receiving an Error check your ALLMAXES.MAX file in the SQR source
directory... OR the file designated by the -M flag... Also... Is 'every'
SQL statement supposed to be executed??? Also... Are you passing
perpetually changing dynamic SQL strings (i.e. WHERE clauses, etc.) -
The statements may need to be re-compiled (by SQR) if the program does
this... Are you receiving an Error or are you just questioning the SQL
statistics generated by the -s flag? Keep in mind ALL SQL statements in
the program appear on the SQL cursor listing 'regardless' if they're
executed or not... including the SQC's used... That may explain the
'ballooning' to 40 cursors...

                                             -Tony DeLia


Andy Schepel wrote:
>
> 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

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com