topic PROC DBLOAD - Question and Alternatives? in SAS Procedures

PROC DBLOAD - Question and Alternatives?

FriedEgg — Wed, 23 Mar 2011 18:02:06 GMT

Hi,

I want to load sas datasets into a Oracle database using a specific tablespace. Here is what I am trying:

proc dbload dbms=oracle data=sashelp.bweight(keep=weight black);
user=test_user;
orapw=test_pass;
path=test_db;
table='PROC_DBLOAD_TEST';
tablespace=test_tablespace;
load;
run;

My first problem is the following:

NOTE: Load ended due to limits. Examine statistics below.
NOTE: Limits - Insert (5000), Error (100), Commit (1000)
NOTE: Inserted (5000) rows into table (mk_prod_dbload_test)
NOTE: Rejected (0) insert attempts see the log for details.

I cannot find information on how to set these limits, I also do not understand why there are error's being reported.

Are there alternatives to using the dbload procedure where I can still define a tablespace?

Thanks,
Matt

Re: PROC DBLOAD - Question and Alternatives?

Ksharp — Thu, 24 Mar 2011 03:43:47 GMT

Hi.
I am not sure whether it yield your problem.
As I remeber. proc dbload has limited for loaded rows.Default is 1000 rows (I have forgotten), and there is an option can maximize the default rows.I have forgotten ,You can refer to documentation.

Ksharp

Re: PROC DBLOAD - Question and Alternatives?

Oleg_L — Thu, 24 Mar 2011 10:06:41 GMT

To set the limit add limit= statement before load statement.

limit=1000000;

Re: PROC DBLOAD - Question and Alternatives?

FriedEgg — Thu, 24 Mar 2011 14:54:03 GMT

Thanks Oleg_L, I totally blanked on the doc sheet here because it is clear as day now. Any idea of a alternative to this procedure for loading to a given tablespace?

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003361011.htm

http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_91/access_oracle_7367.pdf Message was edited by: Fried Egg

Re: PROC DBLOAD - Question and Alternatives?

LinusH — Thu, 24 Mar 2011 18:06:44 GMT

Using the ACCESS LIBANME engine, you can specify tablespace as an option:

http://support.sas.com/kb/5/928.html

The preferred way to load (large) Oracle is usally by using Oracle bulk loader. See the documentation for syntax and examples.

/Linus

Re: PROC DBLOAD - Question and Alternatives?

FriedEgg — Thu, 24 Mar 2011 18:13:45 GMT

Linus, thanks, this is exactly what I was looking for! I do use the Oracle bulk loading on large data sets (typically several million rows or more) but for smaller tables was looking for this alternative.