Modifying the Hashed file Key

[parimi123]

Hi,
Is there way to modify the hashed file key. When i tried to do this using a transformer, it inserted a new record into the hashed file with new key.
Btw, I'm creating the static hashed file from the command prompt using the command
mkdbfile filename 2 modulo 8 -32BIT

Thank You,
Parimi

[kris007]

How do you intend to modify the hashed file key. What did you do in the Transformer stage to do this. Could you elaborate a bit more on your requirement?

Kris

[parimi123]

Hi Kris,
Thanks for the quick reply. Here is the requirement/process of the job.
We have input file which have new inserts and updates to existing database. I do a join between source file and target db and create a hashed file.
Then hashed file is either inserted with new records or updated with the records from source file. The key of the hashed file is used to update the records. But as part of some updates, we need to modify the key.

Then use the hashed file to upsert the target DB.

In the transformer, i am just assigning the new key value to the key field.

Or in simple, my question is there a way to modify the key of hashed file.

Please let me know if you need more information on the requirement.

Thanks,
Poorna

[chulett]

If by 'modify the key' you mean change one of the key field values... you can't. As you've found, it will just insert the 'modified key' record as a new record.

You'll need to do two things. There's no way to delete records from a hashed file, so you'll need to update the 'old key' record to mark it as logically deleted. That would mean a new data field. And then you'll still need to insert the new 'modified key' record.

This would also mean any subsequent lookups would have to check the deleted flag and treat it the same as a hashed 'miss' if you get a hit and the flag is set.

[ray.wurlod]

You can use a UV stage and get a hashed file to behave like an SQL table. Under this design you CAN modify keys in hashed files with an UPDATE statement. That what happens under the covers is actually a delete followed by an insert if you change the key remains completely transparent to you.

[chulett]

True. The hashed file must either reside in the Project or you must manually create a VOC record for it, however, in order to leverage it via a UV stage.