Oracle connectivity

aramachandra · Post by **aramachandra** » Mon Jul 28, 2008 10:12 am

Hi

I need some help in setting my access to Oracle.

We are unix based shop and we have version 8.0.1 running on AIX 5.3

On the unix host the oracle client is installed and I can connect to oracle using sqlplus.

In dsenv file I have the following entries

### Oracle Environment Variables ###
export DB_ALIAS=no_db
export ORACLE_BASE=/oracle
export ORACLE_RELEASE=v920
export ORACLE_HOME=$ORACLE_BASE/product/$ORACLE_RELEASE
export ORACLE_PATH=$ORACLE_HOME/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin

export PATH=$APT_ORCHHOME/bin:$ORACLE_HOME/bin:$PATH
export LIBPATH=$ORACLE_HOME/lib32:$LIBPATH

### end of oracle variables ###

In my datastage job I am trying to do an orachestrate import of oracle meta data to see if I can view my oracle tables and I get this error

##E IIS-DSEE-TCDB-00004 10:49:21(002) <main_program> Unable to access database oracleLibrary orchoracle could not be loaded;<b> Could not load "orchoracle": 0509-022 Cannot load module . </b>
0509-026 System error: A file or directory in the path name does not exist..

The other question I have is
<b>
under the path

/usr/IBM/InformationServer/Server/DSComponents/install

i do have an install.liborchoracle.

If my oracle connection is working on the server itself do I still need to install this part

What does this install do?
</B>

Thanks for all the help in advance

Arvind

aramachandra · Post by **aramachandra** » Mon Jul 28, 2008 11:45 am

This is the output of my

ls -l *or* from PXEngine/lib folder

lrwxrwxrwx 1 root system 11 Dec 07 2007 liborchstatsaix3.so -> orchstats.o
lrwxrwxrwx 1 root system 10 Dec 07 2007 liborchsortaix3.so -> orchsort.o
lrwxrwxrwx 1 root system 12 Dec 07 2007 liborchsasop9aix3.so -> orchsasop9.o
lrwxrwxrwx 1 root system 12 Dec 07 2007 liborchsasop8aix3.so -> orchsasop8.o
lrwxrwxrwx 1 root system 14 Dec 07 2007 liborchsasop612aix3.so -> orchsasop612.o
lrwxrwxrwx 1 root system 14 Dec 07 2007 liborchresourceaix3.so -> orchresource.o
lrwxrwxrwx 1 root system 12 Dec 07 2007 liborchresestaix3.so -> orchresest.o
lrwxrwxrwx 1 root system 13 Dec 07 2007 liborchmonitoraix3.so -> orchmonitor.o
lrwxrwxrwx 1 root system 10 Dec 07 2007 liborchio64aix3.so -> orchio64.o
lrwxrwxrwx 1 root system 13 Dec 07 2007 liborchgeneralaix3.so -> orchgeneral.o
lrwxrwxrwx 1 root system 14 Dec 07 2007 liborchfreqdistaix3.so -> orchfreqdist.o
lrwxrwxrwx 1 root system 14 Dec 07 2007 liborchcommondbaix3.so -> orchcommondb.o
lrwxrwxrwx 1 root system 13 Dec 07 2007 liborchbuildopaix3.so -> orchbuildop.o
-rwxr-xr-x 1 dsadm dstage 21108183 Jan 09 2008 liborchaix3.a
-rwxr-xr-x 1 dsadm dstage 2419732 Jan 09 2008 liborchcoreaix3.a
-rwxr-xr-x 1 dsadm dstage 135705 Jan 09 2008 orchresource.o
-rwxr-xr-x 1 dsadm dstage 4350664 Jan 09 2008 orchgeneral.o

lstsaur · Post by **lstsaur** » Mon Jul 28, 2008 12:29 pm

Oracle client installation is one of the pre-requisites before you start installing the DS server for the first time on a Windows or Unix platform.
I have found that most of time due to Oracle client software's permissions are not given properly to the installer during the initial installation of DS, then the script, install.liborchoracle, will just skip the installation of the liborchoracle despite Oracle's info. was provided during the installation.

So, just set and export your Oracle_Home and APT_ORCHHOME one more time and re-execute the script install.liborchoracle from the command prompt, then you will see "Installing Oracle Driver" and "Oracle installation is complete" messages.

