Oracle Enterprise stage(urgent!)

[milind]

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

[dhwankim]

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

[milind]

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

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

[Teej]

My Oracle Server resides on the same Solaris machine as the Datastage Server, so I cannot use the Remote Server property.

I am sorry, but you are incorrect here.

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.

[milind]

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

My Oracle Server resides on the same Solaris machine as the Datastage Server, so I cannot use the Remote Server property.

I am sorry, but you are incorrect here.

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.

[Teej]

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?.

It is the Oracle database name defined within TNSNAMES.ORA or the LDAP server.

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.

That is definitely not fitting to what the errors are. Can you ensure your DBA see this? Maybe it is something the DBA could fix on their side?

-T.J.

[chulett]

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.

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 

Does that make any more sense?

[Teej]

Are you running Oracle 10g?

-T.J.

[milind]

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

Are you running Oracle 10g?

-T.J.

[ray.wurlod]

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.

[milind]

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

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.

[Eric]

You must Expand the Variable ORACLE_HOME and give it the actual value (ORACLE_HOME = /opt/orasw/app/oracle/product/9i) rather than the reference of $ORACLE_HOME.

[milind]

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

[Teej]

*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:

Code: Select all

sqlplus dwuser@PX.datamatics.com

Does this connect you to the right database?

If not:

Code: Select all

tnsping PX.datamatics.com

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:

Code: Select all

##I TUTL 000031 07:43:37(001) <main_program> The open files limit is 100; raising to 1024.

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.