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...
Creating Hash Files in a remote directory
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 66
- Joined: Mon Feb 21, 2005 10:40 am
- Location: Paris
-
- Premium Member
- Posts: 66
- Joined: Mon Feb 21, 2005 10:40 am
- Location: Paris
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 66
- Joined: Mon Feb 21, 2005 10:40 am
- Location: Paris
Thanks Ray,
Will investigate the CREATE TABLE option further, I didn't realise it actually created Hashed Files.
Will investigate the CREATE TABLE option further, I didn't realise it actually created Hashed Files.
ray.wurlod wrote: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.
-
- Premium Member
- Posts: 66
- Joined: Mon Feb 21, 2005 10:40 am
- Location: Paris
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: