| Author |
Message
|
| junaid |
 Posted: Mon Jun 24, 2019 2:51 am Post subject: DB2 connectivity with IIB Toolkit |
 |
|
Acolyte
Joined: 29 Nov 2018
Posts: 58
|
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 |
|
 |
| abhi_thri |
| Posted: Mon Jun 24, 2019 4:12 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
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 |
|
|
| junaid |
| Posted: Mon Jun 24, 2019 10:28 pm Post subject: DB2 connectivity with IIB Toolkit |
|
|
Acolyte
Joined: 29 Nov 2018
Posts: 58
|
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 |
|
|
| abhi_thri |
| Posted: Tue Jun 25, 2019 1:09 am Post subject: Re: DB2 connectivity with IIB Toolkit |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
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 |
|
|
| junaid |
| Posted: Tue Jun 25, 2019 3:36 am Post subject: |
|
|
Acolyte
Joined: 29 Nov 2018
Posts: 58
|
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 |
|
|
| junaid |
| Posted: Thu Jun 27, 2019 10:12 pm Post subject: DB2 connectivity with IIB Toolkit |
|
|
Acolyte
Joined: 29 Nov 2018
Posts: 58
|
|
| Back to top |
|
|
| abhi_thri |
| Posted: Fri Jun 28, 2019 12:26 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
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 |
|
|
| junaid |
| Posted: Sat Jun 29, 2019 12:17 am Post subject: DB2 connectivity with IIB Toolkit |
|
|
Acolyte
Joined: 29 Nov 2018
Posts: 58
|
| 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 |
|
|
| junaid |
| Posted: Sat Jun 29, 2019 2:24 am Post subject: DB2 connectivity with IIB Toolkit |
|
|
Acolyte
Joined: 29 Nov 2018
Posts: 58
|
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 |
|
|
| fjb_saper |
| Posted: Sat Jun 29, 2019 7:45 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
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 |
|
|
| junaid |
| Posted: Sun Jun 30, 2019 4:11 am Post subject: DB2 connectivity with IIB Toolkit |
|
|
Acolyte
Joined: 29 Nov 2018
Posts: 58
|
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 |
|
|
| fjb_saper |
| Posted: Sun Jun 30, 2019 9:08 am Post subject: Re: DB2 connectivity with IIB Toolkit |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
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 |
|
|
|
|
