DB2 connect

Connects to IBM DB2 databases, using the RJDBC package. Integrates with RStudio database connection pane, see: RStudio Connections Pane

Requirements

• Java
• DB2 JDBC Driver see: http://www-01.ibm.com/support/docview.wss?uid=swg21363866

Usage

Setting up a connection.

library(db2connect)
conn <- db2_connect(
  db_name  = "DATABASENAME",
  username = "username",
  server_address = "server-adress:50000",
  driver_path = "/path/to/db2jcc4.jar"
)

Get first 10 rows from table test_table in schema test_schema.

db2_query(conn, "SELECT * FROM test_schema.test_table LIMIT 10")

As R Markdown supports SQL chunks, this also works

```{sql, connection = conn}
SELECT * 
  FROM test_schema.test_table 
  LIMIT 10
```

List schemas in database.

db2_list_objects(conn)

List tables in a specific schema.

db2_list_objects(conn, schema = "test_schema")

List column and column types in a table.

db2_list_columns(conn, schema = "test_schema", table = "test_table")

Close the connection.

db2_close(conn)

For development of RStudio integration see: Connections Contract

Customization

The db2_connect function can be further customized, accepting the following arguments:

• db_name the name of the database
• username name of user
• password user password
• server_address address to server, including port, in the format <address>:<port>
• driver_path path to your local copy of the DB2 JDBC driver db2jcc4.jar.

All these arguments can be set beforehand using options

options("db2.username" = "username")
options("db2.db_name" = "database")
options("db2.password" = "secret-password")
options("db2.server_address" = "serveradress:port")
options("db2.driver_path" = "path/to/driver")

The password argument also accept quoted functions, thus, one can pass in passwords from a keyring. For example:

options("db2.password" = quote(keyring::key_get("service", "username")))