01-24-2014 01:27 AM
Does anybody know, if there is an option to change the default delimiter of external file created by SAS when using Oracle Bulk Loader?
I want to specify comma as the delimiter. SAS version is 9.3 and is installed on Linux.
01-24-2014 02:14 AM
I want to do it because the default delimiter has quite a bit of whitspaces in between fields and as just the size of external file created by SAS can become huge if there are lot of columns
01-26-2014 03:59 AM
I'm not aware of any option where you could tell the bulk-loader to remove trailing blanks.
There are storage systems with real-time compression meaning that blanks or not wouldn't be that relevant in regards of disk I/O (I assume though that network traffic still gets the uncompressed file and though blanks matter).
01-25-2014 11:09 AM
suggested docs are:
SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (Bulk Loading for Oracle)
If you want to give parmeters for SQLoader: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (parfile)
Needing a lot of space for the datafile of SQLloader SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (datafile)
SAS builds that file and does a call to sqlloader (X-cmd needed or internally check bypassed)
01-26-2014 04:29 AM
The best reference for the bulkloader is: Importing Flex Assets with the BulkLoader Utility - 11g Release 1 (126.96.36.199.0) (oracle bulkloader)
There is no comression for a flat file mentioned (step-3)
The only reasons for asking this question could be:
- running out of space on the default location (home) -> relocate that to one big enough
- suspected performance issues -> sequential IO is normal the fastest possible All options like caching and read-ahead are in place for that.
As bulk loading should not be a regular task in data management, it is initial load. There shoul be no issue.
On the other hand when this is your last escape in a bad perfroming DBMS than evaluate the DBMS.
It could be one of the wrong type. A DBMS designed for OLTP is designed and behaivng different than one that is meant for analytics.
Just when you are handling small ammounts of data you won't notice the difference.
01-29-2014 03:21 AM
Just want to comment on bulk-load situations. I've have implemented bulk-load as in some daily (and weekly/monthly as well) loads, like insert of daily/weekly transactions - this is usually the most efficient way to load data into a DW, in most scenarios you can't configure the "normal" SAS/ACCESS engine to load records as fast as bulk-load.
01-30-2014 08:45 PM
01-26-2014 04:34 AM
You do not by accident have issues with NLS or datatypes
SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (datatypes oracle/sas)
01-31-2014 04:14 AM
Thanks all for the responses.
Seems like SAS only exports the data file in fixed poisition format only, which results in increase in size of datafile if there are lots of character columns(as the data value might not occupy the entire length of the column). Storage although is no an issue for me but I was thinking if SAS somehow supported delimiters for the datafile.