Bulk Loading and Unloading for Netezza

Overview of Bulk Loading for Netezza

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

Data Set Options for Bulk Loading

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=

Bulk Loading Examples for Netezza

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-library';
libname net_air netezza user=myuser pwd=mypwd
        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;

Overview of Bulk Unloading for Netezza

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

Data Set Options for Bulk Unloading

Here are the Netezza bulk-unloading data set options.

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

Bulk Unloading Examples for Netezza

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-library';
libname net_air netezza user=myuser pwd=mypwd
        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 bulk unloading process. The LOGDIR option specifies the directory for the Nzbad and Nzlog files to be generated during the bulk unloading.

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 bulk unloading 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;