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 IndexWebSphere Message Broker SupportDB2 connectivity with IIB Toolkit

Post new topicReply to topic
DB2 connectivity with IIB Toolkit View previous topic :: View next topic
Author Message
junaid
PostPosted: Mon Jun 24, 2019 2:51 am Post subject: DB2 connectivity with IIB Toolkit Reply with quote

Apprentice

Joined: 29 Nov 2018
Posts: 35

Hello,

We have two machines Ubuntu 16.04.3 installed on docker:
Machine1- IIB toolkit installed version 10
Machine2- DB2 server installed version 11.1.4

Now we have a requirement for creating db2 connection from toolkit. What should we install on Machine1 (toolkit ) so that we can communicate with Machine2 (db2) .

Should we go for db2 client or db2 driver on Machine1.

Thanks.
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Mon Jun 24, 2019 4:12 am Post subject: Reply with quote

Master

Joined: 17 Jul 2017
Posts: 298
Location: UK

hi...don't think you need to install anything as IIB toolkit ships with the DB2 drivers.

You can connect to a DB2 instance by defining a client connection at the 'Database Development' perspective, there add a 'New Database connection' where you can configure the DB2 database details.
Back to top
View user's profile Send private message
junaid
PostPosted: Mon Jun 24, 2019 10:28 pm Post subject: DB2 connectivity with IIB Toolkit Reply with quote

Apprentice

Joined: 29 Nov 2018
Posts: 35

Senario

M1: Ubuntu 16.04.3 broker installed for deployment
M2: Centos 7.5.1804 db2 installed
M3: windows 8.1 machine with iib toolkit installed for development of flows

I have successfully created datasource on M3 ( windows machine containing iib toolkit ) now when I deploy on remote broker installed on M1(IIB toolkit) configured as remote node on M3(machine) inside iib toolkit .

I developed a flow using M3(machine) having 'database retrieve node' for performing select operation and found exception :

Code:

<exceptionList>
<RecoverableException>
<File>/build/S1000_slot1/S1000_P/src/DataFlowEngine/MessageServices/ImbDataFlowNode.cpp</File>
<Line>1251</Line>
<Function>ImbDataFlowNode::createExceptionList</Function>
<Type>ComIbmDatabaseRetrieveNode</Type>
<Name>gen/TestDB#FCMComposite_1_8.gen/operation1_Request_Response#FCMComposite_1_1</Name>
<Label>gen.TestDB.operation1_Request_Response.Database Retrieve</Label>
<Catalog>BIPmsgs</Catalog>
<Severity>3</Severity>
<Number>2230</Number>
<Text>Node throwing exception</Text>
<Insert>
<Type>14</Type>
<Text>gen.TestDB.operation1_Request_Response.Database Retrieve</Text>
</Insert>
<DatabaseException>
<File>JDBCCommon.java</File>
<Line>566</Line>
<Function>JDBCConnection::createConnection</Function>
<Type/>
<Name/>
<Label/>
<Catalog>BIPmsgs</Catalog>
<Severity>3</Severity>
<Number>6232</Number>
<Text>Problem encountered obtaining JDBC connection</Text>
<Insert>
<Type>5</Type>
<Text>Broker 'BAHLESBUATDOCKER'; Execution Group 'default'; Message Flow 'gen.TestDB'; Node 'operation1_Request_Response.Database Retrieve'; Node Type 'DatabaseRetrieve</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>com.ibm.db2.jcc.DB2Driver</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>  error message: No suitable driver, SQLState value: 08001, stack trace: [com.ibm.broker.jdbcnodes.JDBCConnection.createConnection(JDBCConnection.java:236)
com.ibm.broker.jdbcnodes.JDBCConnection.getNodeConnection(JDBCConnection.java:149)
com.ibm.broker.jdbcnodes.DatabaseRetrieveNode.evaluate(DatabaseRetrieveNode.java:407)
com.ibm.broker.plugin.MbNode.evaluate(MbNode.java:1452)]</Text>
</Insert>
</DatabaseException>
</RecoverableException>
</exceptionList>


Am i missing some jar or something need to be deployed over M1 or M2 ?

Thanks.
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Tue Jun 25, 2019 1:09 am Post subject: Re: DB2 connectivity with IIB Toolkit Reply with quote

Master

Joined: 17 Jul 2017
Posts: 298
Location: UK

junaid wrote:

Now we have a requirement for creating db2 connection from toolkit.


hi...isn't the above bit misleading as per your second thread as you are not really trying to connect to DB2 via toolkit, instead you are trying to create a flow which requires DB2 connectivity from your Ubuntu Broker server.

In this case you need to create a JDBC provider with the same name as the 'DataSourceName' of the Database Retrieve node created on the Ubuntu server with the DB2 instance details hosted on M2.

Have a look at the below infocenter topic on create/configure a JDBC provider, DB2 is one of default supplied services.

https://www.ibm.com/support/knowledgecenter/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ah61300_.htm
Back to top
View user's profile Send private message
junaid
PostPosted: Tue Jun 25, 2019 3:36 am Post subject: Reply with quote

Apprentice

Joined: 29 Nov 2018
Posts: 35

Thanks for reply.

Now getting this error response while connecting. We have also placed jar files for db2 (db2java.zip db2jcc4.jar db2jcc.jar db2policy.jar db2jcc_license_cu.jar db2qgjava.jar) on the path
Code:
jarsURL   :: /opt/newDrivers/

inside configuration "DB2 - JDBCProviders Configurable Service".



Code:

<exceptionList>
<RecoverableException>
<File>/build/S1000_slot1/S1000_P/src/DataFlowEngine/MessageServices/ImbDataFlowNode.cpp</File>
<Line>1251</Line>
<Function>ImbDataFlowNode::createExceptionList</Function>
<Type>ComIbmDatabaseRetrieveNode</Type>
<Name>gen/TestDB#FCMComposite_1_8.gen/operation1_Request_Response#FCMComposite_1_1</Name>
<Label>gen.TestDB.operation1_Request_Response.Database Retrieve</Label>
<Catalog>BIPmsgs</Catalog>
<Severity>3</Severity>
<Number>2230</Number>
<Text>Node throwing exception</Text>
<Insert>
<Type>14</Type>
<Text>gen.TestDB.operation1_Request_Response.Database Retrieve</Text>
</Insert>
<DatabaseException>
<File>JDBCCommon.java</File>
<Line>566</Line>
<Function>JDBCConnection::createConnection</Function>
<Type/>
<Name/>
<Label/>
<Catalog>BIPmsgs</Catalog>
<Severity>3</Severity>
<Number>6232</Number>
<Text>Problem encountered obtaining JDBC connection</Text>
<Insert>
<Type>5</Type>
<Text>Broker 'BAHLESBUATDOCKER'; Execution Group 'default'; Message Flow 'gen.TestDB'; Node 'operation1_Request_Response.Database Retrieve'; Node Type 'DatabaseRetrieve</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>com.ibm.db2.jcc.DB2Driver</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>  error message: No suitable driver, SQLState value: 08001, stack trace: [com.ibm.broker.jdbcnodes.JDBCConnection.createConnection(JDBCConnection.java:236)
com.ibm.broker.jdbcnodes.JDBCDatabaseManager.getThreadOwnedConnections(JDBCDatabaseManager.java:763)
com.ibm.broker.jdbcnodes.DatabaseRetrieveNode.evaluate(DatabaseRetrieveNode.java:406)
com.ibm.broker.plugin.MbNode.evaluate(MbNode.java:1452)]</Text>
</Insert>
</DatabaseException>
</RecoverableException>
</exceptionList>

Back to top
View user's profile Send private message
junaid
PostPosted: Thu Jun 27, 2019 10:12 pm Post subject: DB2 connectivity with IIB Toolkit Reply with quote

Apprentice

Joined: 29 Nov 2018
Posts: 35

Any Help plz ?
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Fri Jun 28, 2019 12:26 am Post subject: Reply with quote

Master

Joined: 17 Jul 2017
Posts: 298
Location: UK

hi...have you tried the below,
- restarting the broker after creating the jdbc config service
- verifying that the broker user do have the necessary permissions to access the driver files

Also you can please post the jdbc config service definition used
Back to top
View user's profile Send private message
junaid
PostPosted: Sat Jun 29, 2019 12:17 am Post subject: DB2 connectivity with IIB Toolkit Reply with quote

