Skip to content

SAP IQ: Bulk Loading Data from SAS

May 14, 2014

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’
  2. set option public.ALLOW_READ_CLIENT_FILE = ‘ON’

  3. Grant the READCLIENTFILE system permission to the user
  4. grant READCLIENTFILE to myUser

  5. 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.

On SAS

  1. Establish a new SAS/ACCESS connection to SAP IQ
  2. libname dwtest sybaseiq user=&dwuser password=&dwpassword dsn=MySAPIqDb schema=mySchema;

  3. 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.
Advertisements

From → SAP IQ, SAS

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: