datastage连接mysql库_DataStage8.7连接远程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数据库了

/**********************************★如果有任何疑问和错误，欢迎大家赐教!★**********************************/