Handling empty hash files

[sumesh.abraham]

Hi All,

I've a DataStage job which uses an ODBC stage to call a stored procedure and the count of the records is stored in a hash file. This hash file is looked up in a second job which calls a routine for some functionality. If the stored procedure doesn't reurn any records, the hash file will be empty and the second job doesn't call the routine since zero rows are passed from the hash file to the transformer which calls the routine.

How to make sure that the hash file will hold a count of 0 if the stored procedure doesn't return any record(without changing the stored procedure).

Any help will be appreciated.

Thanks,
Sumesh

[kumar_s]

Hi Sumesh,
Hashed file is Datastage entity and Stored procedure is Database entity. So you need to find, where that Hashed file is getting loaded from. There you need to tweak to give the default as zero.

[sumesh.abraham]

Kumar,
Thanks for the reply.

I'm getting the records from the stored proc and finds the count by aggregator and stores the count in a hash file. So when stored proc returns zero records, zero rows will be passed from the ODBC stage and hence the hash file will be empty. When this hash file is used for lookup, the routine in the second job will not be called.

Is there any way to check whether the hash file is empty and if empty, insert a value of 0 to hash file.

Thanks
Sumesh

[DS_SUPPORT]

You can always get the count of a Hashed file

Code: Select all

Select Count(*) from HASHEDFILENAME;

So here you can write a routine and check the rowcount of Hashed file, and based upon the result you can proceed your second job.

[DSguru2B]

You can run the above query in your routine itself using DSExecute(). Also if your hashed file was created in a directory path outside the project directory then you need to VOC pointer to the hashed file before using the select count(*) on it.

[I_Server_Whale]

And you can establish the VOC pointer to the hashed file by executing:

Code: Select all

SETFILE Path_of_the_hashed_file\Hashed_File_Name Hashed_File_Name

Ex:

Code: Select all

SETFILE C:\Hashed_Files\H_ITEM H_ITEM

If the VOC entry already exists, and you want to refresh the VOC to the same hashed file because the hashed file now exists in a different path, then execute:

Code: Select all

SETFILE Path_of_the_hashed_file\Hashed_File_Name Hashed_File_Name OVERWRITING

Ex:

Code: Select all

SETFILE C:\Hashed_Files\H_ITEM H_ITEM OVERWRITING

Whale.

[kcbland]

Why involve a hashed file at all? It seems worthless as a Sequential file is easier to use.

[narasimha]

If I were you, I would store the count of the records using DSSetUserStatus.
I feel this to be a much easier route to take.

[I_Server_Whale]

If I were you, I would store the count of the records using DSSetUserStatus.
I feel this to be a much easier route to take.

I vote for it. The Best Way !!