| Author |
Message
|
| Sridar |
 Posted: Tue Aug 12, 2008 6:09 am Post subject: [Solved]Schema in ODBC.ini file |
 |
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Hi
We have the following environment
Solaris 10 Sparc
Oracle 10g Database
WMB 6.0
MQ 6.0
We have the Broker database schema and a Audit database schema in the Oracle database.
Am creating a 32 bit DSN using the odbc.ini file in var/mqsi/odbc folder.
Can anyone tell me where i need to specify the schema name in this file. The broker database schema is brkdb.
I have given below the tns.ora file and the odbc.ini contents
tns.ora
| Code: |
qa1 =
(DESCRIPTION =
(enable=broken)
(ADDRESS_LIST =
(failover=on)
(load_balance=on)
(ADDRESS = (PROTOCOL = TCP)(Host = polaris.capital.com)(Port = 1538))
(ADDRESS = (PROTOCOL = TCP)(Host = polaris.capital.com)(Port = 1538))
)
(CONNECT_DATA =
(SERVICE_NAME = qa1)
(failover_mode=
(type=select)
(method=basic)
(retries=180)
(delay=5))
)
)
|
odbc.ini
| Code: |
[brkdb]
Driver=/appbin/IBM/mqsi/6.0/merant/lib/UKor820.so
Description=DataDirect 5.0 Oracle
HostName=polaris.capital.com)(Port = 1538))
PortNumber=1538
SID=qa1
EnableDescribeParam=1
OptimizePrepare=1
ServerName=polaris.capital.com)(Port = 1538))
WorkArounds=536870912
ProcedureRetResults=1
|
Can anyone tell me where i have to give the schema details in the odbc.ini file?
_________________
Thanks and Regards
Sridar
Last edited by Sridar on Mon Aug 18, 2008 6:06 am; edited 2 times in total |
|
| Back to top |
|
 |
| Sridar |
| Posted: Tue Aug 12, 2008 11:17 pm Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Hi
I am actually getting this error:
| Code: |
BIP2321E: Database error: ODBC return code '-1'.
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
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''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error.
BIP8040E: Unable to connect to the database.
The database cannot be accessed with the userid and password that were specified when the broker was created.
Check that the database is running, that an ODBC connection has been created and that the userid and password pair specified for ODBC connect on the mqsicreate command are capable of being used to connect to the database using an ODBC connection. Also ensure that the database has a adequate number of database connections available for use.
|
I thought the error could be because of the datasource and that it is not being resolved through the odbc.ini file.
I am able to connect through the sqlplus command in the oracle client.
I have also added $Oracle_Home/lib32 in the library path.
Anyone has any ideas?
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
| marko.pitkanen |
| Posted: Wed Aug 13, 2008 12:36 am Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
Hi,
How you are refering to the table from ESQL?
With DB2 I don't introduce schema of used tables in odbc.ini, but I'm refering to the right schema with in ESQL clause for example:
| Quote: |
WebSphere Message BrokerESQL Version 6 Release
.
.
.
SELECT * FROM Database.Datasource.SchemaName.Table As A
SELECT A.* FROM Database.Datasource.SchemaName.Table As A
SELECT A FROM Database.Datasource.SchemaName.Table AS A |
Marko |
|
| Back to top |
|
|
| Sridar |
| Posted: Wed Aug 13, 2008 3:07 am Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Hi
am not referring from ESQL.
As i have mentioned it is for the broker database while creating the broker.
The database is in a different server.
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
| marko.pitkanen |
| Posted: Wed Aug 13, 2008 4:56 am Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
Ok,
Have you read this?
| Quote: |
When you create a broker, the database tables required by that component are created in the default schema that is associated with the user ID used to access the database. Specify this user ID when you run the mqsicreatebroker command.
For DB2 and Oracle, the default behavior is for the schema name to be the same as the user ID that is used to access the database.
|
|
|
| Back to top |
|
|
| Sridar |
| Posted: Wed Aug 13, 2008 5:56 am Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Hi marko.pitkanen
Does this mean that we cannot create a broker database schema with our own name and that broker will create a broker database schema on the name of the user only.
am giving the command like this
| Code: |
mqsicreatebroker brk -i mqm -a id1234id -q qmgr -n brkdb -u id -p pwd
|
here mqm is the service user id and id is the user id for the broker database schema.
_________________
Thanks and Regards
Sridar
Last edited by Sridar on Sun Aug 17, 2008 8:44 pm; edited 1 time in total |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Aug 13, 2008 2:18 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Talk to your Oracle Admin. Oracle schemas can be user dependent (assigned).
Enjoy 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Aug 13, 2008 11:41 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I good tip for Oracle DB's is to verify that you can indeed login to the Oracle DB using the id & Password you plan on using before trying to create the broker.
This verifies that the tnsnames.ora/listener.ora are all working correctly.
Use sqlPlus and see if you can connect to the DB
Then if the broker creation fails it probably down to one of two problems
1) Human Error - mostly a typo
2) an error in the .odbc.ini
Stephen D
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Sridar |
| Posted: Thu Aug 14, 2008 12:46 am Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Hi smdavies99,
Thanks.
We had an Oracle instantclient earlier.
I replaced with the normal client 10.2 and this time am getting
| Code: |
''[DataDirect][ODBC Oracle driver][Oracle]ORA-12541: TNS:no listener ''
|
We are however able to connect with sqlplus and this is what we get.
| Code: |
$ /appbin/oracle/10.2.0.3/instantclient_10_2/sqlplus id/pwd@qa1
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Aug 14 04:27:33 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
|
I have given the odbc.ini file in my first post.
Do you see anything i had missed or given incorrectly?
_________________
Thanks and Regards
Sridar
Last edited by Sridar on Sun Aug 17, 2008 8:47 pm; edited 1 time in total |
|
| Back to top |
|
|
| Sridar |
| Posted: Thu Aug 14, 2008 12:49 am Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Hi
To add to my previous post the DBA confirmed me that the listeners are indeed running.
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
| Sridar |
| Posted: Mon Aug 18, 2008 5:04 am Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Hi All
This problem got resolved.
It was a simple mistake which i had overlooked because of misunderstanding of the terminology.
For the server name property in the odbc.ini file i had given the value of the server name that was in the tns.ora file.
The tns.ora file Server refers to the database box name, while the odbc.ini file Server refers to the database name or the SID.
I changed it and things are fine.
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
|
|
