Hash file lookup and simultaneous update

[chulett]

The hash stage on the other hand appears to delete hash file completely and then loads it with the records from the XFM output constaint of Not(NOTFOUND), which is not what I want.

Only if you tell it to!

Make sure you don't have the 'Clear file before writing' option checked and that you are writting to the exact same (including case and location) hash file name. Also, if using the 'Create File' option, make sure the 'Delete File before Create' option is not checked.

[miamichillin99]

Chullet, you hit the nail right on the head. I do have the 'Delete File before Create' option and the Create File option. I uncheckd both and now it works with the hash stage. thank you very much!!! But I'm having another problem that's related. If you look up to one of my first posts you will notice that there is a 2nd job to this whole process:

Code: Select all

UV1(With HASH) --> XFM --> OCI_BULK 

I am using the same famous hash file that we used as a lookup in JOB 1 and updated in JOB 1 to now be the source input for this one. I need to use the UV stage b/c I'm doing a LEFT JOIN with another Hash file but that besides the point. I'm getting a column from this hash file called AMOUNT that was declared as a decimal 15,2 in JOB 1. First problem is that JOB 2 gives me a warning that AMOUNT is actually a numeric and not a decimal. Second big problem is that is is moving over the decimal for many of my numbers. It appears to be dividing the numbers by 100. For example the number is 12345.00 and the UV stage "View Data..." shows it as 123.45. If I view it through a the hash stage in JOB 1 it show up correct. It's something with the UV stage that is distorting my numbers. Any idea as to why?

EDITED!!!!

[chulett]

Chullet, you hit the nail right on the head.

I need to use the UV stage b/c I'm doing a LEFT JOIN with another Hash file but that besides the point.

Not necessarily beside the point. Sounds like you could simply stream the first hash file into the job and then do a lookup to the second hash, constraining where you got a hit... unless I'm missing something.

I'm getting a column from this hash file called AMOUNT that was declared as a decimal 15,2 in JOB 1. First problem is that JOB 2 gives me a warning that AMOUNT is actually a numeric and not a decimal. Second big problem is that is is moving over the decimal for many of my numbers. It appears to be dividing the numbers by 100. For example the number is 12345.00 and the UV stage "View Data..." shows it as 123.45. If I view it through a the hash stage in JOB 1 it show up correct. It's something with the UV stage that is distorting my numbers. Any idea as to why?

Not sure on that one. Either some kind of a metadata mismatch or an artifact of using the UV stage in this fashion.

[miamichillin99]

Craig,

I'll try your way of using purely hash stages and a lookup with a contraint and see if that takes care of my other problem...the decimal movement.

Thanks a lot. Have a good weekend.

[kcbland]

Definetely use the Hash stages - you get to use caching and all kinds of cool tricks. If you created a hash file with a UV stage, it puts a bunch of gunk into the internal structures in a DataStage project because the hash file is now treated like a table. Your metadata issues are because the data typing reaalllly matters now a lot.

Clean up your job by "DROPPING" the hash file. Create the hash file fresh and use consistent hash file metadata throughout to make any metadata mismatch error messages go away.

[ray.wurlod]

A hashed file created with a UV stage (or CREATE TABLE) can still use "caching and all the other cool tricks" when accessed through a Hashed File stage - provided it is eligible (smaller than cache limit).

[kcbland]

I need to use the UV stage b/c I'm doing a LEFT JOIN with another Hash file

No caching available doing this. Using the hash file stage as a reference lookup gives you the equijoin, which unconstrained is an outer join on the driving primary input stream. You have the option of caching this lookup, whereas the UV stage will not when building the join.

[miamichillin99]

These two jobs have been the most educational thus far in my Ascential learning process. Thank you all for your help. I was able to get the decimal and data type issue resolved by using two hash stages instead of a UV stage with a left join. Below is my final JOB 2. Again thank you and I hope to keep learning enough as to one day return the favor to other forum members:

Code: Select all

        HASH(New hash I created,which use to left join to HASH1 in a UV stage)  
           |          
           |
HASH1 -->  XFM --> OCI_BULK 
(UPDATED HASH from JOB 1)