Datastage8.5 connect to a remote Oracle database

Source: Internet
Author: User
In work, we need to use Datastage to import Oracle Data to DB2. We found that IBM's documents are not comprehensive,

In work, we need to use Datastage to import Oracle Data to DB2. We found that IBM's documents are not comprehensive,

In work, we need to use Datastage to import Oracle Data to DB2. We found that IBM documents are not very comprehensive,
I asked a lot of people, including my colleagues, such as Lao du, Bryan Zhang, and Robert, to finally connect to Oralce. I just made a record, which is a memo.
Install environment:
Datastage 1, 8.5
Linux 2.6.18
Remote Oracle Database version is 11 GB

1) Install Oracle client

Install Oracle client 11g in the DS server with user oracle, my install option is 'admin '.
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:
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

[Root @ datastage DSEngine] # find/opt-name install. liborchoracle
/Opt/IBM/InformationServer/Server/StagingArea/Installed/PxOracle/install. liborchoracle
/Opt/IBM/InformationServer/Server/DSComponents/install. liborchoracle

[Root @ datastage DSEngine] # 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'

[Root @ datastage DSEngine] # cd $ ORACLE_HOME/lib
[Root @ datastage DSEngine] # ln-s/opt/IBM/InformationServer/Server/StagingArea/Installed/OracleConnector/Server/linux/libccora11g. so licccora11g. so
[Root @ datastage DSEngine] # 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. liborchoracle' with root user.

[Root @ datastage DSEngine] # export DSHOME =/opt/IBM/InformationServer/Server/DSEngine

[Root @ datastage DSEngine] # export APT_ORCHHOME =/opt/IBM/InformationServer/Server/PXEngine

[Root @ datastage DSEngine] # vi/opt/IBM/InformationServer/Server/DSComponents/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

[Root @ datastage DSEngine] #/opt/IBM/InformationServer/Server/DSComponents/install. liborchoracle
Installing Oracle driver.
Installing driver for Oracle Version 10g
Oracle driver installation is completed.

Test the dynamic library

[Root @ datastage DSEngine] # cd $ ORACLE_HOME/lib
[Root @ datastage lib] # ls-al libcc *
Lrwxrwxrwx 1 oracle dba 65 Apr 24 :26 libccora10g. so->/opt/IBM/InformationServer/Server/DSComponents/bin/libccora10g. so
Lrwxrwxrwx 1 oracle dba 65 Apr 24 :25 libccora11g. so->/opt/IBM/InformationServer/Server/DSComponents/bin/libccora11g. so
[Root @ datastage lib] # 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)
[Root @ datastage 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)
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 (0x00002b83920.b000)
/Lib64/ld-linux-x86-64.so.2 (0x00000032b4a00000)
Liborchmonitorx86_64.so =>/opt/IBM/InformationServer/Server/PXEngine/lib/liborchmonitorx86_64.so (0x00002b839151d000)
[Root @ datastage lib] #

Open 'ds Administrator client'
Click 'enable', go

3) Restart DS server and 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


Then I can connect to Oracle in Designer.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.