I do not use the Keyed data structure, but I can appreciate this change that occurred as part of the latest new Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, was significant.
Before the latest TRs the second parameter of the %KDS built function had to be a number to denote how many of the key fields were to be used. The change in the new TRs now allows for that second parameter to also allow a variable.
The old way would look something like this:
01 if (NbrKeyFlds = 1) ; 02 chain %kds(FileKeys:1) TESTFILER ; 03 elseif (NbrKeyFlds = 2) ; 04 chain %kds(FileKeys:2) TESTFILER ; 06 endif ; |
Lines 1 and 2: When the value of NbrKeyFlds is 1 the CHAIN operation code is performed with a hard coded value of 1 in the second parameter of the %KDS.
Lines 3 and 4: When the value is 2 then the hard coded value of 2 is used.
In scenario if there are more than two keys which could be used this is going to grow into a large and rather unnecessary piece of code.
By being able to use a variable as the second parameter I can replace the above code with:
01 chain %kds(FileKeys:NbrKeyFlds) TESTFILER ; |
That one line of RPG code will replace the potentially large piece of code I mentioned, and showed, above. It does make me wonder why it was not designed this way when %KDS was introduced.
You can learn more about the changes to RPG's key data structure built in function from the IBM website here.
This article was written for IBM i 7.4 TR2 and 7.3 TR8.
Thanks!! Very good explanation. so you can only use Physical files? In our application, most of the PF don't have keys. we create logical with the key.
ReplyDeleteYes not logical files, indexes, or views.
DeleteI have never understood why files were created without keys, and then you had to have at least one LF with a key.
In my opinion every PF should have a UNIQUE key.
Since I work on both DB2/400 as well as ORCL, I have found that certain things.
DeleteCertain 4GL tools like SYNON/2E or COOL/2E where you would model the tables, used to generate Physical Files without KEYS and then everything else will be based on keys/logical files.
In a ORACLE world, the Tables aka Physical Files will normally be keyed by a SEQUENCE and the child tables are linked by HEADER as a Referential key. The child tables will have its own Sequence which will be the primary "Unique" key.
For example ORDER_HEADER will ORD_H_SEQ as UNIQUE
ORDER_LINE will have ORD_L_SEQ as UNIQUE and ORD_H_SEQ as a FOREIGN KEY.
ORDER_PAYMENT will have ORD_P_SEQ as UNIQUE and ORD_H_SEQ as a FOREIGN_KEY
ORDER_SHIPTO will have ORDER_S_SEQ as UNIQUE and ORD_H_SEQ and ORD_L_SEQ as FOREIGN KEYs.
Synon and Cool/2E are the same product. When I worked with it I was very frustrated that it would not build keys on the physical files. I did not like that for every table there would always have to be two objects: the physical file and a logical file with a unique index.
DeleteAs for your description of Oracle's sequence columns Db2 has the same: Identity columns. When you are building a structured Db2 database you would do the same, use the Identity columns from one table as foreign keys in other tables.
I had worked only on Synon/2E for just 1 year in late 90s. But never worked on Cool/2E directly. Though worked on interacting with an application that was originally generated with a Cool/2E code in one of my clients. So only 2nd hand experience...
DeleteI have used IDENTITY in tables that I have coded.
Wow, that is a great improvement! Thanks for the update.
ReplyDeleteMichael
There was a time where the access path could get corrupted and all data was lost. By a separate LF this risk didn't exist... but that's a long time ago.
ReplyDelete3 good reasons to create a PF without keys, and then create a primary access path (LF).
ReplyDelete1. It prevents accidental deletion of a PF if the PF has no LF dependencies. (Been there, done that).
2. It allows for "primary index" LF adjustments without having to recreate/regenerate the PF. This is especially true if you have to adjust uniqueness parameters. I suggest NEVER assign unique keys to a physical, unless it's a tiny or throwaway file. Enforce the uniqueness at the LF level.
3. If you are using a record control or status code (e.g. - "active vs. deleted"), it makes it easy to have two sets of indexes, one for active records and one for not. This is especially true if the table setup requires uniqueness for active records, but doesn't care regarding deleted records.
That said, I will often add non-unique keys to a PF. But, then I ALWAYS create a primary index LF as well, even if the key structure is identical.
I did use Synon for a number of years. And, although I disagreed with the Synon developers on many topics (their definition of *NULL, lack of default templates, field naming conventions, and so much more) - I definitely agreed with them regarding their update/display indexes. I just reduced it down to a single primary index.