| Author |
Message
|
| KSkelton |
 Posted: Fri Sep 05, 2003 6:38 am Post subject: Remote Oracle on Solaris from Compute Node problem |
 |
|
Apprentice
Joined: 28 Oct 2001
Posts: 45
|
WMQI 2.1 CSD2
Solaris 8 Broker install with DB2 local
Hi,
I am trying to make a simple Oracle call from my compute node running on a Solaris Broker (it works from Windows brokers).
I have setup the /var/wmqi/odbc/.odbc.ini file to include the Oracle instance and uses the 3.7 Merant Oracle drivers.
tnsnames.ora is setup correctly and is found in: /etc.
/opt/wmqi/merant/lib as well as the Oracle home directory are found in the LD_LIBRARY_PATH.
The compute node has the instance listed as an input (via the add button)
and the table and columns defined underneath it.
Here is the exception:
Text = Child SQL exception
Catalog = WMQIv210
Severity = 3
Number = 2322
Insert =
Type = 5
Text = IM002
Insert =
Type = 2
Text = 0
Insert =
Type = 5
Text = [MERANT][ODBC lib] Data source name not found and no default driver specified
What am I missing?
Is there something else that needs to be in the LD_LIBRARY_PATH like tnsnames.ora or /var/wmqi?
Thanks. |
|
| Back to top |
|
 |
