Comments on: Key on DDS Join Logical Filehttp://itknowledgeexchange.techtarget.com/itanswers/key-on-dds-join-logical-file/
Wed, 13 Dec 2017 18:55:09 +0000hourly1By: philpl1jbhttp://itknowledgeexchange.techtarget.com/itanswers/key-on-dds-join-logical-file/#comment-93115
Fri, 10 Jun 2011 10:11:21 +0000#comment-931151. 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

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

]]>By: martagohttp://itknowledgeexchange.techtarget.com/itanswers/key-on-dds-join-logical-file/#comment-93090
Thu, 09 Jun 2011 14:32:40 +0000#comment-93090This 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.
]]>