oracle DB access from DS subroutines

[ratikmishra1]

How would you access oracle DB from a DS subroutine/batch. I need to log onto oracle and execute a select statement and get the records using a loop, then write out the records to a sequential file. Need to do all this in the Batch job.

Note: I dont want to use shell exits. Would like to use some lib routines to accomplish this.

[tonystark622]

Why don't you just do it in a DataStage job?

Tony

[ratikmishra1]

Why don't you just do it in a DataStage job?

Tony

I can. But it is messy. Like use OCI/ODBC to get the thing into a file. Then read the file to get the values into varaiables. Would like to do it directly in the batch/subroutine so the code would be lot cleaner/reusuable.

[ray.wurlod]

The "lib" routines are those in the DataStage BASIC SQL Client Interface (BCI). They mimic programming using the ODBC API. Search the Forum for BCI.

To use these requires that you have a licensed ODBC driver for connecting to your Oracle (or other) database. The drivers shipped with DataStage are not licensed to use the BCI functions.

The preferred approach, where you don't have the budget for ODBC drivers, is to have your script execute a DataStage job to perform the extraction to a sequential file, then read the results from if you want to.

Either way, this is NOT the kind of processing you'd want to do from a transform function; your throughput rates would be abysmal.

[ratikmishra1]

The "lib" routines are those in the DataStage BASIC SQL Client Interface (BCI). They mimic programming using the ODBC API. Search the Forum for BCI.

To use these requires that you have a licensed ODBC driver for connecting to your Oracle (or other) database. The drivers shipped with DataStage are not licensed to use the BCI functions.

The preferred approach, where you don't have the budget for ODBC drivers, is to have your script execute a DataStage job to perform the extraction to a sequential file, then read the results from if you want to.

Either way, this is NOT the kind of processing you'd want to do from a transform function; your throughput rates would be abysmal.

Thanks Ray for your reply. Is there any place where I can get help on the BCI routines. I did not find the any info in Ascential docs.

[ray.wurlod]

At the TCL prompt, type HELP BCI.

You can download the BASIC SQL Client Interface manual from IBM.

[ratikmishra1]

At the TCL prompt, type HELP BCI.

You can download the BASIC SQL Client Interface manual from IBM.

You have been of great help Ray. Thanks a lot.

[ray.wurlod]

You will develop your routine, and it will work.

After 30 days from first use, it will stop working.

At this point, you will need to pay money to license your ODBC driver.

This is not money to Ascential; this is money to the driver vendor.

If you're not prepared to go this route, don't even start developing using BCI functions.

You have been warned.

[datastage]

You will develop your routine, and it will work.

After 30 days from first use, it will stop working.

At this point, you will need to pay money to license your ODBC driver.

This is not money to Ascential; this is money to the driver vendor.

If you're not prepared to go this route, don't even start developing using BCI functions.

You have been warned.

Ray, you sound like the Oracle from The Matrix Trilogy. Your post is very prophetic.

[chulett]

Sit down. Have a cookie.

[mandyli]

You will use DSJOBREPORT DS routine in after job routine for capturing log information in the file.