Creating Hash Files in a remote directory

[CharlesNagy]

I have read the previous posts from 2005 on this and was wondering if the Datastage function DSU.CreateHashFile could accomplish this. I notice it is in DSU_BP.O , but of course there is no source code, so I have no idea what arguments it takes.

Anybody have any idea where some documentation exists on these DSU functions? I can't seem to find any....

Thanks in advance...

[chulett]

I don't know why you'd need to call a function like this directly. Why not explain what exactly it is you are trying to accomplish and what issues seem to be standing in your way?

[CharlesNagy]

We have some 3000 files to load, and would like to make sure that they load as fast as possible. We have done tests with the default Universe Dynamic files and they tend to slow down quite markedly if we are trying to load large quantities of data.

Therefore we plan to create static hash files with a modulo better suited to the size of the file we are loading. In tests we have found they load much faster. The plan is to create these files automatically, based on the known source data sizes, before running our data load jobs.

This works fine of course for local files useing the CREATE.FILE command, but not of course for files in other directories. Hence my question.

[gateleys]

Use mkdbfile if you want to create these files in directories other than the project folder.

[ray.wurlod]

The "DSU." prefix tells us that this routine (DSU.CreateHashFile) was created at your site; it is not one "out of the box" in DataStage. So if there's no source code, then we can only assume that some consultant created it and removed the source code. You can determine the number of arguments using a VLIST command.

As noted, you can use the mkdbfile command to create hashed files (note: not "hash" files) wherever you like. You can also create UV tables (which are hashed files) wherever you like with optional extensions to the CREATE TABLE statement, that have been discussed elsewhere in the forum.

[CharlesNagy]

Thanks Ray,

Will investigate the CREATE TABLE option further, I didn't realise it actually created Hashed Files.

The "DSU." prefix tells us that this routine (DSU.CreateHashFile) was created at your site; it is not one "out of the box" in DataStage. So if there's no source code, then we can only assume that some consultant created it and removed the source code. You can determine the number of arguments using a VLIST command.

As noted, you can use the mkdbfile command to create hashed files (note: not "hash" files) wherever you like. You can also create UV tables (which are hashed files) wherever you like with optional extensions to the CREATE TABLE statement, that have been discussed elsewhere in the forum.

[CharlesNagy]

For anyone interested in the actual syntax, here it is, as I entered it on my PC. It works just as well on the server at the office:

CREATE TABLE SQLEMPS DATA C:/Ascential/TestStatic/SQLEMPS DICT C:/Ascential/TestStatic/D_SQLEMPS (TYPE 2, MODULO 1, SEPARATION 1, EMPNUM CHAR(10) NOT NULL PRIMARY KEY, EMPNAME CHAR(10), EMPGRADE INTEGER, EMPCITY CHAR(15) );

Surprisingly, it insists that you enter the table name with the DATA and the DICT portions as well. This was not made clear in the manual, and I kept getting an error saying the pathname already existed. It took a bit of stuffing around before I got the above to work.

Now, I am not sure whether there is a limitation on the number of characters you can enter at the command prompt, or from an execute statement, so I changed the above statement to the following code. This looks a bit neater, and I am not aware of any limitation with the DATA statement, so potentially, you could add as many fields as you want.

Cmnd = "CREATE TABLE ":FileName:" DATA ":PathName:"/":FileName:" DICT ":PathName:"/D_":FileName:" (TYPE 2, MODULO 1, SEPARATION 1,"

DATA "EMPNUM CHAR(10) NOT NULL PRIMARY KEY,"
DATA "EMPNAME CHAR(10),"
DATA "EMPGRADE INTEGER,"
DATA "EMPCITY CHAR(15))"

* finished field entry
DATA ";"

Execute Cmnd capturing Output returning failure

This creates a remote hashed file and gives you a VOC entry as well, so you can just Open the file without using OpenPath. The only limitation is that if you want to delete it you must use Drop Table.


[quote="CharlesNagy"]Thanks Ray,

Will investigate the CREATE TABLE option further, I didn't realise it actually created Hashed Files.

[ray.wurlod]

The command string limit is about 2000 characters, but seems to be version-dependent. Your use of the DATA stack is entirely valid.