Hash File Lookup

ewartpm · Post by **ewartpm** » Tue Aug 15, 2006 5:16 am

Hi Guys

I'm getting a strange occurrence when I build the hash file.

The data in the source table contains a hypen i.e. '-' in this case the account number.
A key column on the hash file is the account number varchar(15).

Any account number with a hypen in it is not written to the hash file. There are no warnings in the job log to indicate a problem.

Does anyone know how to fix this problem?

ray.wurlod · Post by **ray.wurlod** » Tue Aug 15, 2006 6:38 am

It won't help to say it oughtn't to happen.

Are you SURE there is no constraint or other obstacle to the write? How many rows does the job indicate were sent to the hashed file? How many actually made it? Can you reproduce it with a different hashed file? How was the hashed file created?

I've just created a test job (7.5.1) and it's entirely happy to load rows into a hashed file with VarChar(15) key and hyphens in the data values - starting, intermediate and trailing, all were accepted.

ArndW · Post by **ArndW** » Tue Aug 15, 2006 6:43 am

Do you have a hidden null in the key string?

chulett · Post by **chulett** » Tue Aug 15, 2006 6:50 am

As noted, Something Else is going on as there are no prohibitions against hypens in any kind of string hashed file field.

Perhaps a constraint is not allowing those rows to be written?

ewartpm · Post by **ewartpm** » Tue Aug 15, 2006 9:48 am

Hi Guys

Thanks for the replies.

No constraint specified in the job. Out of 100 000 rows, get 15 with hyphens which do not appear in the hash file. The link information says that the rows were written to the hash file.

I thought maybe it was a character that Datastage displayed, when using the 'view data' option, that was perhaps something else. I then queries the source data using a sql tool and it showed a hyphen.

I'm stumped!!

kris007 · Post by **kris007** » Tue Aug 15, 2006 9:56 am

Don't trust the link information. It's tricky. Go into the administrator and then count the number of records in your hashed file using the command

Code: Select all

COUNT HASHEDFILENAME

If you are creating your hashed file within your Account then you can execute the above statement. If not( if you are creating it as a pathed hashed file) you will have to use a SET.FILE command to make VOC entry for that hashed file.

chulett · Post by **chulett** » Tue Aug 15, 2006 9:58 am

Ok, another guess... perhaps your key choices are causing these 15 records to collapse down to one? Don't forget there are no such things as duplicates in a hashed file, it's destructive overwrite based on the keys defined - and last one in wins.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 15, 2006 11:09 pm

ewartpm wrote: The link information says that the rows were written to the hash file.

No, the link information says that the rows were SENT to the hashed file.

Something has apparently prevented their being written.

Can you try two diagnostic things? First, replace the Hashed File stage with a Sequential File stage. Second, replace the Hashed File stage with a UV stage that refers to the same hashed file (you may need to create a VOC pointer, for example using the SETFILE command). Can you let us know what happens in each case?

ewartpm · Post by **ewartpm** » Wed Aug 16, 2006 9:25 am

Thanks for the replies. Problem is I don't have 'Premuim Support' so can't see the full answer.

My boss is getting a long , long justification...

ray.wurlod · Post by **ray.wurlod** » Thu Aug 17, 2006 7:41 am

Part of which is "less than US$1 per week".

(There are also corporate premum memberships available that discount this even further!)

Buy now! Send money, for free receipt!

ray.wurlod · Post by **ray.wurlod** » Fri Aug 18, 2006 10:15 am

Have you tried re-loading the table definition, in case what's in your job is out of synch with what's in the Repository? Or re-importing, even?