Oracle Connection Error

[sshettar]

Hi All,

We recently upgraded our datastage from 8.01 to 8.1.
Well when we installed 8.01 we did not install NLS , but right now we have installed 8.1 with NLS as well.

Guess this could be causing our issue connecting to Oracle.
we have diabled NLS in the uvconfig file and also restared the datastage server with regenerating the uvconfig file.

The strange thing we found was that in the server job in 8.1 we could connect to oracle where as in parallel job its failing with following error
note: we also created a project with nls disabled
The following is the full detailed log for the falied job


Item #: 1
Event ID: 0
Timestamp: 2009-11-16 14:08:52
Type: Control
User Name: dsadmin
Message Id: DSTAGE_RUN_I_0070
Message: Starting Job TESTNARASA.

Item #: 2
Event ID: 1
Timestamp: 2009-11-16 14:08:54
Type: Info
User Name: dsadmin
Message Id: DSTAGE_RUN_I_0126
Message: Environment variable settings:
APT_COMPILEOPT=-dalign -O -PIC -library=iostream -c -xarch=v9
APT_COMPILER=/opt/SUNWspro/bin/CC
APT_CONFIG_FILE=/opt/IBM/InformationServer/Server/Configurations/default.apt
APT_ERROR_CONFIGURATION=severity, !vseverity, !jobid, moduleid, errorIndex, timestamp, !ipaddr, !nodeplayer, !nodename, opid, message
APT_LINKER=/opt/SUNWspro/bin/CC
APT_LINKOPT=-G -library=iostream -xarch=v9
APT_MONITOR_MINTIME=10
APT_NO_ONE_NODE_COMBINING_OPTIMIZATION=1
APT_OPERATOR_REGISTRY_PATH=/opt/IBM/InformationServer/Server/Projects/Narasa/buildop
APT_ORCHHOME=/opt/IBM/InformationServer/Server/PXEngine
ASBHOME=/opt/IBM/InformationServer/ASBNode
BELL=^G
CLASSPATH=/opt/oracle/product/10.2.0/jdbc/lib:/opt/oracle/product/10.2.0/jdbc/lib/classes12.zip:.:/export/home/datastage/jdbcconn/td.jar:/export/home/datastage/jdbcconn:
DSHOME=/opt/IBM/InformationServer/Server/DSEngine
DSIPC_OPEN_TIMEOUT=30
DS_ENABLE_RESERVED_CHAR_CONVERT=0
DS_OPERATOR_BUILDOP_DIR=buildop
DS_OPERATOR_WRAPPED_DIR=wrapped
DS_TDM_PIPE_OPEN_TIMEOUT=720
DS_TDM_TRACE_SUBROUTINE_CALLS=0
DS_USERNO=-4513
EDITOR=vi
FLAVOR=-1
HOME=/export/home/datastage
ISFSHMID=33554475
KM_CRYPTO_LIB_PATH=/etc/emc/rsa/rkm_client/lib/libkmcryptolib.so
KM_SUPPORT_LIB_PATH=/etc/emc/rsa/rkm_client/lib/libkmsvcshlib.so
LD_LIBRARY_PATH=/opt/IBM/InformationServer/Server/Projects/Narasa/buildop:/opt/IBM/InformationServer/Server/DSComponents/lib:/opt/IBM/InformationServer/Server/DSComponents/bin:/opt/IBM/InformationServer/Server/DSParallel:/opt/IBM/InformationServer/Server/PXEngine/user_lib:/opt/IBM/InformationServer/Server/PXEngine/lib:/opt/IBM/InformationServer/Server/Projects/Narasa/RT_BP1.O:/opt/IBM/InformationServer/ASBNode/apps/jre/lib/sparcv9/server:/opt/IBM/InformationServer/ASBNode/apps/jre/lib/sparcv9:/opt/IBM/InformationServer/ASBNode/lib/cpp:/opt/IBM/InformationServer/ASBNode/apps/proxy/cpp/sunos-all-sparc_64:/opt/oracle/product/10.2.0/lib:/opt/oracle/product/10.2.0/lib32:/opt/oracle/product/10.2.0/rdbms/lib:/opt/oracle/product/10.2.0/rdbms/lib32:/opt/IBM/InformationServer/Server/branded_odbc/lib:/opt/IBM/InformationServer/Server/DSEngine/lib:/opt/IBM/InformationServer/Server/DSEngine/uvdlls:/usr/lib:/lib
LIBPATH=:/opt/IBM/InformationServer/Server/PXEngine/lib:/opt/IBM/InformationServer/Server/DSEngine/lib:/opt/IBM/InformationServer/ASBNode/lib/cpp:/opt/IBM/InformationServer/ASBNode/apps/proxy/cpp/sunos-all-sparc
LOGNAME=dsadmin
MAIL=/var/mail/datastage
ODBCINI=/opt/IBM/InformationServer/Server/DSEngine/.odbc.ini
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0
ORACLE_SID=xmeta
ORA_NLS10=/opt/oracle/product/10.2.0/nls/data
OSH_STDOUT_MSG=1
PATH=/opt/IBM/InformationServer/Server/Projects/Narasa/wrapped:/opt/IBM/InformationServer/Server/Projects/Narasa/buildop:/opt/IBM/InformationServer/Server/Projects/Narasa/RT_BP1.O:/opt/IBM/InformationServer/Server/DSComponents/lib:/opt/IBM/InformationServer/Server/DSComponents/bin:/opt/IBM/InformationServer/Server/DSParallel:/opt/IBM/InformationServer/Server/PXEngine/user_osh_wrappers:/opt/IBM/InformationServer/Server/PXEngine/osh_wrappers:/opt/IBM/InformationServer/Server/PXEngine/bin:/opt/oracle/product/10.2.0/bin:/opt/IBM//WebSphere/AppServer/java/bin:/opt/oracle/product/10.2.0/bin:/usr/bin:/usr/local/bin:/usr/local/sbin:/usr/ccs/bin:/usr/ucb:/usr/openwin/bin:/opt/oracle/product/10.2.0/bin:/bin:/opt/IBM/InformationServer/Server/DSEngine/bin:/opt/oracle/product/10.2.0/lib32:/opt/IBM/InformationServer/Server/DSEngine:/opt/oracle/product/10.2.0/perl/bin:/opt/oracle/product/10.2.0/perl/lib/site_perl/5.8.3/Apache:/opt/SUNWspro/bin
PIDTOK=4136
PS1=datastage@mclndwetl01-qa$
PWD=/opt/IBM/InformationServer/Server/DSEngine
PX_DBCONNECTHOME=/opt/IBM/InformationServer/Server/DSComponents
R_SHLIB_LD_LIBRARY_PATH=/etc/emc/rsa/rkm_client/lib
SHELL=/bin/ksh
SHLIB_PATH=/opt/IBM/InformationServer/Server/PXEngine/lib:/opt/IBM/InformationServer/Server/DSEngine/lib:/opt/IBM/InformationServer/ASBNode/lib/cpp:/opt/IBM/InformationServer/ASBNode/apps/proxy/cpp/sunos-all-sparc
SSH_CLIENT=10.2.209.216 2076 22
SSH_CONNECTION=10.2.209.216 2076 10.200.8.106 22
SSH_TTY=/dev/pts/2
SUDO_COMMAND=bin/uv -admin -start
SUDO_GID=101
SUDO_UID=200
SUDO_USER=datastage
TERM=
TZ=US/Eastern
UDTBIN=/opt/IBM/InformationServer/Server/DSEngine/ud41/bin
UDTHOME=/opt/IBM/InformationServer/Server/DSEngine/ud41
USER=root
WHO=Narasa
_=/usr/local/bin/sudo

