SAP IQ: Bulk Loading Data from SAS

[Former Member]

During the migration of SAP IQ 15.2 ESD#3 to IQ 16.0 SP3 I experienced a considerable increase in the time required to load dataset from SAS 9.3 on PCs to SAP IQ 16.0 SP3 on a Solaris server using the insert command.  The load of 72K rows when from 1hr to 4hrs and the SAP IQ 16 server was responding slowly to other users.  When trying to use RLV to load the table from SAS, the server stop responding after all the rows where loaded in SAP IQ memory, the commit and the RLV store merge never happened and it was necessary to hot recycle SAP IQ (kill the Unix process and recover the database). I also notice that when number of rows to be loaded where under 10K, the insert performed well.

To solve this problem I worked with Laura Rodriques from The Ottawa Hospital, who provide expertise with SAS and  Saroj Bagai, Support Architect at SAP Active Global Support.

Configure SAP IQ

1. If not already done, set the option ALLOW_READ_CLIENT_FILE to ‘ON’

set option public.ALLOW_READ_CLIENT_FILE = 'ON'

2. Grant the READCLIENTFILE system permission to the user

grant READCLIENTFILE to myUser

3. Grant the load permission to user on the table to load.

grant load on mySchema.MyTable to myUser

Note: RLV is disable on the table in SAP IQ.

On you PC

1. Install the SAP IQ ODBC driver.
2. Create an ODBC Data Source Name to the SAP IQ database.

From SAS

1. Establish a new SAS/ACCESS connection to SAP IQ

libname dwtest sybaseiq user=&dwuser password=&dwpassword dsn=MySAPIqDb schema=mySchema;

2. Use “proc append” to load the data from a local work library in SAS.

proc append base=dwtest.myTable

     (BULKLOAD=YES

       BL_USE_PIPE=NO

       BL_DELETE_DATAFILE=NO)

data=work.mySASDataset;

run;

Where:

base= the table in the SAP IQ database that we are loading the data into.

BULKLOAD=YES calls a DBMS-specific bulk-load facility to insert or append rows to a DBMS table

BL_USE_PIP=NO specifies that a flat file is used to transfer data

BL_DELETE_DATAFILE=NO does not delete these files [saves a .dat file in the SAS User’s home directory; e.g. BL_mySASDataset_1715082846.dat]

data= the library and dataset of the data to be loaded to the SAP IQ Table

Results

Using this process the load of the 72K rows from SAS to IQ was reduced to around 4 seconds.

References:

• SAS Institute Inc. (2010) SAS/ACCESS®9.2 for Relational Databases, Reference, Fourth Edition. Cary, NC, USA: SAS Publishing.
• Laura Rodrigues. (2014) CC Dashboard Data Load Process. Unpublished document.
• SAP AG, (2013) Reference: Statements and Options: SAP Sybase IQ 16.0 SP03. Germany, SAP group.

This post was first published in my personal blog at SAP IQ: Bulk Loading Data from SAS | ARBIME