Setting up DB2 ODBC Driver with MySQL Workbench

 Download drivers:

https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads

https://epwt-www.mybluemix.net/software/support/trial/cst/programwebsite.wss?siteId=849&h=null&p=null

https://www.ibm.com/support/pages/db2-odbc-cli-driver-download-and-installation-information#%5B%3Ch2%3E%5DDownload%5B%3C%2Fh2%3E%5D

1. Copy the compressed file that contains the driver onto the target machine from the internet or a DB2 Version 9 installation CD.
2. Uncompress that file into your chosen install directory on the target machine.
3. cd <uncompressed driver folder>/bin
   db2cli install -setup

Validate that you have the db2dsdriver.cfg file in the correct directory and that the database is registered in the Windows ODBC Administrator.

To verify where the db2dsdriver.cfg file is expected to be from the <install path>\clidriver\lib run the db2cli validate command. This will display the following:

===============================================================================
Client information for the current copy (copy name: DB2CPY11):
===============================================================================

Client Package Type : IBM Data Server ODBC & CLI Driver
Client Version (level/bit) : DB2 v11.1.1010.111 (s1610170100/64-bit)
Client Platform : NT 64BIT
Install/Instance Path : C:\PROGRA~1\CLIDRIVER

Common App Data Path : C:\ProgramData\IBM\DB2\CLIDRIVER
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path : <Common App Data Path>\cfg\db2dsdriver.cfg
DB2CLIINIPATH value : <not-set>
db2cli.ini Path : <Common App Data Path>\cfg\db2cli.ini
db2diag.log Path : <Common App Data Path>\DB2_02\DIAG0000\db2diag.log

Note that DB2DSDRIVER_CFG_PATH and DB2CLIINIPATH are only set if the default locations are not being used.

To check if the database is correct in the db2dsdriver.cfg file execute the db2cli validate -dsn <database name>

This will show:

Keywords Valid For Value
-------------------------------------------------------------------------
DATABASE CLI,.NET,ESQL s23cv10
HOSTNAME CLI,.NET,ESQL tlba23me.torolab.ibm.com
PORT CLI,.NET,ESQL 6006
AUTHENTICATION CLI,.NET SERVER

If you don't see the driver in the Windows ODBC administrator, run db2cli install -setup

Under system data sources there will be no entries hence the reason error Data source name not
found and no default driver specified error was returned.

Resolving The Problem

Register the ODBC driver: run db2cli install -setup

Register the odbc data source: run db2cli -registerdsn -add -dsn <database name> -system

Configuring MariaDB OCBC connection

 Ensure unixodbc is working fine:

[root@unica12node1 lib]# odbcinst -j

unixODBC 2.3.1

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /root/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

Configure /etc/odbdinst.ini

[MariaDB]

Driver=/opt/cdata/cdata-odbc-driver-for-mariadb/lib/libmariadbodbc.x64.so

Description=ODBC Driver for MariaDB

Driver          = /docker/unica/mariadb/lib/libmaodbc.so

Setup           = /docker/unica/mariadb/lib/libmaodbc.so

Driver64        = /docker/unica/mariadb/lib/libmaodbc.so

Setup64         = /docker/unica/mariadb/lib/libmaodbc.so

FileUsage       = 1

Configure /etc/odbc.ini

[root@unica12node1 lib]# cat /etc/odbc.ini

[CData MariaDB Source]

Driver=/docker/unica/mariadb/lib/libmaodbc.so

User=root

Password=P@ssw0rd

Database=unicadb

Server=192.168.1.162

Port=3306

Check driver is registered:

[root@unica12node1 lib]# odbcinst -q -d

[PostgreSQL]

[MySQL]

[MariaDB]

Test connection:

[root@unica12node1 lib]# isql -v "CData MariaDB Source"

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>

Download/install unixodbc

 [root@unica12node1 mariadb]# yumdownloader --destdir=./tmp --resolve unixODBC.x86_64

Loaded plugins: langpacks, product-id, subscription-manager

--> Running transaction check

---> Package unixODBC.x86_64 0:2.3.1-14.el7 will be installed

--> Finished Dependency Resolution

unixODBC-2.3.1-14.el7.x86_64.rpm                                                                                                                   | 413 kB  00:00:03

[root@unica12node1 mariadb]# ls -l

total 3316

drwxr-xr-x 2 root root      26 Sep 16 18:57 lib

