file handling of unquie key

[Guest.Visitor]

The current application has a key that is built by using field keys of other files. This worked very well in the past, but now the key requires changing too much since this key has many relationships between the other files keeping everything synchronized is tedious. A brief example: I have a product master that maintains a multitude of manufacturer’s products, part of the key in the product master is the manufacturer code field from another file. The way companies are buying other companies today this once stable field is no longer and when it needs changing, 13 other files are affected.

So my question what is the best way to maintain a unique key in a series of related files if there is no applicative primary key? And is this the best way?

I have not programmed (RPG) in over twenty years, but I oversee IT operations so when I bring a contract programmer in I need to ensure this file be simplified. I have read using the RRN is not a good idea, but my thought is to just use a numeric number and just keep the other fields that are currently part of the key “free” to update on demand.

And how in today’s RPG code does one exactly auto create this unique key? Sorry if I appear a little out of it, it’s been a while.

[D.Abramowitz]

I'm not sure I understand. Are you saying that the values in the key fields require changing?

Dave

[Guest.Visitor]

Yes, portions of the key values change. In the example the mfg code for BB Bakery may be B111, GG Bakery buys them; the products need to be changed to GG bakery therefore the portion of the key meaning mfg code must be changed from B111 to say G222. Thanks Dave.

[efnkay]

As I understand your post...You have umpteen files with related key values but not necessarily tied via key-constraints. For the later I would skip good old RPG in favor of running SQL update statements over the related files. It's not too heavy, and always make a back-up of your target files and play with update statements before you use them in a loaded gun. (on production)

UPDATE PRODUCT_MASTER
SET MFG_CODE = 'G222'
WHERE MFG_CODE = 'B111'

[efnkay]

Sorry...If you need to change portions of key fields, use something like:

UPDATE PRODUCT_MASTER
SET MFG_CODE = 'G222' || SUBSTR(MFG_CODE,5)
WHERE MFG_CODE LIKE 'B111%'

[Guest.Visitor]

Thank you, I think I may have generalized too much, I will try to do better. We create a manufacturer’s product in our product master, say it’s:
Croissant Dough with Butter, pack 1/15#, manufacturer is A Bakers Inc
Our unique fourteen byte key is: A100D1005AC015
A100 =A Bakers Inc, D10=dough category, 05=croissants/bagels, A=F (frozen) storage code, C=grade (good-better-best), 015=pack
There is another table with the same key of purchase transactions. When any portion of the current key in the product masters changes; we have to hit the Purchases table and change it as well. An example is we accidentally set this croissant up as Dry instead of frozen; the key now needs to change to (A100D1005DC015) in both tables.
We need the above codes to validate to descriptions in various tables, but I do not believe they need to part of the key, I thought a better solution is to replace old key and rebuild the files just keeping the relationship between tables such as the Purchases table with a simple unique numeric key matching the product master. Then if something that was part of the old key needs updating it would not require hitting any other tables for a key change. Is that a better way? And how does one quickly determine a unique numeric key when creating a new product key in the RPG code (in the ongoing program, not the rebuild)? We used to use the date time stamp; I read it is not a good option due to the speed of today’s machines.
I hope I have made this clearer. Thanks.

[efnkay]

I understand your desire to handle this programattically...But let me assume that these product changes occur when your system is quiesced...or nobody else has these files open. Your approach will probably work, but rather than tracking or "changing" key values,
maybe just doing a read (of existing), test for old value, set new value and write (new), followed by a delete (of existing), could be a
trade for "simplicity", albeit a little cumbersome. Include an occassional reorg on these files then if you do it that way.

RPG will work fine for you...Again if the frequency of these changes wasn't every other day...I'd still just use SQL statements
with maybe the only drawback being you have to hit each file with a similar statement:

STRSQL (For interactive running) But you could bunch your statements up in a source member,
each statement ended by a semi-colon, and use the RUNSQLSTM command to execute the script.

Example:

UPDATE *LIBL/PROD_MAST A
SET KEY_FIELD = 'A100D1005DC015'
WHERE A.KEY_FIELD = 'A100D1005AC015';

UPDATE *LIBL/PURCHASE B
SET KEY_FIELD = 'A100D1005DC015'
WHERE B.KEY_FIELD = 'A100D1005AC015';

You don't have to use a table reference (A, B), I did for less ambiguity.
So on and so forth...Good Luck!

[don.brown@msd.net.au]

Unique key

After reading the posts I am not sure I understand the actual database design. I read you are using this composite key for multiple files but I am not sure if this is just to get back to the item master or for additional reasons. I would look at the normalisation of the database to see why duplicate data is needed across multiple files - I am not saying change it but have a look at why you have this composite key.

We use unique numbers as the primary key to quite a few files and then use logicals with the necessary access path to retrieve data in what ever sequence needed. There are a number of ways to assign the unique number, store the number in a data area or store it in a file or retrieve the last record added and add 1. We use a control file and a service program to retrieve the next available number. Make sure the unique number will never wrap - we use 11/0.

Then I would look to make each part of the composite key a field on the Item Master, and if not already add these to your maintenance program so users can change it if necessary.

On the other files that currently have the composite key and this will depend why you have the composite key as mentioned above, add a new field which is the unique key to the Item Master and remove the composite key.

If however you need part or all of the composite key on any of the other files, add individual fields for each part of the key rather than building the composite key, it is much easier to maintain. Also if this information is needed you may wish to add a trigger to the file and write a program that if the Item Master is changed the trigger program will update any other files automatically.

I am thinking from the details provided you only need the unique key for the Item Master for each of the related files and the information in the composite key only needs to be defined once and the definition should be on the Item Master.

Hope this helps

Cheers Don

[Guest.Visitor]

Thank you Don

There is no specific reason why this key is a composite of "fields". I am trying to streamline processes and every time we need to change an incidental part of the key we have to hit the other files, the key composites are not relevent to the other files, just this one. Thanks again for confirming my thoughts and giving me some ideas to maintain the numeric key.

It helps alot

Sue