Using multiple SQL statements as input file source

[vijay]

Hello folks:
I have an output file from an program which contains the following information.

update table1 set col1 = 10 where col3='ID';
update table2 set col5 = 10 where col23='ID';
delete from table1 where col3='ID';
insert into table3 values(......);
......................;
......................;
EOF

Can I use this file as input source to run the ETL job?
What is the best practice on how to use this input file?
Please advice.
Thanks in advance,
VJ

[ray.wurlod]

You probably can "use this file as input source to run the ETL job". However, to answer your implied question, you cannot use it to supply instructions to DataStage. You would have to apply the statements against the database directly, using the database's own scripting language (such as PL/SQL for Oracle).

The only kind of input file DataStage can use in any sensible way is one that contains rows of data.

Depending on your release of DataStage you may have been able to use the job API to construct a job programmatically then run that job, but the job API no longer exists in DataStage.

In summary, if you want to use SQL statements, fine.
If you want DataStage to use SQL statements, select an appropriate stage type and construct user-defined SQL, or load the SQL statements into a stored procedure in the database and use DataStage to invoke that.


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518