External database configuration in IBM Sterling B2B Integrator

Database management is achieved in IBM Sterling Integrator with the help of JDBC database pool. Each database which needs to be used in IBM B2Bi environment is added in the server as a new JDBC database pool. Sample pools for database vendors such as Oracle, IBM DB2, MySQL etc. is given in the jdbc.properties.in file. This sample pools can be used as reference in order to create new entry of database pool of external database. This blog aims to provide detailed explanation on configuration as well as utilization of external database in IBM B2B Sterling Integrator.

For detailed documentation of JDBC database pool , go through the link
https://www.ibm.com/support/knowledgecenter/ru/SS3JSW_5.2.0/com.ibm.help.performance_mgmt.doc/SIPM_DB_GenImplConsiderations.html

Here, MySQL database is used to configure in the IBM Sterling Integrator environment.

Steps Involved:

1) Stop IBM Sterling Integrator server if it is running.

2) Create database pool entry for MySQL database i.e. get sample pool (mysqlPool) from jdbc.properties.in file for MySQL database and update the details such as, database URL, username, password, database name and test SQL query in the sample pool. Required fields which needs to be updated are highlighted in yellow in the below pool example,

mysqlPool_dev.driver=com.mysql.jdbc.Driver

mysqlPool_dev.url=jdbc:mysql://localhost:3306/userdata

mysqlPool_dev.user=root

mysqlPool_dev.password=password

#mysqlPool_dev.maxconn=20

#mysqlPool.storedProcClassName=com.sterlingcommerce.woodstock.util.frame.jdbc.SybaseStoredProcQuery

mysqlPool_dev.varDataClassName=com.sterlingcommerce.woodstock.util.frame.jdbc.MySQLVarData

mysqlPool_dev.catalog=userdata

mysqlPool_dev.type=local

mysqlPool_dev.transaction=false

mysqlPool_dev.testOnReserve=true

mysqlPool_dev.testOnReserveQuery=select * from users

mysqlPool_dev.testOnReserveInterval=60000

mysqlPool_dev.maxRetries=100

mysqlPool_dev.blobPageSize=1024000

mysqlPool_dev.compressBlob=true

mysqlPool_dev.dbvendor=mysql

mysqlPool_dev.buffersize=500

mysqlPool_dev.maxsize=30

mysqlPool_dev.initsize=0

mysqlPool_dev.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.ConnectionFactory

mysqlPool_dev.behaviour=2

mysqlPool_dev.lifespan=0

mysqlPool_dev.idletimeout=86400000

mysqlPool_dev.housekeepinginterval=3600000

mysqlPool_dev.waittime=1000

mysqlPool_dev.errorMissingTable=1146

mysqlPool_dev.systemPool=true

In order to add above pool entry in the IBM B2Bi, it needs to be added in either customer_overrides.properties or jdbc_customer.properties file.

3) Once database pool entry is added for external database then restart IBM B2Bi server. In order to verify newly added pool entry, database pool will be visible in Sterling Integrator dashboard under

Operations->System->Troubleshooter->Database Usage

 

4) Once configuration is successful then next step is to install corresponding drivers in IBM B2Bi environment so that external database can be utilized in the Sterling Integrator by using business process as well as database map.

Download JDBC connector jar for MySQL database and install JDBC driver of MySQL by below command,

SI_Install_folder/bin>install3rdParty.cmd mySQL_JDBC_Driver 1_0 -d C:\mysql-connector-java-5.1.42\mysql-connector-java-5.1.42-bin.jar

Once JDBC connector jar been deployed onto the server then it will create database jar entry into the dynamicclasspath.cfg file (SI_Install_folder/properties folder).

5) In order to achieve database mapping in Sterling Integrator, ODBC driver needs to be used to create data source instance. Download ODBC driver for MySQL database (mysql-connector-odbc-3.51.30-win32.msi) and install it in the system.

6) To open 32-bit version of Microsoft ODBC Administrator, type "%windir%\syswow64\odbcad32.exe" in windows Run.

Once ODBC driver is installed for external database then MySQL driver will be shown under Drivers tab in Microsoft ODBC Administrator. Create new data source entry under User DSN and select "MySQL ODBC 3.51 Driver" then specify below details,

•          Data Source Name (same as database pool name "mysqlPool_dev")
•          IP address
•          Username/Password
•          Database Name

Newly configured data source can be imported in the SQL map and database mapping can be performed.