Reading hash file

ray.wurlod · Post by **ray.wurlod** » Mon Feb 07, 2005 9:12 pm

Welcome aboard! :D

What do you mean by "practising on hashed files"? Did someone create them for you? Or did you create them and, if so, how?

To read the data from a hashed file you can use a Hashed File stage (or a UV stage under certain circumstances - search the Forum for SETFILE). Process this through a DataStage job that writes to a Sequential File stage and, once the job has been compiled and run, whatever contents of the hashed file you specified have been copied to the text file. You can read it with DataStage's data browser, or from the operating system.

You can not read a hashed file from the operating system. A hashed file is like a database table; not meant to be accessed from the operating system but, instead, from a query language such as SQL, executed from within the dssh command environment or the Command window of the Administrator client.

naren6876 · Post by **naren6876** » Tue Feb 08, 2005 8:54 am

Hi ray,
Thanks for your help.Now i can create a Hash File and write the contents to a sequential file. I have one more question. "Can I merge the contents of two input hash files and generate a single sequential file "

Thanks in advance.

Naren

ray.wurlod wrote:Welcome aboard! :D

What do you mean by "practising on hashed files"? Did someone create them for you? Or did you create them and, if so, how?

To read the data from a hashed file you can use a Hashed File stage (or a UV stage under certain circumstances - search the Forum for SETFILE). Process this through a DataStage job that writes to a Sequential File stage and, once the job has been compiled and run, whatever contents of the hashed file you specified have been copied to the text file. You can read it with DataStage's data browser, or from the operating system.

You can not read a hashed file from the operating system. A hashed file is like a database table; not meant to be accessed from the operating system but, instead, from a query language such as SQL, executed from within the dssh command environment or the Command window of the Administrator client.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 08, 2005 3:43 pm

Yes, provided there is information in the one that can be used as the key to the other. If so, you can use one on the stream input and the other on a reference input.

Otherwise you can use a UV stage and SQL (for example to join on non-key columns, form a UNION, etc.)

naren6876 · Post by **naren6876** » Tue Feb 08, 2005 6:00 pm

Hi Ray,
Thanks for your help.
My actual requirement is,I have one sequential file as source and one hash file as reference link.I have one column in the sequential file and five columns in the hash file where sequential file.column = key column of hash file.here I need to generae a sequential file with all the columns of hash file.
Here my actual problem is, what to write in the key expression column of hash file in the transformer stage.Please help me out.

Thanks in advance

Naren

ray.wurlod wrote:Yes, provided there is information in the one that can be used as the key to the other. If so, you can use one on the stream input and the other on a reference input.

Otherwise you can use a UV stage and SQL (for example to join on non-key columns, form a UNION, etc.)

ray.wurlod · Post by **ray.wurlod** » Tue Feb 08, 2005 7:58 pm

Just drag the column name from the Sequential File link into the key expression field. A line will be painted on the canvas to show that this has been done.

Make sure that the input from the sequential file is a stream input, not a reference input.

Drag columns from both input links onto the output link. If the lookup fails, all columns from the reference input link (the hashed file link) are set to NULL by DataStage. It's similar to a left outer join. If you want to make it an inner join, constrain the output from the Transformer stage so that only rows for which the lookup succeeded are passed out.

naren6876 · Post by **naren6876** » Tue Feb 08, 2005 11:30 pm

Hi Ray,
Thanks for your reply.
Actually my sequential file has only one column.And my hash file has 4 columns in that 2 of them are key columns.If I drag from my sequential file column to the one column of hash file, the other key column of hash file is in red color. i.e i need to map something to that column.what should i map to the second key column of hash file.

Thanks in advance
Naren

ray.wurlod wrote:Just drag the column name from the Sequential File link into the key expression field. A line will be painted on the canvas to show that this has been done.

Make sure that the input from the sequential file is a stream input, not a reference input.

Drag columns from both input links onto the output link. If the lookup fails, all columns from the reference input link (the hashed file link) are set to NULL by DataStage. It's similar to a left outer join. If you want to make it an inner join, constrain the output from the Transformer stage so that only rows for which the lookup succeeded are passed out.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 09, 2005 1:54 am

This isn't DataStage's problem, it's yours. It would happen exactly the same if you were doing a lookup against a database.

You have two key columns, you must provide two values.

Where do you propose to get the second value from? If it's a constant, fill in its value (strings must be quoted) in the key expression field. If it's derivable from the input column, you must provide appropriate derivation expressions in the key expression fields.

naren6876 · Post by **naren6876** » Wed Feb 09, 2005 6:25 am

Hi Ray,
Thanks for your reply.
I need to get the value from reference hash file itself.

Thanks in advance.

naren

ray.wurlod wrote:This isn't DataStage's problem, it's yours. It would happen exactly the same if you were doing a lookup against a database.

You have two key columns, you must provide two values.

Where do you propose to get the second value from? If it's a constant, fill in its value (strings must be quoted) in the key expression field. If it's derivable from the input column, you must provide appropriate derivation expressions in the key expression fields.

kcbland · Post by **kcbland** » Wed Feb 09, 2005 2:34 pm

You have to have the values in your source data that can successfully match to the key values in your hash file. If your hash file key should just be error_cd, then build your hash file that way. You must change the job that creates that hash file and reprocess the data into the hash file. Now, go back to the job that transforms data and references the hash file and reload that hash files definition. You should have a one-to-one mapping it it will work.

There's nothing like doing the DataStage tutorial that comes on the cd, or better yet, taking the DS324 Beginners course to learn all of this. Good luck.