| Author |
Message
|
| bbakerman |
 Posted: Mon May 01, 2006 7:56 pm Post subject: Using Oracle instantclient 10_2 on AIX and MB v5CSD6 |
 |
|
Apprentice
Joined: 17 Dec 2003
Posts: 41
|
I thought some people might be interested in how to get MB v5 connected to an Oracle backend using the "newish" Oracle instantclient software.
I buggered around with this for a few days and finally got it working. As it turned out this was still faster than trying to find an 8i oracle client for AIX 5 so it saved m,e time.
So I thought this might help a few other people save time as well.
The Oracle instantclient allows OCI/JDBC applications to connect to an Oracle server without having to have the full Oracle client installed.
First off go to
http://www.oracle.com/technology/tech/oci/instantclient/index.html
and get the instantclient software you need.
I grabbed the basic and sqlplus packages. Unzip them into a directory of you choice. I used /usr/oracle/instantclient_10_2.
You should then have :
| Code: |
-rwxr-xr-x 1 root system 1593906 May 02 13:31 classes12.jar
-rwxr-xr-x 1 root system 1525 May 02 13:31 glogin.sql
-rwxr-xr-x 1 root system 21870588 May 02 13:31 libclntsh.a
-rwxr-xr-x 1 root system 4238382 May 02 13:31 libnnz10.so
-rwxr-xr-x 1 root system 5701464 May 02 13:31 libocci.a
-rwxr-xr-x 1 root system 70107430 May 02 13:31 libociei.so
-rwxr-xr-x 1 root system 20666086 May 02 13:31 libocijdbc10.so
-rwxr-xr-x 1 root system 21686747 May 02 13:31 libsqlplus.so
-rwxr-xr-x 1 root system 1432666 May 02 13:31 libsqlplusic.so
-rwxr-xr-x 1 root system 1540330 May 02 13:31 ojdbc14.jar
-rwxr-xr-x 1 root system 21616795 May 02 13:31 sqlplus
|
You need to set LD_LIBRARY_PATH and LIBPATH to include this new directory.
| Code: |
LIBPATH=$LIBPATH:/usr/oracle/instantclient_10_2;
export LIBPATH
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/oracle/instantclient_10_2
export LD_LIBRARY_PATH
|
You can use sqlplus to connect to your target Oracle database.
| Code: |
sqlplus userid/password@//hostname:port/db_name_sid
|
or in my case
| Code: |
sqlplus usrv500/passwordXXX@//10.33.129.3:1521/learn1
|
In my case this connected successfully to a old legacy Oracle 8 database
| Code: |
SQL*Plus: Release 10.2.0.2.0 - Production on Tue May 2 13:38:06 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning and Parallel Server options
|
The next bit is to get the broker configured so it can make and ODBC connection to the Oracle database (via the OCI fo the instantclient code)
Again make sure the .profile of the user you run broker v5 as has the LIBPATH and LD_LIBRARY_PATH entries.
Inside the /var/mqsi/odbc.odbc.ini I had these entries
| Code: |
[ODBC Data Sources]
ORACLEWZEZDB=DataDirect 410 Oracle Driver
[ORACLEWZEZDB]
Driver=/usr/opt/mqsi/merant/lib/UKor818.so
Description=DataDirect 410 Oracle Driver
ServerName=//10.33.129.3:1521/learn1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
WorkArounds2=2
[ODBC]
Trace=0
TraceFile=/var/wmqi/odbc/odbctrace.out
TraceDll=/usr/opt/wmqi/merant/lib/odbctrac.so
InstallDir=/usr/opt/wmqi/merant
|
The instantclient code is able to "resolve" the qualified server name "//10.33.129.3:1521/learn1" in order to make a connection to the Oracle datbase over the network.
Finally you have to tell the broker what userid/password to use for this DSN entry
So (when the broker is stopped) you invoke
| Code: |
mqsisetdbparms brokername -n DataSourceName -u DataSourceUserId -p DataSourcePassword
|
or in my case
| Code: |
mqsisetdbparms TESTBRK01 -n ORACLEWZEZDB -u usrv500 -p passwordXXX
|
Now you have a DataSource ready for the broker to use. You must then use ORACLEWZEZDB as the data source name in the compute node containing the SQL you want to execute.
If you havent got the LIBPATH and LD_LIBRARY_PATH set properly you will get an error in the syslog something like:
| Code: |
May 2 11:49:59 draco user:err|error MQSIv500[1069152]: (TESTBRK01.train)[5141]BIP2321E: Database error: ODBC return code '-1'. : TESTBRK01.9db23920-0a01-0000-0080-dbbae121a8bc: /build/S500_P/src/DataFlowEngine/ImbOdbc.cpp: 213: ImbOdbcHandle::checkRcInner: :
May 2 11:49:59 draco user:err|error MQSIv500[1069152]: (TESTBRK01.train)[5141]BIP2322E: Database error: SQL State 'IM003'; Native Error Code '80'; Error Text 'Specified driver could not be loaded'. : TESTBRK01.9db23920-0a01-0000-0080-dbbae121a8bc: /build/S500_P/src/DataFlowEngine/ImbOdbc.cpp: 366: ImbOdbcHandle::checkRcInner: :
|
If you havent got the Oracle "ServerName" right you will get something like:
| Code: |
May 2 12:20:07 draco user:err|error MQSIv500[356528]: (TESTBRK01.train)[5141]BIP2321E: Database error: ODBC return code '-1'. : TESTBRK01.9db23920-0a01-0000-0080-dbbae121a8bc: /build/S500_P/src/DataFlowEngine/ImbOdbc.cpp: 213: ImbOdbcHandle::checkRcInner: :
May 2 12:20:07 draco user:err|error MQSIv500[356528]: (TESTBRK01.train)[5141]BIP2322E: Database error: SQL State 'HY000'; Native Error Code '12154'; Error Text '[DataDirect][ODBC Oracle driver][Oracle]ORA-12154: TNS:could not resolve the connect identifier specified '. : TESTBRK01.9db23920-0a01-0000-0080-dbbae121a8bc: /build/S500_P/src/DataFlowEngine/ImbOdbc.cpp: 366: ImbOdbcHandle::checkRcInner: :
|
Good luck. |
|
| Back to top |
|
 |
