Create Hashed File in Datastage Routine

[csimms]

Mathieu,

Below is the transform function code that I use to create a type 30 hash file. By modifying the DICT array, you should be able to specify the required fields for you file.

Hope this helps you.

Chris


RoutineName = "rout.CreateQueueHashTable"
JobName = "XTekCreateQueueHashFile"

Ans = 0

CREATE.QUEUE = "CREATE.FILE hash_XTekQueue 30"

f = @FM

DIM DICT(17)

DICT( 2) = "JOB_SEQUENCE": f:"D":f: 0:f:"MD0":f:f: "10R":f:"S":f:f:"DECIMAL,10,0":f
DICT( 3) = "JOB_NAME": f:"D":f: 1:f: f:f:"256L":f:"S":f:f:"VARCHAR,256" :f
DICT( 4) = "PRIORITY_NUMBER":f:"D":f: 2:f:"MD0":f:f: "10R":f:"S":f:f:"DECIMAL,10,0":f
DICT( 5) = "QUEUED_DATE": f:"D":f: 3:f: f:f: "19L":f:"S":f:f:"VARCHAR,19" :f
DICT( 6) = "RUN_DATE": f:"D":f: 4:f:"MD0":f:f: "10R":f:"S":f:f:"DECIMAL,10,0":f
DICT( 7) = "RUN_TIME": f:"D":f: 5:f:"MD0":f:f: "10R":f:"S":f:f:"DECIMAL,10,0":f
DICT( = "START_DATE": f:"D":f: 6:f: f:f: "19L":f:"S":f:f:"VARCHAR,19" :f
DICT( 9) = "END_DATE": f:"D":f: 7:f: f:f: "19L":f:"S":f:f:"VARCHAR,19" :f
DICT(10) = "PARAMS_1": f:"D":f: 8:f: f:f:"256L":f:"S":f:f:"VARCHAR,256" :f
DICT(11) = "PARAMS_2": f:"D":f: 9:f: f:f:"256L":f:"S":f:f:"VARCHAR,256" :f
DICT(12) = "PARAMS_3": f:"D":f:10:f: f:f:"256L":f:"S":f:f:"VARCHAR,256" :f
DICT(13) = "PARAMS_4": f:"D":f:11:f: f:f:"256L":f:"S":f:f:"VARCHAR,256" :f
DICT(14) = "JOB_STATUS": f:"D":f:12:f: f:f: "25L":f:"S":f:f:"VARCHAR,25" :f
DICT(15) = "@REVISE": f:"PH":f:f:f:f:f:f:f:f
DICT(16) = "@":f:"PH":f:"ID.SUP JOB_SEQUENCE JOB_NAME PRIORITY_NUMBER QUEUE_DATE RUN_DATE RUN_TIME START_DATE END_DATE PARAMS_1 PARAMS_2 PARAMS_3 PARAMS_4 JOB_STATUS":f:f:f:f:f:f:f
DICT(17) = "@KEY":f:"PH":f:"JOB_SEQUENCE":f:f:f:f:f:f:f

OPEN "hash_XTekQueue" TO FileVar THEN
CLOSE FileVar
EXECUTE "DELETE.FILE hash_XTekQueue"
END

EXECUTE CREATE.QUEUE

OPEN "DICT", "hash_XTekQueue" TO FileVar THEN
REC = ""

FOR INDEX = 2 TO 17
ID = DICT(INDEX)<1>

FOR I = 2 TO 9
REC<I-1> = DICT(INDEX)<I>
NEXT I

WRITE REC TO FileVar, ID
NEXT INDEX

CLOSE FileVar
END
ELSE
Ans = -1
END


Chris Simms

Senior Systems Engineer
Extended Technologies Corporation
9708 Skillman Road
Dallas, TX 75243
csimms@xtekcorp.com
www.xtekcorp.com
214-540-4108 (Desk)
214-540-4000 (Front Desk)
214-540-4004 (Fax)

[ray.wurlod]

The Hashed File stage itself can create a hashed file (since version 4.2 of DataStage).
On the DataStage CD there is a utility called Hashed File Calculator that generates the command externally to DataStage - the utility is more about getting the correct sizing for the hashed file.
There are three different commands for creating a hashed file:
mkdbfile
CREATE.FILE
CREATE TABLE
Only the CREATE TABLE statement automatically creates the metadata for the hashed file. If you use CREATE.FILE or mkdbfile, you must subsequently build the "file dictionary", which is what most of the previous post concerns itself with.

[ray.wurlod]

I do have a utility for creating a UniVerse table (= hashed file) from a Table Definition that exists in the Repository. Ernie Ostic at Ascential was given this, but would probably have lost it in the three years since!