dynamically generation sequential file

[nivas]

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

[dspxlearn]

nivas,
can you plese brief your requirement with an example?

[nivas]

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

[chulett]

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.

[gateleys]

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

[nivas]

Hi Chullet

We are still using 5.X version.
Thanks for the reply. Could you please through some sample code for the basic routine or some more hints. I am looking for some help in the routine.

thanks
nivas

[gateleys]

We are still using 5.X version.

Now, I am not sure if the above hint that I gave will work.

5.x?? Man, that's like ... from those Spartan and Athenian days!!!

[nivas]

Hi

Could you please provide some more help in code for the routine.
Once the file is opened ,
commands for insert the records into the file or how do we insert records into the file and how the loop will go

thanks
nivas

[ray.wurlod]

We can't help you with that until you document your business requirement that governs which record goes into which file.

[nivas]

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

[ray.wurlod]

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.

[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

[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

[chulett]

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.

[nivas]

thank you very much.