tips and tricks

Hi All;

This is my Informatica 9.5.1 hot fix 2 preinstallation check list for IBM AIX.

1. Download Informatica software 9.5.1 HF1 for AIX from my.informatica.com via Global Customer Support.
2. Get the correct licence files from Global Customer support
3. Install Java version 6.
   http://www.ibm.com/developerworks/java/jdk/aix/service.html#java6
4. Install JDK 1.6.0 build pap6460sr13fp2-20130424_01(SR13 FP2) exactly.
   http://pic.dhe.ibm.com/infocenter/sb2bi/v5r2/index.jsp?topic=%2Fcom.ibm.help.sys_rqmts.doc%2FSysRqmts_DwnldAIXJDK.html
5. There are some problems with AIX TAR, Install GNUTAR.
   http://www.gnu.org/software/tar/
6. Create User for informatica. (ex: infa, infauser, etluser... etc.)
7. Set u limit to 16.000 (or unlimited.)
   set ulimit -n to 16000
8. Create a database user for each Power Designer domain. (ex: INFADOM)
9. Create a database user for each Power Designer repository. (ex: INFAREP)
10. Verify that the database users have CONNECT, RESOURCE, and CREATE VIEW privileges.
11. For Oracle repositories Set the open_cursors parameter to 1000 or higher
12. If exist, open the following port range: 6000 to 6113 on firewall.
13. Do not believe curse of 13.
14. Reserve at least 1GB for Domain and repository database space (both users in the same tablespace)
15. Reserve at least 30GB file system space for installation on the server

For base Informatica 9.5.1 preinstallation checklist please click

Although Power Center shows this as possible, unfortunately you can not move binary or raw columns from source to target normally. You can try relational writer or external loader result is same. Binary/Raw column will be null.
It is impossible to convert this column as well.

Easiest way to do this is changing column definitions to varchar or nvarchar2 in Power Center. You do not have to change it on Database level at all. Data will start to move around as magic :)

find . -exec grep -l "search string" {} \;

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

Simple as is :)

Hi All;

*For Informacitca 9.5.1. Hot fix 2 preistallation check list please click here

This is my Informatica 9.5.1 preinstallation check list for IBM AIX.

1. Download Informatica software 9.5.1 HF1 for AIX from my.informatica.com via Global Customer Support.
2. Get the correct licence files from Global Customer support
3. Install Java version 6.
   http://www.ibm.com/developerworks/java/jdk/aix/service.html#java6
4. Install JDK 1.6.0 build pap6460sr9fp2-20110627_03(SR9 FP2) exactly.
   http://pic.dhe.ibm.com/infocenter/sb2bi/v5r2/index.jsp?topic=%2Fcom.ibm.help.sys_rqmts.doc%2FSysRqmts_DwnldAIXJDK.html
5. There are some problems with AIX TAR, Install GNUTAR.
   http://www.gnu.org/software/tar/
6. Create User for informatica. (ex: infa, infauser, etluser... etc.)
7. Set u limit to 16.000 (or unlimited.)
   set ulimit -n to 16000
8. Create a database user for each Power Designer domain. (ex: INFADOM)
9. Create a database user for each Power Designer repository. (ex: INFAREP)
10. Verify that the database users have CONNECT, RESOURCE, and CREATE VIEW privileges.
11. For Oracle repositories Set the open_cursors parameter to 1000 or higher
12. If exist, open the following port range: 6000 to 6113 on firewall.
13. Do not believe curse of 13.
14. Reserve at least 1GB for Domain and repository database space (both users in the same tablespace)
15. Reserve at least 30GB file system space for installation on the server

It'is realy unique and easy to substract dates in Oracle.

If you select

SELECT TO_DATE ('01/02/2013 11:07:16','DD/MM/YYYY HH24:MI:SS') - TO_DATE('01/02/2013 11:03:37','DD/MM/YYYY HH24:MI:SS') FROM DUAL;

Result is:
0,00253472222222222


To achive same result in Sybase IQ correct syntax is:
SELECT CONVERT(NUMERIC(16,10), DATEDIFF(SECOND,  CONVERT(TIMESTAMP, '2013-02-01 11:03:37'), CONVERT(TIMESTAMP, '2013-02-01 11:07:16'))) / 60 / 60 / 24


DATEDIFF: Datepart, Small date, Big date returns: INT

Tric is converting datediff result to numeric :)