Bulk Loading and Unloading for Aster

Loading

Bulk loading is the fastest way to insert large numbers of rows into an Aster table. You must specify BULKLOAD=YES to use the bulk-load facility. The bulk-load facility uses the Aster loader client application to move data from the client to the Aster database.

Data Set Options with Bulk Loading

Here are the Aster bulk-load data set options. For detailed information about these options, see About the Data Set Options for Relational Databases.

• BL_DATAFILE=
• BL_DATAFILE_PATH=
• BL_DBNAME=
• BL_DELETE_DATAFILE=
• BL_DELIMITER=
• BL_HOST=
• BL_OPTIONS=
• BL_PATH=
• BULKLOAD=
• BULKUNLOAD=

Examples

This example shows how you can use a SAS data set, SASFLT.FLT98, to create and load a large Aster table, FLIGHTS98.

LIBNAME sasflt 'SAS-library';
LIBNAME net_air ASTER user=myusr1 pwd=mypwd1
        server=air2 database=flights dimension=yes;

PROC sql;
create table net_air.flights98
       (bulkload=YES bl_host='queen' bl_path='/home/aster_loader/'
          bl_dbname='beehive')
      as select * from sasflt.flt98;
quit;

You can use BL_OPTIONS= to pass specific Aster options to the bulk-loading process.

You can create the same table using a DATA step.

data net_air.flights98(bulkload=YES bl_host='queen' 
     bl_path='/home/aster_loader/'
     bl_dbname='beehive');
     set sasflt.flt98;
run;

You can then append the SAS data set, SASFLT.FLT98, to the existing Aster table, ALLFLIGHTS. SAS/ACCESS Interface to Aster to write data to a flat file, as specified in the BL_DATAFILE= option. Rather than deleting the data file, BL_DELETE_DATAFILE=NO causes the engine to retain it after the load completes.

PROC append base=net_air.allflights
    (BULKLOAD=YES
     BL_DATAFILE='/tmp/fltdata.dat'
     BL_HOST='queen'
     BL_PATH='/home/aster_loader/'
     BL_DBNAME='beehive'
     BL_DELETE_DATAFILE=NO )
data=sasflt.flt98;
run;

Unloading

Bulk unloading is the fastest way to insert large numbers of rows from an Aster table. To use the bulk-unloading facility, specify BULKUNLOAD=YES. (See BULKUNLOAD=.)The bulk-unloading facility uses the Aster Remote External Table interface to move data from the client to the Aster Performance Server into SAS.

Here are the Aster bulk-unloading data set options:

• BL_DATAFILE=
• BL_DELETE_DATAFILE=
• BL_DELIMITER=
• BL_USE_PIPE=
• BULKLOAD=
• BULKUNLOAD=

Example

This example shows you how you can read a large Aster table to create and populate a SAS data set:

libname db aster  server ='redqueen.unx.sas.com'
     db='accesstesting'  user='dbitest'  password='dbigrp1'  dimension=yes;

proc sql;
select * from db.employees(bulkunload=YES);
create table work.employees as select * from db.employees)bulkunload=YES
     bl_options=' ' BL_DATAFILE='c:\bl_data.dat'  BL_USE_PIPE=NO
     BL_DELETE_DATAFILE=NO);
quit;

proc append base=work.employees  data=db.empployees(bulkunload=YES
     bl_options=' '  BL_DATAFILE='c:\bl_data.dat'   BL_USE_PIPE=NO  
     BL_DELETE_DATAFILE=NO);
run;