Item #: 3
Event ID: 2
Timestamp: 2009-11-16 14:08:54
Type: Info
User Name: dsadmin
Message Id: DSTAGE_RUN_I_0121
Message: Parallel job initiated

Item #: 4
Event ID: 3
Timestamp: 2009-11-16 14:08:54
Type: Info
User Name: dsadmin
Message Id: DSTAGE_RUN_I_0470
Message: OSH script
# OSH / orchestrate script for Job TESTNARASA compiled at 14:08:37 16 NOV 2009
#################################################################
#### STAGE: Oracle_Enterprise_0
## Operator
oraread
## Operator options
-dboptions '{user=dwstg,password=[&__V0S0P1_password]}'
-query 'SELECT TO_CHAR(SYSDATE,\'YYYYMMDD\') DATE1 FROM DUAL'
-server 'edw'
## General options
[ident('Oracle_Enterprise_0'); jobmon_ident('Oracle_Enterprise_0')]
## Outputs
0> [modify (
DATE1:not_nullable string[max=20]=DATE1;
keep
DATE1;
)] 'Oracle_Enterprise_0:DSLink2.v'
;
#################################################################
#### STAGE: Sequential_File_1
## Operator
export
## Operator options
-schema record
{final_delim=end, delim=',', quote=double}
(
DATE1:string[max=20];
)
-file '/home/TESTNARASA'
-overwrite
-rejects continue
## General options
[ident('Sequential_File_1'); jobmon_ident('Sequential_File_1')]
## Inputs
0< [] 'Oracle_Enterprise_0:DSLink2.v'
;
# End of OSH code

