Connect to Database using Job control
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Connect to Database using Job control
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
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
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?
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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.
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
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.ray.wurlod wrote:You could have had a more robust script, that only generated an exit status of 0 if absolutely everything was OK.
The whole ODBC/BCI thing is a PITA. IMHO, of course.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am