Creating a directory based hash file from a Basic routine

[Anjan Roy]

Hi All,

We have a defined directory for writing all the hash files. This is done for backup and restore purpose.

Now, I am writting a basic routine to create a hash file. Is there a way I can get the hash file created in the specified directory and not in the project directory? I would like to have the file created in the directory and then create F-Pointers to the file.

Any help would be appreciated.

-Thanks
Anjan

[mhester]

The only way to do this would be to use the mkdbfile command located in the datastage\engine\bin directory. You can do this via a program or manually and the command would look something like -

Code: Select all

...\engine\bin\mkdbfile c:\HASHTEST 30 1 4 20 50 80 256

where

• 30 = file type
  1 = modulo
  4 = separation
  20 = hash type
  50 = max load
  80 = split load
  256 = large record size

and if you want to create the dictionary for this file then the following command will create the dict -

Code: Select all

....\datastage\engine\bin\mkdbfile c:\D_HASHTEST 3 1 1

Regards,

[ray.wurlod]

Not the only way! The CREATE TABLE statement supports DATA and DICT clauses that can be used to specify location of the data and file dictionary portions.

Code: Select all

CREATE TABLE hashed_file_name
DATA /dirpath/hashed_file_name
DICT /dirpath/D_hashed_file_name
(
  column definitions...
);

As a bonus, this also creates the VOC entry.
On the down side (maybe) it means that data types are enforced. But you can make everything VARCHAR to get around that.

[him121]

hi..
adding to this questions..
we have almost all HASH file stored in C:\projecttemp\ppl directory..

now i want to read this HASH file ...in routine...

when i apply 'SELECT SRC_SYS_ID FROM HASH' it gave me error that hash file doesnt exsists....

so how can i do DS Admin Query in Routines on HASH file stored in particular directory...

thanx..

Himanshu

[ray.wurlod]

Search the forum for the SETFILE command.

You need to create a pointer (which is stored in the VOC file) to tell DataStage SQL queries where the hashed file is.

[mhester]

I still believe mkdbfile is "easiest" way to create the file. Most will not use the create table since it requires defining columns etc... Not generally what most people want to do.

[Anjan Roy]

Thanks for the response.

Next question -

If I create a file using CREATE TABLE command, do I need to explicitely create an F-pointer or it gets created automatically?

Not the only way! The CREATE TABLE statement supports DATA and DICT clauses that can be used to specify location of the data and file dictionary portions.

Code: Select all

CREATE TABLE hashed_file_name
DATA /dirpath/hashed_file_name
DICT /dirpath/D_hashed_file_name
(
  column definitions...
);

As a bonus, this also creates the VOC entry.
On the down side (maybe) it means that data types are enforced. But you can make everything VARCHAR to get around that.

[Anjan Roy]

Adding to my previous questions - If I create a file using CREATE TABLE command, is there a command like DROP TABLE that would remove the voc entry as well as the O/S file?

[ray.wurlod]

"As a bonus, it also creates the VOC entry." This is the F pointer to which you refer.

Yes, you drop it using DROP TABLE hashedfilename; if it was created with CREATE TABLE.

I prefer not to use mkdbfile because it does not populate a file dictionary, and I DO like to have my metadata recorded.