| Author |
Message
|
| muthu_tek |
 Posted: Sat Apr 23, 2011 9:08 pm Post subject: JDBC TYPE 4 Oracle Connection Problem in Message Broker |
 |
|
Apprentice
Joined: 21 Jan 2010
Posts: 36
Location: Coimbatore,India
|
Dear Once,
I try to access oracle database from java compute node in message broker 6.1.0.5. I configured configurable service for JDBC Type 4 connection But I couldnt access database.
JDBCProviders
DEVSOA
| Code: |
connectionUrlFormat='jdbc:oracle:thin:muthu/tek123@10.56.11.123:
1521:DEVSOA'
connectionUrlFormatAttr1='DEVSOA'
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='DB_SERVICES'
databaseType='Oracle'
databaseVersion='10.2.0'
description='default_Description'
environmentParms='default_none'
jarsURL='D:\oracle\app\product\10.2.0\client_1\jdbc\lib'
portNumber='1521'
securityIdentity='default_User@default_Server'
serverName='10.56.11.123'
type4DatasourceClassName='oracle.jdbc.xa.client.OracleXADataSource'
type4DriverClassName='oracle.jdbc.OracleDriver' |
And My java compute code is
| Code: |
try{
Connection conn = getJDBCType4Connection("DEVSOA", JDBC_TransactionType.MB_TRANSACTION_AUTO);
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet result = stmt.executeQuery
("SELECT NAME FROM EMPLOYEE");
System.out.println("Got results:");
while(result.next()) { // process results one row at a time
String val = result.getString(1);
System.out.println("val = " + val);
}
}catch(Exception se){
se.printStackTrace();
System.out.println("Database Error -------------->>"+se.getMessage());
}
|
I am getting exception like
<com.ibm.broker.plugin.MbRecoverableException class:MbNode method:getJDBCType4Connection source:BIPv610 key:6233 >
at com.ibm.broker.plugin.MbNode.getJDBCType4Connection(MbNode.java:1569)
at DbServicesMsgFlow_JavaCompute.evaluate(DbServicesMsgFlow_JavaCompute.java:37)
at com.ibm.broker.javacompute.MbRuntimeJavaComputeNode.evaluate(MbRuntimeJavaComputeNode.java:179)
at com.ibm.broker.plugin.MbNode.evaluate(MbNode.java:1476)
Database Error -------------->>
BIPv610:6233]BIP6233E: An error occurred in node: JavaCompute There was a problem establishing a connection to the given database URL: DEVSOA Exception details: java.lang.reflect.InvocationTargetException
The node was unable to establish a connection to the given database URL.
Contact your WebSphere Message Brokers administrator. : Failed to obtain JDBC Connection
What should be a problem ? and I tried using securityIdentity ='mysec' on configurable service
mqsisetdbparms brokername -n jdbc:mysec -u muthu-p tek123.
But Its also not worked.
Is anyone tried this ?  |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Sat Apr 23, 2011 9:28 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
There was a similar problem posted here a week or so ago.
The advice given (by mqjeff I think) was DO NOT CHANGE THIS VALUE.
| Quote: |
connectionUrlFormat='jdbc:oracle:thin:muthu/tek123@10.56.11.123:
1521:DEVSOA'
|
Put is back to its original value and try again.
_________________
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 |
|
|
| muthu_tek |
| Posted: Sat Apr 23, 2011 10:14 pm Post subject: |
|
|
Apprentice
Joined: 21 Jan 2010
Posts: 36
Location: Coimbatore,India
|
Hi smdavies99,
Thanks for your reply,
| Quote: |
| The advice given (by mqjeff I think) was DO NOT CHANGE THIS VALUE. |
I tried even that
JDBCProviders
DEVSOA
| Code: |
connectionUrlFormat='jdbc:oracle:thin:[user]/[password]@[serverName]:[portNu
mber]:[connectionUrlFormatAttr1]'
connectionUrlFormatAttr1='DEVSOA'
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='DBSERVICES'
databaseType='Oracle'
databaseVersion='10.2.0'
description='default_Description'
environmentParms='default_none'
jarsURL='D:\oracle\app\product\10.2.0\client_1\jdbc\lib'
portNumber='1521'
securityIdentity='Ora_Security'
serverName='10.64.98.165'
type4DatasourceClassName='oracle.jdbc.xa.client.OracleXADataSource'
type4DriverClassName='oracle.jdbc.OracleDriver' |
But getting same exception!! |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Apr 24, 2011 5:52 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| smdavies99 wrote: |
The advice given (by mqjeff I think) was DO NOT CHANGE THIS VALUE.
| Quote: |
connectionUrlFormat='jdbc:oracle:thin:muthu/tek123@10.56.11.123:
1521:DEVSOA'
|
|
muthu -
Have you confirmed that the port and host are the correct values, and that you can actually telnet to that from the broker machine? Does the DBA see that you have attempted to connect to the database and then failed for some reason? Is there an additional message in the system log during EG startup or etc?
Otherwise, take a service trace and see if you can find more information about the cause of the failure...
You might also try applying FixPack 9 to get lots of fixes since fixpack 5. |
|
| Back to top |
|
|
| muthu_tek |
| Posted: Mon Apr 25, 2011 4:35 am Post subject: |
|
|
Apprentice
Joined: 21 Jan 2010
Posts: 36
Location: Coimbatore,India
|
Dear mqjeff,
Thanks for your reply,
| Quote: |
Have you confirmed that the port and host are the correct values, and that you can actually telnet to that from the broker machine? Does the DBA see that you have attempted to connect to the database and then failed for some reason? Is there an additional message in the system log during EG startup or etc?
Otherwise, take a service trace and see if you can find more information about the cause of the failure... |
I tested telnet my ip address and port, Its pinging properly and more over I didn't get any exception in my DBA. I am able to establish connection using type4 jdbc connection in java without configurable service So I am clear that DBA is not throwing any exception.
Like this I am able to connect my DBA :
| Code: |
String url ="jdbc:oracle:thin:muthu/tek123@10.56.11.123:1521:DEVSOA";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, "muthu", "tek123");
}
catch( Exception e ) {
System.out.println("Failed to load oracle driver.");
return;
}
|
But couldn't access from broker by using Configurable service DEVSOA
And I tried service trace in execution group level in broker with my DEVSOA Configurable Service. I got below message by service trace .
| Code: |
'RECOVERABLE reason =Unable to locate details for JDBCProvider registry entry: DEVSOA, in JDBCDatabaseManager constructor' |
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Apr 25, 2011 4:49 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
is that the *first* exception you see in the trace?
You might need to trace the startup of the eg.
You've confirmed that the security id is correctly setup with mqsisetdbparms? |
|
| Back to top |
|
|
| muthu_tek |
| Posted: Tue Apr 26, 2011 12:19 am Post subject: |
|
|
Apprentice
Joined: 21 Jan 2010
Posts: 36
Location: Coimbatore,India
|
| Quote: |
| You've confirmed that the security id is correctly setup with mqsisetdbparms? |
Hi mqjeff,
Yes,You are correct. Issue is in mqsisetdbparms only.
Wrong Command
| Code: |
| mqsisetdbparms OMNTO31T -n jdbc:Ora_Security -u muthu -p tek123 |
I missed one colon in jdbc:Ora_Security
Correct Commad
| Code: |
| mqsisetdbparms OMNTO31T -n jdbc::Ora_Security -u muthu -p tek123 |
Now Works fine .Thanks ..
And one more doubt Mr.mqjeff.
For example , If we have multiple database and every database has own individual userid and password .In this senerio how can set securityIdentity in configurable service.If I want access some tables from another database by using Type 4 JDBC connection ,how can achieve this senerio.
Note :
All my database available under the DEVSOA ServiceName.
| Code: |
DEVSOA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.56.11.123)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = devsoa)
)
) |
And Can we do this,
Once created generic configurable service for accessing multiple database ?
And I dont want stop broker again for mqsisetdbparms for every database .
How can we achieve this ?
Thanks for knowledge sharing!!!!  |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Apr 26, 2011 1:15 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Why is it important to only use one configurable service?
Why is it important to specify the security ID on the configurable service itself, rather than somewhere else? |
|
| Back to top |
|
|
| muthu_tek |
| Posted: Tue Apr 26, 2011 1:39 am Post subject: |
|
|
Apprentice
Joined: 21 Jan 2010
Posts: 36
Location: Coimbatore,India
|
| Quote: |
Why is it important to only use one configurable service?
Why is it important to specify the security ID on the configurable service itself, rather than somewhere else? |
Yes I have to fetch Database Name Dynamically from Database tables.
For Example,
I have a database EMPLOYEE_SERVICES .It has table name called Employee and this table has a values DATABASE Names [ BONUS_SERVICES,PF_SERVICES,etc... ] .
First I am fetching data from Employee tables in default EMPLOYEE_SERVICES Database, After that I am trying to connect other Database BONUS_SERVICES dynamically and try to fetch data from Bonus table in BONUS_SERVICES Database.
Note :
Any Database may come which available in DEVSOA Service Name.
How can i do this?
_________________
Thanks and Yours,
Marimuthu Udayakumar |
|
| Back to top |
|
|
|
|