Item #: 5
Event ID: 4
Timestamp: 2009-11-16 14:08:55
Type: Info
User Name: dsadmin
Message Id: IIS-DSEE-TFCN-00001
Message: main_program: IBM WebSphere DataStage Enterprise Edition 8.1.0.5281
Copyright (c) 2001, 2005-2008 IBM Corporation. All rights reserved

Item #: 6
Event ID: 5
Timestamp: 2009-11-16 14:08:55
Type: Info
User Name: dsadmin
Message Id: IIS-DSEE-TFCN-00006
Message: main_program: conductor uname: -s=SunOS; -r=5.10; -v=Generic_138888-07; -n=mclndwetl01-qa; -m=sun4v

Item #: 7
Event ID: 6
Timestamp: 2009-11-16 14:08:56
Type: Info
User Name: dsadmin
Message Id: IIS-DSEE-TOSH-00002
Message: main_program: orchgeneral: loaded
orchsort: loaded
orchstats: loaded

Item #: 8
Event ID: 7
Timestamp: 2009-11-16 14:08:56
Type: Fatal
User Name: dsadmin
Message Id: IIS-DSEE-TDOR-00608
Message: main_program: (aptoci.C:66). Message: OCI_ERROR: Bad Oracle environment.

Item #: 9
Event ID: 8
Timestamp: 2009-11-16 14:08:56
Type: Fatal
User Name: dsadmin
Message Id: IIS-DSEE-TFOP-00020
Message: Oracle_Enterprise_0: Error occurred during initializeFromArgs().

Item #: 10
Event ID: 9
Timestamp: 2009-11-16 14:08:56
Type: Fatal
User Name: dsadmin
Message Id: IIS-DSEE-TDOR-00140
Message: Oracle_Enterprise_0: APT_OraReadOperator: connect failed.

Item #: 11
Event ID: 10
Timestamp: 2009-11-16 14:08:56
Type: Fatal
User Name: dsadmin
Message Id: IIS-DSEE-TCOS-00029
Message: main_program: Creation of a step finished with status = FAILED.

Item #: 12
Event ID: 11
Timestamp: 2009-11-16 14:09:01
Type: Info
User Name: dsadmin
Message Id: DSTAGE_RUN_I_0123
Message: Contents of phantom output file
Program "DSR_GETJOB": Line 50, Index /opt/IBM/InformationServer/Server/Projects/Narasa/I_DS_JOBS/INDEX.000 is configured to use a NLS locale (US-ENGLISH) for collation. NLS is not currently enabled on this system.
Program "DSR_GETJOB": Line 50, Index /opt/IBM/InformationServer/Server/Projects/Narasa/I_DS_JOBS/INDEX.001 is configured to use a NLS locale (US-ENGLISH) for collation. NLS is not currently enabled on this system.
Program "DSR_GETJOB": Line 50, Index /opt/IBM/InformationServer/Server/Projects/Narasa/I_DS_JOBS/INDEX.002 is configured to use a NLS locale (US-ENGLISH) for collation. NLS is not currently enabled on this system.
Program "DSR_GETJOB": Line 50, Index /opt/IBM/InformationServer/Server/Projects/Narasa/I_DS_JOBS/INDEX.003 is configured to use a NLS locale (US-ENGLISH) for collation. NLS is not currently enabled on this system.
DataStage Job 1 Phantom 4567

Item #: 13
Event ID: 12
Timestamp: 2009-11-16 14:09:02
Type: Control
User Name: dsadmin
Message Id: DSTAGE_RUN_I_0075
Message: Job TESTNARASA aborted.

End of report.
...........................................


could some one help us resolve this issue?
any help is highly appreciated..

Thanks

[chulett]

note: we also created a project with nls disabled

And Oracle jobs in that project throw the same error? Or work fine?

[sshettar]

It throws the same error...

[chulett]

Can you post the contents of your dsenv file, please?

[sshettar]

Sure

the dsenv file is as described below

