Is There a Way to access database table with Datastage BASIC

olgc · Post by **olgc** » Tue Dec 23, 2003 11:06 am

Hi there,

We are trying to build a data mart which master controlling job is based on three database tables. One of them is dynamic which is built just before the master job starts to run. So Datastage job control code will be used to write the master controlling job. The problem is how to access the database tables with the Datastage BASIC? If there is no way to do this. Could anyone tell how a db table can be accessed with the Datastage BASIC?

Have a great holiday to every one!

Regards,

ray.wurlod · Post by **ray.wurlod** » Tue Dec 23, 2003 4:05 pm

It depends. Which database?
There is a set of functions within DataStage BASIC collectively known as the BASIC SQL Client Interface (or BCI) that allow your DataStage BASIC code to act as a client to the database. However, access is via an ODBC driver, so that you must have an appropriate ODBC driver for that database installed, configured and licensed. The drivers installed with DataStage are licensed only for use by DataStage components, and will stop working after 30 days when used by the BCI unless licensed. This is not done through Ascential but, rather, through the driver vendor (Data Direct Technologies). Or you can use any other UNIX-based ODBC driver.
Search the archives (topic BCI should get you most of the way there) for techniques, etc.

kcbland · Post by **kcbland** » Tue Dec 23, 2003 4:14 pm

Go back and reread Ray's post. This should emphasize that while the underlying technology supports what you want to do, you don't want to do it.

It will be much simpler to do shell calls to the native database command line interface and run sql scripts that way, and parse the screen output. This is handled thru DS API's such as DSExecute, for operating system command execution, and native database command line programs such as DBACCESS, isql, and sqlplus. You bypass all requirements for learning how to do RDBMS work with DS BASIC, as well as licensing issues for drivers. You'll be 100% guaranteed to work every time, as you're using the native connectivity for your database.

roy · Post by **roy** » Wed Dec 24, 2003 4:20 am

Hi,
from what Ray said, does this mean that using the odbc.h interface in DS basic control job to access a DB with let's say a data direct wire odbc driver will cause it to stop working after 30 days?
or will this only happen due to use via external utilities/programs ?

kcbland · Post by **kcbland** » Wed Dec 24, 2003 7:57 am

roy wrote:Hi,
from what Ray said, does this mean that using the odbc.h interface in DS basic control job to access a DB with let's say a data direct wire odbc driver will cause it to stop working after 30 days?
or will this only happen due to use via external utilities/programs ?

Dont know, but you get a big and nasty text message when you use the ODBC driver telling you that what you are doing is not licensed.

This is a path best not taken. How can I emphasize this more? There's no emoticon for what you are doing, while possible, is a bad idea? You're not going to build a transactional system with data entry and such. All you probably would need to do is either fetch a row from a table, or update a row in a table. This is perfectly handled from your BASIC BATCH or routine, but instead of doing SQL within the BASIC, you run a SQL script from the command line to do it. This is simplicity, and it costs nothing because there's no extra licensing!

ray.wurlod · Post by **ray.wurlod** » Wed Dec 24, 2003 3:27 pm

Or, even better, construct a DataStage job to do it for you, and run that - with as many parameters as you wish, from your code. The job can move data to and from sequential or hashed file(s), both of which are readily manipulated - without licensing - by DataStage BASIC.

olgc · Post by **olgc** » Tue Apr 06, 2004 12:15 pm

[quote="ray.wurlod"]Or, even better, construct a DataStage job to do it for you, and run that - with as many parameters as you wish, from your code. The job can move data to and from sequential or hashed file(s), both of which are readily manipulated - without licensing - by DataStage BASIC.[/quote]

Thank you, guys, before this post, I already done with seqential file. First it reads table to a seqential file, then reads it to an array, works with array in the code. When complete, unloads the array to a seqential file again and updates the table with the sequential file. How does this sound?

kduke · Post by **kduke** » Tue Apr 06, 2004 4:17 pm

I think the preferred way would be to write a job to transfer data to and from the sequential file. What you did seems to work then use it but to stay within the product DataStage and leverage it to read and write to ODBC is much better than writing a bunch of BASIC code. I think the general rule of thumb is the less done in BASIC the better.

Even for guys like me that have used this BASIC for many years, I use BASIC only when neccessary. Ray and Ken can easily write the program you need in BASIC but it is not the best solution. DataStage is a visual paradigm. Please stay within the GUI. I am sure you can make it work either way. Your choice.