Bulk loading data to remote Sybase IQ 15.X Server using IBM Infosphere DataStage

Prior to Sybase IQ version 15.X the bulk loading of data was restricted to local systems because of  the mechanism of the LOAD TABLE command.

With the release of Sybase IQ 15, a new feature client side load has been introduced which allows LOAD TABLE command to pull data from remote data files.

Support for this feature has been built into the Sybase Enterprise Stage to provide users of Infosphere DataStage the capability to load data from DataStage to remote Sybase IQ servers. Sybase Enterprise Stage requires few steps to enable bulk loading to remote Sybase IQ server. 

Pre-requisites to using Sybase Enterprise Stage to load data into table on remote Sybase IQ server

 

1. Check that the Sybase IQ server version is 15.1 ESD1 or above : The capability to allow load table from remote data files which are FIFOs is only available from Sybase IQ 15.1 ESD1 onwards. As Sybase Enterprise Stage uses data files which are FIFOs the functionality can only be supported when connecting to Sybase IQ 15.1 ESD1.

2. Install Sybase Open Client SDK 15.5 on Infosphere DataStage engine tier and configure the $SYBASE/interfaces file to add the remote Sybase IQ server ipaddress and port number. The Sybase Enterprise Stage is based on Sybase Open Client API's. However the remote client load feature is only enabled for ODBC interface to Sybase IQ. Hence the Sybase Enterprise Stage converts the supplied Sybase IQ server name to equivalent ODBC Connection String. For this it gets the CS_CONNECTED_ADDR option for the connection using ct_con_props.

3. Install the Sybase IQ network client 15.1 ESD1 : This software installs the Sybase IQ ODBC driver and the dbisql utility, which supports the new client side load feature.

Enable remote client load feature on Sybase IQ 15.X server

 

Once the pre-requistes are met, the following needs to be done to enable the remote client load feature on Sybase IQ server.

1. Enable the allow_read_client_file and allow_write_client_file options of the Sybase IQ 15.X server. Set these options once on every IQ server. Enable the allow_read_client_file server option property using the isql or dbisql utility:

   set option allow_read_client_file=on
   set option allow_write_client_file=on   

   GRANT READCLIENTFILE TO <group | user>

Configure Sybase Enterprise Stage to perform bulk load to remote Sybase IQ 15.X server

 

1.Use the script $DSHOME/../DSComponents/install/install.liborchsybase to change current Sybase Enterprise library version to 15. By default the Sybase Enterprise library version is 12.

2. On the DataStage Server, define a environment variable APT_SYBASE_REMOTE_LOAD and set it to When this environment variable is set, the Sybase Enterprise Stage will try to load the given table using the new syntax 'LOAD TABLE <mytable> USING CLIENT FILE'.

3.On the DataStage Server, set the environment variables to include Sybase IQ
SYBASE=<Path to SDK 15.5>
SYBASE_OCS=OCS-15_0
PATH=$SYBASE/$SYBASE_OCS/bin:<Sybase IQ network client>/IQ-15_2/bin
LIBPATH=$SYBASE/$SYBASE_OCS/lib:<Sybase IQ network client>/IQ-15_2/lib

Now the Sybase Enterprise Stage is ready to load data into the remote Sybase IQ server.

4.Create a Parallel job with Sybase Enterprise Stage as target stage.
Set write Method=IQ Write.

Disclaimer: "The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions"