topic Re: How to enable Oracle bulk loader in SAS Enterprise Guide

How to enable Oracle bulk loader

deleted_user — Fri, 29 Aug 2008 15:29:31 GMT

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

Re: How to enable Oracle bulk loader

Patrick — Sat, 30 Aug 2008 02:44:28 GMT

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

Re: How to enable Oracle bulk loader

deleted_user — Tue, 16 Sep 2008 20:22:31 GMT

We don't have SAS admin here. Could you tell me how to do that? Thanks.

Re: How to enable Oracle bulk loader

Doc_Duke — Wed, 17 Sep 2008 01:11:22 GMT

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

Re: How to enable Oracle bulk loader

LinusH — Wed, 17 Sep 2008 06:55:16 GMT

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

Re: How to enable Oracle bulk loader

deleted_user — Wed, 17 Sep 2008 20:48:58 GMT

I did not find the files such as ObjectSpawner.bat on my PC or MetadataServer.sh.

Re: How to enable Oracle bulk loader

deleted_user — Wed, 17 Sep 2008 20:49:46 GMT

on the UNIX server.

Re: How to enable Oracle bulk loader

LinusH — Thu, 18 Sep 2008 11:16:22 GMT

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

/Linus

Re: How to enable Oracle bulk loader

Patrick — Fri, 19 Sep 2008 13:04:31 GMT

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

Re: How to enable Oracle bulk loader

deleted_user — Sat, 20 Sep 2008 02:26:24 GMT

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

Re: How to enable Oracle bulk loader

JMarkW — Thu, 15 Jan 2009 13:58:54 GMT

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.....