DBConnTest, IBM Sterling B2B Integrator (B2Bi) Java Database Connectivity (JDBC) Test Tool - afuruk/DBConnTest GitHub Wiki

JDBC Database Connection Tool

Overview

The B2Bi JDBC Tester (DBConnTestV2.jar) introduces a way to troubleshoot, validate, and test JDBC connections to a database.

This tool meets the need of a Java Program for testing JDBC connections. IBM Sterling B2B Integrator supports JDBC Type-4 drivers to connect to a database.

Download

The zip file contains the DBConnTestV2.jar, dbconntest.properties and readme.txt.

Use Case Scenarios:

  • Test the jdbc connection for a brand new installation/upgrade of B2Bi, using the same Java version and JDBC driver as required by the B2Bi system requirements
  • Test the B2Bi jdbc driver from the /dbjar/jdbc/ directory
  • Test 3rd party pool connections
  • Troubleshoot "db connection failed" type errors
  • Validate database connection credentials such as user name, password, host name and port
  • *** This utility cannot be used to test SSL with DB2 at this time. This feature will be available in the next release

Property File Parameters

Parameter Valid Value
DB_VENDOR
  • Oracle
  • DB2
  • MSSQL
  • DB2i_app
  • DB2i_toolbox
DB_HOST IP or Host Name of Database Server
DB_PORT TCP/IP Database Port
DB_DATA Database Catalogue Name (SID or ServiceName in Oracle)
DB_USER Database User
DB_PASS Database Password
DRIVER_PATH
  • Full Path to Type 4 JDBC Driver
Example (Linux): /apps/oracle/ojdbc7.jar
Example (Windows): C\:\\ORACLE\\Driver\\ojdbc7.jar
SSLTLS Optional:
  • false (default)
  • true
TRUSTSTORE_PATH Optional (If SSLTLS=true):
  • Path to the JKS keystore / truststore
Example (Linux): /apps/oracle/DBConnTestV2/truststore.jks
Example (Windows): C\:\\ORACLE\\truststore.jks
TRUSTSTORE_PASSWORD Optional (If SSLTLS=true):
  • The password to the keystore / truststore specified in TRUSTSTORE_PATH
Example: OracleTrustStorePasswd
J2SSE_OVERRIDE_TLS Optional (If DB_VENDOR=MSSQL):
  • false (default)
  • true

Installation

  1. Download DBConnTestV2.zip and unzip all the contents into the same directory
  2. Edit dbconntest.properties

Fill in parameters:
DB_VENDOR=
DB_HOST=
DB_PORT=
DB_DATA=
DB_USER=
DB_PASS=
DRIVER_PATH=

  1. Open command prompt to the directory with the extracted DBConnTest.zip files and the edited dbconntest.properties.

Execute the following command: java -jar DBConnTestV2.jar

  1. If testing a B2Bi connection use the same Java executable that the application is using.

Example Linux/Unix: /tmp/DBConnTest]$ /path/to/B2Bi/install/jdk/bin/java -jar DBConnTestV2.jar
Example Windows: C:\temp\DBConnTest> C:\path\to\B2Bi\install\jdk\bin\java.exe -jar DBConnTestV2.jar

Sample Execution 


$ java -jar DBConnTestV2.jar

  Using DB vendor -> Oracle


Using the following properties:
 Type:   Oracle
 Host:   192.168.1.1
 Port:   1721
 Name:   orcl
 User:   USER_5264
 Pass:   password
 Driver Path:    /tmp/DBConnTest/ojdbc7.jar

 SSLTLS: true
 Truststore Path: /tmp/DBConnTest/truststore.jks
 Truststore Password: OracleTrustStorePasswd


 Use the catalog name as an Oracle Service Name instead of SID? (y/n) y

        Oracle JDBC URL:        jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST =(ADDRESS=(PROTOCOL=TCPS)(HOST=192.168.1.1)(PORT=1721)))(CONNECT_DATA=(SERVICE_NAME=orcl)))

        Connection successful.

Run default query? (y/n) y

Querying build number... SELECT PRODUCT_LABEL, BUILD_NUMBER FROM SI_VERSION WHERE PRODUCT_LABEL='SI'

        Product Label   Build Number
        SI              5020604

Run custom query? (y/n) y
Please enter the custom query: SELECT count(*) FROM ARCHIVE_INFO

Running custom query... SELECT count(*) FROM ARCHIVE_INFO


         9009

All done



Usage of dbconntest.properties
Example Pools:

Oracle using Service Name Example

DB_VENDOR=Oracle
DB_HOST=192.168.1.1
DB_PORT=1521
DB_DATA=SERVER.COMPANY.COM (Service Name example)
DB_USER=ORA_USER
DB_PASS=password
DRIVER_PATH=C:\ORACLE\Driver\ojdbc7.jar

Oracle using SID Example

DB_TYPE=Oracle
DB_HOST=192.168.1.1
DB_PORT=1521
DB_DATA=SERVER12C (SID example)
DB_USER=ORA_USER
DB_PASS=password
DRIVER_PATH=/apps/oracle/drivers/ojdbc6.jar

Oracle with SSL/TLS Example

DB_VENDOR=Oracle
DB_HOST=192.168.1.1
DB_PORT=1721
DB_DATA=orcl
DB_USER=USER_5264
DB_PASS=password
DRIVER_PATH=/apps/oracle/ojdbc7.jar
SSLTLS=true
TRUSTSTORE_PATH=/apps/oracle/DBConnTestV2/truststore.jks
TRUSTSTORE_PASSWORD=OracleTrustStorePasswd

MSSQL Example

DB_TYPE=MSSQL
DB_HOST=192.168.1.2
DB_PORT=1433
DB_DATA=SI_USER
DB_USER=SI_USER
DB_PASS=password
DRIVER_PATH=/opt/apps/GIS_INSTALLS/dbjar/jdbc/MSSQL/sqljdbc4.jar

MSSQL with TLS override Example

DB_VENDOR=MSSQL
DB_HOST=192.168.1.3
DB_PORT=1433
DB_DATA=SI_USER
DB_USER=SI_USER
DB_PASS=password
DRIVER_PATH=/opt/apps/GIS_INSTALLS/dbjar/jdbc/MSSQL/sqljdbc4.jar
J2SSE_OVERRIDE_TLS=true

DB2 LUW Example

DB_TYPE=DB2
DB_HOST=192.168.1.4
DB_PORT=50001
DB_DATA=sterling
DB_USER=db2
DB_PASS=password
DRIVER_PATH=/opt/apps/GIS_INSTALLS/dbjar/jdbc/DB2/db2jcc.jar

DB2 for i Toolbox Driver Example

DB_VENDOR=DB2i_toolbox
DB_HOST=192.168.1.5
DB_PORT=8471
DB_DATA=B2Bi_52
DB_USER=SI_USER
DB_PASS=password
DRIVER_PATH=C:\Iseries\Drivers\jt400.jar

DB2 for i App Driver Example

DB_VENDOR=DB2i_app
DB_HOST=192.168.1.6
DB_DATA=B2Bi_526
DB_USER=sterling
DB_PASS=password
DRIVER_PATH=/QSBI/DATA/Java/db2_classes16.jar

Support/Feedback

Please contact the creators of this tool via email

⚠️ **GitHub.com Fallback** ⚠️