Hash File update

[Loobian]

Hi all,

I want to create an hash file with 3 fields but only one key. I also want that every time i load records into the hash, if the key's already there, no insertions/update will be made.

i want to keep loading records and maintain the first (distinct) record (without modification) per key in the hash file.

I've noticed that every time a new record with a key that already exists in the hash, is loaded, the other fields are updated.

Any Sugestions?

Thanks in advance.
RP

[ArndW]

Hello Loobian,

hashed file keys are unique; so every time you do a write to the hashed file with the same key the system does an update of all columns and overwrites the old data. This is the way that the system works and it cannot be changed, so in your case you would have to add another column with a differening value to the first key in order to do an effective insert instead of an update.

[loveojha2]

As a work around you would need to use the same Hashed file as a lookup

If lookup fails then only you would be writing to the Hashed file.

Code: Select all

                        Hashed File
                             |
                             V
SRC------------------------> TRNS-------------->Hashed File

Moreover don't check the Allow stage write to cache when writing to the Hashed file and select Disabled for pre-load file to memory for lookup Hashed file stage.
May be this is what you are looking for.

[ArndW]

Loobian,

you can control whether the hashed file used in a lookup is loaded to memory or not, and can also control the locking for updates.

[kcbland]

Just choose disable read caching on the reference and don't select write caching to the hash file. Now every row is immediately written and available to read. Don't mess with the locking, you can get yourself into trouble if you are not totally understanding how hash files and locking work. There's no need to use locking for most purposes, especially if this is the only job accessing the hash file at that moment.

[ray.wurlod]

If you don't mind the warning messages, load it with a UV stage (which uses SQL) with the update rule "insert new rows only".