IBM Db2 is a database system designed to handle mission critical workloads and now it can be accessed natively on z/OS with IBM Open Enterprise SDK for Go using an open-source module called go_ibm_db. This blog will share our motivation, the porting process , and how to get started.

1. Motivation

Low power latency, real-time analytics, and the ability to work with cloud, hybrid, or on-prem solutions makes Db2 an ideal database solution. This creates significant excitement and motivated us to create this solution for the open-source community.

2. Porting Process

 This port utilizes the Db2 ODBC DLL for a simple and fast enablement. Meaning that there is no need for CGO in this process – a decision that is likely to be met with relief. This is because users may work on systems without a C compiler or have other restrictions.

The connection method chosen for this solution is displayed in Figure 1. Since the program in this demo is using the DLL, it's beneficial to use Figure 1 as a frame of reference for the relationship between an application, the Db2 for z/OS ODBC driver for z/OS, and the Db2 for z/OS subsystem.

Figure 1: Relationship between Db2 for z/OS ODBC components [IBM]

Figure 1, is a model of the ODBC components extracted from the Db2 for z/OS documentation. The figure indicates that this process requires the use of a DLL. Conveniently a process for accessing DLLs on z/OS is available in the ibmruntimes/go-recordio repository.

If you don’t have Db2 for z/OS available on your system, you also don't have the DLL, speak with your system administrator to assess this possibility. You may wish to visit this link for installation instructions

In go-ibm_db, an init function is used to locate and load the Db2 ODBC driver DLL during the program's initialization stage.  Following that, all the necessary functions are implemented by calling their equivalent DLL functions. They utilize the go-recordio function utils.CfuncEbcdic() that assists in processing EBCDIC communication. The return of getFunc() is used inside of CfuncEbcdic() to get the handle of the function being used.

Upon closer inspection, you'll notice that all parameters are passed as unintptr, and the parameters that return a value are also wrapped and passed as an unsafe.Pointer.

There were several constants that needed to be defined for the DLL that the module also requires. These values were extracted using a script that was run on the Db2 for z/OS header files.

This concludes much of the port. However, some considerations were needed for differences in datatypes, such as translating wchar_t or wide character types and other quirks such the ODBC API not handling null terminated strings on z/OS.

3. Get Started

First thing you want is to define four environment variables:

1.     IBM_DB_HOME

2.     STEPLIB

3.     DSNAOINI

4.     SUBSYSTEM

IBM_DB_HOME is set to your High Level Qualifier (HLQ) for Db2 – You may need to get this information from your system administrator.

export IBM_DB_HOME="DSNC10"

Next update the STEPLIB environment variable to include the Db2 SDSNEXIT, SDSNLOAD and SDSNLOD2 data sets

export STEPLIB=$STEPLIB:$IBM_DB_HOME.SDSNEXIT:$IBM_DB_HOME.SDSNLOAD:$IBM_DB_HOME.SDSNLOD2

Followed by defining your SUBSYSTEM. This will be used to setup your ODBCINI file, and nothing else after that. If you don’t know what this value is please ask your system administrator.

If you have defined all the three earlier environment variables, you can complete the DSNAOINI  process by running a script (1) or doing it manually (2).

1.     Script: Run the script below in your command line:

export DSNAOINI="$HOME/ODBCOINI_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

The line export DSNAOINI="$HOME/ODBCOINI_CAF" completed the last but essential component of the initialization.

2.     Manual: If you choose not to use the above script:

         a.     Create a file and place the following content:

; 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

      b.     Change the value in front of MVSDEFAULTSSID to the name of your SUBSYSTEM

      c.     Set DSNAOINI to the path of your DSNAOINI file:

export DSNAOINI=<PATH_TO_ DSNAOINI>

       d.     Tag the file as EBCDIC

/bin/chtag -t -c 1047 "$DSNAOINI"

Initialization done! You can save the export statements to your environment initialization script so that you don’t have to do this step again.

Start Using go_ibm_db

Go relies on repository tags to get the latest versions of a repo. Currently go_ibm_db is in the process of updating its git tag so the easiest way to try out this port is in a Go workspace. For this reason and for simplicity, you will be setting up a Go workspace. If you would like to learn for more about Go workspaces, click this link.

Create your Go workspace using the following steps

mkdir zos-workspace
cd zos-workspace
git clone https://github.com/ibmdb/go_ibm_db.git
mkdir example1
cd example1
vim main.go

Enter this sample Go code we got from go_ibm_db

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)
	}
}