Join Logical File on IBM i (AS/400)

I see much reference to multi-format logicals. What I need is something like this. Is the following done in a logical file? Which sort of logical file if so? Here is the join logical spec but it is not compiling.

Also, if there is a more modern way to do this, I would be happy to learn. these 2 files used are PF. All we want is to have a set of records from ICBLDTIR which only has the location = PICK, but this data is only in that location master. on field LMLTPC.

R PALREC                  JFILE(ICLOCMLM ICBLDTIR) 
               CPD7989-*    
                          JOIN(ICLOCMLM ICBLDTIR)     
                          JFLD(LMLOC1 IRLOC1) 
               CPD7486-*        
                          JFLD(LMLOC2 IRLOC2)    
               CPD7486-*     
                          JFLD(LMLOC3 IRLOC3)
               CPD7486-*         
  IRLOC1                                                
  IRLOC2                                                
  IRLOC3                                                
  LMLTPC                                                
S LMLTPC                    COMP(EQ 'PICK')

its getting these errors:

* CPD7486      20        3      Message . . . . :   Keyword specified at incorrect level.           
* CPD7989      30        1      Message . . . . :   Number of join specifications not valid.

Solution

What you have described in your question is not what we would call a multi-format logical. Each record your program reads will return the same format, called MYREC, regardless that the single format is composed from two files.

While a join logical may have been the recommended solution a decade or two ago, DDS is generally not the direction you should focus on. IBM is focused on improving performance and features with SQL. SQL can give you more feature, better overall performance, and make you more productive.

SQL gives you a variety of ways to deliver what you are seeking. The solution which is closest to your join logical file is a join logical view.

CREATE VIEW myview AS
  SELECT irloc1, irloc2, irloc3
    FROM icbldtir
    JOIN iclocmlm   ON  lmloc1 = irloc1
                    and lmloc2 = irloc2
                    and lmloc3 = irloc3
                    and lmltpc = 'PICK'

Or you could put the SELECT statement in your program with embedded SQL.

To accomplish this join efficiently, you will want to ensure that you have a keyed access path (ie. an index) over each physical file (ie table) by the three location fields. If not, it is quite simple to do in SQL.

CREATE INDEX myfile_idx1
          ON myfile      (field1, field2, field3)

Now some might say, so what? All we did here was essentially the same as the join logical file. But not only do you have the optimizer working on your behalf, but you can lend it a hand. At the present stage, with either method, the system will have to read a record from each file in order to create each result record. It must read the index entry first, then get the record referred to by the index. But suppose we could just read an index without having to actually read the record itself? That will speed things up. And the system can generally fit more index entries on a memory page, giving an extra speed advantage. So how do we do this? Well, if the index contains all the information the optimizer needs, then it will use index-only access. So all we have to do is put our additional field on the index. We don't need it as a sort field, so we will just attach it as an additional field.

CREATE INDEX iclocmlm_i2
          ON iclocmlm    (lmloc1, lmloc2, lmloc3)
      RCDFMT iclocmlmr2  ADD lmltpc