Desktop productivity for business analysts and programmers

How to enable Oracle bulk loader

Reply
N/A
Posts: 0

How to enable Oracle bulk loader

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
Respected Advisor
Posts: 4,132

Re: How to enable Oracle bulk loader

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
N/A
Posts: 0

Re: How to enable Oracle bulk loader

We don't have SAS admin here. Could you tell me how to do that? Thanks.
Trusted Advisor
Posts: 2,114

Re: How to enable Oracle bulk loader

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
Super User
Posts: 5,386

Re: How to enable Oracle bulk loader

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
N/A
Posts: 0

Re: How to enable Oracle bulk loader

I did not find the files such as ObjectSpawner.bat on my PC or MetadataServer.sh.
N/A
Posts: 0

Re: How to enable Oracle bulk loader

on the UNIX server.
Super User
Posts: 5,386

Re: How to enable Oracle bulk loader

Who is you SAS representative (which receives SW and licenses)? For details help how to manage this contact SAS support.

/Linus
Data never sleeps
Respected Advisor
Posts: 4,132

Re: How to enable Oracle bulk loader

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
N/A
Posts: 0

Re: How to enable Oracle bulk loader

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
Contributor
Posts: 55

Re: How to enable Oracle bulk loader

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.....
Ask a Question
Discussion stats
  • 10 replies
  • 186 views
  • 0 likes
  • 5 in conversation