| Kateel |
| Posted: Tue May 02, 2006 5:18 am Post subject: |
|
|
Novice
Joined: 01 May 2006
Posts: 17
|
| I guess for AIX you only need to set LIBPATH and not LD_LIBRARY_PATH (which is applicable to Solaris and Linux only) |
|
| Back to top |
|
|
| Ian |
| Posted: Fri May 12, 2006 1:16 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Note, you can choose one of the following installation types when installing an Oracle Client:
* Administrator
* Runtime
* Custom
* Instant Client
In light of this discussion (and because we have recently received a customer PMR which also covers this question) we will update the Message Broker support pages to clarify this point.
The combination of Message Broker and the DataDirect drivers has been tested and verified to work with the Oracle Runtime Client.
This does not extend to the Oracle Instant Client.
If customers have a specific need for this then they should submit a requirement to IBM.
It is worth noting that (as in this case) that some customers may configure Message Broker with the Oracle Instant Client which may *appear* to work. This does not infer that it will work in *ALL* situations. Further, and most importantly, this does not mean that IBM will support this configuration. Supported configurations have been tested as part of our lifecycle test process which covers supported platforms, database version and Message Broker functionality (and in the case of database functionality this would cover areas such as Coordinated Transactions).
This has been done for the Oracle Runtime Client but not the Oracle Instant Client and therefore from an IBM Message Broker perspective the latter is not supported.
_________________
Regards, Ian |
|
| Back to top |
|
|
| Sridar |
| Posted: Thu Aug 21, 2008 9:34 pm Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
HI Ian
Thanks a lot.
I was trying to connect using a InstantClient and could not reason out why my broker was failing to connect even if everything was correct.
However since the thread is a old thread and WMB 6+ is also out can you let me know if the instantclient non-compatability still holds.
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
| Sridar |
| Posted: Thu Aug 21, 2008 9:37 pm Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
HI Ian
Thanks a lot.
I was trying to connect using a InstantClient and could not reason out why my broker was failing to connect even if everything was correct.
However since the thread is a old thread and WMB 6+ is also out can you let me know if the instantclient non-compatability still holds.
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
| Ian |
| Posted: Wed Aug 27, 2008 1:20 pm Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Websphere Message Broker does not provide support for the Oracle Instant Client.
In summary, the combination of Message Broker and the DataDirect drivers has been tested and verified to work with the Oracle Runtime Client.
This does not extend to the Oracle Instant Client.
This applies WMB V5, V6 and V6.1
The WMB V6.1 the SOE states that "Oracle Database Server requires the Oracle Runtime Client if using 32-bit Execution Groups".
This distinction is because the 64 bit ODBC driver is a 'wire protocol' driver and does not need any Oracle client - it does the network hop to the server itself.
_________________
Regards, Ian |
|
| Back to top |
|
|
| Sridar |
| Posted: Wed Aug 27, 2008 8:42 pm Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Thanks Ian,
The Reply is very useful for us now in planning our Deployment platform.
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
|
|