| vmcgloin |
| Posted: Tue Sep 23, 2003 4:55 am Post subject: |
|
|
Knight
Joined: 04 Apr 2002
Posts: 560
Location: Scotland
|
I have the same symptoms (except on AIX). How did you resolve this?
Thanks,
Vicky |
|
| Back to top |
|
|
| KSkelton |
| Posted: Tue Sep 23, 2003 5:09 am Post subject: |
|
|
Apprentice
Joined: 28 Oct 2001
Posts: 45
|
Vicky,
I did not run the mqsi_setupdatabase command as found in the Admin guide on page 32:
For Oracle 8.1.7 and Sybase
You are recommended to complete this setup after you have installed your
database, but you can complete it before database installation if you specify the
intended database installation directory correctly during this setup.
You must issue the following command:
mqsi_setupdatabase <database_type> <database_install_directory>
where <database_type> is oracle or sybase and <database_install_directory> is
the directory in which the database is (or will be) installed. |
|
| Back to top |
|
|
| vmcgloin |
| Posted: Tue Sep 23, 2003 6:56 am Post subject: |
|
|
Knight
Joined: 04 Apr 2002
Posts: 560
Location: Scotland
|
| Thanks. I did not realise I had to do that for a user database. |
|
| Back to top |
|
|
| Testo |
| Posted: Tue Oct 28, 2003 5:57 am Post subject: Similar error... please help! |
|
|
Centurion
Joined: 26 Feb 2003
Posts: 120
Location: Italy - Milan
|
I have this trace from a simple test flow to verify the connection to an Oracle 8.1.7 DB on a Sun Solaris machine:
DatabaseException BIP2322E: Database error: SQL State 'IM002';
Native Error Code '0'; Error Text '[DataDirect][ODBC lib] Data source name not found and no default driver specified'.
The error has the following diagnostic information:
SQL State 'IM002'
SQL Native Error Code '0'
SQL Error Text '[DataDirect][ODBC lib] Data source name not found and no default driver specified'
I have:
- setup the /var/wmqi/odbc/.odbc.ini file to include the Oracle instance and uses the 3.7 Merant Oracle drivers
- /opt/wmqi/merant/lib as well as the $ORACLE_HOME/lib are in the $LD_LIBRARY_PATH
- run the command 'mqsi_setupdatabase oracle8 $ORACLE_HOME' creating succesfully the two links
I don't have the tsnnames.ora file anywhere....
The broker is fine and deploys are very fast.... The only problem is with a User DB I need for my application.
Help!
Andrea Tedone
IBM AMS EMEA SOUTH |
|
| Back to top |
|
|
| Testo |
| Posted: Tue Oct 28, 2003 10:13 am Post subject: Resolved! |
|
|
Centurion
Joined: 26 Feb 2003
Posts: 120
Location: Italy - Milan
|
It was simply a problem with the Oracle instance name, now resolved.
Cheers,
Andrea Tedone |
|
| Back to top |
|
|
| lliu |
| Posted: Mon Nov 17, 2003 7:27 am Post subject: Re: Remote Oracle on Solaris from Compute Node problem |
|
|
Newbie
Joined: 24 Jan 2002
Posts: 6
Location: AXA IM
|
I have the same problem: the wmqi broker (with db2 database) on solaris cannot access remote Oracle database from Compute Node. I did the following set up:
1. modified odbc.ini file:
[SOPHIS_INTERFACE]
Driver=/opt/wmqi/merant/lib/UKor816.so
WorkArounds=536870912
WorkArounds2=2
Description=
ServerName=RISKD
LogonID=wmqi
Password=wmqi_pw
EnableDescribeParam=1
OptimizePrepare=1
NetworkAddress=10.128.192.106,1527
2. run mqsi_databasesetup command:
mqsi_setupdatabase Oracle $ORACLE_HOME
the output from the command is: the softlinks are set up.
But the broker still cannot access the oracle database. Can someone tell me what else I need to do to make the connection between the wmqi broker and an remote oracle database.
Thanks
Lynn |
|
| Back to top |
|
|
| vmcgloin |
| Posted: Mon Nov 17, 2003 7:31 am Post subject: |
|
|
Knight
Joined: 04 Apr 2002
Posts: 560
Location: Scotland
|
Did you add the matching line at the top of the odbc.ini file?
Also, I do not think the userid and password will be picked up from the odbc.ini file (at least in 2.1) - someone else might clarify this. So, it is worth checking that you have access to the required database & tables with the userid and password that the broker service uses. This will also show that your tnsnames.ora file is correctly setup.
Can you maybe post the actual error that you are getting too?
Good luck. |
|
| Back to top |
|
|
| lliu |
| Posted: Mon Nov 17, 2003 7:42 am Post subject: |
|
|
Newbie
Joined: 24 Jan 2002
Posts: 6
Location: AXA IM
|
Thanks. There is a line on the top part of odbc.ini to declare the use of Merant Oracle driver.
I can access the remote Oracle database with broker ID/password from the broker server outside of the WMQI through sqlplus. Therefore, I think the tnsnames.ora is setup properly. But just the broker cannot connect to the Oracle database.
Please help.
Thanks |
|
| Back to top |
|
|
| lliu |
| Posted: Mon Nov 17, 2003 9:02 am Post subject: |
|
|
Newbie
Joined: 24 Jan 2002
Posts: 6
Location: AXA IM
|
OK, we've made some progress. We tested the flow using a known Sybase ODBC access and it worked OK. Now having bounced the broker (for the 1,000th time  ), we are reaching the Oracle DB. However, we cannot access the data.
The error is:-
| Quote: |
(0x3000000)Text = '[MERANT][ODBC Oracle 8 driver][Oracle 8]ORA-01017: invalid username/password; logon denied
|
However, when we use SQLPlus from the broker server, we can access the data. The ID and password we are using for this is the same as the ID under which the broker runs.
Is there something else missing?
Thanks,
Lynn. |
|
| Back to top |
|
|
| lliu |
| Posted: Thu Nov 20, 2003 3:27 am Post subject: |
|
|
Newbie
Joined: 24 Jan 2002
Posts: 6
Location: AXA IM
|
Folks,
We have got the connection working now but it does not behave as we expected.
Our broker processes run under the ID "wmqi".
| Quote: |
wmqi 2570 1 0 12:17:59 ? 0:00 bipservice BWMQD02
wmqi 2592 2571 0 12:18:07 ? 0:05 DataFlowEngine BWMQD02 97bc06fa-f500-0000-0080-81d1297688ac PMS 0
wmqi 2571 2570 0 12:17:59 ? 0:01 bipbroker BWMQD02
wmqi 2579 2571 0 12:18:00 ? 0:06 DataFlowEngine BWMQD02 0f8206fa-f500-0000-0080-81d1297688ac MMS 1
wmqi 3071 429 0 12:25:19 pts/6 0:00 grep BWMQD02
|
And this ID is the one used by WMQI to access our Sybase databases.
However, the Oracle database is being accessed by the "db2admin" ID.
Can anyone explain this?
Thanks,
Lynn. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Nov 20, 2003 6:07 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| lliu wrote: |
Folks,
We have got the connection working now but it does not behave as we expected.
Our broker processes run under the ID "wmqi".
And this ID is the one used by WMQI to access our Sybase databases.
However, the Oracle database is being accessed by the "db2admin" ID.
|
There are two users defined to WMQI. The "service user", which in your case is 'wmqi', and the "database user", which I assume in your case is 'db2admin' (which may not be the best practice).
Generally, WMQI uses the database user to access *all* databases. There are exceptions - for instance with an SQLServer ODBC datasource on Windows you can override the user for each particular datasource in a couple of ways. I assume that something similar is going on with your Sybase ODBC data sources - that you've somehow specified an override for the datasource to use the service user instead or that the datasource itself only uses the "user running the process".
So what you're seeing is what should be happening - except that your Sybase stuff happens differently. That is, it's the Sybase access that's "unusual", not the Oracle access.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