-rw-r--r-- 1 root root  837805 Jan 11  2020 mariadb-connector-odbc-3.1.0-rc-rhel7-x86_64.tar.gz

drwxr-xr-x 3 root root      17 Sep 16 18:57 share

drwxr-xr-x 2 root root      46 Sep 16 19:33 tmp

-rw-r--r-- 1 root root  875028 Sep 16 11:08 unixODBC-2.2.0-5.i386.rpm

-rw-r--r-- 1 root root 1676145 Sep 16 11:02 unixODBC-2.3.9.tar.gz

[root@unica12node1 mariadb]# cd tmp

[root@unica12node1 tmp]# ls -l

total 416

-rw-r--r-- 1 root root 423196 Jun 11  2019 unixODBC-2.3.1-14.el7.x86_64.rpm

[root@unica12node1 tmp]# rpm -i unixODBC-2.3.1-14.el7.x86_64.rpm

[root@unica12node1 tmp]# odbcinst

**********************************************

* unixODBC - odbcinst                        *

**********************************************

*                                            *

* Purpose:                                   *

*                                            *

*      An ODBC Installer and Uninstaller.    *

*      Updates system files, and             *

*      increases/decreases usage counts but  *

*      does not actually copy or remove any  *

*      files.                                *

*                                            *

* Syntax:                                    *

*                                            *

*      odbcinst Action Object Options        *

*                                            *

* Action:                                    *

*                                            *

*      -i         install                    *

*      -u         uninstall                  *

*      -q         query                      *

*      -j         print config info          *

*      -c         call SQLCreateDataSource   *

*      -m         call SQLManageDataSources  *

*      --version  version                    *

*                                            *

* Object:                                    *

*                                            *

*      -d driver                             *

*      -s data source                        *

*                                            *

* Options:                                   *

*                                            *

*      -f file name of template.ini follows  *

*         this (valid for -i)                *

*      -r get template.ini from stdin, not   *

*         a template file                    *

*      -n Driver or Data Source Name follows *

*      -v turn verbose off (no info, warning *

*         or error msgs)                     *

*      -l system dsn                         *

*      -h user dsn                           *

*                                            *

* Returns:                                   *

*                                            *

*      0   Success                           *

*     !0   Failed                            *

*                                            *

* Please visit;                              *

*                                            *

*      http://www.unixodbc.org               *

*      pharvey@codebydesign.com              *

**********************************************

[root@unica12node1 tmp]# odbcinst -j

unixODBC 2.3.1

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /root/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

Creation of unicadb on mariadb

 [root@database bin]# ./mysql -h localhost -u root -pP@ssw0rd

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

Your MariaDB connection id is 13

Server version: 10.5.5-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> create database unicadb;

Query OK, 1 row affected (0.011 sec)

MariaDB [(none)]> create user platuser identified by 'P@ssw0rd';

Query OK, 0 rows affected (0.025 sec)

MariaDB [(none)]> grant all privileges on unicadb.* to platuser;

Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> create user campuser identified by 'P@ssw0rd';

Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> create user mouser identified by 'P@ssw0rd';

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> create user intuser identified by 'P@ssw0rd';

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> create user lrnuser identified by 'P@ssw0rd';

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> create user platweb identified by 'P@ssw0rd';

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> create user platcallcntr identified by 'P@ssw0rd';

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant all privileges on unicadb.* to campuser;

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant all privileges on unicadb.* to mouser;

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> grant all privileges on unicadb.* to intuser;

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant all privileges on unicadb.* to lrnuser;

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> grant all privileges on unicadb.* to platweb;

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> grant all privileges on unicadb.* to platcallcntr;

Query OK, 0 rows affected (0.000 sec)

Mariadb Connection Issues (Host not allowed to connect to this MariaDB Server)

 Here is  a good source:

https://mariadb.com/kb/en/troubleshooting-connection-issues/

Here is my problem:

[root@database bin]# ./mysql -h localhost -u root -pP@ssw0rd

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

Your MariaDB connection id is 7

Server version: 10.5.5-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

+------+------------------------------------+

| User | Host                               |

+------+------------------------------------+

|      | rhel-server-7.6-x86-64.localdomain |

+------+------------------------------------+

1 row in set (0.005 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%'   IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION;

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

+------+------------------------------------+

| User | Host                               |

+------+------------------------------------+

| root | 192.168.1.%                        |

|      | rhel-server-7.6-x86-64.localdomain |

+------+------------------------------------+

2 rows in set (0.001 sec)

MariaDB [(none)]>