DataStage8.7连接远程Oracle数据库
♣参考资料：
http://www-01.ibm.com/support/docview.wss?uid=swg21441132
http://space.itpub.net/6126/viewspace-722164
http://www-01.ibm.com/support/docview.wss?uid=swg21398527
注明：此文档由dl_lang的文章“Datastage8.5连接Oracle数据库”整理而来
1.软件环境
1).DataStage 8.7
2).RHEL6.2
3).Oracle Client11g（R1）
说明：DataStage要连接oracle数据库，通常情况下，有两种方式，一种是ODBC的方式，这种方式不需要安装Oracle Client，但是需要配置ODBC源；第二种方式就是通过Oracle Client，通过在Oracle Client里面配置TNS以访问远程数据库。当然安装Oracle Server也是可以的。
2.问题描述
DataStage client通过连接器Oracle Connector连接Oracle数据库是出现如下的问题:
DataStage无法与此连接器连接
Error loading connector library libccora11g.so.libclntsh.so.11.1:cannot open shared object file:No such file or directory
3.原因分析
没有将Oracle的环境变量写入到DataStage的dsenv文件
4.解决方法
1) 、确保Oracle Client能成功连接Oracle Server（oracle用户登录）
a) 检查TNS配置
以oracle用户登录（我安装oracle client的时候，是创建了oracle用户的。），然后在$ORACLE_HOME/network/admin文件夹配置tnsnames.ora，我的配置如下：
[oracle@is-server admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:/home/oracle/product/11.1.0/cleint_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL_242 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLESERVER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
当然你也可以启动netca配置TNS
b) 确认oracle用户的配置文件.bash_profile已经成功配置了ORACLE_HOME
[oracle@is-server ~]$ cat .bash_profile
# User specific environment and startup programs
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/cleint_1
export ORACLE_SID=orcl
export PATH=H$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:usr/lib
c) 测试Oracle client能连接Oracle Server
[oracle@is-server ~]$ sqlplus scott/tiger@orcl_242
2)、DataStage Server端配置（可以以安装DataStage Server的用户登录,也可以是root用户）
a) 在dsenv文件里面配置相应的参数（其中dsenv文件在$DSHOME）
[root@is-server DSEngine]# echo $DSHOME
/opt/IBM/InformationServer/Server/DSEngine
[root@is-server DSEngine]# cat dsenv
# add by snowmice in 2012-08-30
Export DSHOME=/opt/IBM/InformationServer/Server/DSEngine
export ORACLE_HOME=/home/oracle/product/11.1.0/cleint_1
export TNS_ADMIN=/home/oracle/product/11.1.0/cleint_1/network/admin
export LD_LIBRARY_PATH=$APT_ORCHHOME/bin:$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib
export PATH=$PATH:$ORACLE_HOME/bin:$APT_ORCHHOME/bin
export ORACLE_BASE=/home/oracle
export LIBPATH=$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/lib:$LIBPATH
b) 查找缺失的libccora11g.so的具体位置
[root@is-server DSEngine]# find /opt -name libccora*
/opt/IBM/InformationServer/Server/DSComponents/bin/libccora10g.so
/opt/IBM/InformationServer/Server/DSComponents/bin/libccora11g.so
/opt/IBM/InformationServer/Server/StagingArea/Installed/OracleConnector/Server/linux/libccora10g.so
/opt/IBM/InformationServer/Server/StagingArea/Installed/OracleConnector/Server/linux/libccora11g.so
我们缺少的是libccora11g.so
c) 在lib文件里面建立连接文件
[root@is-server DSEngine]# cd $ORACLE_HOME/lib
[root@is-server lib]# ln -s /opt/IBM/InformationServer/Server/StagingArea/Installed/OracleConnector/Server/linux/libccora11g.so licccora11g.so
[root@is-server lib]# ln –s /opt/IBM/InformationServer/Server/StagingArea/Installed/OracleConnector/Server/linux/libccora10g.so licccora10g.so
d) 然后执行install.liborchoracle
找到install.liborchoracle
[root@is-server DSEngine]# find /opt -name install.liborchoracle
/opt/IBM/InformationServer/Server/DSComponents/install/install.liborchoracle
/opt/IBM/InformationServer/Server/StagingArea/Installed/PxOracle/install/install.liborchoracle
设置需要的环境变量
[root@is-server lib]# export DSHOME=/opt/IBM/InformationServer/Server/DSEngine
[root@is-server lib]# export APT_ORCHHOME=/opt/IBM/InformationServer/Server/PXEngine
更改对应的设置
[root@is-server DSEngine]$ cd /opt/IBM/InformationServer/Server/StagingArea/Installed/PxOracle/install/
[root@is-server install]$ vi install.liborchoracle
找到
install_driver() {
case $version in
9 ) VER='9i';;
10 ) VER='10g';;
0 ) return;;
esac
改成
install_driver() {
case $version in
9 ) VER='9i';;
10|11 ) VER='10g';;
0 ) return;;
esac
然后执行install.liborchoracl
[root@is-server install]#/opt/IBM/InformationServer/Server/StagingArea/Installed/PxOracle/install/install.liborchoracle
Installing Oracle driver.
Please choose the Oracle Driver version you want to install
from the following menu:
9 -> support for Oracel 9i
10 -> supprt for Oracle 10g
0 -> exit.
Enter version: [0]: > 10 --选择10进行安装
{10}
Installing driver for Oracle Version 10g
Oracle driver installation is completed.
e) 测试下动态链接库
[root@is-server install]# cd $ORACLE_HOME/lib
[root@is-server lib]# ls -al libcc*
lrwxrwxrwx 1 root root 99 Aug 30 19:44 libccora11g.so -> /opt/IBM/InformationServer/Server/StagingArea/Installed/OracleConnector/Server/linux/libccora11g.so
打印可执行档依赖的共享库文件
[root@is-server lib]# 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)
……
f) 这个时候我们可以在DataStage client打开“DS Administrator Client”查看环境变量
3)、重新启动DataStage servr服务和IS agent(请注意顺序)
[1] stop DS engine
[root@datastage DSEngine]# cd /opt/IBM/InformationServer/Server/DSEngine/bin
[root@datastage DSEngine]#./uv –admin –stop
[2] stop IS agent
[root@datastage DSEngine]# cd /opt/IBM/InformationServer/ASBNode/bin
[root@datastage DSEngine]#./NodeAgents.sh stop
[3] start IS agent
[root@datastage DSEngine]# cd /opt/IBM/InformationServer/ASBNode/bin
[root@datastage DSEngine]#./NodeAgents.sh start
[4] start DS engine
[root@datastage DSEngine]# cd /opt/IBM/InformationServer/Server/DSEngine/bin
[root@datastage DSEngine]#./uv –admin –start
4)、这个时候，我们就可以在DataStage的Designer client里面连接Oracle数据库了
/**********************************★如果有任何疑问和错误，欢迎大家赐教!★**********************************/
