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
Creation of Hashed Files
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
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).