| Author |
Message
|
| mqmqmq |
 Posted: Mon Sep 01, 2008 2:49 am Post subject: Remote Oracle Connection from Message Broker |
 |
|
Novice
Joined: 02 Jul 2008
Posts: 22
|
Hi,
I have Linux environment and Message Broker v6.0. I have a message flow which should fetch data from remote Oracle database via ODBC. In Windows I can connect and fetch the data from that database by using Oracle SQL Developer. I can also create an ODBC connection in Windows. So the Oracle Database exists and should be OK.
In the message flow's Compute Node properties I have defined Data source (MQ_PR, see the ODBC def below). This Data source is defined in .odbc.ini file in Linux. The $ODBCINI environment variable points to this file.
In the Compute Node there is a very simple SQL which works when I execute that in Oracle SQL Developer in Windows (without the "Database" word). Below you can find part of the code from the message flow:
SET strSQLToOracle = 'SELECT id as id, D1 as D1, D2 as D2 FROM Database.languages';
SET InputLocalEnvironment.Variables.PR1.DBResult1[] = PASSTHRU(strSQLToOracle) ;
I have also run mqsisetdbparam in Linux (stopped and started Broker) successfully and the needed DSN definitions exists in /registry/XXXX_XXXX/currentversion/DSN -directory.
When I run the flow I receive the following error message (Web Service call). Without any Data source definition in Compute Node properties the flow works (ie, the other stuff works in flow code):
| Quote: |
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SOAP-ENV:Body>
<SOAP-ENV:Fault>
<faultcode>SOAP-ENV:Server</faultcode>
<faultstring>BIP3113E: Exception detected in message flow HTTP Input (broker XXXX_XXXX_XX)</faultstring>
<faultactor>HTTP:/MessageBroker/PRSrv</faultactor>
<detail>
<text>Exception. BIP2230E: Error detected whilst processing a message in node 'PRWSFlow.getData Req'. : /build/S600_P/src/DataFlowEngine/ImbComputeNode.cpp: 464: ImbComputeNode::evaluate: ComIbmComputeNode: AtonWSFlow#FCMComposite_1_60
BIP2321E: Database error: ODBC return code '-1'. : /build/S600_P/src/DataFlowEngine/ImbOdbc.cpp: 227: ImbOdbcHandle::checkRcInner: :
BIP2322E: Database error: SQL State ''82''; Native Error Code '0'; Error Text ''523 80''. : /build/S600_P/src/DataFlowEngine/ImbOdbc.cpp: 355: ImbOdbcHandle::checkRcInner: :</text>
</detail>
</SOAP-ENV:Fault>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope> |
Below you can find .odbc.ini file (IP, Port, SID etc. modified):
| Code: |
[ODBC Data Sources]
MQSIBKDB=IBM DB2 ODBC Driver
MYDB=IBM DB2 ODBC Driver
ORACLEDB=DataDirect 5.0 Oracle
SYBASEDB=DataDirect 5.0 Sybase Wire Protocol
SQLSERVERDB=DataDirect 5.0 SQL Server Wire Protocol
WBRKBKDB=IBM DB2 ODBC Driver
MQ_PR=DataDirect 5.0 Oracle
[WBRKBKDB]
Driver=/opt/IBM/db2/V8.1/lib/libdb2.so
Description=WBRKBKDB DB2 ODBC Database
Database=WBRKBKDB
[MQSIBKDB]
Driver=/opt/IBM/db2/V8.1/lib/libdb2.so
Description=MQSIBKDB DB2 ODBC Database
Database=MQSIBKDB
[MYDB]
Driver=/opt/IBM/db2/V8.1/lib/libdb2.so
Description=MYDB DB2 ODBC Database
Database=MYDB
[ORACLEDB]
Driver=<Your install directory>/merant/lib/UKor820.so
Description=DataDirect 5.0 Oracle
EnableDescribeParam=1
OptimizePrepare=1
ServerName=<Your Oracle host>
WorkArounds=536870912
ProcedureRetResults=1
[MQ_PR]
Driver=/opt/ibm/mqsi/6.0/merant/lib/UKor820.so
Description=DataDirect 5.0 Oracle
EnableDescribeParam=1
OptimizePrepare=1
ServerName=POKK=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11.11.11.11)(PORT=1111))(CONNECT_DATA=(SID=POKK)))
WorkArounds=536870912
ProcedureRetResults=1
[SYBASEDB]
Driver=<Your install directory>/merant/lib/UKase20.so
Description=DataDirect 5.0 Sybase Wire Protocol
Database=<Your Database Name>
ServerName=<YourServerName>
EnableDescribeParam=1
OptimizePrepare=1
SelectMethod=0
NetworkAddress=<YourServerName,YourPortNumber>
SelectUserName=1
[SQLSERVERDB]
Driver=<Your install directory>/merant/lib/UKmsss20.so
Description=DataDirect 5.0 SQL Server Wire Protocol
Address=<Your SQLServer host>,<your SQLServer server port>
Database=<Your Database Name>
AnsiNPW=Yes
QuotedId=No
[ODBC]
Trace=1
TraceFile=/var/mqm/log/odbctrace.out
TraceDll=/opt/ibm/mqsi/6.0/merant/lib/odbctrac.so
InstallDir=/opt/ibm/mqsi/6.0/merant
UseCursorLib=0
IANAAppCodePage=4 |
If someone has any ideas what might be the problem I would be very happy. Is the syntax in Server Name in MQ_PR ODBC definition correct? This is quite urgent as I should make this work in two days and I have already fought enough with this.
Thanks a lot!
BR,
----
PR |
|
| Back to top |
|
 |
