Topic
6 replies Latest Post - ‏2011-02-11T08:31:37Z by kevin.W
kevin.W
kevin.W
5 Posts
ACCEPTED ANSWER

Pinned topic problem when DataStage8.5 connect to Oracle11g on AIX6

‏2011-01-20T06:21:53Z |
Hi All
I have a error when i use datastage connect to Oracle and import the table definition. Below is the detailed situation.

enviroment:
OS:AIX6.1,64bit,power6 processor, LANG=en_US
Data Stage Version:8.5

Installation profile
three tiers install on same machine, repository use DB2 (default).
Oracle Client 11.2 (64bit) also install on this machine, I can use SQLPLUS connect to Oracle server (11.2, 64bit, AL32UTF8) on another machine.

"dsenv" setting
add "/oracle/product/11.2.0-64/lib" to the "LIBPATH"
add "export TNS_ADMIN=/oracle/product/11.2.0-64/network/admin"

Problem
1. I use Oracle Connector(parallel) create a Link, then use this Link import Metadata, when i press Test connection, there is a dialog with "The OCI function OraOCIEnvNlsCreate:OCI_UTF16ID returned status -1. Error code: NULL, Error message: NULL" popup, and the connection failed.
2. I use Oracle Enterprise(parallel) create a Link, then use it import Metadata, when i click Ellipsis button list all the tables in target database, there is a dialog with "cannot get list of table names from database" popup, after I click OK on this dialog, the detail error message popup.
#######################################################################
*** Error executing command: orchdbutil tablenames -dbtype oracle -server gddbrac -dboptions *********
##I IIS-DSEE-TFCN-00001 12:37:21(000) <main_program>
IBM WebSphere DataStage Enterprise Edition 8.5.0.5746
Copyright (c) 2001, 2005-2008 IBM Corporation. All rights reserved
##I IIS-DSEE-TFCN-00006 12:37:21(001) <main_program> conductor uname: -s=AIX; -r=1; -v=6; -n=cgdc-gddc1-ser-a04; -m=00C42C464C00
##E IIS-DSEE-TCDB-00004 12:37:21(002) <main_program> Unable to access database oracleLibrary orchoracle could not be loaded; Could not load "orchoracle":
0509-022 Cannot load module /opt/IBM/InformationServer/Server/DSComponents/bin/orchoracle.o.
0509-150 Dependent module /opt/IBM/InformationServer/Server/DSComponents/bin/libclntsh.so could not be loaded.
0509-103 The module has an invalid magic number.
0509-022 Cannot load module /opt/IBM/InformationServer/Server/DSComponents/bin/orchoracle.o.
0509-150 Dependent module /opt/IBM/InformationServer/Server/DSComponents/bin/orchoracle.o could not be loaded.
###########################################################################

from the message I found the DS search some files in DSCompoments/bin, but these files are in the oracle bin directory. I can't find the error in dsenv file, so i copied these files into DSComponents/bin, this time the error message changed to "OCI_ERROR: Bad Oracle environment".
I am not sure which enviroment variable I missed, please tell me.

3. I use Oracle OCI(Server) create a Link and import a table, it works fine.

So, my question is why I can't use the Oracle Connector and Oracle Enterprise to connect the Oracle. Thanks.

