Composite keys in hashed files

mleroux · Post by **mleroux** » Tue Sep 21, 2004 4:16 am

Are there any special considerations when using a composite key (multiple key columns) in a hashed file?

I looked at a job where one of the hashed file lookups wasn't giving any matching rows, even though the keys in both the lookup and input stages were corresponding perfectly. The lookups that were working fine had only one key column each, whereas the problematic hashed file had two (contiguous) key columns.

I modified the job to use a single, concatenated key column (Keyvalue1 : ":" : Keyvalue2) which then worked fine.

Tried a search in the documentation and on DSXchange, but couldn't really find anything. I don't think I've ever had to work with a composite key in a hashed file but it seems strange that it would be problematic.

Can one use composite keys in hashed files? If so, any ideas as to why the job in question was problematic?

andru · Post by **andru** » Tue Sep 21, 2004 5:46 am

Hash Files can have composite keys. I have worked with lots of hash files with composite key. Sometime, hash file lookup doent return proper values if you do a lookup on a integer column. That necessitates to convert the integers to char field and then perform the lookup.
It would have worked for you when you concatenate composite keys because, it would consider the int as char when you concatenate them.

Mike · Post by **Mike** » Tue Sep 21, 2004 6:23 am

Multiple lookup keys in a hash file is no problem. Just remember that DataStage always does a string compare on the key fields. Use the key expression to format the lookup value to EXACTLY the same format that is stored in the hash file.

For example, you may need to trim excess space characters in a varchar. (e.g. "ABC " does not match "ABC")

You may need to fmt numeric values. (e.g. "1.00" does not match "1")

The most common problem that I see is a metadata mismatch between the process that creates the hash file and the process that does a lookup on the hash file. If the metadata does not match, the lookup will almost always fail.

Mike

kduke · Post by **kduke** » Tue Sep 21, 2004 8:05 am

If you create a composite key in a job then the separator is @TM. There are ways around this but that is the default. So your key should be:

Code: Select all

Key = KeyField1 : @TM : KeyField2

This has been covered lots of times but maybe under "compound keys" or "muti-part keys". Might be hard to search for. Ray has discussed them in great detail. I am sure there is a Full Wurlod on it out there somewhere.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 21, 2004 3:53 pm

When you're using a Hashed File stage there are no special considerations. The stage (with the underlying engine) looks after everything. All you have to do is to make sure you've identified ALL the key columns accurately.

As will all hashed file metadata (whether single or multiple key), the Position of the key column(s) is 0, the position of the non-key columns is a positive integer. These must correspond to the definitions in the hashed file dictionary (that is, its metadata).

You only need to be concerned about the key separator character if you're reading the hashed file with DataStage BASIC. It's only then that you have to build the physical representation of the key.

Pretty sure that there is a post about this stuff, though indirect. Try a search for "@KEY_SEPARATOR".

FanWeik · Post by **FanWeik** » Mon Jan 10, 2005 2:15 pm

Hello,

I also need to do a lookup with a 2-part key in a PX job agaist a DB2 table. I concatenated the 2 fields in the Lookup stage in the format that's mentioned (Keyvalue1 : ":" : Keyvalue2). The compile was ok but the run generated the following error.

main_program: Syntax error: Error in "lookup" operator: Error in input redirection: Error in input parameters: Error in view adapter: Error in binding: Error in field type: Expected identifier; got: "":"", line 91
Expected operator name, got: ")", line 97; text: copy

Thanks for your help!!
Fan

ray.wurlod · Post by **ray.wurlod** » Mon Jan 10, 2005 3:05 pm

You've moved way off topic! In general you don't use hashed files in PX, and your post indicates that you want to perform a lookup in PX.

Ideally, post the question on the PX forum.

Basically, though, you no longer create a composite key - that's purely for hashed file. Instead, you declare as many key columns as there are key columns in the probe table.

chucksmith · Post by **chucksmith** » Mon Jan 10, 2005 5:29 pm

Back to hash files. You may want to verify that the key structure of the hash file actually matches what you find on your columns tab. You can list the dictionary (metadata) of the hash file at the command prompt in DataStage Administrator using the

Code: Select all

LIST DICT YourHashFileName

command. Verify that all columns you think are part of the key are also considered that in the dictionary. If not, the easiest thing to do is delete the hash file and recreate it.

This often happens if the primary key columns change after the hash file is initially created.