Strange data in a manually-created hash file

[mleroux]

Some additional info:

When I import the hash file's def, I get one field called AT.ID, Varchar length 10. It should be one field, either Timestamp len 19 or Varchar len 19.

[ray.wurlod]

You're not doing anything "wrong", per se. It's just that what you're looking at with the text editor is the file header, which contains management information, not data. The data starts later in the hashed file (exactly how much later depends on its tuning parameters).

@ID is the default name for the key in hashed files. AT.ID is a "legal" (starts with an alphabetic character) equivalent.

What you wrote to the hashed file had a sequential number (the variable i) as the key value. This is what you're seeing.

StartDateInt won't appear until you create a file dictionary entry for field number 1. The following command is incomplete, but will suffice:

Code: Select all

INSERT INTO DICT DimDate(@ID, F1, LOC, FORMAT) VALUES ('StartDateInt', 'D', '1', '19L');

Then, if you're going to use the data browser, you will need to re-import the "file definition" from the hashed file.

Alternately, you could simply define the second column in your DataStage table definition, and set the Location field in the Sequential File stage to 1 (and the Type field to "S").

[mleroux]

Thanks! I had to make two corrections to Ray's code:

Code: Select all

INSERT INTO DICT DimDateHash(@ID, F1, LOC, FORMAT) VALUES ('DateID', 'D', 1, '19L');

It is, of course, the actual hash file name (DimDateHash) that is used and not the routine's file handle (DimDate).

LOC is a number and not a string so it's 1 and not '1'.

Does anybody know where one would typically find some documentation on working with the UniVerse dictionary? I couldn't really find anything in the DataStage online documentation.

[richdhan]

Hi,

Pls search the forum. This has already been discusssed.

Pls follow the following link

viewtopic.php?t=88327

HTH
--Rich

[kduke]

Here are a couple quick commands to see what the fields are in hash file and to see what the fields are in the DICT.

Code: Select all

LIST DICT DateDimHash

Code: Select all

LIST DICT.DICT

F1 should be a VARCHAR. Other fields are CONV or F3 for the conversion. F4 or NAME is the column heading. F5 or FORMAT is the length and justification like 15L. F6 or SM is whether it is "S" single or "M" multivalued.

Ray, this is a nice way to create dictionary items. I think I will modify my create DDL routine to generate these INSERT statements.