Dynamic number of files output from a table?

[liz]

Hi everyone,

I am quite new to DataStage and I am in the process of creating a job to extract data from an Oracle table, perform transformations and save it as a csv file. My problem is that a csv file has to be limited to x number of rows. If there are more than x rows in the table, then multiple csv files should be created each with a max size of x rows to store the entire table. So for example, if the table has 10 rows and the limit for a file is 5 rows then there should be 2 files created: file_1 (which contains the first 5 rows) and file_2 (which contains rows 6-10). Is there a way to do this dynamically in DataStage so that by setting the row limit for the file a different number of csv files are created depending on how many rows are in the table when the job is run?

Thank you in advance,
Liz

[ray.wurlod]

Welcome aboard. :D

A CSV can have as many lines (rows) as you like - the limitation is in some of the tools that use it.

Use your DataStage job to write to a single file.
Then write another DataStage job, or a BAT file, to split that file into pieces each containing no more rows than your tool can manage.
In DataStage you can base the decision on @INROWNUM system variable; use a Transformer stage with five outputs, send the first 32768 rows to the first output, the second 32768 rows to the second output, and so on. Create as many outputs as your maximum possible number of rows might require.