How to write to SQL-database in a routine?

[Jdrost]

Hello,

I want to write directly to a SQL-database in a DS-routine. Does anyone have an example how to do this?

[kcbland]

Which database? The easiest is to use a CALL DSExecute(...) API to run a system command, such as sqlplus, isql, sqlcmd, dbaccess, etc. Just formulate the DML or use an existing script and invoke it using the command line program.

Please don't do this to avoid using an ODBC/OCI stage for loading data. I'm assuming you mean to write a row into some table. If you're loading data as part of the ETL process, stick to using the tool.

[kduke]

The whole purpose of DataStage is to do this for you. You lose a lot of the beauty of DataStage when you go around it. The only reason to do this is to do a pure update. Please explain why you feel you need to go around DataStage.

[DeepakCorning]

I agree... It is like creating another tool with in Datastage to generate SQLs

[ray.wurlod]

Doing it from a routine incurs one of two fairly severe penalties. Either the routine executes a shell script (with all the overheads of establishing and managing a new shell and return structure), or the routine uses BCI functions (which involves paying for a Data Direct ODBC licence). Why re-invent the wheel?

[Jdrost]

Which database? The easiest is to use a CALL DSExecute(...) API to run a system command, such as sqlplus, isql, sqlcmd, dbaccess, etc. Just formulate the DML or use an existing script and invoke it using the command line program.

Please don't do this to avoid using an ODBC/OCI stage for loading data. I'm assuming you mean to write a row into some table. If you're loading data as part of the ETL process, stick to using the tool.

How do I handle userID and password for the server the database is on?

[ray.wurlod]

However you like. They can be passed (as command line arguments) from DataStage to the shell script, for one example. They could be environment variables (perhaps encrypted) for another example.