| mqmqmq |
| Posted: Mon Sep 01, 2008 4:11 am Post subject: |
|
|
Novice
Joined: 02 Jul 2008
Posts: 22
|
An another question concerning my original post (sounds like a newbie, but I have to ask):
Do I need an Oracle client installed in my Broker server to be able to connect to remote Oracle database via ODBC? Or are those merant libraries enough?
If I do need Oracle client installed, what kind of references I have to create (and how to do it?) to the Oracle client libraries (LIBPATH?)?
If some guru reads this post, please take a coffee beside you and write some help for this matter...
Thanks again!
BR,
--
PR
Last edited by mqmqmq on Mon Sep 01, 2008 4:21 am; edited 1 time in total |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Mon Sep 01, 2008 4:13 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
yes you required an Oracle Client to communicate with remote Oracle DB
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| mqmqmq |
| Posted: Mon Sep 01, 2008 4:41 am Post subject: |
|
|
Novice
Joined: 02 Jul 2008
Posts: 22
|
Hi again,
Thanks Gaya3 for your reply!
Below is a part of the example .odbc.ini file:
| Code: |
[ORACLEDB]
Driver=<Your install directory>/merant/lib/UKor820.so
Description=DataDirect 5.0 Oracle
EnableDescribeParam=1
OptimizePrepare=1
ServerName=<Your Oracle host>
WorkArounds=536870912
ProcedureRetResults=1 |
Where should ServerName in the example above refer? Inside the tsnames.ora file where are the connector descriptions or directly to remote Oracle database (IP address)? If it is the latter where should I put SID and port information? Is it even possible to write the following if I want to make this work:
| Code: |
| ServerName=POKK=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11.11.11.11)(PORT=1111)) |
Thanks!
This is my last post for today, I promise:-)
BR,
--
PR |
|
| Back to top |
|
|
| AkankshA |
| Posted: Mon Sep 01, 2008 8:04 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
Server name would be the Ip address of the remote DB server...
cvonfigure your tnsnames.ora file for adding sid and other information...
_________________
Cheers |
|
| Back to top |
|
|
| mqmqmq |
| Posted: Mon Sep 01, 2008 10:29 pm Post subject: |
|
|
Novice
Joined: 02 Jul 2008
Posts: 22
|
Thanks for the reply!
It seems that Broker (IBM) supports Oracle Runtime Client and not Oracle Instant Client with DataDirect drivers.
I still have problems with connection to remote Oracle Database. If I have the IP as a ServerName (see below) and OracleClient is installed and tnsnames.ora is configured correctly, how does Broker use these OracleClient files? Does Broker do that automatically or should some Linux environment variables be set? Do I need to execute some profile file (like db2profile in DB2) if that exists after installation of OracleClient?
| Code: |
[MQ_PR]
Driver=/opt/ibm/mqsi/6.0/merant/lib/UKor820.so
Description=DataDirect 5.0 Oracle
EnableDescribeParam=1
OptimizePrepare=1
ServerName=11.11.11.11
WorkArounds=536870912
ProcedureRetResults=1 |
I know, these issues are easy when you get it done right even once so could someone please write little more help with this. I'm sure that this is not far a way from successful connection.
Thanks!
BR,
--
PR |
|
| Back to top |
|
|
| diego_maradona21 |
| Posted: Tue Sep 02, 2008 12:00 am Post subject: |
|
|
Novice
Joined: 24 Jul 2008
Posts: 17
|
have you tried this:
| Code: |
POKK = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =111.11.1.11)(PORT = 1111))(CONNECT_DATA = (SID = POKK)))
|
in tnsnames.ora
than in .odbc.ini:
| Code: |
[POKK]
Driver=/opt/IBM/mqsi/6.0/merant/lib/UKor820.so
Description=DataDirect 5.0 Oracle
EnableDescribeParam=1
OptimizePrepare=1
ServerName=POKK
WorkArounds=536870912
ProcedureRetResults=1
|
|
|
| Back to top |
|
|
| mqmqmq |
| Posted: Fri Sep 05, 2008 4:23 am Post subject: |
|
|
Novice
Joined: 02 Jul 2008
Posts: 22
|
As I thought it was quite close to have a success connection to remote Oracle DB. Now everything works! Thanks to all who have replied to my topic!
BR,
--
PR |
|
| Back to top |
|
|
|
|
