Bulk Loading for SAP IQ

Loading

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

Data Set Options for Bulk Loading

Here are the SAP IQ bulk-load data set options.

• BL_CLIENT_DATAFILE=
• BL_COLUMN_DELIMITER= (alias BL_DELIMITER=)
• BL_DATAFILE=
• BL_DELETE_DATAFILE=
• BL_OPTIONS=
• BL_ROW_DELIMITER=
• BL_SERVER_DATAFILE=
• BL_USE_PIPE=
• BULKLOAD=

Bulk Loading Examples

In this example, the SASFLT.FLT98 SAS data set creates and loads FLIGHTS98, a large SAP IQ table. For SAP IQ 12.x, this works only when the SAP IQ server is on the same server as your SAS session.

libname sasflt 'SAS-library';
libname mydblib sapiq host=iqsvr1 server=iqsrv1_users
    db=users user=iqusr1 password=iqpwd1;

proc sql;
create table mydblib.flights98
       (bulkload=YES)
        as select * from sasflt.flt98;
quit;

When the SAP IQ server and your SAS session are not on the same server, you need to include additional options, as shown in this example.

libname sasflt 'SAS-library';
libname mydblib sapiq host=iqsvr1 server=iqsrv1_users
    db=users user=iqusr1 password=iqpwd1;
proc sql;
create table mydblib.flights98
 (  BULKLOAD=YES
   BL_USE_PIPE=NO
    BL_SERVER_DATAFILE='/tmp/fltdata.dat'
    BL_CLIENT_DATAFILE='/tmp/fltdata.dat' )
 as select * from sasflt.flt98;
quit;

In this example, you can append the SASFLT.FLT98 SAS data set to the existing SAP IQ table, ALLFLIGHTS. The BL_USE_PIPE=NO option forces SAS/ACCESS Interface to SAP IQ 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=mydblib.allflights
     (BULKLOAD=YES
       BL_DATAFILE='/tmp/fltdata.dat'
       BL_USE_PIPE=NO
       BL_DELETE_DATAFILE=NO)
data=sasflt.flt98;
run;