Date:         Wed, 11 Jul 2007 14:55:56 +1000
Reply-To:     David Birch <David.Birch@DISABILITY.QLD.GOV.AU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         David Birch <David.Birch@DISABILITY.QLD.GOV.AU>
Subject:      Re: Libname to send back to Oracle taking long time
In-Reply-To:  A<CA8F89971ADA9F47A6C915BA2397844207B41D09@MAILBE2.westat.com>
Content-Type: text/plain; charset="us-ascii"

Jerry,

It's some time since I've used SAS/Access for Oracle, but back then I
had similar issues loading data back into Oracle due to SAS defaulting
to a Commit for every insert policy.  Now, you can probably alter the
policy quite easily.  At the time I overcame the problem by using
pass-thru SQL.  However, the BulkLoad method is probably the best
option.

HTH,
Dave.


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Sigurd Hermansen
Sent: Wednesday, 11 July 2007 3:20 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Libname to send back to Oracle taking long time

Jerry:
The BulkLoad method makes the most sense. It bypasses integrity
constraints for each tuple insert in favor of writing integrity
constraint violations to a file.

As an experiment, I'd try an equivalent SQL construct:

select * from (select * from ora_air.ORACLE_TABLE) union all (select *
from PRD_DATA.sas_dataset)
;

Oracle doesn't do UNION's efficiently, so SAS/Access for Oracle may
substitute an equivalent of BulkLoad.

Here's what you have to do to give the UNION the best chance of working:
1. create sas_dataset with the exact structure and integrity constraints
as ORACLE_TABLE;
2. assign date formats;
3. extract primary key values from ORACLE_TABLE and intersect with
equivalent primary key variables in sas_dataset to identify, then
exclude, key integrity violations.

If ORACLE_TABLE has indexes defined, you may need to redo the indexes.
S

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Jerry
Sent: Tuesday, July 10, 2007 12:56 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Libname to send back to Oracle taking long time


Hi,

So I am loading data back into oracle using the libname facilities for
4.5 million records and its taking about 10 hours consistently.

I am using the following:


libname ora_air Oracle user=uid password=xxxx path=OracleDB1; libname
PRD_DATA "\\server\directory\";

proc sql ;
insert into ora_air.ORACLE_TABLE( char1, date1, date2, Num1,
sasdatefmt=(date1='datetime20.',date2='datetime20.'))
select char1, date1, date2, Num1
from PRD_DATA.sas_dataset;
quit;


char1 is a character with length of 39
i have 2 dates values and a number values being sent.

There are 4.5 million records and it is taking me 10 hours!!!

SAS support suggested using BulkLoad, but for some reason our server and
SAS Admins is having trouble getting that to work. Is there any other
suggestions out there?




********************************* DISCLAIMER *********************************
The information contained in the above e-mail message or messages (which includes any attachments) is confidential and may be legally privileged.  It is intended only for the use of the person or entity to which it is addressed.  If you are not the addressee any form of disclosure, copying, modification, distribution or any action taken or omitted in reliance on the information is unauthorised.  Opinions contained in the message(s) do not necessarily reflect the opinions of the Queensland Government and its authorities.  If you received this communication in error, please notify the sender immediately and delete it from your computer system network.