CODEWITHSUNDEEP
db2odbcunixodbc
procrastinate_later
procrastinate_later

Reputation: 669

Why isn't unixODBC isql working with DB2 Community on Docker?

I'm trying to get ODBC connections working on Linux to DB2 Community, running in a docker container. The native db2cli command works, but I can't get the unixODBC tool isql to connect. I think I have a configuration problem, but I'm getting stuck. Any ideas?

Motivation: trying to get DB2 automated tests working for arrow-odbc for this issue: https://github.com/pacman82/arrow-odbc/issues/63

DB2 ODBC/CLI driver install

Download from: https://www.ibm.com/support/pages/download-fix-packs-version-ibm-data-server-client-packages

mkdir -p /opt/ibm/
cp v11.5.8_linuxx64_odbc_cli.tar.gz /opt/ibm/
cd /opt/ibm/
tar xvf v11.5.8_linuxx64_odbc_cli.tar.gz

export PATH=/opt/ibm/odbc_cli/clidriver/bin/:$PATH
export LD_LIBRARY_PATH=/opt/ibm/odbc_cli/clidriver/lib/

DB2 Community on Docker

Now start DB2 LUW in a docker container

docker run -itd --name mydb2 --privileged=true -p 50000:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=testpassword -e DBNAME=testdb -v $HOME/db2_community:/database icr.io/db2_community/db2:latest

DB2 CLI/ODBC config

https://www.ibm.com/docs/en/db2-warehouse?topic=package-configuring

Note that this command only works on Windows: db2cli registerdsn -add -dsn db2docker

sudo chgrp ross /opt/ibm/odbc_cli/clidriver/cfg/db2dsdriver.cfg
sudo chmod 664 /opt/ibm/odbc_cli/clidriver/cfg/db2dsdriver.cfg

cd $HOME

db2cli writecfg add -dsn db2docker -database testdb -host localhost -port 50000

That produces

ross@ross-T480-linux:/opt/ibm/odbc_cli/clidriver/cfg$ cat db2dsdriver.cfg
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<configuration>
  <dsncollection>
    <dsn alias="db2docker" host="localhost" name="testdb" port="50000"/>
  </dsncollection>

  <databases>
    <database host="localhost" name="testdb" port="50000"/>
  </databases>

</configuration>

Connection Validation

ross@ross-T480-linux:~$ db2cli validate -dsn db2docker -connect -user db2inst1 -passwd testpassword

===============================================================================
Client information for the current copy:
===============================================================================

Client Package Type       : IBM Data Server Driver For ODBC and CLI
Client Version (level/bit): DB2 v11.5.8.0 (s2209201700/64-bit)
Client Platform           : Linux/X8664
Install/Instance Path     : /opt/ibm/odbc_cli/clidriver
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path      : /opt/ibm/odbc_cli/clidriver/cfg/db2dsdriver.cfg
DB2CLIINIPATH value       : <not-set>
db2cli.ini Path           : /opt/ibm/odbc_cli/clidriver/cfg/db2cli.ini
db2diag.log Path          : /opt/ibm/odbc_cli/clidriver/db2dump/db2diag.log

===============================================================================
db2dsdriver.cfg schema validation for the entire file:
===============================================================================

Success: The system db2dsdriver.cfg schema validation completed successfully without any errors.

===============================================================================
db2cli.ini validation for data source name "db2docker":
===============================================================================

Note: The validation utility could not find the configuration file db2cli.ini. 
The file is searched at "/opt/ibm/odbc_cli/clidriver/cfg/db2cli.ini".

===============================================================================
db2dsdriver.cfg validation for data source name "db2docker":
===============================================================================

[ Parameters used for the connection ]

Keywords                  Valid For     Value
---------------------------------------------------------------------------
DATABASE                  CLI,.NET,ESQL testdb
HOSTNAME                  CLI,.NET,ESQL localhost
PORT                      CLI,.NET,ESQL 50000

===============================================================================
Connection attempt for data source name "db2docker":
===============================================================================

[SUCCESS]

===============================================================================
The validation is completed.
===============================================================================

Now, try a test script


ross@ross-T480-linux:~$ echo "VALUES current_schema;" > db2_test_script.sql
ross@ross-T480-linux:~$ db2cli execsql -dsn db2docker -user db2inst1 -passwd testpassword -inputsql db2_test_script.sql 
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
> VALUES current_schema;
FetchAll:  Columns: 1
  1 
  DB2INST1
FetchAll: 1 rows fetched.
>

unixODBC config for DB2 LUW

Ok, now, here's where I get stuck

Edit /etc/odbcinst.ini:

[Db2]
Description = Db2 Driver
Driver = /opt/ibm/odbc_cli/clidriver/lib/libdb2o.so
fileusage=1
dontdlclose=1

https://www.ibm.com/docs/en/db2/11.5?topic=keywords-dbalias

https://www.ibm.com/docs/en/db2/11.5?topic=file-data-server-driver-configuration-example

$HOME/.odbc.ini

[db2dockerodbc]
DRIVER=DB2
DBAlias=db2docker

However, this fails

ross@ross-T480-linux:~$ isql -v db2dockerodbc db2inst1 testpassword
[     ][unixODBC][IBM][CLI Driver] SQL1531N  The connection failed because the name specified with the DSN connection string keyword could not be found in either the db2dsdriver.cfg configuration file or the db2cli.ini configuration file.  Data source name specified in the connection string: "DB2DOCKERODBC".

[ISQL]ERROR: Could not SQLConnect

Upvotes: 0

Views: 472

Answers (1)

procrastinate_later
procrastinate_later

Reputation: 669

I found what I was doing wrong! DBAlias is a keyword for db2cli.ini, and it doesn't go in .odbc.ini

Instead, you need the DSN name in .odbc.ini to match what's in db2driver.cfg, which is db2docker in my example. (FYI, it looks like there are other DB2 config files where you can define a DSN, also.)

So, this works:

ODBC Driver config

ross@ross-T480-linux:~$ cat /etc/odbcinst.ini 
[DB2]
Description = DB2 Driver
Driver = /opt/ibm/odbc_cli/clidriver/lib/libdb2o.so
fileusage=1
dontdlclose=1

IBM driver config

ross@ross-T480-linux:~$ cat /opt/ibm/odbc_cli/clidriver/cfg/db2dsdriver.cfg
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<configuration>
  <dsncollection>
    <dsn alias="db2docker" host="localhost" name="testdb" port="50000"/>
  </dsncollection>

  <databases>
    <database host="localhost" name="testdb" port="50000"/>
  </databases>

</configuration>

ODBC connection config

ross@ross-T480-linux:~$ cat ~/.odbc.ini 
[db2docker]
Description = testdb on DB2 Community on Docker 
DRIVER=DB2

Then, this works

ross@ross-T480-linux:~$ isql -v db2docker db2inst1 testpassword
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> VALUES current_date
+-----------+
| 1         |
+-----------+
| 2023-08-24|
+-----------+
SQLRowCount returns -1
1 rows fetched
SQL> VALUES current_schema
+---------------------------------------------------------------------------------------------------------------------------------+
| 1                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------+
| DB2INST1                                                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL> quit
ross@ross-T480-linux:~$

Upvotes: 0

Related Questions