Connect to Database using Job control

Sreenivasulu · Post by **Sreenivasulu** » Fri Nov 04, 2005 3:50 am

HI ,

We need to connect to database(oracle) using datastage basic language.

I need to use this in the job control tab in job properties i.e i need to invoke "sql plus". Is there any database connection command like ExecSH which we use to execute unix commands.

Appreciate your help

Regards
Sreenivasulu

ArndW · Post by **ArndW** » Fri Nov 04, 2005 4:16 am

Sreenivasulu,

I won't argue with your stating that you "need" to connect to Oracle from the job control; since there are other easier methods of doing this - i.e. writing a dummy job that takes your SQL command as a parameter and uses that in a OCI or ODBC stage.

Since you can run unix scripts from a DSExecute command, what is stopping you from calling the SQLPlus? I've done so without problems in the past. Are you getting an error?

Sreenivasulu · Post by **Sreenivasulu** » Fri Nov 04, 2005 5:03 am

HI Arnd,

I am using datastage server 5 and want to implement recoverability.
i.e if the data has already been loaded for the current batch then truncate the target table and start loading.

Therefore i want to prevent loading if the truncate feature does not work.

Hence i wanted to use the Job control to connect to database and
truncate the target before load. If the truncate fails (due to appropriate keys not found ) then it would not trigger the job loading to the target table.

Earlier we were using a before-job-routine to call a shell script which
did this feature. But the problem is if this script failed then also
the loading job got executed.

Hope you understood my problem

Regards
Sreeni

ray.wurlod · Post by **ray.wurlod** » Fri Nov 04, 2005 6:11 am

You could have had a more robust script, that only generated an exit status of 0 if absolutely everything was OK.

You can use DataStage BASIC functions to access a database, but you will need to purchase an ODBC driver licence. Don't be fooled by the fact that it works with the DataStage-supplied drivers when you first try it - you get 30 days trial, then it suddenly and embarrassingly stops working.

The functions you require form the BASIC SQL Client Interface, or BCI, a library of functions that mimic the ODBC API.

Search the forum for examples, or hire a consultant to create code for you. You can also download a manual on the BCI functions from IBM's UniVerse manualsweb site.

Sreenivasulu · Post by **Sreenivasulu** » Fri Nov 04, 2005 6:35 am

Hi Ray,

I would try this.

Thanks a lot for the suggestions.

Regards
Sreeni

chulett · Post by **chulett** » Fri Nov 04, 2005 8:12 am

ray.wurlod wrote:You could have had a more robust script, that only generated an exit status of 0 if absolutely everything was OK.

This *is* the answer - you need a better script, one that properly communicates what happened in sqlplus back to DataStage. Most times, when people have this issue it's because they haven't done this step and their script only checks whether sqlplus was called correctly - not if the executed sql actually ran ok. Fix that and fix your problem.

The whole ODBC/BCI thing is a PITA. IMHO, of course.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 04, 2005 3:19 pm

Sure is, especially the number of dollars they want for an ODBC driver licence.

Sreenivasulu · Post by **Sreenivasulu** » Tue Nov 08, 2005 5:51 am

HI Roy,

Out client does not want to go for a ODBC license.

I would try to find an alternate way to solve this issue.

Thanks for your suggestions.

Regards
Sreeni

chulett · Post by **chulett** » Tue Nov 08, 2005 7:58 am

As noted earlier, the script you had in place sounds like it would work - you just need to make it a little more 'robust' so that errors are reported properly back to the script caller.