SAS/ACCESS Interface to Netezza

Bulk Loading and Unloading for Netezza

Overview

Bulk loading is the fastest way to insert large numbers of rows into a Netezza table. To use the bulk-load facility, specify BULKLOAD=YES. The bulk-load facility uses the Netezza Remote External Table interface to move data from the client to the Netezza Performance Server.

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

BL_DATAFILE=
BL_DELETE_DATAFILE=
BL_DELIMITER=
BL_OPTIONS=
BL_USE_PIPE=
BULKLOAD=

Examples

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

libname sasflt 'SAS-data-library';
libname net_air netezza user=louis pwd=fromage
        server=air2 database=flights;

proc sql;
create table net_air.flights98
       (bulkload=YES bl_options='logdir "c:\temp\netlogs"') 
        as select * from sasflt.flt98;
quit;

You can use BL_OPTIONS= to pass specific Netezza options to the bulk-loading process. The logdir option specifies the directory for the nzbad and nzlog files to be generated during the load.

This next example shows how you can append the SAS data set, SASFLT.FLT98, to the existing Netezza table, ALLFLIGHTS. The BL_USE_PIPE=NO option forces SAS/ACCESS Interface to Netezza 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 leave it after the load has completed.

proc append base=net_air.allflights
 (BULKLOAD=YES
  BL_DATAFILE='/tmp/fltdata.dat'
  BL_USE_PIPE=NO
  BL_DELETE_DATAFILE=NO)
data=sasflt.flt98;
run;

Unloading

Overview

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

Here are the Netezza bulk-unload data set options:

BL_DATAFILE=

BL_DELETE_DATAFILE=

BL_DELIMITER=

BL_OPTIONS=

BL_USE_PIPE=

BULKLOAD=

Examples

This first example shows how you can read the large Netezza table, FLIGHTS98, to create and populate a SAS data set, SASFLT.FLT98:

libname sasflt 'SAS-data-library';
libname net_air netezza user=louis pwd=fromage
        server=air2 database=flights;

proc sql;
create table sasflt.flt98
        as select * from net_air.flights98
       (bulkunload=YES bl_options='logdir "c:\temp\netlogs"');
quit;

You can use BL_OPTIONS= to pass specific Netezza options to the unload process. The logdir option specifies the directory for the nzbad and nzlog files to be generated during the unload.

This next example shows how you can append the contents of the Netezza table, ALLFLIGHTS, to an existing SAS data set, SASFLT.FLT98. The BL_USE_PIPE=NO option forces SAS/ACCESS Interface to Netezza to read data from a flat file, as specified in the BL_DATAFILE= option. Rather than deleting the data file, BL_DELETE_DATAFILE=NO causes the engine to leave it after the unload has completed.

proc append base=sasflt.flt98
data=net_air.allflights
 (BULKUNLOAD=YES
  BL_DATAFILE='/tmp/fltdata.dat'
  BL_USE_PIPE=NO
  BL_DELETE_DATAFILE=NO);
run;

Top of Page