Datastage 8.5 连接远程Oracle 数据库

[img]http://dl2.iteye.com/upload/attachment/0095/4548/2db99575-cb36-31d7-9512-c856eee36bda.jpg[/img]
工作中需要使用Datastage 把Oracle的数据导到DB2 中，发现IBM 的文档不是很全，
在问了很多人，包括同事老杜、Bryan Zhang， Robert等，终于可以连接Oralce 了，就做了一个纪录，算是备忘吧。
Install environment:
Datastage 8.5
Linux 2.6.18
Remote Oracle Database version is 11g

1) Install Oracle client

Install Oracle client 11g in the DS server with user oracle, my install option is ‘Administrator ‘.
Copy tnsnames.ora file from the Oracle server to $ORACLE_HOME/network/admin. Please pay attention to the HOST, we can use IP or HOSTNAME.

[oracle@datastage admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: C:\app\ligj\product\11.2.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORAJERRY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IBM-RS05L7GG9A3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORAJERRY)
)
)



Add ORACLE_HOME in .bash_profile under oracle user.

Login oracle server with:
Sqlplus usera/pwd@ORAJERRY

2) Configuration on DS server

Add those parameters in the dsenv under DSHOME, following is my:
DSHOME=/opt/IBM/InformationServer/Server/DSEngine

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/client_1
export TNS_ADMIN=/home/oracle/app/oracle/product/11.2.0/client_1/network/admin
export LD_LIBRARY_PATH=$APT_ORCHHOME/lib:$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORAC LE_HOME/rdbms/lib
export PATH=$PATH:$ORACLE_HOME/bin:$APT_ORCHHOME/bin
export ORACLE_BASE=/home/oracle/app
export LIBPATH=$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/lib:$LIBPATH

# find /opt -name install.liborchoracle
/opt/IBM/InformationServer/Server/StagingArea/Installed/PxOracle/install/install.liborchoracle
/opt/IBM/InformationServer/Server/DSComponents/install/install.liborchoracle

# find /opt -name libccora*
/opt/IBM/InformationServer/Server/StagingArea/Installed/OracleConnector/Server/linux/libccora11g.so
/opt/IBM/InformationServer/Server/StagingArea/Installed/OracleConnector/Server/linux/libccora10g.so
/opt/IBM/InformationServer/Server/DSComponents/bin/libccora11g.so
/opt/IBM/InformationServer/Server/DSComponents/bin/libccora10g.so

My error message are ‘Can’t find libccora10g.so’

# cd $ORACLE_HOME/lib
#ln –s /opt/IBM/InformationServer/Server/StagingArea/Installed/OracleConnector/Server/linux/libccora11g.so licccora11g.so
#ln –s /opt/IBM/InformationServer/Server/StagingArea/Installed/OracleConnector/Server/linux/libccora10g.so licccora10g.so

Then execute the ‘/opt/IBM/InformationServer/Server/StagingArea/Installed/PxOracle/install/install.liborchoracle’ with root user.

# export DSHOME=/opt/IBM/InformationServer/Server/DSEngine

# export APT_ORCHHOME=/opt/IBM/InformationServer/Server/PXEngine

# vi /opt/IBM/InformationServer/Server/DSComponents/install/install.liborchoracle

Alter this:
install_driver() {
case $version in
9 ) VER='9i';;
10 ) VER='10g';;
0 ) return;;
esac
To

install_driver() {
case $version in
9 ) VER='9i';;
10|11 ) VER='10g';;
0 ) return;;
esac

Then can support Oracle 11g client

Execute it

# /opt/IBM/InformationServer/Server/DSComponents/install/install.liborchoracle
Installing Oracle driver.
Installing driver for Oracle Version 10g
Oracle driver installation is completed.

Test the dynamic library