#!/bin/sh
####################################################################
#
# dsenv - DataStage environment file
#
# Licensed Materials - Property of IBM (c) Copyright IBM Corp. 1997, 2007 All Rights Reserved.
# This is unpublished proprietary source code of IBM Corporation
# The copyright notice above does not evidence any actual or
# intended publication of such source code.
#
# This script is sourced by the DataStage dsrpcd daemon to establish
# proper environment settings for DataStage client connections.
#
# This script may also be sourced by bourne shells to establish
# proper environment settings for local DataStage use.
#
####################################################################

# PLATFORM SPECIFIC SECTION

set +u

if [ -z "$DSHOME" ] && [ -f "/.dshome" ]
then
DSHOME=`cat /.dshome`
export DSHOME
fi

if [ -z "$DSHOME" ]
then
DSHOME=/opt/IBM/InformationServer/Server/DSEngine; export DSHOME
fi

if [ -z "$DSRPCD_PORT_NUMBER" ]
then
true
##DSRPCD_PORT_NUMBER_TAG##
fi

if [ -z "$APT_ORCHHOME" ]
then
APT_ORCHHOME=/opt/IBM/InformationServer/Server/PXEngine; export APT_ORCHHOME
fi


#if [ -z "$UDTHOME" ]
#then
UDTHOME=/opt/IBM/InformationServer/Server/DSEngine/ud41 ; export UDTHOME
UDTBIN=/opt/IBM/InformationServer/Server/DSEngine/ud41/bin ; export UDTBIN
#fi

#if [ -z "$ASBHOME" ] && [ -f "$DSHOME/.asbnode" ]
#then
ASBHOME=`cat $DSHOME/.asbnode`
export ASBHOME
#fi

#if [ -z "$ASBHOME" ]
#then
#ASBHOME=`dirname \`dirname $DSHOME\``/ASBNode
#export ASBHOME
#fi

if [ -n "$DSHOME" ] && [ -d "$DSHOME" ]
then
ODBCINI=$DSHOME/.odbc.ini; export ODBCINI
HOME=${HOME:-/}; export HOME

#LANG="<langdef>";export LANG
#LC_ALL="<langdef>";export LC_ALL
#LC_CTYPE="<langdef>";export LC_CTYPE
#LC_COLLATE="<langdef>";export LC_COLLATE
#LC_MONETARY="<langdef>";export LC_MONETARY
#LC_NUMERIC="<langdef>";export LC_NUMERIC
#LC_TIME="<langdef>";export LC_TIME
#LC_MESSAGES="<langdef>"; export LC_MESSAGES

ORACLE_SID=xmeta
ORACLE_HOME=/opt/oracle/product/10.2.0
export ORACLE_HOME ORACLE_SID

LD_LIBRARY_PATH=$ASBHOME/apps/jre/lib/sparcv9/server:$ASBHOME/apps/jre/lib/sparcv9:$ASBHOME/lib/cpp:$ASBHOME/apps/proxy/cpp/sunos-all-sparc_64:$APT_ORCHHOME/lib:$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/rdbms/lib32:`dirname $DSHOME`/branded_odbc/lib:`dirname $DSHOME`/DSComponents/lib:`dirname $DSHOME`/DSComponents/bin:$DSHOME/lib:$DSHOME/uvdlls:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH
export PATH

fi

........................

[chulett]

$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/rdbms/lib32

This is overkill and it needs to see the 32bit libraries rather than the 64bit ones. For Oracle, all you should need is $ORACLE_HOME/lib32 in your LD_LIBRARY_PATH.

[lstsaur]

Your job's error, "Message: Oracle_Enterprise_0: APT_OraReadOperator: connect failed." Also, I saw you are using root user for the Enterprise_Stage?

[chulett]

Hmmm... stopped when I saw "Message: OCI_ERROR: Bad Oracle environment", which is why I asked about the dsenv file.

[sshettar]

Well in development server we did the upgrade first and there it is working fine. Well over there we installed the 8.1 without NLS

and the dsenv file is exactly same as the one in development and we have not been facing any issues in development waht so ever.

Any help on how we could fix the dsenv to make it work in our QA server.( in QA we did the installation with NLS)

Any help is highly appreciated

Thanks in advance

[chulett]

So, other than what I posted which you've chosen to disregard, in other words? Then it seems to me that you should punt this over to your official support provider.

[sshettar]

Oops my bad...
I'm sorry Craig i dint realise you had given the solution of fixing our dsenv file.

I shall change that one and check if it works and will let you know the outcome..

Sorry about it once again...

Thanks

[chulett]

No guarantees but it's an educated guess as to the problem. Let us know if it helps at all.