BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi,

I use SAS ACCESS interface to Oracle installed on a UNIX server through Enterprise Guide 4.1. When I wanted to create a table in the Oracle database using
-----------------------------------------------------------------------------------------------------------------------------
proc sql;
create table ora_db.person_1 (BULKLOAD=YES BL_OPTIONS='ERRORS=899, LOAD=5000') as
select person_code, first_name, last_name from person_1;
quit;
-----------------------------------------------------------------------------------------------------------------------------

The error message in the log is
--------------------------------------------------------------------------------------
WARNING: Shell escape is not valid in this SAS session.
ERROR: Requested function is not supported.
--------------------------------------------------------------------------------------

Searching the SAS site, I was told that
----------------------------------------------------------------------------------------------------------------------
When you use SAS/ACCESS® Interface to Oracle and you invoke SAS with the -NOXCMD command,
the Oracle bulk loader is automatically disabled and you receive the following error:

WARNING: Shell escape is not valid in this SAS session.
ERROR: Requested function is not supported.
... ... ... ...
----------------------------------------------------------------------------------------------------------------------


How can I enable the bulk uploading function?

Thank you in advance.

myao
10 REPLIES 10
Patrick
Opal | Level 21
Hi Myao
You have to talk to your SAS admin so that he changes the SAS system option from NOXCMD to XCMD.
http://support.sas.com/onlinedoc/913/getDoc/en/hostunx.hlp/a000663635.htm
HTH
Patrick
deleted_user
Not applicable
We don't have SAS admin here. Could you tell me how to do that? Thanks.
Doc_Duke
Rhodochrosite | Level 12
You probably need to call tech support on this one. The tech support web page didn't have anything useful when I searched for <"enterprise guide" noxcmd>.

The noxcmd option is set as part of the initial call to SAS from EGuide, it's not something an administrator has set. (When I run SAS independent of EGuide, the XCMD is allowed, but when I run EGuide, the noxcmd option is in effect.).

Please post your findings; inquiring minds want to know....

Doc Muhlbaier
Duke
LinusH
Tourmaline | Level 20
I believe the following note will apply to this. The key is to add -allowxcmd to your object spawner startup (you are using a workspace server from EG, right?)

http://support.sas.com/kb/14/696.html

Regards,
Linus
Data never sleeps
deleted_user
Not applicable
I did not find the files such as ObjectSpawner.bat on my PC or MetadataServer.sh.
deleted_user
Not applicable
on the UNIX server.
LinusH
Tourmaline | Level 20
Who is you SAS representative (which receives SW and licenses)? For details help how to manage this contact SAS support.

/Linus
Data never sleeps
Patrick
Opal | Level 21
The person/department which maintains the SAS installation at your site has to make this change (needs admin rights - I call such a person SAS admin).
To contact SAS Tech support is no way to go - this is not a defect and SAS Tech support can't do anything else than telling you to ask your SAS admin to enable Shell escape.
HTH
Patrick
deleted_user
Not applicable
Myao,

I would suggest that you use multi-row inserts instead. You can easily set INSERTBUFF option (to smthing like insertbuff=10000) with your libname statement and achieve even better speeds than those with BULKLOADer + you have better ERROR control.

I have had real life experience beating BULKLOADER twice now. Here is also a paper that have similar conclusions. I would suggest that you play around with INSERTBUFF settings – 5000, 10000, 15000, etc. and see what works for you.

www2.sas.com/proceedings/sugi29/106-29.pdf

Hope this helps.
ikp
JMarkW
Fluorite | Level 6
Did you see any improvement in performance by changing the DBCOMMIT settings along with INSERTBUFF?

I'm curious whether I should set them to the same value or not.....

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1326 views
  • 0 likes
  • 5 in conversation