Architecting, installing and maintaining your SAS environment

SASBatch - problem with Oracle libnames

Reply
Occasional Contributor
Posts: 17

SASBatch - problem with Oracle libnames

 Hi folks,

 

I have a problem with assigning Oracle libraries when run sasbatch commant.

 

The problem is as follows. I created a program, and I would like to run this program in a cron. So to crontab I added a line to execute this program, like:

00 12 * * 1-5 nohup /opt/sas/config/Lev1/SASApp/BatchServer/sasbatch.sh -log (...) -batch -noterminal -logparm "rollover=session"  -sysin (...) &

But unfortunatelly, I got an error when SASBatch try to assign Oracle libref:

ERROR: Libref XXX failed to assign from logical server.
ERROR: ORACLE connection error: ORA-12154 (...)

So my question is: how should I configure my environment to run SASBatch like for example typical SAS EG session, when assignement of this libraries are automatic? (I mean: the same library is assigned from SAS EG without any problem, but when I run SASBatch, this problem occures.)

 

Many thanks in advance!

New Contributor
Posts: 4

Re: SASBatch - problem with Oracle libnames

It looks like the job is trying to assign the library but isn't getting the tns alias correct. "I think" cron will run as root user rather than your own user.

 

Check the metadata permissions for the root user registered in metadata to make sure it has read access to the server, auth domain and library definitions.

 

Could be wrong but this does look like you are missing the read metadata for the oracle server definition.

Super User
Super User
Posts: 7,948

Re: SASBatch - problem with Oracle libnames

Most likely the issue is that cron jobs do not normally source your startup files. So your default settings for environment variables are not right. So commands that work when you open a command shell and run them don't work right when run  via crontab entries.

 

Normally what I do is schedule shell scripts to run via CRON.  Then I can include in the shell script the code to set the environment. Usually just by sourcing my profile.

 

Something like this

#!/bin/ksh
#----------------------------------------------------------------------
# cdr_npid.cron
#----------------------------------------------------------------------
. /home/abernt/.profile
. /home/abernt/.kshrc
#
# Set ORACLE_HOME as not set in system startup files
export ORACLE_HOME=/Volumes/app/oracle/product/9.2.0.5_64
#
#----------------------------------------------------------------------
# Check CDR metadata for subjects and observations against SAS datasets
#----------------------------------------------------------------------
cd /Repository/Admin_reports/npid_check
sasi -noterminal -rsasuser cdr_npid_check
SAS Employee
Posts: 16

Re: SASBatch - problem with Oracle libnames

This is because cron runs as root by default.  You can use 

 

sudo su sasuser -c "/opt/sas/config/Lev1/SASApp/BatchServer/sasbatch.sh -log (...) -batch -noterminal -logparm "rollover=session"  -sysin (...) &"

 and 'sasuser' will run the command instead.  if it is OK to run the command as root then I agree with Tom that sourcing the correct profiles will allow root to access a users environment. 

Trusted Advisor
Posts: 1,758

Re: SASBatch - problem with Oracle libnames

Hello @ssomak,

 

what @Andy547 mentions is correct. Since it runs in a different shell, and as privileged user, the session will miss several environment variables.

 

Hence, one option is to force the command to run under the desired user, and the user will have the environment variables, or you can add the missing variables, for the batch sessions, in your config files https://docs.oracle.com/cd/B19306_01/server.102/b14219/net12150.htm

 

Super User
Posts: 9,941

Re: SASBatch - problem with Oracle libnames

Posted in reply to JuanS_OCS

Sorry, but @JuanS_OCS and @Andy547 are both wrong here. Every user on a UNIX System has their own crontab file in /var/spool/cron/crontabs.

cron jobs are always run with the userid that issued the crontab (or at) command.

So, if user xxxx issues crontab and makes a crontab entry, that job will be run with user xxxx when the time comes, and the entry can be found in /var/spool/cron/crontabs/xxxx. cron itself needs to run as superuser so that the user-switch is seamless; /var/spool/cron/crontabs is read/writable only by root and the system group cron.

 

What is important is where you put your Oracle-specific environment variables. If they're not in $APPSERVER_ROOT/appservercontext_env_usermods.sh, then the sasbatch.sh can't pick them up. So make sure that your Oracle specifics are in a high enough place in the configuration tree that all supposed SAS servers/processes can pick them up for a given context.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Trusted Advisor
Posts: 1,758

Re: SASBatch - problem with Oracle libnames

[ Edited ]
Posted in reply to KurtBremser

Hello @KurtBremser,

 

first of all, thank you for your comment. In many case the corrections are very positive, for everyone.

 

While in this case I think we are not wrong here (you can google for cron environmentvariables at any time) , I agree, as mentioned on my previous post, that adding the variables to the SAS config files would work the best, and then your SAS sessions are user variables-independent.

 

The other option is more in regards that not every environment/company allow to do this by policies. Hence, some companies still need to add DB variables in the scope of the user session. Which makes sense regarding security.

Super User
Posts: 9,941

Re: SASBatch - problem with Oracle libnames

Posted in reply to JuanS_OCS

Here I have to beg to differ:

 

I quote from your post:

"Since it runs in a different shell, and as privileged user"

 

And from @Andy547:

"This is because cron runs as root by default."

 

Now, while the cron process itself has to run as superuser (so that it can switch to any other user seamlessly), only those cron jobs that have been submitted by the superuser itself will run as root. All others will run under the userid that submitted them, period.

What is true is that cron jobs won't pick up the user's .profile, so they work with the system-wide entries in /etc/environment and /etc/profile.

 

If one does not want to add certain things to the sasbatch_usermods.sh, one can still implement code there that looks for something (say oracle.sh) in the current users's $HOME and sources it if found:

if [ -e $HOME/oracle.sh ]
then
. $HOME/oracle.sh
fi

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
SAS Employee
Posts: 16

Re: SASBatch - problem with Oracle libnames

Posted in reply to KurtBremser

KurtBremser.  I think we are agreeing.  The problem is in the original question.  I should have asked; "Are you using the Linux system wide cron tables (like /etc/cron/daily) or are you adding the jobs yourself using the crontab command from your userid?"  The way this is done makes a difference to a helpful answer.  One runs as root by default the other runs as the submitting user by default.  I assumed he meant crontab when he said crontab.  You assumed he meant crontab when he said crontab. 

 

So all three of us are correct depending on how you read the original question.  If he is running as a user using the crontab command I like your answer best.  If from /etc/cron/... I like using su best as it runs the user's .profile and gets the correct environment. 

 

I like the addition of the extra source or an oracle.sh if it is available for a user.  it could be in the default profile or the sasbatch_usermods.sh.  Very elegant solution. 

 

Super User
Posts: 9,941

Re: SASBatch - problem with Oracle libnames

I see. Since AIX (which I'm working on) has no /etc/cron directory and therefore no "system-wide" tables (and the AIX cron would not support that anyway), cron jobs are always run with the submitting user's id. All superuser jobs are in /var/spool/cron/crontabs/root.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
SAS Employee
Posts: 16

Re: SASBatch - problem with Oracle libnames

I have a question;  Do you use the linux crontab command or did you add the sasbatch command to the system wide /etc/cron/... entries.  it makes a difference in how you can get this to work.  Thinking about it, I assume you are using the crontab command.  Is that correct?  If so then Kurt's answer is best.  If not then some version of adding 'su' to switch to a user that has access to Oracle may be best.  

Ask a Question
Discussion stats
  • 10 replies
  • 271 views
  • 1 like
  • 6 in conversation