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

["Richard B Baird" <rbaird@xxxxxxxxxxxxxxxxxxxxx>]

Joel,

thanks for the reply, see inline.

>From my experience, the first file in a two-file join should have the
>smaller amount of records in it if possible.

this is what I thought.

>The first KEYFLD _MUST_ be from the first file in the OPNQRYF.  When you
>switch SELCOPY to the first file, add a new KEYFLD from SELCOPY to the
front
>of the KEYFLD list.

This, I didn't know.  Which makes me wonder why I didn't get a runtime
error when I executed the opnqryf.   I'm sure the first keyfld wasn't from
the primary file.

>Do you have a QRYSLT command in the OPNQRYF?  That can make a difference
>too.

No.

>Run this interactively.  Does it read thru every record in the file?
(Does
>it count 20,000, 40,000, etc?)  If yes, it is creating an access path.

No.  It pauses for a few minutes on the opnqryf command (it shows it
building the index on wrkusrjob from another session).  It then continues
to call my program which then just starts reading every record in the file,
rather than the ones that should have been selected by the opnqryf join.

>In your opinion, should it create an new access path?  If no, keep
changing
>things to force it to use an access path that already exists.

There are no logical build over these files at all.  they are from a canned
package (CA/Masterpiece) which is mostly mainframe cobol modified to run on
the 400.   from what I can tell, their programs use some proprietary api's
to retrieve data (probably via sql).

>I think you had the right idea with puting SELCOPY in front.  Try changing
>OPNSCOPE from the default to *JOB.  Sometimes that does the trick.

>Also, instead of reading the result of OPNQRYF with RPG, try using
>CPYFRMQRYF instead.  This will copy to a PF so you can see if the OPNQRYF
is
>behaving properly.  This isolates the problem to the OPNQRYF and removes
any
>problems caused by the RPG pgm.

>Let us know if any of these ideas help!

All good ideas.  I'll let you know.

thanks,

Rick