Loading files into Oracle tables

[srinivas Guduru]

Hi,

I have 150 files (text) as source. I need to load 150 files data into 150 Oracle tables. Without any changes for columns order and data types.
Every file has different number of columns. Delimiter is same (^) for all the files.
I need to load 150 files data into 150 tables every day and simultaneously

File1 --------- Table1
File2 ------- Table2
File3 ------- Table4

My approach is I have to develop 150 Datastage jobs for loading 150 tables.

Instead of developing 150 jobs, is there any solution in datastage that can load 150 tables simultaneously (From 150 files).
If there is no solution in datastage , is there any other way to solve this issue?
If you have any solution, can you please explain me.


Thanks

Srini

[ray.wurlod]

Use one job, with a schema file (whose pathname is passed as a job parameter) to describe the structure and Runtime Column Propagation enabled. Pass the file name and the Oracle table name as job parameters also.

[jwiles]

Of course make sure it's a multi-instance job if you're going to run 150 loads with it at the same time. AND make sure your job log retention is set to a number of days (1 or more) rather than number of runs. It's not pretty when the log of a running job instance is deleted automatically by the limit, as happened at one of my clients.

Regards,

[chulett]

AND hope your DataStage server and Oracle target can handle that many simultaneous PX jobs / connections.

[jseclen]

Hi Srini

You can make 150 jobs to load each table to Oracle, another idea you can group similary table (records) to load in a single job, in this case, you build fewer jobs.

Another alternative would be create a single job and use parameters and enabled RCP.

Your job must define this parameters

1) Schema file name
2) Table name

TEXT FILE ---> TRANSFORMER --> ORACLE STAGE

In the TEXT FILE add the roperty Schema file = #SCHEMA_FILE#
In the transformer don't defined fields, because dont appear fields
In the Oracle stage, define the Table Name = #TABLE_NAME#
Mark the job as Multiple Instance, this is a check in job properties



Then when you run the job asign an Id and pass the parameters, in this case, the name of the schema and table.