search

ibmdb / go_ibm_db

GoLang Driver for Db2 family of database servers
BSD 3-Clause "New" or "Revised" License
107 stars 37 forks source link

readme

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

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.4.5
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=%PATH%;C:\Users\uname\go\src\github.com\ibmdb\clidriver\bin
or 
set PATH=%PATH%;%IBM_DB_HOME%\bin

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.4.5
export IBM_DB_HOME=/home/uname/go/src/github.com/ibmdb/clidriver
export CGO_CFLAGS=-I$IBM_DB_HOME/include
export CGO_LDFLAGS=-L$IBM_DB_HOME/lib
Linux:
export LD_LIBRARY_PATH=/home/uname/go/src/github.com/ibmdb/clidriver/lib
or
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$IBM_DB_HOME/lib
Mac:
export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/home/uname/go/src/github.com/ibmdb/clidriver/lib
or
export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:$IBM_DB_HOME/lib
cd .../go_ibm_db/installer
source setenv.sh

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@v0.4.5 or go install github.com/ibmdb/go_ibm_db/installer@latest


## How to Install in z/OS

- You may install go_ibm_db using the below command

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.
> * IBM_DB_HOME
> * STEPLIB
> * DSNAOINI

Please refer to the [ODBC Guide and References](https://www.ibm.com/support/knowledgecenter/SSEPEK/pdf/db2z_12_odbcbook.pdf) cookbook for how to configure your ODBC driver.   Specifically, you need to ensure you have:

1. Apply Db2 on z/OS PTF [UI60551](https://www-01.ibm.com/support/docview.wss?uid=swg1PH05953) 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`):

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

  1. 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

  2. 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.

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: