Technical Stuff

Start from basics

MYSQL ODBC Connectivity with IBM Integration Bus

MYSQL ODBC Connectivity with IBM Integration Bus
MYSQL ODBC Connectivity with IBM Integration Bus
5 (100%) 3 votes
Image1

Hello Everyone,Today we are going to take a look at MySQL connectivity with IBM Integration Bus.

In this scenario IBM Integration Bus and MySQL is installed,configured and running on the same server.MySQL is running on its default port 3306.

 

Below steps are being performed on Ubuntu.

Step 1: Check whether MySQL is up and running

[email protected]:/etc# sudo service mysql status

mysql.service - MySQL Community Server

   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

   Active: active (running) since Thu 2019-10-31 11:46:24 IST; 23min ago

 Main PID: 14295 (mysqld)

    Tasks: 29 (limit: 4915)

   CGroup: /system.slice/mysql.service

           └─14295 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Oct 31 11:46:24 user-ThinkPad-X250 systemd[1]: Starting MySQL Community Server...

Oct 31 11:46:24 user-ThinkPad-X250 systemd[1]: Started MySQL Community Server.

[email protected]:/etc# 

 

Step 2: Check whether your able to login to the MySQL server

[email protected]:~$ mysql -u root -p

Enter password: <Enter your password>

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

 

Step 3: Download the libmyodbc5a.so(As MySQL version is 5.7) and libodbcinst.so.2 file and paste it in IBM Installation Directory/server/ODBC/unixodbc/lib directory.

 

Step 4: Go to IIB installation directory/server/ODBC/unixodbc.Open the odbc.ini file. You will find a list of data source stanza in it.MySQL connectivity with IBM Integration Bus is not provided by default in it.So for connecting IIB with MySQL you will have to paste the MySQL stanza in it

;# Mysql stanza

[MYSQLDB]

Driver=/home/user/Cateina/Software/iib-10.0.0.15/server/ODBC/drivers/lib/libmyodbc5a.so

Setup=/home/user/Cateina/Software/iibv10.0.0.15/server/ODBC/drivers/lib/

Description=Mysql ODBC

HostName=localhost

SERVER=127.0.0.1

PortNumber=3306

UID=root

PASSWORD=********

User=root

CatalogOptions=0

EnableStaticCursorsForLongData=0

ApplicationUsingThreads=1

EnableDescribeParam=1

OptimizePrepare=1

WorkArounds=536870912

ProcedureRetResults=1

ColumnSizeAsCharacter=1

LoginTimeout=0

accessibleTables=Yes

accessibleProcedures=Yes

Database=ESBDB

 

Changes that you will have to do in this stanza 

 

  1. Driver : As MySQL connectivity is not by default provided in IIB,driver required for the same is also not provided.You will have to download the MySQL driver file (libmyodbc5a.so).Place the driver in the IIB installation directory/server/ODBC/drivers/lib folder 
  2. Hostname : The hostname on which MySQL is up and running.
  3. Port: MySQL default port is 3306.While configuring MySQL if the port number is changed from its default,specify the configured port
  4. SERVER: The server on which MySQL is up and running
  5. UID/User: The user using which you access MySQL
  6. Password: Password to access MySQL
  7. Database: Database that you want to access

 

Step 5: Check whether ODBCINI and ODBCSYSINI environment variable is set

 

              echo $ODBCINI

              /home/user/Cateina/Software/IIB10/iib-10.0.0.15/server/ODBC/unixodbc/odbc.ini

 

              echo $ODBCSYSINI

              /home/user/Cateina/Software/IIB10/iib-10.0.0.15/ie02/etc  

 

If the ODBCINI and ODBCSYSINI environment variable is not set add the below lines in the    mqsiprofile 

 

ODBCINI=/home/user/Cateina/Software/IIB10/iib-10.0.0.15/server/ODBC/unixodbc/odbc.ini

export ODBCINI

ODBCSYSINI=/home/user/Cateina/Software/IIB10/iib-10.0.0.15/server/ODBC/unixodbc

export ODBCSYSINI

 

Step 6: Go to IIB installation directory and set the username and password for the MYSQLDB stanza

 

Command: mqsisetdbparms TESTNODE_root -n MYSQLDB -u root -p ‘********’

 

[email protected]:~/Cateina/Software/IIB10/iib-10.0.0.15/server/ODBC/unixodbc# mqsisetdbparms TESTNODE_root -n MYSQLDB -u root -p '*******' 

BIP8071I: Successful command completion. 

 

Step 7: Check whether the credentials are correct using below command

 

Command : mqsireportdbparms TESTNODE_root -n MYSQLDB -u root-p ‘********’

 

[email protected]:~/Cateina/Software/IIB10/iib-10.0.0.15/server/ODBC/unixodbc# mqsireportdbparms TESTNODE_root -n MYSQLDB -u root -p '********'

BIP8180I: The resource name 'MYSQLDB' has userID 'root'. 

BIP8201I: The password you entered, '*******' for resource 'MYSQLDB' and userId 'root' is correct. 

BIP8207I: The Integration node has not been restarted since the last change made to this datasource by the mqsisetdbparms command. 

BIP8071I: Successful command completion. 

 

Step 8: Check whether MySQL DB is connected to IIB

 

Command: mqsicvp TESTNODE_root -n MYSQLDB

 

[email protected]:~/Cateina/Software/IIB10/iib-10.0.0.15/server/ODBC/unixodbc# mqsicvp TESTNODE_root -n MYSQLDB

BIP8299I: User 'root' from security resource name 'MYSQLDB' will be used for the connection to datasource 'MYSQLDB'. 

BIP8290I: Verification passed for the ODBC environment. 

BIP8270I: Connected to Datasource 'MYSQLDB' as user 'root'. The datasource platform is 'MySQL', version '8.0.18'. 

