dynamically generation sequential file
Moderators: chulett, rschirm, roy
dynamically generation sequential file
Hi
I am in need of generating sequential files based on the distinct value of one source table column which is in sqlserver. Please advice me .
thanks allot in advance.
thanks
nivas
I am in need of generating sequential files based on the distinct value of one source table column which is in sqlserver. Please advice me .
thanks allot in advance.
thanks
nivas
dynamically generating sequential file
The sqlserver 2005 table has few columns. In it one column say for example has 10 distinct values. Then I need to create 10 files. The records from the Table should go to these 10 files based on the distinct column value. If the distinct values are 20 then I should create 20 files.
thanks
nivas
thanks
nivas
Please verify that you will be using Server jobs for this and that you are indeed still running a 5.x version of DataStage.
If that's the case, I'd think your answer will be to write out a single file with this unique value as the first field and then use some other mechanism to split it up into separate files based on the field's content. This could be a custom BASIC routine or a C/C++ program, for example.
If that's the case, I'd think your answer will be to write out a single file with this unique value as the first field and then use some other mechanism to split it up into separate files based on the field's content. This could be a custom BASIC routine or a C/C++ program, for example.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: dynamically generating sequential file
For each distinct value, call a routine that can create the file dynamically by using the value as parameter. You may use the following -
Code: Select all
*vDistinctValue is received as parameter
vDirPath = "C:\Data\"
vFileName = "MyDynamicFile_":vDistinctValue;*You may also append Date in addition to the value
vFilePath = vDirPath:vFileName
OpenSeq vFilePath To file.var
Else Create file.var
gateleys
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I have a table with columns for example COL1, COL2, COL3 and COL4.
In that COL1 have for example 10,20,30 and 40 distinct values. Now Ineed to create four files with names file_10, file_20, file_30 and file_40.
All records which has COL1=10 should go to file_10
All records which has COL1=20 should go to file_20
All records which has COL1=30 should go to file_30
All records which has COL1=40 should go to file_40
Please help me on my requirement.
thanks
nivas
In that COL1 have for example 10,20,30 and 40 distinct values. Now Ineed to create four files with names file_10, file_20, file_30 and file_40.
All records which has COL1=10 should go to file_10
All records which has COL1=20 should go to file_20
All records which has COL1=30 should go to file_30
All records which has COL1=40 should go to file_40
Please help me on my requirement.
thanks
nivas
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Easy enough. One job to create the files, the other to distribute the data between them using constraints in a Transformer stage and job parameter references. The second job will make use of job parameters, and have as many output links as there are files (probably more output links - as many as the maximum possible number of distinct values).
The first job is necessary to determine the number and values of job parameters to give to the second job.
The first job is necessary to determine the number and values of job parameters to give to the second job.
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.
The number of distinct values will dynamically change.So the number of files to be created dynamically change. Even the distinct values itslef are not same. They change dynamically. So I can not use them in constraints.
As chulett said I need to do that through routine. I am looking for code help to do the same I explained before with example through the routine.
thanks
nivas
As chulett said I need to do that through routine. I am looking for code help to do the same I explained before with example through the routine.
thanks
nivas
The number of distinct values will dynamically change.So the number of files to be created dynamically change. Even the distinct values itslef are not same. They change dynamically. So I can not use them in constraints.
As chulett said I need to do that through routine. I am looking for code help to do the same I explained before with example through the routine.
thanks
nivas
As chulett said I need to do that through routine. I am looking for code help to do the same I explained before with example through the routine.
thanks
nivas
Not sure anyone will just hand over a working routine for you. How much coding experience do you have? It wouldn't be all that hard to write what you need in BASIC, but it will take a bit of research to know all of the functions and syntax you'd need.
From a high level, I'd think you could do something like this rather easily. One source file of all records, this 'unique value' as a field and the data sorted by this value. Then a loop to read the sequential file and a simple write to the current output file with whatever naming convention needed. Every time you see a new value in that field, close the current output file you had open and open a new one before writing. And of course do that (without the close) for the initial value.
As for the functions, search the forums for and read the pdf documents (the BASIC manual, primarily) for the following functions:
OPENSEQ
READSEQ
CLOSESEQ
WRITESEQ
WEOFSEQ
Those are the basic tools for working with sequential files. I do know that if you search the forum you'll find posted routines using them so should serve as examples of their usage. Note that all will not necessarily be available to non-Premium members.
Take your best shot at it and if you have issues, post whatever code you came up with and the issue(s) you are having. Specific questions will get you specific answers but don't just look for a code handout.
From a high level, I'd think you could do something like this rather easily. One source file of all records, this 'unique value' as a field and the data sorted by this value. Then a loop to read the sequential file and a simple write to the current output file with whatever naming convention needed. Every time you see a new value in that field, close the current output file you had open and open a new one before writing. And of course do that (without the close) for the initial value.
As for the functions, search the forums for and read the pdf documents (the BASIC manual, primarily) for the following functions:
OPENSEQ
READSEQ
CLOSESEQ
WRITESEQ
WEOFSEQ
Those are the basic tools for working with sequential files. I do know that if you search the forum you'll find posted routines using them so should serve as examples of their usage. Note that all will not necessarily be available to non-Premium members.
Take your best shot at it and if you have issues, post whatever code you came up with and the issue(s) you are having. Specific questions will get you specific answers but don't just look for a code handout.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers