How does Golang operate the DB2?

This is a creation in Article, where the information may have evolved or changed.

Go-db2-example

IBM DB2 is a commercially available db that does not have go in the default supported programming languages and is not described today as an example of how go ODBC connects IBM DB2.

DB2 ODBC Driver

Installing DB2 ODBC Driver

There are several sources of DB2 ODBC driver:

• The DB2 installation package comes with ODBC drivers, and, like common JDBC drivers, ODBC drivers are typically brought in from the installation package.
• In addition, some separate DB2 kits also contain ODBC drivers, such as: DB2 application Development Client, DB2 run-time client, Db2 Data Server Runtime Client, etc.
• Standalone ODBC-free driver package: DB2 driver for ODBC Cli

The

DB2 CLI (DB2 call Level Interface) is a SQL interface that interacts with DB2, based on ODBC specifications and SQL/CLI international standards.

Download macos64_odbc_cli.tar.gz, unzip to any directory;

Create a data source configuration

DB2 has two data source configuration files, with the path under <install-odbc-dir>/cfg

• Db2cli.ini # Text Format, this file can be used for all ODBC drivers
• DB2DSDRIVER.CFG # XML format, introduced after version 9.5, can be used DB2 Data Server Driver for ODBC and CLI

Db2cli.ini Example

; db2cli.ini data source
[DB2_SAMPLE]
Database=SAMPLE
Protocol=TCPIP
Port=50000
Hostname=my-db2-machine
UID=my-OS-user
PWD=my-OS-password

db2dsdriver.cfg Example

<parameter name="name" value="value"/>
<!--  db2dsdriver.cfg data source -->
<configuration>
   <dsncollection>
      <dsn alias="DB2_SAMPLE" name="SAMPLE" host="my-db2-machine" port="50000">
         <parameter name="UserID" value="my-db2-user"/>
         <parameter name="Password" value="my-db2-password"/>
      </dsn>
   </dsncollection>
</configuration>

relatively simple, do not do too much introduction, CFG also has example. Note that when the same DSN exists in the configuration file, the Db2cli.ini file is loaded preferentially;

After the configuration is complete, verify that the file is correct

$ cd /home/myuser/db2/odbc_cli/clidriver/bin/
$ ./db2cli validate -dsn DB2_SAMPLE
 db2cli validate -dsn sample

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

Client Package Type       : IBM Data Server Driver For ODBC and CLI
Client Version (level/bit): DB2 v10.5.0.5 (special_35187/64-bit)
Client Platform           : Darwin
Install/Instance Path     : .../clidriver
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path      : .../clidriver/cfg/db2dsdriver.cfg
DB2CLIINIPATH value       : <not-set>
db2cli.ini Path           : .../clidriver/cfg/db2cli.ini
db2diag.log Path          : .../clidriver/db2dump/db2diag.log

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

Note: The validation utility could not find the configuration file
db2dsdriver.cfg. The file is searched at
".../clidriver/cfg/db2dsdriver.cfg".

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

[ Keywords used for the connection ]

Keyword                   Value
---------------------------------------------------------------------------
DATABASE                  sample
PROTOCOL                  TCPIP
HOSTNAME                  127.0.0.1
SERVICENAME               50000
UID                       db2inst1
PWD                       ********

Connect under test

$ echo "select count(1) from syscat.tables" |db2cli execsql -dsn sample [ -user *** -passwd *** ]
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.
> select count(1) from syscat.tables
FetchAll:  Columns: 1
  1
  643
FetchAll: 1 rows fetched.

where the user name and password can be omitted, configuration INI file;

UnixODBC

The first step is to install the Unixodbc Driver Manager, an open source project that can help users in non-win platforms easily access the target database using ODBC.

Unlike other connection methods, ODBC application apps typically load links to the ODBC Driver Manager instead of specific ODBC drivers.
The ODBC Driver Manager is an interface and bridge between ODBC applications and ODBC drivers.

At run time, the application provides a connection string, the DSN, that defines the ODBC data source to connect to, and in turn defines the ODBC driver that will handle the connection.
UNIXODBC loads the requested ODBC driver and passes all ODBC API calls to the target driver, that is, DB2 ODBC driver;
The process is as follows:
App---> UnixODBC---> DB2 ODBC Driver

For DB2 ODBC drivers, ODBC applications need to provide an ODBC data source with the same name as the DB2 ODBC driver data source.
UNIXODBC loads the appropriate data source driver (DB2 ODBC driver) and passes the data source configuration information to the loaded DB2 ODBC DRIVER,DB2 ODBC driver to check its data source's configuration file, judging its name to be the same as the name it passes;

Installing UNIXODBC

brew install unixODBC

Other platform Common UNIXODBC official website can;

Registering DB2 ODBC driver and data sources

viewing ODBC configuration Files

$ odbcinst -j
unixODBC 2.3.6
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: .../.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Among them, the Odbcinst.ini configuration example is as follows:

# Example odbcinst.ini driver definition for DB2 Data Server Driver for ODBC and
# CLI
[DB2]
Description=DB2 ODBC Driver
Driver=/usr/lib/libdb2.so # Replace /usr/lib with the directory where your
                          # driver shared object is located.
Fileusage=1               #
Dontdlclose=1             # IBM recommend setting Dontdlclose to 1, which stops
                          # unixODBC unloading the ODBC Driver on disconnect.
                          # Note that in unixODBC 2.2.11 and later, the Driver
                          # Manager default for Dontdlclose is 1.

As

mentioned above, in order for an application to connect to the DB,UNIXODBC Manager by ODBC, you need to know where to load the driver, and you need to specify the ODBC-driven connection parameters, such as: Ip,user, etc.;
To apply these parameters, configure the relevant DSN parameter connection;

The ~/.odbc.ini configuration is as follows:

[DB2_SAMPLE]Driver=DB2

therefore, when the user program needs to connect the Db2_sample,

- First, unixODBC will load the DB2 ODBC driver driver.
- After that, the DB2 ODBC driver will find the relevant configuration of the dns with the same name in db2cli.ini/db2dsdriver.cfg;
- If the user name and password provided by the user program, the username and password in the configuration file will be ignored.

The following isql is the ODBC program that UNIXODBC comes with, it can verify the DSN configuration is correct, the connection is OK;

$ isql -v DB2_SAMPLE username password
Error 1: The Data Source Name (DSN) in the ~/.odbc.ini configuration file was not found;
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

$ isql -v DB2_SAMPLE username password
Error 2: The DB2 ODBC driver configuration file did not find a matching DSN name configuration information.
[ ][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: "DB2_SAMPLE".

$ isql -v DB2_SAMPLE
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> 

Connect DB2 with Go

See source

PS: For win under the go, take time to try again.