Kevin
Updated on 2011-02-11T08:31:37Z at 2011-02-11T08:31:37Z by kevin.W
  • R_Sethi
    R_Sethi
    3 Posts
    ACCEPTED ANSWER

    Re: problem when DataStage8.5 connect to Oracle11g on AIX6

    ‏2011-01-26T16:46:58Z  in response to kevin.W
    I am also seeing the same error :
    "The OCI function OraOCIEnvNlsCreate:OCI_UTF16ID returned status -1. Error code: NULL, Error message: NULL"

    We tried to set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 , Recycle datastage , ASBNode . However the error still remains.
  • kevin.W
    kevin.W
    5 Posts
    ACCEPTED ANSWER

    Re: problem when DataStage8.5 connect to Oracle11g on AIX6

    ‏2011-01-27T09:24:01Z  in response to kevin.W
    I fix one problem, now I can use the "Oracle Enterprise" to develop my job.
    I add the "PATH" environment variable to the dsenv file, which point to the oracle bin directory, and now I can use "Oracle Enterprise" to import MetaData.
    I checked the /etc/envirenment file, It contains the "PATH" variable, and point to the correct oracle bin directory, but it not work, maybe some export variable cover the PATH in /etc/envirenment file.

    About "Oracle Connector", I found It can't import MetaData but can works fine on runtime (job execute).
    I create a simple job have two stage "Oracle Connector", "Sequential file" and a link between them. Although I can't use "Oracle Connector" to import MetaData, but this job works fine. So now I use Enterprise Stage import MetaData and use Connector in the JOB.

    hope this will help somebody

    Kevin.W
  • R_Sethi
    R_Sethi
    3 Posts
    ACCEPTED ANSWER

    Re: problem when DataStage8.5 connect to Oracle11g on AIX6

    ‏2011-01-27T16:50:25Z  in response to kevin.W
    Hi All,

    Yes the PATH variable needs to be set to $ORACLE_HOME/bin. Adding this variable to the dsenv file and recycling all services fixed the Oracle COnnector issue for us. It is required to be added to the dsenv file and recycling ASBNode and datastage is also required. Here are the directives needed in the dsenv file to use Oracle Connector :(eg is from our system AIX 6.1 , datastage 8.5 connecting to Oracle 11g Enterprise)

    ORACLE_HOME=/opt/oracle/product/11.1.0/client_1; export ORACLE_HOME
    PATH=$ORACLE_HOME/bin:$PATH; export PATH
    NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1;export NLS_LANG
    LIBPATH=/opt/oracle/product/11.1.0/client_1/lib/:$LIBPATH ; export LIBPATH ### LIBPATH will contain all other paths for datastage libraries also.

    We also added the following :
    TNS_ADMIN=/opt/oracle/product/11.1.0/client_1/network/admin; export TNS_ADMIN

    Hope this helps.

    Ritu
  • kevin.W
    kevin.W
    5 Posts
    ACCEPTED ANSWER

    Re: problem when DataStage8.5 connect to Oracle11g on AIX6

    ‏2011-01-28T01:53:27Z  in response to kevin.W
    Hi R_Sethi!

    Even I add the PATH to the dsenv, when I use "Oracle Connector" to import MetaData or TestConnection, I also receive the error message - "The OCI function OraOCIEnvNlsCreate:OCI_UTF16ID returned status -1. Error code: NULL, Error message: NULL". But when I exexute the job, there is no error and it works fine.

    have you fix this error?
    Kevin.W
  • R_Sethi
    R_Sethi
    3 Posts
    ACCEPTED ANSWER

    Re: problem when DataStage8.5 connect to Oracle11g on AIX6

    ‏2011-01-28T18:30:50Z  in response to kevin.W
    yes . Problem is resolved now. We had made following changes in dsenv and recycled all of datastage, WAS , DB2 and ASBNode

    NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1; export NLS_LANG
    ORACLE_HOME=/opt/oracle/product/11.1.0/client_1; export ORACLE_HOME
    PATH=$TWB_ROOT/:$TD_SECURITY/bin:$ORACLE_HOME/bin:$PATH
    ODBCINST=/opt/teradata/client/odbc/odbcinst.ini; export ODBCINST
    TNS_ADMIN=/opt/oracle/product/11.1.0/client_1/network/admin; export TNS_ADMIN
    export NLS_LANG TWB_ROOT PATH LIBPATH

    NOTE : LIBPATH also points to : /opt/oracle/product/11.1.0/client_1/lib
  • kevin.W
    kevin.W
    5 Posts
    ACCEPTED ANSWER

    Re: problem when DataStage8.5 connect to Oracle11g on AIX6

    ‏2011-02-11T08:31:37Z  in response to kevin.W
    Hi R_Sethi,

    Sorry about my lated reply because of a long vacation of Chinese New Year.

    I also fix the problem now. The mistake that I cant use Oracle Connector to import Metadata is I only restarted the engine tier not all the tier.

    Thanks for your help!
    Kevin.W