topic Append a large dataset to multiple datasets at the same time in SAS Procedures

Append a large dataset to multiple datasets at the same time

Yeti — Thu, 09 Apr 2015 16:28:45 GMT

Greetings,

I have a large dataset that is appended to multiple datasets (i.e. monthly and history oracle tables).

Currently I am using bulkload to load it into these two Oracle tables. It is working just fine but it takes twice the amount of time since it creates the same DAT file again to load to next table.

Is there a way I can append this dataset to multiple datasets at the same time? Are there options to re-use the BL.DAT file without recreating? I googled for answers to no avail.

Thank you very much.

Have a great day.

Naresh

Re: Append a large dataset to multiple datasets at the same time

ballardw — Thu, 09 Apr 2015 17:16:03 GMT

I should think it possible to upload the set to Oracle once and then use pass through SQL to append to the target tables.

Re: Append a large dataset to multiple datasets at the same time

jakarman — Thu, 09 Apr 2015 18:31:06 GMT

It is the bulkload process that is the dedicated fastest Oracle loader SQL-loader that is creating tables at Oracle. http://support.sas.com/resources/papers/proceedings09/099-2009.pdf. Without the loading process is much slower (journaling/logging and commits). It is the SAS (Is it?) creating that dat-file yup SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition
For reuse that dat-fil it must

a/ be able to be kept. BL_DELETE_DATAFILE=NO (yes is default)

b/ reuse that dataset letting SAS not create that .dat file.

I see what you mean there is nothing there to stop that.

The only options would be to start the SLQ-Loader manualy/scripted. SQL*Loader Control File Reference probably not worth all the effort introducing new risks/errors.

Tip: review the BL_DEFAULT_DIR='host-specific-directory-path' as that one should fast and big enough.

Re: Append a large dataset to multiple datasets at the same time

Yeti — Sat, 11 Apr 2015 15:52:14 GMT

Thank you very much gentlemen. I think I am not going to spend too much time on this one.

I will keep bulkload for one process and for another load process, I think will just use INSERTBUFF=32767 buffsize=32000 in the libname option.

Thank you all. Have a great weekend.

Naresh

Re: Append a large dataset to multiple datasets at the same time

Patrick — Sun, 12 Apr 2015 04:39:00 GMT

I believe the "best" way would be to load the source table once via bulk into an Oracle staging table (not sure if it would work with a global temporary table) and then use pass-through SQL with an append hint in a multi-table insert. Something like below:

INSERT /*+APPEND */ ALL INTO <Ora table 1> INTO <Ora table 2> SELECT * from <staging_table>

In case you have table partitioning then loading directly into a partition can also speed up things quite a bit.

"I think will just use INSERTBUFF=32767 buffsize=32000": Make sure you set also DBCOMMIT to something better than the installation default.