Connecting an R development environment to a Db2 database

You can connect a local R development environment such as RStudio®, Rcmdr, or Rattle to a Db2 database.

1. In your local R environment, install the ibmdbR package by entering the following command:

   install.packages("ibmdbR")

   Your local R environment accesses the Comprehensive R Archive Network (CRAN) and automatically downloads and installs the ibmdbR package and any prerequisite packages that are not already installed.
2. Create an ODBC driver connection between your R development environment and the Db2 database:
   1. Set up your database as an ODBC data source.
   2. Open your locally installed R development environment.
   3. At the R prompt, enter the following statements to create the connection. Replace the placeholders with the database details and connect credentials that you collected beforehand.
      • If your locally installed R development environment runs in the Db2 database:

        library(ibmdbR)
        host.name <- "placeholderForYourHostName"
        port <-"placeholderForPortNumber" # 50000 if not using SSL or 50001 if using SSL
        user.name <-"placeholderForYourUserName"
        pwd <- "placeholderForYourPassword"
        con.text <- paste("placeholderForYourDSNName;DRIVER=BLUDB",
                          ";Database=BLUDB",
                          ";Hostname=",host.name,
                          ";Port=",port,
                          ";PROTOCOL=TCPIP",
                          ";UID=", user.name,
                          ";PWD=",pwd,sep="")
        # Connect to using a odbc Driver Connection string to a remote database
        con <- idaConnect(con.text)

      • If your locally installed R development environment does not run in the Db2 database:

        library(ibmdbR)
        driver.name <- "{placeholderForYourDriverName}"
        db.name <- "placeholderForYourDatabaseName"
        host.name <- "placeholderForYourHostName"
        port <-"placeholderForYourPort"
        user.name <-"placeholderForYourUserName"
        pwd <- "placeholderForYourPassword"
        con.text <- paste("placeholderForYourDSNName;DRIVER=",driver.name,
                          ";Database=",db.name,
                          ";Hostname=",host.name,
                          ";Port=",port,
                          ";PROTOCOL=TCPIP",
                          ";UID=", user.name,
                          ";PWD=",pwd,sep="")
        # Connect to using a odbc Driver Connection string to a remote database
        con <- idaConnect(con.text)

      Note that the statement that is used to create the connection object uses the idaConnect() method, not the odbcConnect() or odbcDriverConnect() method.
   4. Initialize the analytics package by issuing the following R command:

      idaInit(con)

   5. To test whether the connection is working, issue the following R command:

      idaShowTables()

      The console displays a list of all the tables and views in the current schema.