hash file

[sumeet]

Suppose we have this hash file called HASH.
This same file is called at two places in the job .
This hash is created from target table TGT in which both the insert and update is being done
Source table SRC gives out the following columns:
Key1, key2, col1, col2,col3

We look up HASH twice . one for update and other for insert.* are to maintain spaces)


******************* HASH
********************:**** update
********************: **** ____________
SRC _________ TRANSFORMER ____________ TGT
******************** :
*********************: insert
****************** HASH
******************** |
******************** |
********************TGT
For Update:
Src.key1=Updt.key1 and src.key2=Updt.key2
Then if src.col1<>updt.col1 and src.col1<>0 then src.col1 else updt.col1
This is repeated for all three columns.

For Insert
Src.key1<> InSt.key1 and src.key2<> InSt.key2
Then insert the row.

My question is if the job started at time = T and new insert was done at time =T1 and update at time =T2.

Will the HASH receive the changes as well whenever the new insert or update is done so that it can use them for checking the conditions in future
i.e. will HASH be able to look up for these values of Keys and cols

[loveojha2]

The Answer is yes, If pre-load to memory option is Disabled, in the lookup Hashed file.

[ArndW]

Plus you need to disable the write cache option when writing to the file so that updates are immediate.

[ray.wurlod]

Plus you need to lock for updates so that no lost updates will occur to the hashed file.

Did you search for answers to this question? I posted exactly this advice in the past week.

[chulett]

The answer will only be Yes if the hashed misses are actually written back to the hashed file in question. I got the impression that the two hashed stages in the 'diagram' were both lookups:

We look up HASH twice . one for update and other for insert.

If that's the case, they'll need to add another stage and link to the job.

[sun rays]

Suppose we have this hash file called HASH.
This same file is called at two places in the job .
This hash is created from target table TGT in which both the insert and update is being done
Source table SRC gives out the following columns:
Key1, key2, col1, col2,col3

We look up HASH twice . one for update and other for insert.* are to maintain spaces)

Are you doing a look-up against the hash twice, or you writing to it once and using for a look-up in another stage.

[sumeet]

thanks for quick answers.

yes i am looking up the same hash file.

At one place i am creating it and using it for look up to do the insert for any incoming new key columns

And at other place i am using it to do the lookup for old keys , so that updates can be done.

So it boils down to if any new inserts are made to OCI then will that be UPDATED in HASH file as well on the fly.

Thanks a lot guys.