README
¶
cli
import "github.com/asifjalil/cli"
Overview
Package cli provides access to a DB2 database using DB2 Call Level Interface (CLI) API. This requires cgo and DB2 cli/odbc driver libdb2.so. It is not possible to use this driver to create a statically linked Go package because IBM doesn't provide the DB2 cli/odbc driver as libdb2.a static library. On Windows, DB2 cli/odbc library is not compatiable with gcc, but cgo requires gcc. Hence, this driver is not supported on Windows.
cli is based on alexbrainman's odbc package: https://github.com/alexbrainman/odbc.
This package registers a driver for the standard Go database/sql package and used through the database/sql API.
import _ "github.com/asifjalil/cli"
Error Handling
The package has no exported API except two functions-SQLCode() and SQLState()-for inspecting DB2 CLI error. The function signature is as follows:
func (e *cliError) SQLCode() int
func (e *cliError) SQLState() string
Since package cli is imported for side-effects only, use the following code pattern to access SQLCode() and SQLState():
func checkError(err error) {
type sqlcode interface {
SQLCode() int
}
if err != nil {
if err, ok := err.(sqlcode); ok {
log.Println(err.SQLCode())
}
log.Fatal(err)
}
}
The local interface can include SQLState() also for inspecting SQLState from DB2 CLI.
SQLCODE is a return code from a IBM DB2 SQL operation. This code can be zero (0), negative, or positive.
0 means successful execution.
Negative means unsuccessful execution with an error.
For example -911 means a lock timeout occurred with a rollback.
Positive means successful execution with a warning.
For example +100 means no rows found or end of table.
Search "SQL messages" in DB2 Information Center to find out more about SQLCODE.
SQLSTATE is a return code like SQLCODE. But instead of a number, it is a five character error code that is consistent across all IBM database products. SQLSTATE follows this format: ccsss, where cc indicates class and sss indicates subclass. Search "SQLSTATE Messages" in DB2 Information Center for more detail.
Connection String
This driver uses DB2 CLI function SQLConnect and SQLDriverConnect in driver.Open(...). To use SQLConnect, start the name or the DSN string with keyword sqlconnect. This keyword is case insensitive. The connection string needs to follow this syntax to be valid:
"sqlconnect;[DATABASE=<database_name>;][UID=<user_id>;][PWD=<password>;]"
[...] means optional. If a database_name is not provided, then SAMPLE is used as the database name. Also note that each keyword and value ends with a semicolon. The keyword "sqlconnect" doesn't take a value but ends with a semi-colon. Examples:
db, err := sql.Open("cli", "sqlconnect;")
db, err := sql.Open("cli", "sqlconnect; DATABASE=\"SAMPLE\";")
Any other connection string must follow the connection string rule that is valid with SQLDriverConnect. For example, this is a valid dsn/connection string for SQLDriverConnect:
"DSN=Sample; UID=asif; PWD=secrect; AUTOCOMMIT=0; CONNECTTYPE=1;"
Examples:
db, err := sql.Open("cli", "DSN=Sample; UID=asif; PWD=secrect; AUTOCOMMIT=0; CONNECTTYPE=1;")
db, err := sql.Open("cli", "DATABASE=db; HOSTNAME=dbhost; PORT=40000; PROTOCOL=TCPIP; UID=me; PWD=secret;")
Search SQLDriverConnect in DB2 LUW Information Center for more detail.
Installation
IBM DB2 for Linux, Unix and Windows (DB2 LUW) implements its own ODBC driver. This package uses the DB2 ODBC/CLI driver through cgo. As such this package requires DB2 C headers and libraries for compilation. If you don't have DB2 LUW installed on the system, then you can install the free, community DB2 version DB2 Express-C. You can also use IBM Data Server Driver package. It includes the required headers and libraries but not a DB2 database manager.
To install, download this package by running the following:
go get -d github.com/asifjalil/cli
Go to the following directory:
$GOPATH/src/github.com/asifjalil/cli
In that directory run the following to install the package:
./install.sh
This script and this driver only works on Mac OS and Linux.
Usage
See example_test.go.
Index
Examples
Package files
column.go conn.go driver.go error.go stmt.go strutil.go tx.go
Generated by godoc2md
Documentation
¶
Overview ¶
Package **cli** provides access to a **DB2 database** using DB2 Call Level Interface (**CLI**) API. This requires **cgo** and DB2 _cli/odbc_ driver **libdb2.so**. It is not possible to use this driver to create a statically linked Go package because IBM doesn't provide the DB2 _cli/odbc_ driver as _libdb2.a_ static library. On **Windows**, DB2 _cli/odbc_ library is not compatiable with **gcc**, but **cgo** requires **gcc**. Hence, this driver is not supported on Windows.
**cli** is based on *alexbrainman's* odbc package: https://github.com/alexbrainman/odbc.
This package registers a driver for the standard Go **database/sql** package and used through the **database/sql** API.
import _ "github.com/asifjalil/cli"
### Error Handling The package has no exported API except two functions-**SQLCode()** and **SQLState()**-for inspecting DB2 CLI error. The function signature is as follows:
func (e *cliError) SQLCode() int func (e *cliError) SQLState() string
Since package **cli** is imported for side-effects only, use the following code pattern to access SQLCode() and SQLState():
func checkError(err error) {
type sqlcode interface {
SQLCode() int
}
if err != nil {
if err, ok := err.(sqlcode); ok {
log.Println(err.SQLCode())
}
log.Fatal(err)
}
}
The local interface can include SQLState() also for inspecting SQLState from DB2 CLI.
**SQLCODE** is a return code from a IBM DB2 SQL operation. This code can be zero (0), negative, or positive.
0 means successful execution. Negative means unsuccessful execution with an error. For example -911 means a lock timeout occurred with a rollback. Positive means successful execution with a warning. For example +100 means no rows found or end of table.
Search "SQL messages" in DB2 Information Center to find out more about SQLCODE.
**SQLSTATE** is a return code like SQLCODE. But instead of a number, it is a five character error code that is consistent across all IBM database products. SQLSTATE follows this format: ccsss, where cc indicates class and sss indicates subclass. Search "SQLSTATE Messages" in DB2 Information Center for more detail.
### Connection String This driver uses DB2 CLI function **SQLConnect** and **SQLDriverConnect** in driver.Open(...). To use **SQLConnect**, start the name or the DSN string with keyword sqlconnect. This keyword is case insensitive. The connection string needs to follow this syntax to be valid:
"sqlconnect;[DATABASE=<database_name>;][UID=<user_id>;][PWD=<password>;]"
[...] means optional. If a database_name is not provided, then SAMPLE is used as the database name. Also note that each keyword and value ends with a semicolon. The keyword "sqlconnect" doesn't take a value but ends with a semi-colon. Examples:
db, err := sql.Open("cli", "sqlconnect;")
db, err := sql.Open("cli", "sqlconnect; DATABASE=\"SAMPLE\";")
Any other connection string must follow the connection string rule that is valid with SQLDriverConnect. For example, this is a valid dsn/connection string for SQLDriverConnect:
"DSN=Sample; UID=asif; PWD=secrect; AUTOCOMMIT=0; CONNECTTYPE=1;"
Examples:
db, err := sql.Open("cli", "DSN=Sample; UID=asif; PWD=secrect; AUTOCOMMIT=0; CONNECTTYPE=1;")
db, err := sql.Open("cli", "DATABASE=db; HOSTNAME=dbhost; PORT=40000; PROTOCOL=TCPIP; UID=me; PWD=secret;")
Search **SQLDriverConnect** in DB2 LUW *Information Center* for more detail.
## Installation IBM DB2 for Linux, Unix and Windows (DB2 LUW) implements its own ODBC driver. This package uses the DB2 ODBC/CLI driver through cgo. As such this package requires DB2 C headers and libraries for compilation. If you don't have DB2 LUW installed on the system, then you can install the free, community DB2 version *DB2 Express-C*. You can also use *IBM Data Server Driver package*. It includes the required headers and libraries but not a DB2 database manager.
To install, download this package by running the following:
go get -d github.com/asifjalil/cli
Go to the following directory:
$GOPATH/src/github.com/asifjalil/cli
In that directory run the following to install the package:
./install.sh
This script and this driver only works on Mac OS and Linux.
## Usage See `example_test.go`.
Example ¶
package main
import (
"context"
"database/sql"
"fmt"
"log"
"os"
"strings"
"time"
_ "github.com/asifjalil/cli"
)
func main() {
ExampleOpen()
ExampleLoad()
// Skip ExampleProc because the output is not always the same.
ExampleProc()
}
func ExampleOpen() {
var val float64
connStr := getConStr()
qry := "SELECT double(1.1) FROM sysibm.sysdummy1"
log.Println(strings.Repeat("#", 30))
log.Println("Shows how to connect, query, and disconnect from a DB2 database using the \"cli\" driver.")
log.Printf("sql.Open(\"cli\",\"%s\")\n", connStr)
db, err := sql.Open("cli", connStr)
checkError(err)
defer db.Close()
log.Println("Connected...")
log.Printf("db.QueryRow(\"%s\").Scan(&val)\n", qry)
err = db.QueryRow(qry).Scan(&val)
checkError(err)
log.Println(val)
fmt.Println(val)
log.Println("Disconnecting...")
log.Printf("db.Close()")
}
func ExampleProc() {
connStr := getConStr()
var (
snapTime time.Time
dbsize int64
dbcapacity int64
)
procStmt := "call sysproc.get_dbsize_info(?, ?, ?, 0)"
db, err := sql.Open("cli", connStr)
checkError(err)
defer db.Close()
log.Println(strings.Repeat("#", 30))
log.Println("Shows how to use a stored procedure with OUTPUT parameters")
log.Printf("Running %q\n", procStmt)
_, err = db.ExecContext(context.Background(), procStmt,
sql.Out{Dest: &snapTime},
sql.Out{Dest: &dbsize},
sql.Out{Dest: &dbcapacity})
checkError(err)
log.Printf("snapshot time: %v, dbsize: %d, dbcapacity: %d\n", snapTime, dbsize, dbcapacity)
log.Println("success ...")
}
func ExampleLoad() {
tabname := "loadtable"
createStmt := fmt.Sprintf("CREATE TABLE %s (Col1 VARCHAR(30))", tabname)
dropStmt := fmt.Sprintf("DROP TABLE %s", tabname)
connStr := getConStr()
tmpflName := "_TEST/test.del"
log.Println(strings.Repeat("#", 30))
log.Println("Shows how to load a table using SYSPROC.ADMIN_CMD and the \"cli\" driver.")
if home := os.Getenv("DATABASE_HOMEDIR"); home == "" {
log.Println("DATABASE_HOMEDIR is not set; skipping ExampleLoad")
return
} else {
tmpflName = home + "/" + tmpflName
}
db, err := sql.Open("cli", connStr)
checkError(err)
defer db.Close()
log.Println("Table to load: ", createStmt)
_, err = db.Exec(createStmt)
checkError(err)
var (
rows_read, rows_skipped,
rows_loaded, rows_rejected, rows_deleted,
rows_committed, rows_partitioned, num_agentinfo_entries sql.NullInt64
msg_retrieval, msg_removal sql.NullString
)
admin_cmd := fmt.Sprintf("CALL SYSPROC.ADMIN_CMD('LOAD FROM %s"+
" OF DEL REPLACE INTO %s NONRECOVERABLE')", tmpflName, tabname)
log.Println("load command: ", admin_cmd)
rows, err := db.Query(admin_cmd)
checkError(err)
rows.Next()
err = rows.Scan(&rows_read, &rows_skipped, &rows_loaded,
&rows_rejected, &rows_deleted, &rows_committed,
&rows_partitioned, &num_agentinfo_entries,
&msg_retrieval, &msg_removal)
checkError(err)
log.Println("Rows Read : ", rows_read.Int64)
log.Println("Rows Skipped : ", rows_skipped.Int64)
log.Println("Rows Loaded : ", rows_loaded.Int64)
log.Println("Rows Rejected : ", rows_rejected.Int64)
log.Println("Rows Deleted : ", rows_deleted.Int64)
log.Println("Rows Committed: ", rows_committed.Int64)
log.Println("Msg Retrieval : ", msg_retrieval.String)
log.Println("Msg Removal : ", msg_removal.String)
err = rows.Close()
checkError(err)
var col1 string
selectStmt := fmt.Sprintf("SELECT Col1 FROM %s", tabname)
log.Println("select: ", selectStmt)
rows, err = db.Query(selectStmt)
checkError(err)
for rows.Next() {
err = rows.Scan(&col1)
checkError(err)
log.Println(col1)
fmt.Println(col1)
}
checkError(rows.Err())
err = rows.Close()
checkError(err)
log.Println("Cleanup: ", dropStmt)
_, err = db.Exec(dropStmt)
checkError(err)
}
func checkError(err error) {
if err != nil {
log.Fatal(err)
}
}
func getConStr() string {
config := struct {
database string
uid string
pwd string
}{
database: "sample",
uid: "",
pwd: "",
}
if name := os.Getenv("DATABASE_NAME"); name != "" {
config.database = name
}
if user := os.Getenv("DATABASE_USER"); user != "" {
config.uid = user
}
if pass := os.Getenv("DATABASE_PASSWORD"); pass != "" {
config.pwd = pass
}
return fmt.Sprintf("DATABASE = %s; UID = %s; PWD = %s;",
config.database, config.uid, config.pwd)
}
Output: 1.1 Hello World
Source Files