Re: opnqryf question - opnid, joins, overides, etc.

[jpcarr@xxxxxxxxxxxx]

Rick

I said;
>>CPI4321
>>"Access path built for file xxxxxx"
>>The second level text on that message will tell you why it was building
an
>>index(ie what path you should have out there already).

You said
>I haven't been given permission (yet) to build logicals over the files.
>this WOULD be the best way to solve the problem, but I have yet to
convince
>the powers that be.

If you get the CPI4321 and read the message, you may be able to convince
who ever that runtime would drop  xx%

I said;
>>Other messages will help you too.

You said;
>I'm getting the SQL optimizer messages, but they don't explain why my RPG
>program isn't using the access path built by the opnqryf statement.
>Use this command  PRTSQLINF on the CLP program and look at the spool file
>created for clues.

Other people's replys I think pointed you there.  Match the file name in
your RPG with the file name in the OverRide.

You said;
>PRTSQLINF doesn't work on the cl program - it says the program contains no
>sql statements.

Sorry I forgot,  I don't use OPNQRYF any more for this type of thing.   I
use embedded SQL to do it for me.
Like this(Below),  You can also do much better joins in SQL than OPNQRYF.
If it were an SQL program,  the PRTSQLINF would tell you how long the SQL
statement ran the last time,  what files it used,  etc.


C/EXEC SQL
C+  Declare
C+             FGQ1
C+   cursor for
C+   Select
C+             FGQRPRDN,
C+             FGQPDAT,
C+             sum(FGQQUANT)
C+   from
C+             DCPFFGQ
C+   where
C+             FGQPDAT    between :FROM_DATE   and :TO_DATE
C+   and       FGQSHFT    between :FROM_SHIFT  and :TO_SHIFT
C+   and       FGQRPRDN   between :L1FPRD      and :L1TPRD
C*   and       FGQRMLOT   between :FROM_LOT    and :TO_LOT
C+   and       FGQWKC1    between :L1FWC1      and :L1FWC2
C+   and       FGQBLEND   between :FROM_BLEND  and :TO_BLEND

C+   group by
C+             FGQRPRDN,
C+             FGQPDAT
C/END-EXEC
 *
C/EXEC SQL
C+  Fetch       FGQ1
C+   into
C+             :FGQRPRDN,
C+             :FGQPDAT,
C+             :FGQQUANT
C/END-EXEC