Key on DDS Join Logical File

15 pts.
Join logical files
I have created a join logical file. On the second file there is a field that has to be a part of the key. But I get the following error message "Key field not from first file on JFILE keyword." Is there way to get past this, or another way of doing this than using a Join file?

Answer Wiki

Thanks. We'll let you know when a new response is added.

The join logical file can only be keyed on fields from the primary file. You can select/omit based on any fields included in the join.

I don’t believe that OPNQRYF has this restriction — this can be placed in an CL just before the call to your program (assuming that you are writing a program)

Or your program can access the files seperately without a join.

Or use a join and a read loop to find the record(s) with the required value in the field from the secondary file.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Martago
    This is a serious limitation of the join files, reducing its usability. I too wanted to create a join file with a key field in the secondary file - a description field, related to a code field in the primary file. I wanted to access the primary file not by the code field, but by its description, without having to incorporate this field in the primary file. That's the whole point of relational databases with its related tables. Open query files is not the answer to a file that I want always available and for all users.
    10 pointsBadges:
  • TomLiotta
    ...reducing its usability. Because of what could be sacrificed by having composite keys across multiple tables, it's hard to see how there is much reduction in usability. Consider a trivial example -- a primary table with one row joined to a secondary table with 1000 rows. How many index entries must exist? Well, clearly there must be 1000 of them. Each entry must contain a pointer to the single row in the primary and a pointer to one of the rows in the secondary. Now, add a row to the primary. How many index operations must result? Well, DB2 would need to create an index entry for each value of the composite key, so 1000 index insertions. Okay, let's be a little less trivial -- the primary has 10000 rows and the secondary has 2 million. But maybe the thought is that the index would only be over the JOIN fields. In that case, an index of the primary is all that's needed (assuming relational access). Or maybe the thought is that the index would be over a field such as OrderNumber in both tables plus OrderLine in the secondary table. In that case, the result will be effectively the same as having one index over the primary for OrderNumber and an index over the secondary for OrderNumber and OrderLine. Regardless, the question really comes down to how such an index would be used. There are only two meaningful reasons -- for ordering (which is already handled by appropriate indexes over the base tables) or for record-level access by key (which is generally outside of relational set-at-a-time operations). Given the potential performance drags from massive index locks and/or seizes during update operations, and given how easy it is to code RPG CHAINs to multiple tables on demand (which is where record-level access by key is done anyway), it's hard to see how usability is reduced at all. Sure, you might need to code two CHAINs instead of one. But I don't see how that's a significant burden. And as noted, OPNQRYF provides that capability for any time it really, really is necessary without requiring DB2 to be constantly restructuring the index while other jobs are waiting. If OPNQRYF isn't satisfactory, you can create stored procs, UDFs and CTEs that can give customized versions for any rare need. As for "all users", users rarely have a need to know that such things as indexes even exist. And I can't think of any time in my experience in nearly forty years when a user knew, or needed to know, that an index key made any difference to them. There was nothing they could do with the knowledge. All of that is intended to say that I simply don't understand how usability is reduced by any significant amount. I actually had a use for a composite key over two PFs twice -- once in 1984 when I first used OPNQRYF to do that job and again somewhere in the past ten years that I've mostly forgotten. Both times it was due to a poorly designed application database that I wanted to force some fancy function into. So, I know there are cases where it would be handy. But I'm far from wanting to give up what I have in return. I'm certainly willing to learn about more common uses, though, if they can be described. Tom
    125,585 pointsBadges:
  • philpl1jb
    1. Although you cannot define a key on fields in the secondary, you can order by fields in both files. 2. Perhaps you should define the description file as the primary 3. There is no need for Join logical files. A few lines of more efficient code can produce the same results. If you describe what you are doing perhaps we can give you a few suggestions. Phil
    54,090 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: