Talking to IBM DB2 with Golang

In my recent project, I need to talk to DB2 database to construct some REST API services.

First of all, we need a DB2 driver for golang program to talk to DB2. There are some previous art done by people. However it was either not actively maintained or doesn’t support on all the major platforms. I decided to use the common ODBC driver to communicate with the DB2.

Setup ODBC/CLI for DB2

Download the DB2 ODBC/CLI driver from IBM site.

Then simply untar/unzip it to a directory.

Configure DB2 ODBC/CLI on Windows

My development and testing environment is on Windows. I setup the db2 driver on Windows first.

Common App Data Path : C:\ProgramData\IBM\DB2\Some_Directory_Name
db2cli.ini Path : <Common App Data Path>\cfg\db2cli.ini

4. Edit the db2cli.ini file where you identified from last step

[MyDB]
Protocol=TCPIP
Hostname=192.168.xxx.xxx
Port=50000
Database=mydb
CurrentSchema=myschema

5. Register the DB into ODBC as a system level DB

bin\db2cli registerdsn -add -dsn MyDB -system

At this stage we are ready to talk to the DB2 with ODBC on Windows.

Configure DB2 ODBC/CLI on Linux

My Linux is Redhat.

[DB2]
Description=DB2 Driver
Driver=#{target_dir}/clidriver/lib/libdb2o.so
fileusage=1
dontdlclose=1

Replace #{targe_dir} with the full path name of where you untar the drivers.

4. Update system /etc/odbc.ini file

[MyDB]
Protocol=TCPIP
Hostname=192.168.xxx.xxx
Port=50000
Database=mydb
CurrentSchema=myschema
Driver=#{target_dir}/clidriver/lib/libdb2o.so

5. Also update the #{target_dir}/clidriver/clidriver/cfg/db2cli.ini with the same content as shown above. I am not sure why the ODBC CLI driver looks at the two ini files.

So we are ready for the ODBC setup for both Windows and Linux.

Use ODBC to connect to DB2

The connection to DB2 becomes straight forward. Use the ODBC driver,

package restServerimport (
"fmt"
"os"
"regexp"
_ "github.com/alexbrainman/odbc"
"github.com/jmoiron/sqlx"
"github.com/sirupsen/logrus"
)
func DB2Connect() (*sqlx.DB, error) {
dbName := os.Getenv("MY_DBNAME")
uid := os.Getenv("MY_USERNAME")
password := os.Getenv("MY_CREDENTIAL")
//some decryption code is ignored here... db, err := sqlx.Open("odbc", fmt.Sprintf("DSN=%s;uid=%s;pwd=%s", dbName, uid, password))
if err != nil {
return nil, err
}
return db, nil
}

We can then use all the standard golang DB programming stuff. I am using sqlx to perform query and execution, and squirrel to contruct SQL statement.

Notes: We could not run cross compiling. Different platform has to be compiled separately.

Cloud explorer

Cloud explorer