Home

Awesome

go_ibm_db

Interface for GoLang to DB2 for z/OS, DB2 for LUW and DB2 for i database servers.

API Documentation

For complete list of go_ibm_db APIs and examples please check APIDocumentation.md

Prerequisite

yum install go git tar libpam

Note:

How to Install in Windows

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/installer@v0.5.2

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

    set PATH="C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin";%PATH%
    set LIB="C:\Program Files\IBM\IBM DATA SERVER DRIVER\lib";%LIB%

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

cd .../go_ibm_db/installer
setenvwin.bat

How to Install in Linux/Mac

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/installer@v0.5.2

Please check https://github.com/ibmdb/go_ibm_db/blob/master/INSTALL.md for detailed installation instructions.

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

How to Install in z/OS

go install github.com/ibmdb/go_ibm_db/installer@latest

Configure ODBC driver on z/OS

Note You must have the following environment variables set.

Please refer to the ODBC Guide and References cookbook for how to configure your ODBC driver. Specifically, you need to ensure you have:

  1. Apply Db2 on z/OS PTF UI60551 to pick up new ODBC functionality to support Node.js applications.

  2. Binded the ODBC packages. A sample JCL is provided in the SDSNSAMP dataset in member DSNTIJCL. Customize the JCL with specifics to your system.

  3. Set the IBM_DB_HOME environment variable to the High Level Qualifier (HLQ) of your Db2 datasets. For example, if your Db2 datasets are located as DSNC10.SDSNC.H and DSNC10.SDSNMACS, you need to set IBM_DB_HOME environment variable to DSNC10 with the following statement (can be saved in ~/.profile):

    # Set HLQ to Db2 datasets.
export IBM_DB_HOME="DSNC10"

  4. Update the STEPLIB environment variable to include the Db2 SDSNEXIT, SDSNLOAD and SDSNLOD2 data sets. You can set the STEPLIB environment variable with the following statement, after defining IBM_DB_HOME to 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

  5. 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 DSNAOINI z/OS UNIX environment variable. For compatibility with ibm_db, the following properties must be set:

    In COMMON section:

    MULTICONTEXT=2
CURRENTAPPENSCH=ASCII
FLOAT=IEEE

    In SUBSYSTEM section:

    MVSATTACHTYPE=RRSAF

    Here 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
EOF

Reference Chapter 3 in the [ODBC Guide and References](https://www.ibm.com/support/knowledgecenter/SSEPEK/pdf/db2z_12_odbcbook.pdf) for more instructions.

<a name="Licenserequirements"></a>For z/OS and iSeries Connectivity and SQL1598N error

How to run sample program

example1.go:-

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.

example2.go:-

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

example3.go:-(POOLING)

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

example4.go:-(POOLING- Limit on the number of connections)

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

For Running the Tests:

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>

If not using environment variables, update connection information in
go_ibm_db/testdata/config.json file.

For Secure Database Connection using SSL/TSL

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. SSLServerCertificate should point to the SSL Certificate from server or an CA signed certificate. Also, PORT must be SSL port and not the TCPI/IP port. Make sure Db2 server is configured to accept connection on SSL port else go_ibm_db will throw SQL30081N error.

Value of SSLServerCertificate keyword must be full path of a certificate file generated for client authentication. It normally has *.arm or *.cert or *.pem extension. ibm_db do not support *.jks format 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_db uses IBM ODBC/CLI Driver for connectivity and it do not support a *.jks file as keystoredb as keystore.jks is meant for Java applications. Note that *.jks file is a Java Key Store file and it is not an SSL Certificate file. You can extract SSL certificate from JKS file using below keytool command:

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_db supports 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=true in connection string as it is a JDBC connection keyword and go_ibm_db ignores it. Corresponding ibm_db connection keyword for sslConnection is Security hence, use Security=SSL; in connection string instead.

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 IBMCertTrustStore from IBM site, ibm_db will not work with it; you need to download Secure Connection Certificates.zip file that comes for IBM DB2 Command line tool(CLP). Secure Connection Certificates.zip has *.kdb and *.sth files that should be used as the value of SSLClientKeystoreDB and SSLClientKeystash in connection string.

Logging:

# Log on console:
go run sample.go -trace

# Log to a file (e.g., log.txt)
go run sample.go -trace log.txt