# cd $ORACLE_HOME/lib
# ls -al libcc*
lrwxrwxrwx 1 oracle dba 65 Apr 24 17:26 libccora10g.so -> /opt/IBM/InformationServer/Server/DSComponents/bin/libccora10g.so
lrwxrwxrwx 1 oracle dba 65 Apr 24 17:25 libccora11g.so -> /opt/IBM/InformationServer/Server/DSComponents/bin/libccora11g.so
# ldd libccora10g.so
linux-vdso.so.1 => (0x00007fff5d960000)
/opt/IBM/InformationServer/Server/DSComponents/lib/libicui18n.so (0x00002af26ea97000)
libicuio.so.32 => /opt/IBM/InformationServer/Server/PXEngine/lib/libicuio.so.32 (0x00002af26ecb5000)
libicuuc.so.32 => /opt/IBM/InformationServer/Server/PXEngine/lib/libicuuc.so.32 (0x00002af26edc1000)
libfcl.so => /opt/IBM/InformationServer/Server/PXEngine/lib/libfcl.so (0x00002af26efaf000)
libclntsh.so.10.1 => /home/oracle/app/oracle/product/11.2.0/client_1/lib/libclntsh.so.10.1 (0x00002af26f2b3000)
libstdc++.so.6 => /opt/IBM/InformationServer/ASBNode/apps/proxy/cpp/linux-all-x86_64/libstdc++.so.6 (0x00002af2718dd000)
libm.so.6 => /lib64/libm.so.6 (0x00002af271af9000)
libc.so.6 => /lib64/libc.so.6 (0x00002af271d7c000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00002af2720d3000)
libicudata.so.32 => /opt/IBM/InformationServer/Server/PXEngine/lib/libicudata.so.32 (0x00002af2722e2000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00002af273071000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00002af27328d000)
libdl.so.2 => /lib64/libdl.so.2 (0x00002af2734a5000)
libicutu.so.32 => /opt/IBM/InformationServer/Server/PXEngine/lib/libicutu.so.32 (0x00002af2736a9000)
libxml4c.so.56 => /opt/IBM/InformationServer/Server/DSComponents/lib/libxml4c.so.56 (0x00002af2737b8000)
liborchx86_64.so => /opt/IBM/InformationServer/Server/PXEngine/lib/liborchx86_64.so (0x00002af273c56000)
liborchcorex86_64.so => /opt/IBM/InformationServer/Server/PXEngine/lib/liborchcorex86_64.so (0x00002af2747d5000)
libnnz11.so => /home/oracle/app/oracle/product/11.2.0/client_1/lib/libnnz11.so (0x00002af274a23000)
libaio.so.1 => /usr/lib64/libaio.so.1 (0x00002af274deb000)
/lib64/ld-linux-x86-64.so.2 (0x00000032b4a00000)
liborchmonitorx86_64.so => /opt/IBM/InformationServer/Server/PXEngine/lib/liborchmonitorx86_64.so (0x00002af274fed000)
# ldd libccora11g.so
linux-vdso.so.1 => (0x00007fffe5ffd000)
/opt/IBM/InformationServer/Server/DSComponents/lib/libicui18n.so (0x00002b838afc7000)
libicuio.so.32 => /opt/IBM/InformationServer/Server/PXEngine/lib/libicuio.so.32 (0x00002b838b1e5000)
libicuuc.so.32 => /opt/IBM/InformationServer/Server/PXEngine/lib/libicuuc.so.32 (0x00002b838b2f1000)
libfcl.so => /opt/IBM/InformationServer/Server/PXEngine/lib/libfcl.so (0x00002b838b4df000)
libclntsh.so.11.1 => /home/oracle/app/oracle/product/11.2.0/client_1/lib/libclntsh.so.11.1 (0x00002b838b7e3000)
libstdc++.so.6 => /opt/IBM/InformationServer/ASBNode/apps/proxy/cpp/linux-all-x86_64/libstdc++.so.6 (0x00002b838de0d000)
libm.so.6 => /lib64/libm.so.6 (0x00002b838e029000)
libc.so.6 => /lib64/libc.so.6 (0x00002b838e2ac000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00002b838e603000)
libicudata.so.32 => /opt/IBM/InformationServer/Server/PXEngine/lib/libicudata.so.32 (0x00002b838e812000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00002b838f5a1000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00002b838f7bd000)
libdl.so.2 => /lib64/libdl.so.2 (0x00002b838f9d5000)
libicutu.so.32 => /opt/IBM/InformationServer/Server/PXEngine/lib/libicutu.so.32 (0x00002b838fbd9000)
libxml4c.so.56 => /opt/IBM/InformationServer/Server/DSComponents/lib/libxml4c.so.56 (0x00002b838fce8000)
liborchx86_64.so => /opt/IBM/InformationServer/Server/PXEngine/lib/liborchx86_64.so (0x00002b8390186000)
liborchcorex86_64.so => /opt/IBM/InformationServer/Server/PXEngine/lib/liborchcorex86_64.so (0x00002b8390d05000)
libnnz11.so => /home/oracle/app/oracle/product/11.2.0/client_1/lib/libnnz11.so (0x00002b8390f53000)
libaio.so.1 => /usr/lib64/libaio.so.1 (0x00002b839131b000)
/lib64/ld-linux-x86-64.so.2 (0x00000032b4a00000)
liborchmonitorx86_64.so => /opt/IBM/InformationServer/Server/PXEngine/lib/liborchmonitorx86_64.so (0x00002b839151d000)
#

Open ‘DS Administrator Client’
Click ‘Environment’ , go to

3) Restart DS server and IS agent:

1) stop DS engine
# cd /opt/IBM/InformationServer/Server/DSEngine/bin
#./uv –admin –stop
2) stop IS agent
# cd /opt/IBM/InformationServer/ASBNode/bin
#./NodeAgents.sh stop
3) start IS agent
# cd /opt/IBM/InformationServer/ASBNode/bin
#./NodeAgents.sh start
4) start DS engine
# cd /opt/IBM/InformationServer/Server/DSEngine/bin
#./uv –admin –start


Then I can connect to Oracle in Designer.