wirting a basic routine to run sql

dsfan2004 · Post by **dsfan2004** » Thu Jan 20, 2005 4:41 pm

Hi everyone,

is there anyway I can write a sql statement in a Datastage Basic routine?

kduke · Post by **kduke** » Thu Jan 20, 2005 5:28 pm

The easiest way is to run a shell script which runs sqlplus or whatever.

dsfan2004 · Post by **dsfan2004** » Thu Jan 20, 2005 5:35 pm

I need to run a select statement and return the result ( the result is always a single value ), is there a way i can do it?

kduke wrote:The easiest way is to run a shell script which runs sqlplus or whatever.

kduke · Post by **kduke** » Thu Jan 20, 2005 6:54 pm

Sure. You can write it to a sequential file but why not do it in a job. You could write it to a hash file or a sequential file in a job. It would be easy to read that from a routine.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 20, 2005 9:10 pm

Of course you can write one.

But what do you want to do with it then?

If it's "UniVerse" SQL you can execute it directly, via DSExecute.

If it's not, you can write it to a file as others have noted, then invoke some kind of script to execute it.

Another possibility is to write a routine that is an ODBC client, using a library of functions collectively called the BASIC SQL Client Interface (BCI). However, to use this on a UNIX server you will need to purchase a licensed ODBC driver to connect to the database in question. The drivers supplied with DataStage are licensed only to be used with stages, not with routines.

Search the Forum for more information.