DSXchange

Hi World,

What is the best way to create a hashed file using the Hashed Stage and the Create Options.. there are many kinds of Hashed but what is the fast to lookup ?

I want to create a hashed with 2 millions of records, with 8 fields, the record length is 83, the 20 first positions are the key .. all fields are characters..

Thanks for your answers...

Saludos

Miguel Seclen
Lima - Peru

HFC is a good utility. It generates a CREATE.FILE or mkdbfile command that you can copy/paste. Better is to take a note of the parameter values, and use them in the HashedFile stage's "create" window, since this takes care of the metadata (file dictionary) as well. Executing the command generated by HFC directly does not create the metadata for the hashed file.

Or you could create the hashed file with a CREATE TABLE statement, which would also create the metadata. Syntax extensions exist for file type, modulo and separation, or for dynamic hashed file sizing parameters. For example:
CREATE TABLE CodeLookup (
TYPE 18, MODULO 112557, SEPARATION 4,
KEYCOL VARCHAR NOT NULL,
NONKEYCOL1 VARCHAR, ...,
PRIMARY KEY (KEYCOL)
);

More information on the syntax extensions can be found within the DataStage Engine (via a telnet connection) by entering the command
HELP SQL CREATE TABLE
or from the UniVerse SQL Reference Guide which you can download from the IBM website.


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

Hi Ray,

What is the difference between the commands CREATE.FILE y mkdbfile??

Thanks. [8D]

Miguel Seclen
Lima - Peru

Miguel Seclen
Lima - Peru

The difference is the same as the difference between specifying in the Hashed File stage that the hashed file is in an account or in a directory.
CREATE.FILE creates the hashed file in (the project) account, adding an item to the VOC (vocabulary) file. The hashed file can be accessed with a query language tool, and opened in DataStage BASIC with the OPEN or OPENPATH statement.
The mkdbfile creates the hashed file in a directory, and does not add an item to the VOC file. The hashed file cannot be accessed with a query language, and can only be opened in DataStage BASIC with an OPENPATH statement, unless a VOC item is subsequently created with a SETFILE command (see archives).