README
¶
Package cli
import _ "github.com/asifjalil/cli"
Overview
Package cli provides access to a DB2 database on Linux, Unix, and Windows (LUW) using DB2 Call Level Interface (CLI) API. 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.
###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 only works on Mac OS and Linux. For Windows, please set the CGO_LDFLAGS and CGO_CFLAGS, so cgo can locate the DB2 CLI library and CLI C header files. Then run:
go install
##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** on Linux, Unix, and Windows (LUW) using DB2 Call Level Interface (**CLI**) API. **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:
got 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 only works on Mac OS and Linux. For Windows, please set the CGO_LDFLAGS and CGO_CFLAGS, so cgo can locate the DB2 CLI library and CLI C header files. Then run:
go install
##Usage See `example_test.go`.
Example ¶
package main
import (
"database/sql"
"fmt"
"io/ioutil"
"log"
"os"
"strings"
_ "github.com/asifjalil/cli"
)
func main() {
ExampleOpen()
ExampleLoad()
}
func ExampleOpen() {
var val float64
connStr := "SQLConnect; Database = Sample;"
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 ExampleLoad() {
tabname := "loadtable"
createStmt := fmt.Sprintf("CREATE TABLE %s (Col1 VARCHAR(30))", tabname)
dropStmt := fmt.Sprintf("DROP TABLE %s", tabname)
connStr := "SQLConnect; Database = Sample;"
log.Println(strings.Repeat("#", 30))
log.Println("Shows how to load a table using SYSPROC.ADMIN_CMD and the \"cli\" driver.")
tmpflName := prepData(tabname)
db, err := sql.Open("cli", connStr)
checkError(err)
defer os.Remove(tmpflName)
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 prepData(filePrefix string) string {
wContents := []byte("Hello\nWorld\n")
tmpfile, err := ioutil.TempFile("", filePrefix)
checkError(err)
_, err = tmpfile.Write(wContents)
checkError(err)
err = tmpfile.Close()
checkError(err)
return tmpfile.Name()
}
func checkError(err error) {
if err != nil {
log.Fatal(err)
}
}
Output: 1.1 Hello World
Source Files