ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » JDBC TYPE 4 Oracle Connection Problem in Message Broker

Post new topic  Reply to topic
 JDBC TYPE 4 Oracle Connection Problem in Message Broker « View previous topic :: View next topic » 
Author Message
muthu_tek
PostPosted: Sat Apr 23, 2011 9:08 pm    Post subject: JDBC TYPE 4 Oracle Connection Problem in Message Broker Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
smdavies99
PostPosted: Sat Apr 23, 2011 9:28 pm    Post subject: Reply with quote

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
View user's profile Send private message
muthu_tek
PostPosted: Sat Apr 23, 2011 10:14 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
mqjeff
PostPosted: Sun Apr 24, 2011 5:52 am    Post subject: Reply with quote

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
View user's profile Send private message
muthu_tek
PostPosted: Mon Apr 25, 2011 4:35 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
mqjeff
PostPosted: Mon Apr 25, 2011 4:49 am    Post subject: Reply with quote

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
View user's profile Send private message
muthu_tek
PostPosted: Tue Apr 26, 2011 12:19 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
mqjeff
PostPosted: Tue Apr 26, 2011 1:15 am    Post subject: Reply with quote

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
View user's profile Send private message
muthu_tek
PostPosted: Tue Apr 26, 2011 1:39 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » JDBC TYPE 4 Oracle Connection Problem in Message Broker
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.