Re: SQR limitation on SQL statements compiled

[Andy Schepel <atsml@YAHOO.COM>]

It is more involved. First, there is no dynamic SQL in this program.
Second, there are no error or warning messages generated. Lastly, we
have reduced the overall number of cursors to 29 so only pertinent
ones are compiled. Here are the facts:

.. Running under MVS, the cursor listing shows that the majority of SQL
Selects are re-compiled, usually once per execution of the SQL select.
Several had compile counts greater than execute counts! Either way,
compiling a single SQL Select 245 times for 245 employees is going to
extrapolate to insanity when considering that the production SQR will
process 50k+ employees. And this is just one cursor.

.. Transfering the SQR code to the NT environment and executing it
against the same tables (via DB2 Connect) yields the same execute
counts and rows returned counts for all cursors... The output is
identical. However, all the compile counts are now 1 (one). DB2 is not
at fault here, it must be the SQR compiler for MVS.

With that evidence in hand, I called PeopleSoft late yesterday and
they politely said it was a known performance issue resulting from 7.5
changes. I politely responded by stating that SQR and PeopleTools are
separate entities. This is a SQRIBE issue.

Evidently, the help stopped there. Anyone aware of a solution to the
MVS problem?

---Tony DeLia <tdelia@EROLS.COM> wrote:
>
> 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
>

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