===========================

databaseProviderVersion      = 8.0.18

driverVersion                = 05.03.0009

driverOdbcVersion            = 03.80

driverManagerVersion         = 03.52.0002.0002

driverManagerOdbcVersion     = 03.52

databaseProviderName         = MySQL

datasourceServerName         = 127.0.0.1 via TCP/IP

databaseName                 = ESBDB

odbcDatasourceName           = MYSQLDB

driverName                   = libmyodbc5a.so

supportsStoredProcedures     = Yes

procedureTerm                = stored procedure

accessibleTables             = No

accessibleProcedures         = No

identifierQuote              = `

specialCharacters            = None

describeParameter            = No

schemaTerm                   = 

tableTerm                    = table

sqlSubqueries                = 31

activeEnvironments           = 0

maxDriverConnections         = 0

maxCatalogNameLength         = 192

maxColumnNameLength          = 192

maxSchemaNameLength          = 0

maxStatementLength           = 8192

maxTableNameLength           = 192

supportsDecimalType          = Yes

supportsDateType             = Yes

supportsTimeType             = No

supportsTimeStampType        = No

supportsIntervalType         = No

supportsAbsFunction          = Yes

supportsAcosFunction         = Yes

supportsAsinFunction         = Yes

supportsAtanFunction         = Yes

supportsAtan2Function        = Yes

supportsCeilingFunction      = Yes

supportsCosFunction          = Yes

supportsCotFunction          = Yes

supportsDegreesFunction      = Yes

supportsExpFunction          = Yes

supportsFloorFunction        = Yes

supportsLogFunction          = Yes

supportsLog10Function        = Yes

supportsModFunction          = Yes

supportsPiFunction           = Yes

supportsPowerFunction        = Yes

supportsRadiansFunction      = Yes

supportsRandFunction         = Yes

supportsRoundFunction        = Yes

supportsSignFunction         = Yes

supportsSinFunction          = Yes

supportsSqrtFunction         = Yes

supportsTanFunction          = Yes

supportsTruncateFunction     = Yes

supportsConcatFunction       = Yes

supportsInsertFunction       = Yes

supportsLcaseFunction        = Yes

supportsLeftFunction         = Yes

supportsLengthFunction       = Yes

supportsLTrimFunction        = Yes

supportsPositionFunction     = Yes

supportsRepeatFunction       = Yes

supportsReplaceFunction      = Yes

supportsRightFunction        = Yes

supportsRTrimFunction        = Yes

supportsSpaceFunction        = Yes

supportsSubstringFunction    = Yes

supportsUcaseFunction        = Yes

supportsExtractFunction      = Yes

supportsCaseExpression       = Yes

supportsCastFunction         = Yes

supportsCoalesceFunction     = Yes

supportsNullIfFunction       = Yes

supportsConvertFunction      = No

supportsSumFunction          = Yes

supportsMaxFunction          = Yes

supportsMinFunction          = Yes

supportsCountFunction        = Yes

supportsBetweenPredicate     = Yes

supportsExistsPredicate      = Yes

supportsInPredicate          = Yes

supportsLikePredicate        = Yes

supportsNullPredicate        = Yes

supportsNotNullPredicate     = Yes

supportsLikeEscapeClause     = Yes

supportsClobType             = Yes

supportsBlobType             = Yes

charDatatypeName             = char

varCharDatatypeName          = varchar

longVarCharDatatypeName      = long varchar

clobDatatypeName             = N/A

timeStampDatatypeName        = N/A

binaryDatatypeName           = binary

varBinaryDatatypeName        = varbinary

longVarBinaryDatatypeName    = long varbinary

blobDatatypeName             = N/A

intDatatypeName              = integer

doubleDatatypeName           = double

varCharMaxLength             = 0

longVarCharMaxLength         = 0

clobMaxLength                = 0

varBinaryMaxLength           = 0

longVarBinaryMaxLength       = 0

blobMaxLength                = 0

timeStampMaxLength           = 0

identifierCase               = Mixed

escapeCharacter              = \

longVarCharDatatype          = -1

clobDatatype                 = 0

longVarBinaryDatatype        = -4

blobDatatype                 = 0

BIP8273I: The following datatypes and functions are not natively supported by datasource 'MYSQLDB' using this ODBC driver: Unsupported datatypes: 'TIME, TIMESTAMP, INTERVAL' Unsupported functions: 'CONVERT' 

Examine the specific datatypes and functions not supported natively by this datasource using this ODBC driver.  

When using these datatypes and functions within ESQL, the associated data processing is done within IBM Integration Bus rather than being processed by the database provider.  

  

Note that "functions" within this message can refer to functions or predicates. 

BIP8071I: Successful command completion. 

Do let me know, how you find the blog. Was it helpful to you? So, if you like the blog. Do subscribe to get notified…!!!

Thank You!
Happy Learning !!!

If you have doubt or queries, you can comment us or can mail us on [email protected]

If you have any Recommendation for future blog, you can email us on [email protected]

Follow Us on :

www.facebook.com/stufftechnical

www.instagram.com/technicalstuff.in

 

Top Searches:

  1. Top Linux Commands
  2. Introduction to linux.
  3. Websphere hack
  4. My Story – Jboss Hardening
  5. Installation and configuration of Apache 2.2 on Linux
  6. DB Auto Re-connect.
  7. Redirection in Jboss.
  8. SSL Implementation in Jboss
  9. Best SSL Guide.
  10. Types of SSL.
  11. Best Jboss Server Security Guide.
  12. Introduction to hardening in Middleware.
  13. Hardening In Jboss EAP 5.1
  14. Enable TLS1.2 in Jboss EAP 7

Leave a Reply

%d bloggers like this: