DSXchange

Dear Experts,

We are scheduling our ETL batch to run every early morning at 2AM. Unfortunately for the past 4 days every time the scheduled sequencer kicks in, the job will failed because one of the scheduled job is calling Oracle SQL Loader, but somehow the Scheduled Job doesn't have the correct Oracle path in the environment (from the Director Job Log), an error message 'sqlloader not found'.
If we click and rerun the Main Sequencer to continue/rerun the ETL batch, no problem occurs, the Environment path are also correct.
We have 2 Oracle path in AIX, 11gR1 and 11gR2, but all our .profile for datastage user (only 1 in the server, dsadm) and oracle user (only 1 in the server, oracle), are pointing to the correct path which is 11gR2.
If we schedule the job to run during office hour and we are login to DataStage, the ETL batch runs perfectly as well.
We are suspecting because every 2AM, since no one is login in DS Environment, somehow DS is using the wrong path in the environment, but we just don't know which profile DS is using to run the scheduled job.
Is there any other .profile that we have to set? We have set the Oracle_Home and the lib and the bin inside the .profile of dsadm (which run the DS daemon and scheduled job) and oracle user as well as in dsenv file.
Anybody has the same experience with us? Pleas kindly share your solution or workaround for this.

Thanks...
Endy

How did you schedule the jobs to run?

Is it within the Director scheduler or an external scheduler.

If you did it via Director, then it set up a CRON entry under the user id that created it in Director.

Go look to see if that user has sourced dsenv in their profile so that their CRON execution shell picks up the proper variables.

As noted, the important file is $DSHOME/dsenv rather than .profile. Make sure that ORACLE_HOME (and, if relevant, ORACLE_SID) are correctly set in the dsenv script. Otherwise, introduce them into your jobs as parameters so that they can be set on a job-by-job basis.

Thanks Paul & Ray,
We initially thought the .profile is more important.
After further checking, we realized we've missed to put the ORACLE_HOME/bin and /lib in the dsenv.
Now it works perfectly fine.
Thanks again...

Cheers..
Endy