Apprentice

Joined: 29 Nov 2018
Posts: 35

abhi_thri wrote:

- restarting the broker after creating the jdbc config service


We have restarted broker after configuring db related info @ User interface.


abhi_thri wrote:

- verifying that the broker user do have the necessary permissions to access the driver files


.jar files (as above) have 777 permissions for testing purpose.

abhi_thri wrote:

Also you can please post the jdbc config service definition used


Code:

mqsireportproperties BAHLESBUATDOCKER -c JDBCProviders  -o DB2 -r

JDBCProviders
  DB2
    connectionUrlFormat='jdbc:db2://xxx.xxx.xxx.xxx:50000/DBNAME:user=USERNAME;password=PASSWORD;'
    connectionUrlFormatAttr1=''
    connectionUrlFormatAttr2=''
    connectionUrlFormatAttr3=''
    connectionUrlFormatAttr4=''
    connectionUrlFormatAttr5=''
    databaseName='test123'
    databaseType='DB2 Universal Database'
    databaseVersion='11.1'
    description='default_Description'
    environmentParms='default_none'
    jarsURL='/opt/newDrivers/'
    jdbcProviderXASupport='true'
    maxConnectionPoolSize='0'
    portNumber='50000'
    securityIdentity='default_User@default_Server'
    serverName='default_Database_Server_Name'
    type4DatasourceClassName='com.ibm.db2.jcc.DB2XADataSource'
    type4DriverClassName='com.ibm.db2.jcc.DB2Driver'

BIP8071I: Successful command completion.
Back to top
View user's profile Send private message
junaid
PostPosted: Sat Jun 29, 2019 2:24 am Post subject: DB2 connectivity with IIB Toolkit Reply with quote

Apprentice

Joined: 29 Nov 2018
Posts: 35

Hello,

Thanx for your amazing support.

It is solved now.

Actually the JDBC configs did not commit, that's why the issue raised.

Thanx alot.
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Sat Jun 29, 2019 7:45 am Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20091
Location: LI,NY

Anyways: Looking at your Connection URL format: It is all wrong.
The Connection URL format is a TEMPLATE. You should not replace the template values with the values needed for the connection, but specify the values for the place holders in the template in the configurable service.
So for a template of:
Code:
'jdbc:db2://[host]:[port]/[dbname]:user=[USERNAME];password=[PASSWORD];'

You will need to supply in the configurable service the following parameters
Code:
host = xxx.xxx.xxx.xxx
port = 50000  //default port for DB2 YMMV
dbname = mydb2dbname
// and bind it via setmqsidbparms to an identity with username and password.


Have fun
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
junaid
PostPosted: Sun Jun 30, 2019 4:11 am Post subject: DB2 connectivity with IIB Toolkit Reply with quote

Apprentice

Joined: 29 Nov 2018
Posts: 35

Dear Grand High Poobah

Are you saying we should avoid configure params from JDBCProvider @ broker's web interface because it is template ?

what should be the best way forward to do it so. can you provide solution link or hint for it and how can we hide sensitive info(username and password) of DB config as it went as plainText in params.[/b]
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Sun Jun 30, 2019 9:08 am Post subject: Re: DB2 connectivity with IIB Toolkit Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20091
Location: LI,NY

junaid wrote:
Dear Grand High Poobah

Are you saying we should avoid configure params from JDBCProvider @ broker's web interface because it is template ?

what should be the best way forward to do it so. can you provide solution link or hint for it and how can we hide sensitive info(username and password) of DB config as it went as plainText in params.[/b]


Look at the original ConnectionURLFormat. Think about it 15 times before changing it, and then leave as is.
Remember the part like [hostname] are a place holder. At runtime it will substitute the hostname that you have configured in the rest of the configurable service into the ConnectionURLFormat. The same way specify user and password like user=[userid],password=[password]...
In the JDBC configurable service you need to reference an entry from mqsisetdbparms that you created with jdbc:name -u userid -p password
The values that you set with mqsisetdbparms are then substituted at runtime in the ConnectionUrlFormat....

Have fun
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportDB2 connectivity with IIB Toolkit
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.