Tuesday, May 26, 2020

Key data structure big improvement

change to RPG %kds

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.

7 comments:

  1. 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.

    ReplyDelete
    Replies
    1. Yes not logical files, indexes, or views.

      I 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.

      Delete
    2. Since I work on both DB2/400 as well as ORCL, I have found that certain things.

      Certain 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.

      Delete
    3. 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.

      As 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.

      Delete
    4. 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...

      I have used IDENTITY in tables that I have coded.

      Delete
  2. Wow, that is a great improvement! Thanks for the update.
    Michael

    ReplyDelete
  3. 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.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.