aramachandra · Post by **aramachandra** » Mon Jul 28, 2008 12:36 pm

Thanks

What does the install.liborchoracle install?

I am finding that above install file under

/usr/IBM/InformationServer/Server/DSComponents

When i run it i get the following permissions error

$ ./install.liborchoracle
Installing Oracle driver.
Setting PX_DBCONNECTHOME variable for PXOperators
You do not have write permssions at <./DSComponents/bin>
Exiting.
$

But as you see below it has full privilege so I am a little confused

drwxrwxrwx 2 dsadm dstage 8192 Jul 28 13:34 bin

arvind

aramachandra · Post by **aramachandra** » Mon Jul 28, 2008 12:56 pm

After the install I now get this message when I try to import a metadata

##I IIS-DSEE-TUTL-00031 13:54:38(001) <main_program> The open files limit is 2000; raising to 2147483647.
##E IIS-DSEE-TCDB-00004 13:54:38(002) <main_program> Unable to access database oracleLibrary orchoracle could not be loaded; Could not load "orchoracle": 0509-022 Cannot load module /usr/IBM/InformationServer/Server/DSComponents/bin/orchoracle.o.
0509-150 Dependent module libclntsh.a(shr.o) could not be loaded.
0509-022 Cannot load module libclntsh.a(shr.o).
0509-026 System error: A file or directory in the path name does not exist.
0509-022 Cannot load module /usr/IBM/InformationServer/Server/DSComponents/bin/orchoracle.o.
0509-150 Dependent module /usr/IBM/InformationServer/Server/DSComponents/bin/orchoracle.o could not be loaded..

aramachandra · Post by **aramachandra** » Mon Jul 28, 2008 12:58 pm

Is ORACLE_SID mandatory environment variable to be defined

In my dsenv I do not define the variable.

As per our DBA since the oracle host is not on the same host as datastage it is not required to define that

Arvind

chulett · Post by **chulett** » Mon Jul 28, 2008 12:59 pm

aramachandra · Post by **aramachandra** » Mon Jul 28, 2008 1:09 pm

Thanks

I am assuming a "No" means I do not need to define the ORACLE_SID environment variable

The modules it was complaining about are avaiabable as shown below

$ pwd
/oracle/product/v920/lib32
$ ls -altr libcln*
-rw-r--r-- 1 oracle dba 12993753 Jul 01 12:47 libclntsh.a
-rw-r--r-- 1 oracle dba 17478721 Jul 01 12:49 libclntst9.a

I am wondering if it is a path issue

arvind

chulett · Post by **chulett** » Mon Jul 28, 2008 1:10 pm

Correct.

aramachandra · Post by **aramachandra** » Mon Jul 28, 2008 2:24 pm

Here is another question

I have theoracle 9i client driver installed.

But I am accessing a 8i database.

It works using sqlplus so I am assuming the tool will be ok as well

Arvind

aramachandra · Post by **aramachandra** » Mon Jul 28, 2008 2:31 pm

I have the following in my PXENGINE/lib folder

orchoracle.o -> /usr/IBM/InformationServer/Server/DSComponents/bin/orchoracle9i.o
drwxr-xr-x 2 dsadm dstage 4096 Jul 28 14:03 .
$ pwd
/usr/IBM/InformationServer/Server/PXEngine/lib

chulett · Post by **chulett** » Mon Jul 28, 2008 3:32 pm

aramachandra wrote:I have the oracle 9i client driver installed. But I am accessing a 8i database.

I for one wouldn't assume any level of ok-ness here. There's no guarantee from Oracle with regards to backwards compatibility of the client. What will work is to use the client from the lowest version of the database you are accessing - in this case, 8i to access 8i and 9i.

keshav0307 · Post by **keshav0307** » Mon Jul 28, 2008 7:53 pm

you should have liborchoracle9ii686.so somewhere in your library paths
and in $PXENGINE/lib one link orchoracle.so to point this file.

jasper · Post by **jasper** » Tue Jul 29, 2008 12:28 am

as far as I know there is no issue with connecting from a 9i client to an 8 database. However there is an issue from datastage 8 with connecting to Oracle 8. No matter which driver: it is not supported.

chulett · Post by **chulett** » Tue Jul 29, 2008 8:03 am

That's interesting... and unfortunate. No such problem in earlier versions that I've ever heard.