tips and tricks

Informatica Sybase IQ external loader with client side load option

In default to load data to Sybase IQ with Informatica external loader you have to transfer data to Sybase server.

With following code you can add follwing features to Informatica Sybase IQ External loader

• To be able to load data directly from Informatica Server
• Parallel External load, now instead of getting error, Informatica is waiting other transactions locking target table to finish. (Default unlimited, you can specify wait time if needed)
• Cleaning data, log and ctl files after successful load.

V1.1 updates

• External loader errors are logged file_name.err file

To configure your external loader simply point to this new file

Save following script to your Sybase IQ bin64 directory.
Download link
https://docs.google.com/file/d/0B48wKM149H9fWVFDdUZ6LUNtWWc/edit?usp=sharing

Source Code:

#!/bin/sh
#Special thanks to Jean-Philippe for initial code
print()
{
        printf "$1" >> $LOG_FILE
}
#Change this with your dbisql pathext_load_exec=/sybase/iq/IQ-15_4/bin64/dbisql
connect_str=$2
ctl_file=$4
serv_data_dir=`dirname $ctl_file`
LOG_FILE=$serv_data_dir/dbisql.log
if [ -e $LOG_FILE ]
then
                rm $LOG_FILE
fi
print "ext_load_exec=$ext_load_exec\n"
print "connect_str=$connect_str\n"
print "ctl_file=$ctl_file\n"
print "serv_data_dir=$serv_data_dir\n"

#replacing from with client file to avoid copying data to IQ server
cp $ctl_file ${ctl_file}.bak
cat ${ctl_file}.bak | sed 's/FROM /USING CLIENT FILE /g' > $ctl_file

#Getting data file name to delete after successful  load
ctl_str=`echo $ctl_file`
num=`echo ${#ctl_str}`
let "num-=4"
del_file=`expr substr $ctl_str 1 $num`
#adding lock wait for parallelisimcp $ctl_file ${ctl_file}.bak

#grepping file name
load_str=`egrep "LOAD TABLE" $ctl_file`
num=`echo ${#load_str}`
let "num-=11"
target_table=`expr substr "$load_str" 11 $num`

#adding lock wait
sed -e "10s/$/ \\
lock table  $target_table in write mode wait;/" ${ctl_file}.bak > ${ctl_file}
#adding commit to release table
echo ";commit;" >> ${ctl_file}
print "$ext_load_exec -c \"$connect_str\" $ctl_file\n"
LOG_FILE2=${ctl_file}\.err
$ext_load_exec -nogui -c "$connect_str" -q $ctl_file > $LOG_FILE2 2>&1

res=`ls -ltr $LOG_FILE2 |awk '{print $5}'`

res=$?
if [ $res -eq 0 ]
then
print "result=$res\n"
rm -f ${del_file}*;
exit $res
fi