Oracle Enterprise stage(urgent!)
Moderators: chulett, rschirm, roy
Oracle Enterprise stage(urgent!)
Hi,
I have just installed Oracle 9i and Datastage PX on Solaris.I have made all the necessary configurations required.I am able to connect via ODBC to oracle in a server job.I am also able to run a parallel job using sequential access stage.
I imported a schema from the oracle server.But while developing a parallel job using the Oracle Enterprise database stage, i encounter the following error when i try to view data after supplying the username/password and the username.oracle table:
##W TCOS 000049 07:08:40(000) <main_program> Parameter specified but not used in flow: DSProjectMapName
>##E TDOR 000008 07:08:43(001) <Oracle_Enterprise_0> Missing server name. Can't connect
>##E TDOR 000140 07:08:43(002) <Oracle_Enterprise_0> APT_OraReadOperator: connect failed.
Please reply ASAP !!!..
Regards
I have just installed Oracle 9i and Datastage PX on Solaris.I have made all the necessary configurations required.I am able to connect via ODBC to oracle in a server job.I am also able to run a parallel job using sequential access stage.
I imported a schema from the oracle server.But while developing a parallel job using the Oracle Enterprise database stage, i encounter the following error when i try to view data after supplying the username/password and the username.oracle table:
##W TCOS 000049 07:08:40(000) <main_program> Parameter specified but not used in flow: DSProjectMapName
>##E TDOR 000008 07:08:43(001) <Oracle_Enterprise_0> Missing server name. Can't connect
>##E TDOR 000140 07:08:43(002) <Oracle_Enterprise_0> APT_OraReadOperator: connect failed.
Please reply ASAP !!!..
Regards
My Oracle Server resides on the same Solaris machine as the Datastage Server, so I cannot use the Remote Server property.
Any further suggestions, do reply.
Thanks
Any further suggestions, do reply.
Thanks
dhwankim wrote:Do you use Remote Server?
Maybe yes, then use Remoter Server Property.
It 's in Connection folder and you can see Available porperties to add box.
Cheers
D.H Kim
I am sorry, but you are incorrect here.milind wrote:My Oracle Server resides on the same Solaris machine as the Datastage Server, so I cannot use the Remote Server property.
You should use the Remote Server at all time. Doing otherwise assumes that you have ONE single Oracle Server, period. But on one single machine here for Development and QA, there are no less than ~15 separate Oracle servers, for many different clients we serves, partitioned into DEV and QA to isolate the testing environment. We also have Production boxes (at least 5 for this project alone at last colunt) with several Oracle Servers on each.
For the Remote Server property, they are asking for your Schema Name. Provide it at all time (especially via a parameter to ease your migration pains.)
-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
Hi guys,
Presently I have just one Oracle Server I need to conect to and it resides locally.And even when I give the Remote Server value, get the following error:
>##E TDOR 000000 01:29:43(001) <Oracle_Enterprise_0> connect failed for env: PX, user: dwuser, password: *****
>
>Oracle error: -2127
>
>
>##E TDOR 000140 01:29:43(002) <Oracle_Enterprise_0> APT_OraReadOperator: connect failed.
Any help would be greatly appreciated
Thanks
Presently I have just one Oracle Server I need to conect to and it resides locally.And even when I give the Remote Server value, get the following error:
>##E TDOR 000000 01:29:43(001) <Oracle_Enterprise_0> connect failed for env: PX, user: dwuser, password: *****
>
>Oracle error: -2127
>
>
>##E TDOR 000140 01:29:43(002) <Oracle_Enterprise_0> APT_OraReadOperator: connect failed.
Any help would be greatly appreciated
Thanks
Teej wrote:I am sorry, but you are incorrect here.milind wrote:My Oracle Server resides on the same Solaris machine as the Datastage Server, so I cannot use the Remote Server property.
You should use the Remote Server at all time. Doing otherwise assumes that you have ONE single Oracle Server, period. But on one single machine here for Development and QA, there are no less than ~15 separate Oracle servers, for many different clients we serves, partitioned into DEV and QA to isolate the testing environment. We also have Production boxes (at least 5 for this project alone at last colunt) with several Oracle Servers on each.
For the Remote Server property, they are asking for your Schema Name. Provide it at all time (especially via a parameter to ease your migration pains.)
-T.J.
It is the Oracle database name defined within TNSNAMES.ORA or the LDAP server.milind wrote:I have done all the settngs as you have mentioned.Is the user defined environment variables and the path setting to be done in Datastage administrator or in dsenv file?And the ORACLE_SID has to be an sid like "orcl" or the database name?.
Your dsenv environment should have ORACLE_HOME and LD_LIBRARY_PATH pointing to the lib directory for your ORACLE_HOME. Both defined after "PLATFORM SPECIFIC SECTION". Don't forget to export these variables.
If you still have problems, within Director, please look at the 2nd line of your running job -- starting with "Environment variable settings", and ensure that your settings are correct. If this is not helpful, perhaps we could take a look for ourselves how your configurations are.
-T.J.
>##E TDOR 000000 01:29:43(001) <Oracle_Enterprise_0> connect failed for env: PX, user: dwuser, password: *****
>
>Oracle error: -2127
Code: Select all
> oerr ora 01217
01217, 00000, "logfile member belongs to a different logfile group"
// *Cause: A member of a multiple-member logfile group specified in a
// CREATE CONTROLFILE is not part of the same group as previous
// members.
// *Action: Group together the correct members for the CREATE CONTROLFILE
// command.
-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
TJ, you got the error number wrong.... 2127, not 1217.
Not sure what that one is, I *think* errors in that range are SQL errors, not ORA errors as nothing comes up for ORA 2127.
Does that make any more sense?
Not sure what that one is, I *think* errors in that range are SQL errors, not ORA errors as nothing comes up for ORA 2127.
Code: Select all
$ oerr sql 2127
02127, 00000, "Precompiler/SQLLIB version mismatch"
// *Cause: The program was linked to an older version of SQLLIB, which is
// incompatible with this release of the Oracle Precompilers.
// *Action: Relink the program with a newer version of SQLLIB
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi TJ,
Ia m running oracle 9i.Below is the copy/paste from my Director's second line:
Environment variable settings:
APT_ORCHHOME=/opt/dsadm/Ascential/DataStage/PXEngine
DSHOME=/opt/dsadm/Ascential/DataStage/DSEngine
HOME=/
LD_LIBRARY_PATH=/opt/dsadm/Ascential/DataStage/Projects/DSPX/RT_BP10.O:/opt/dsadm/Ascential/DataStage/DSCAPIOp:/opt/dsadm/Ascential/DataStage/RTIOperators:/opt/dsadm/Ascential/DataStage/DSParallel:/opt/dsadm/Ascential/DataStage/PXEngine/user_lib:/opt/dsadm/Ascential/DataStage/Projects/DSPX/buildop:/opt/dsadm/Ascential/DataStage/DSEngine/java/jre/lib/sparc/client:/opt/dsadm/Ascential/DataStage/DSEngine/java/jre/lib/sparc:/opt/dsadm/Ascential/DataStage/branded_odbc/lib:/opt/dsadm/Ascential/DataStage/DSEngine/lib:/opt/dsadm/Ascential/DataStage/DSEngine/uvdlls::/opt/dsadm/Ascential/DataStage/PXEngine/bin:/opt/dsadm/Ascential/DataStage/PXEngine/lib:/opt/orasw/app/oracle/product/9i/lib:/opt/orasw/app/oracle/product/9i/rdbms/lib:/usr/lib:/lib
ODBCINI=/opt/dsadm/Ascential/DataStage/DSEngine/.odbc.ini
ORACLE_HOME=$ORACLE_HOME
ORACLE_SID=PX.datamatics.com
ORAHOME=/opt/orasw/app/oracle/product/9i
PATH=/opt/dsadm/Ascential/DataStage/Projects/DSPX/wrapped:/opt/dsadm/Ascential/DataStage/Projects/DSPX/buildop:/opt/dsadm/Ascential/DataStage/Projects/DSPX/RT_BP10.O:/opt/dsadm/Ascential/DataStage/DSCAPIOp:/opt/dsadm/Ascential/DataStage/RTIOperators:/opt/dsadm/Ascential/DataStage/DSParallel:/opt/dsadm/Ascential/DataStage/PXEngine/user_osh_wrappers:/opt/dsadm/Ascential/DataStage/PXEngine/osh_wrappers:/usr/sbin:/usr/bin:/opt/dsadm/Ascential/DataStage/PXEngine/bin:/opt/orasw/app/oracle/product/9i/bin:/bin:/opt/SUNWspro/bin
TZ=GMT+5
_INIT_NET_STRATEGY=none
_INIT_PREV_LEVEL=S
_INIT_RUN_LEVEL=3
_INIT_RUN_NPREV=0
_INIT_UTS_ISA=sparc
_INIT_UTS_MACHINE=sun4u
_INIT_UTS_NODENAME=DatastagePX
_INIT_UTS_PLATFORM=SUNW,Ultra-5_10
_INIT_UTS_RELEASE=5.8
_INIT_UTS_SYSNAME=SunOS
_INIT_UTS_VERSION=Generic
LOGNAME=dsadm
DS_USERNO=-1486
WHO=DSPX
TERM=
BELL=^G
FLAVOR=-1
DS_ENABLE_RESERVED_CHAR_CONVERT=1
DS_TDM_PIPE_OPEN_TIMEOUT=720
DS_TDM_TRACE_SUBROUTINE_CALLS=0
DSIPC_OPEN_TIMEOUT=30
APT_CONFIG_FILE=/opt/dsadm/Ascential/DataStage/Configurations/default.apt
DS_OPERATOR_BUILDOP_DIR=buildop
DS_OPERATOR_WRAPPED_DIR=wrapped
APT_COMPILEOPT=-dalign -g -PIC -library=iostream -c
APT_COMPILER=/opt/SUNWspro/bin/CC
APT_LINKER=/opt/SUNWspro/bin/CC
APT_LINKOPT=-G -library=iostream
OSH_STDOUT_MSG=1
APT_ERROR_CONFIGURATION=severity, !vseverity, !jobid, moduleid, errorIndex, timestamp, !ipaddr, !nodeplayer, !nodename, opid, message
APT_OPERATOR_REGISTRY_PATH=/opt/dsadm/Ascential/DataStage/Projects/DSPX/buildop
Thanks
Ia m running oracle 9i.Below is the copy/paste from my Director's second line:
Environment variable settings:
APT_ORCHHOME=/opt/dsadm/Ascential/DataStage/PXEngine
DSHOME=/opt/dsadm/Ascential/DataStage/DSEngine
HOME=/
LD_LIBRARY_PATH=/opt/dsadm/Ascential/DataStage/Projects/DSPX/RT_BP10.O:/opt/dsadm/Ascential/DataStage/DSCAPIOp:/opt/dsadm/Ascential/DataStage/RTIOperators:/opt/dsadm/Ascential/DataStage/DSParallel:/opt/dsadm/Ascential/DataStage/PXEngine/user_lib:/opt/dsadm/Ascential/DataStage/Projects/DSPX/buildop:/opt/dsadm/Ascential/DataStage/DSEngine/java/jre/lib/sparc/client:/opt/dsadm/Ascential/DataStage/DSEngine/java/jre/lib/sparc:/opt/dsadm/Ascential/DataStage/branded_odbc/lib:/opt/dsadm/Ascential/DataStage/DSEngine/lib:/opt/dsadm/Ascential/DataStage/DSEngine/uvdlls::/opt/dsadm/Ascential/DataStage/PXEngine/bin:/opt/dsadm/Ascential/DataStage/PXEngine/lib:/opt/orasw/app/oracle/product/9i/lib:/opt/orasw/app/oracle/product/9i/rdbms/lib:/usr/lib:/lib
ODBCINI=/opt/dsadm/Ascential/DataStage/DSEngine/.odbc.ini
ORACLE_HOME=$ORACLE_HOME
ORACLE_SID=PX.datamatics.com
ORAHOME=/opt/orasw/app/oracle/product/9i
PATH=/opt/dsadm/Ascential/DataStage/Projects/DSPX/wrapped:/opt/dsadm/Ascential/DataStage/Projects/DSPX/buildop:/opt/dsadm/Ascential/DataStage/Projects/DSPX/RT_BP10.O:/opt/dsadm/Ascential/DataStage/DSCAPIOp:/opt/dsadm/Ascential/DataStage/RTIOperators:/opt/dsadm/Ascential/DataStage/DSParallel:/opt/dsadm/Ascential/DataStage/PXEngine/user_osh_wrappers:/opt/dsadm/Ascential/DataStage/PXEngine/osh_wrappers:/usr/sbin:/usr/bin:/opt/dsadm/Ascential/DataStage/PXEngine/bin:/opt/orasw/app/oracle/product/9i/bin:/bin:/opt/SUNWspro/bin
TZ=GMT+5
_INIT_NET_STRATEGY=none
_INIT_PREV_LEVEL=S
_INIT_RUN_LEVEL=3
_INIT_RUN_NPREV=0
_INIT_UTS_ISA=sparc
_INIT_UTS_MACHINE=sun4u
_INIT_UTS_NODENAME=DatastagePX
_INIT_UTS_PLATFORM=SUNW,Ultra-5_10
_INIT_UTS_RELEASE=5.8
_INIT_UTS_SYSNAME=SunOS
_INIT_UTS_VERSION=Generic
LOGNAME=dsadm
DS_USERNO=-1486
WHO=DSPX
TERM=
BELL=^G
FLAVOR=-1
DS_ENABLE_RESERVED_CHAR_CONVERT=1
DS_TDM_PIPE_OPEN_TIMEOUT=720
DS_TDM_TRACE_SUBROUTINE_CALLS=0
DSIPC_OPEN_TIMEOUT=30
APT_CONFIG_FILE=/opt/dsadm/Ascential/DataStage/Configurations/default.apt
DS_OPERATOR_BUILDOP_DIR=buildop
DS_OPERATOR_WRAPPED_DIR=wrapped
APT_COMPILEOPT=-dalign -g -PIC -library=iostream -c
APT_COMPILER=/opt/SUNWspro/bin/CC
APT_LINKER=/opt/SUNWspro/bin/CC
APT_LINKOPT=-G -library=iostream
OSH_STDOUT_MSG=1
APT_ERROR_CONFIGURATION=severity, !vseverity, !jobid, moduleid, errorIndex, timestamp, !ipaddr, !nodeplayer, !nodename, opid, message
APT_OPERATOR_REGISTRY_PATH=/opt/dsadm/Ascential/DataStage/Projects/DSPX/buildop
Thanks
Teej wrote:Are you running Oracle 10g?
-T.J.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
One that jumps out immediately is that LD_LIBRARY_PATH does not include $ORACLE_HOME/lib or any other Oracle library.
ORACLE_HOME=$ORACLE_HOME doesn't achieve much!
PATH doesn't include $ORACLE_HOME/bin or any of the other Oracle executable-containing directories.
Chances are, that TNSNAMES.ORA may need work, too.
ORACLE_HOME=$ORACLE_HOME doesn't achieve much!
PATH doesn't include $ORACLE_HOME/bin or any of the other Oracle executable-containing directories.
Chances are, that TNSNAMES.ORA may need work, too.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi Ray,
My $ORACLE_HOME points to /opt/orasw/app/oracle/product/9i.As you can see that the LD_LIBRARY_PATH has the /lib directory and the PATH has the /bin directory.
The TNSNAMES.ORA also has the entry for connecting to the Oracle server.
Thanks
My $ORACLE_HOME points to /opt/orasw/app/oracle/product/9i.As you can see that the LD_LIBRARY_PATH has the /lib directory and the PATH has the /bin directory.
The TNSNAMES.ORA also has the entry for connecting to the Oracle server.
Thanks
ray.wurlod wrote:One that jumps out immediately is that LD_LIBRARY_PATH does not include $ORACLE_HOME/lib or any other Oracle library.
ORACLE_HOME=$ORACLE_HOME doesn't achieve much!
PATH doesn't include $ORACLE_HOME/bin or any of the other Oracle executable-containing directories.
Chances are, that TNSNAMES.ORA may need work, too.
That is done too !..but no progress.
I noticed one more thing...The import Orchestrate schema itself fails when tried via the Database option.This throws a similar error:
*** Error executing command: orchdbutil show DWUSER.EMP -dbtype oracle -dboptions *********
##I TFCN 000001 07:43:37(000) <main_program>
DataStage XE Parallel Extender V7.0.0
Copyright (C) 2003, 1997 - 2002 Ascential Software, Inc.
All Rights Reserved
##I TUTL 000031 07:43:37(001) <main_program> The open files limit is 100; raising to 1024.
##I TCDB 000003 07:43:37(002) <main_program> Oracle: loaded
##I TCDU 000009 07:43:37(003) <main_program> Database connect options: dbname= <unspecified>, server=<unspecified>, dboptions='{user=dwuser,password=dwuser}'
##I TCDU 000005 07:43:37(004) <main_program> Database connect options: dbname= <unspecified>, server=<unspecified>, dboptions='{user=dwuser,password=dwuser}'DB type 'oracle' presumed to be oracle.
##E TCDU 000000 07:43:37(005) <main_program> Database connect options: dbname= <unspecified>, server=<unspecified>, dboptions='{user=dwuser,password=dwuser}'connect failed for env: null, user: dwuser, password: *****
Oracle error: -2127
##E TCDU 000010 07:43:37(006) <main_program> Uname to connect to database
Notice that the orchdbutil is being used.
I have not specified the Database Server as its a local Oracle server and the Database Name is grayed out.
Thanks
I noticed one more thing...The import Orchestrate schema itself fails when tried via the Database option.This throws a similar error:
*** Error executing command: orchdbutil show DWUSER.EMP -dbtype oracle -dboptions *********
##I TFCN 000001 07:43:37(000) <main_program>
DataStage XE Parallel Extender V7.0.0
Copyright (C) 2003, 1997 - 2002 Ascential Software, Inc.
All Rights Reserved
##I TUTL 000031 07:43:37(001) <main_program> The open files limit is 100; raising to 1024.
##I TCDB 000003 07:43:37(002) <main_program> Oracle: loaded
##I TCDU 000009 07:43:37(003) <main_program> Database connect options: dbname= <unspecified>, server=<unspecified>, dboptions='{user=dwuser,password=dwuser}'
##I TCDU 000005 07:43:37(004) <main_program> Database connect options: dbname= <unspecified>, server=<unspecified>, dboptions='{user=dwuser,password=dwuser}'DB type 'oracle' presumed to be oracle.
##E TCDU 000000 07:43:37(005) <main_program> Database connect options: dbname= <unspecified>, server=<unspecified>, dboptions='{user=dwuser,password=dwuser}'connect failed for env: null, user: dwuser, password: *****
Oracle error: -2127
##E TCDU 000010 07:43:37(006) <main_program> Uname to connect to database
Notice that the orchdbutil is being used.
I have not specified the Database Server as its a local Oracle server and the Database Name is grayed out.
Thanks
*sits here and thinks*
Okay, there are two different ways to define an Oracle SID - first is to set the absolute default. I will have to dig out that information when I get to work. The second is to explicitly define it within each stage.
I see that ORACLE_SID is defined. I see that LD_LIBRARY_PATH is defined. I see that PATH is defined. You will need to fix ORACLE_HOME.
On the same server where Oracle and DataStage are, run the following command:
Does this connect you to the right database?
If not:
If not, then it's tnsnames.ora (or your LDAP server) that needs to be fixed.
While you're waiting for us to get back to you, you should take a look at this message:
This indicates that your kernel parameters are not upgraded to what the Install and Upgrade Guide (pdf doc, part of the manuals you could read within your DataStage folder under the Start | Program menu - if it was installed.)
If you are not successful with this thus far, I will have to give you some step-by-step Orchestrate code that utilizes the Oracle Stage. This way, you can run it off the command line and determine whether it is DataStage or the configuration that is off.
-T.J.
Okay, there are two different ways to define an Oracle SID - first is to set the absolute default. I will have to dig out that information when I get to work. The second is to explicitly define it within each stage.
I see that ORACLE_SID is defined. I see that LD_LIBRARY_PATH is defined. I see that PATH is defined. You will need to fix ORACLE_HOME.
On the same server where Oracle and DataStage are, run the following command:
Code: Select all
sqlplus dwuser@PX.datamatics.com
If not:
Code: Select all
tnsping PX.datamatics.com
While you're waiting for us to get back to you, you should take a look at this message:
Code: Select all
##I TUTL 000031 07:43:37(001) <main_program> The open files limit is 100; raising to 1024.
If you are not successful with this thus far, I will have to give you some step-by-step Orchestrate code that utilizes the Oracle Stage. This way, you can run it off the command line and determine whether it is DataStage or the configuration that is off.
-T.J.
Developer of DataStage Parallel Engine (Orchestrate).