[Metricbeat] IBM DB2 Module

[sorantis]

As ancient as it might sound, IBM DB2 is a widely used database engine, that's currently ranked 6 in the DB engines ranking. Today DB2 represents a family of data management products with a focus on AI powered features.
Recently IBM introduced a free version of the database to boost developer engagement. (Download options can be found here).

Today Metricbeat already provides integrations with many database engines, however IBM DB2 is not yet supported. There is a workaround that suggests our users to use the JDBC input with Logstash and collect DB2 metrics. This workaround is not straightforward and requires prior knowledge of configuring JDBS inputs on the user side. Having a Metricbeat module for IBM DB2 would remove this complexity and significantly increase user experience.

User Story:
As a user I want to use Metricbeat for monitoring my DB2 instances so that I could easily observe the state and performance of the DB2 databases.

Suggested targets:

• Tablespaces.
• I/O and storage (BUFFERPOOL).
• Concurrency and locks.
• SQL performance.

[amandahla]

@sorantis I would like to help but I don't have DB2 to test.

Do you think I can use this image?
https://hub.docker.com/r/ibmcom/db2

[amandahla]

I think we can use ODBC in this case to avoid CGO. I wrote this test but I need help to construct a query to select tablespace data.

1. Install ODBC and DB2 driver
   Reference: https://medium.com/@zhimin.wen/talking-to-ibm-db2-with-golang-dad8bb6ee4b4

Files:

/etc/odbc.ini 
[MyDB]
Protocol=TCPIP
Hostname=127.0.0.1
Port=50000
Database=testdb
CurrentSchema=db2inst1
Driver=/home/METRICBEATS/db2_odbc_10.5fp10/odbc_cli/clidriver/lib/libdb2o.so

/etc/odbcinst.ini 
[DB2]
Description=DB2 Driver
Driver=/home/METRICBEATS/db2_odbc_10.5fp10/odbc_cli/clidriver/lib/libdb2o.so
fileusage=1
dontdlclose=1

/home/METRICBEATS/db2_odbc_10.5fp10/odbc_cli/clidriver/cfg/db2cli.ini
[MyDB]
Protocol=TCPIP
Hostname=127.0.0.1
Port=50000
Database=testdb
CurrentSchema=db2inst1
Driver=/home/METRICBEATS/db2_odbc_10.5fp10/odbc_cli/clidriver/lib/libdb2o.so

2. Run docker image to simulate DB2

docker run -itd --name mydb2 --privileged=true -p 50000:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=123456 -e DBNAME=testdb -e SAMPLEDB=true -v /home/METRICBEATS/db2_logs/:/database ibmcom/db2

3. Run test program

package main

import (
	"fmt"

	_ "github.com/alexbrainman/odbc"
	"github.com/jmoiron/sqlx"
)

// Metric defines tablespace WIP metric
type Metric struct {
	TbName     string `db:"TBSPACE"`
	PageSize   int64  `db:"PAGESIZE"`
	ExtendSize int64  `db:"EXTENTSIZE"`
}

func main() {
	dbName := "MyDB"
	uid := "db2inst1"
	password := "123456"

	db, err := sqlx.Open("odbc", fmt.Sprintf("DSN=%s;uid=%s;pwd=%s", dbName, uid, password))
	if err != nil {
		fmt.Println(err)
	}

	err = db.Ping()
	if err != nil {
		fmt.Println(err)
	}

	rows, err := db.Queryx("select TBSPACE,PAGESIZE,EXTENTSIZE from syscat.tablespaces")
	if err != nil {
		fmt.Println(err)
	}

	metric := Metric{}
	for rows.Next() {
		err := rows.StructScan(&metric)
		if err != nil {
			fmt.Println(err)
		}
		fmt.Printf("%#v\n", metric)
	}
}

Output:

main.Metric{TbName:"SYSCATSPACE", PageSize:4096, ExtendSize:4}
main.Metric{TbName:"TEMPSPACE1", PageSize:4096, ExtendSize:32}
main.Metric{TbName:"USERSPACE1", PageSize:4096, ExtendSize:32}
main.Metric{TbName:"SYSTOOLSPACE", PageSize:4096, ExtendSize:4}

[sorantis]

@amandahla thanks for initiating this!
Not a DB2 expert, but I'd start with the officially provided Docker image and the MON functions.

[amandahla]

I'm not sure if we can do this here/now but my husband(@rikatz ) gave me the suggestion to do something generic to collect from any ODBC connection.

So you can set any query to get values.

Would be something like this:

...
rows, err := db.Queryx("SELECT varchar(tbsp_name, 30) as tbsp_name, reclaimable_space_enabled, tbsp_free_pages, tbsp_page_top, tbsp_usable_pages FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t ORDER BY tbsp_free_pages ASC")
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()

	// Extracted from
	// https://stackoverflow.com/questions/23507531/is-golangs-sql-package-incapable-of-ad-hoc-exploratory-queries/23507765#23507765
	cols, err := rows.Columns()
	if err != nil {
		fmt.Println(err)
	}

	vals := make([]interface{}, len(cols))
	for i := 0; i < len(cols); i++ {
		vals[i] = new(interface{})
		if i != 0 {
			fmt.Print("\t")
		}
		fmt.Print(cols[i])
	}


	for rows.Next() {
		err = rows.Scan(vals...)
		if err != nil {
			fmt.Println(err)
			continue
		}

		for i := 0; i < len(vals); i++ {
			if i != 0 {
				fmt.Print("\t")
			}
			printValue(vals[i].(*interface{}))
		}
		fmt.Println()

	}

	if rows.Err() != nil {
		fmt.Println(rows.Err())
	}
}

func printValue(pval *interface{}) {
	switch v := (*pval).(type) {
	case nil:
		fmt.Print("NULL")
	case bool:
		if v {
			fmt.Print("1")
		} else {
			fmt.Print("0")
		}
	case []byte:
		fmt.Print(string(v))
	case time.Time:
		fmt.Print(v.Format("2006-01-02 15:04:05.999"))
	default:
		fmt.Print(v)
	}
}

Output:

TBSP_NAME       RECLAIMABLE_SPACE_ENABLED       TBSP_FREE_PAGES TBSP_PAGE_TOP   TBSP_USABLE_PAGES
TEMPSPACE1      0       0       0       1
SYSCATSPACE     1       4992    27772   32764
SYSTOOLSPACE    1       8044    144     8188
USERSPACE1      1       8064    96      8160

[botelastic]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[sorantis]

keep it open

[amandahla]

News here? I did a quick search and unfortunately didn't find a go-native way to connect with DB2 like the others databases, all drivers uses CGO. Same for ODBC :(
Same problem since last year, I guess.

[sorantis]

There's a go driver for DB2, not sure how stable it is though.

[amandahla]

Yes, but it seems that it uses CGO also, not native. :(

[jsoriano]

For oracle we use a driver that also uses cgo, so maybe we can do something similar for db2. @sayden what do you think?

[botelastic]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.