Address nameless columns in Hashed files

[mavrick21]

Hello Gurus,

There's a hashed file which has two columns - Col1 and Col2. Col1 contains vachar data and is a key. Col2 is a timestamp field. I need to update Col2 data to '1700-01-01 00:00:00'.

When I do LIST.DICT here is what I get

Code: Select all

               Type &
Field......... Field. Field........ Conversion.. Column......... Output Depth &
Name.......... Number Definition... Code........ Heading........ Format Assoc..

@ID            D    0                            SDKMaxLastUpdDt 10L    S
                                                 tm_E

1 records listed.

My try with the following code was unsuccessful:
UPDATE <hashed file> SET Col2 = '1700-01-01 00:00:00'

Got error like - Invalid token at Col2.

How do I address columns in a Hashed file which has no names?

Thanks
-Mav

[ray.wurlod]

If you want to use SQL to insert or update, you will need to provide column names in the file dictionary. Search DSXchange for "INSERT INTO DICT" for examples.

Otherwise you can use DataStage BASIC code (which does not need column names) or an operating system command such as UVwrite.

[chulett]

Or just build a mapping... err, job... to do that.

[zulfi123786]

Otherwise you can use DataStage BASIC code (which does not need column names) or an operating system command such as UVwrite.

Was wondering how would an update work without providing the column name, how does it reference what has to be updated?

[chulett]

Hashed files columns are referenced positionally, not by name. Ever wondered why the 'column names' you use in the stage don't really matter? That's why and I assume part of the answer.

[zulfi123786]

When we try to update a column in a Hashed file, there should be an identifier that holds the position, right?

[ray.wurlod]

Not necessarily. Depends how the hashed file was created in the first place and whether the metadata were available at that time.

[suresh.angadi]

Hi,

delete the hash file with delete.file <File Name> then recreate and populate the data and then try to update the hash file.

Regards
Suresh

[ray.wurlod]

That does not address the issue, which is the lack of metadata in the hashed file's file dictionary.

[zulfi123786]

say the hashfile was created with a path and no VOC entry and i need to update the hashfile (xyz.hs) for the key column value=100 to set a column no 3 to 'A'

to achieve this using SQL i guess we need to make a VOC entry first, define the metadata and then fire the update right?

[ray.wurlod]

Ordinarily the entry for the field definition would be in the file dictionary rather than the VOC file. If you want to use SQL to change the record, then you will also need a VOC entry for the hashed file itself.

[zulfi123786]

Thanks Ray.