oracle DB access from DS subroutines
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 42
- Joined: Wed Aug 18, 2004 2:49 pm
oracle DB access from DS subroutines
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.
Note: I dont want to use shell exits. Would like to use some lib routines to accomplish this.
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
-
- Charter Member
- Posts: 42
- Joined: Wed Aug 18, 2004 2:49 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 42
- Joined: Wed Aug 18, 2004 2:49 pm
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 wrote: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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 42
- Joined: Wed Aug 18, 2004 2:49 pm
You have been of great help Ray. Thanks a lot.ray.wurlod wrote:At the TCL prompt, type HELP BCI.
You can download the BASIC SQL Client Interface manual from IBM.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
WARNING
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: WARNING
ray.wurlod wrote: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.
Byron Paul
WARNING: DO NOT OPERATE DATASTAGE WITHOUT ADULT SUPERVISION.
"Strange things are afoot in the reject links" - from Bill & Ted's DataStage Adventure
WARNING: DO NOT OPERATE DATASTAGE WITHOUT ADULT SUPERVISION.
"Strange things are afoot in the reject links" - from Bill & Ted's DataStage Adventure