Re: Join Logical File Problem -- MIDRANGE-L

Raj,

Walden posted a response to the list that pretty much goes along the same lines
as what I would recommend.  To reduce the index creation time, you would need to
create permanent joined logicals.

As Walden mentioned, do a STRDBG (Start debug), run your query then check your
job log for the recommendations that OPNQRYF comes up with.

Seasons Greetings,
James W. Kilgore
email@James-W-Kilgore.com

P.S. You may want to set jour jobs logging level to 4 00 *SECLVL

Rajan.Srinivasan@allfun.com wrote:

> James,
>                     Thanks for  your help.   I am sorry , I could'nt able
> to understand your idea.  Could you please expain me in more specific. On
> your mail, you have mentioned that  you are using  OPNQRYF on your RPG to
> generate the report.
>
> In our case,  we already have the OPNQRYF  on the CL, which creates a PF ,
> which is used to generate the report on our
> RPG.
>
> We actually want to elliminate this OPNQRYF because it builds the access
> path every time the user selects an option, which also takes a long time.
>
> In our  CL, we have  19 different  OPNQRYF command which uses  different
> Key flds,  some of key flds from Primary file,
> some of the flds from secodary files.  the key flds are all  mixed.
>
> Since I can'nt   use the key flds from Secondary file, I am struck.
>
> Now is there a  way that I can use  *NONE in the LF ( where secondary file
> key flds are used ), and handle in RPG?.
>
> Thanks again
>
> Regards
> Raj
>
> From: email@james-w-kilgore.com (James W Kilgore) AT Internet on 12-18-98
>       10:50 PM
>
> To:   MIDRANGE-L@midrange.com AT Internet@ccmail
> cc:    (bcc: Rajan Srinivasan/Servicing/SuperiorBank)
> Subject:  Re: Join Logical File Problem
>
> Rajan,
>
> Think about this, 19 views to "maybe" up to 3 files.  You can create
> logical files with *none (?) as place holders for missing information in
> the secondary files and hard code the logical selections within your report
> request program.
>
> We faced a similar problem doing sales analysis.  Some requests were by
> sales person, or customer. or item. etc. and then some requests were for
> product class within territory or item within customer within sales rep,
> etc. and have about
> 19
> ways to view the data by one way, by two ways was 19 * 18 ways and three
> ways
> was
> 19* 18* 16 ways. That's a LOT of logicals!
>
> At first we started hard coding the known requests just to get the job
> done.
> Once
> we had a breather, we created a few physical files (1 view, 2 views, 3
> views, 4 views) just to have a OPNQRYF  FORMAT definition.
>
> Now when a user makes a request, (one of thousands of mathematical
> possibilities)
> they are all covered through a call to a single RPG program that builds the
> JOIN/MAPFLD/QRYSLT strings that the CL caller passes to an QCMDEXC and the
> resulting OPNQRYF optimizes to existing logicals and requests are served
> within
> a
> respectable time frame.
>
> The trick, make the RPG program capable of handling four deep/ninteen
> variables (in any combination) without illogical structure and pass back
> either the requirements of an OPNQRYF or SQL structure to satisfy the user
> request.
>
> When you get really creative, you can serve 19 entities to "N" levels
> (combinations) within a single request constructor and go home at the end
> of the day feeling a true sense of achievement! :-)
>
> Rajan.Srinivasan@allfun.com wrote:
>
> > Hello  All,
> >                          We   are   trying   to  replace  an  Open
> Query
> > File  with   Join  Logical  File  and  got  struck.  Any help is
> > greatly  Appriciated.
> >
> >  IN  BRIEF  :      JOIN  LOGICAL FILE  -   Not allowing  to have  Key
> field
> > from  Secondary  File fields.
> >
> > We  are  using  the  Open Query  File.  We are trying  to  take out the
> > Open Query file, ( which generates the acceess path
> > every time, which takes long time ),  and  use   Join Logical file with
> > the relavent Key list.
> >
> > IN  DETAIL. :
> >
> > We  are showing a  selection  screen , of which  the  user can select  19
> > different  selection creteria to
> > print the report  in  19  different ways.  It is being  called and
> > executed from  a  CL  to  RPG.
> >
> > The data is being taken from  3  Physical  files.
> >
> > On  CL,  depends on the user selection,  we  run  Open Query ,  ( which
> is
> > joining  three  PF ,  and putting into an another PF),
> > and  that PF  is being used in  the  RPG.
> >
> > " We want  to  take out the  Open Query, and  create 19 different  join >
> Logical file ( to join all 3  PF ) ,  with  Selection  creteria  and  with
> > Key Field"  and  use the join  logical file to generate the report."
> >
> > PROBLEM.
> >                          I can able to create  19  different   LF,  over
> > 3  different  PF.    But,   """" The Key filed used on the open query is
> > taking fields   from  all  three  PF,  instead of  only  from  the
> Primary
> > fille."
> >
> >                         I  read in the  manual that  "  I can  use  only
> > the fields  from the Primary file  for the Key field ", which is correct
> > because when I use  fields from secondary files  for Key field,  I am >
> getting a compile time error.
> >
> > WHAT WE WANT TO DO
> > Basically we want to ellimate the process  to generate the access path  (
> > to take out the open query file from CL ) every time  when the user
> select > any option to generate the report.
> >
> > Actual  Open Query  File  statement  in CL
> >
> > OPNQRYF    FILE((FILE 1) (FILE 2) (FILE 3)) +
> >
> >                           FORMAT(CRTPF1) QRYSLT('(X090 *EQ  "R")')
> >
> >                           KEYFLD((XR180) (XR070) (XR110) (XA133) (XA131)
> > (XA010) (CHKDIG)   (ICLASS))
> >
> >                           JFLD((ALOAN DLOAN) (CLOAN DLOAN))
> >
> >                           JDFTVAL(*YES) MAPFLD((ALOAN 'A2LOAN' *CHAR 10)
> > (MR010 'SMR010' *CHAR 9) (HKDIG 'CHKDIG' *CHAR 1) (DLOAN 'MR010 +
> >
> >                          *CAT HKDIG' *CHAR 10) (CLOAN 'ILOAN' *CHAR 10))
> >
> > Any  help is greatly appriciated.
> >
> > Thanks in advance
> >
> > Raj
>
> ___________________________________________________________________________
>       ______________________________________________
> >
> > +---
> > | This is the Midrange System Mailing List!
> > | To submit a new message, send your mail to MIDRANGE-L@midrange.com. > |
> To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. > | To
> unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. > |
> Questions should be directed to the list owner/operator: david@midrange.com
> > +---
>
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com. | To
> subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. | To
> unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. |
> Questions should be directed to the list owner/operator: david@midrange.com
> +---
>
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---