Contents


Building Scala applications that access IBM Data Servers

Use IBM Data Server Driver for JDBC and SQLJ in Scala applications to access IBM Data Servers

Comments

Scala is a concise, expressive and powerful programming language that is increasingly becoming the language of choice in the field of big data analytics. Apache Spark is a prime example of a fast data analytics platform that was built from the ground-up using Scala. The Scala API for Spark is the most up-to-date among all the APIs it offers.

Real-world applications that access big data systems and traditional relational database management systems (RDBMS) want easy connectivity to both. When Scala is your language of choice for such an application, working with IBM DB2 is easy.

Scala code gets compiled into byte code that runs on a Java™ virtual machine (JVM), which allows Scala applications to directly call Java libraries. Therefore, accessing DB2 from a Scala application is simply a matter of using the existing JDBC driver for DB2: the IBM Data Server Driver for JDBC and SQLJ.

Accessing DB2 from a Scala application is simply a matter of using the existing JDBC driver for DB2: the IBM Data Server Driver for JDBC and SQLJ.

The IBM Data Server Driver for JDBC and SQLJ is a pure Java driver that comes packaged as a single jar file (db2jcc.jar or db2jcc4.jar) and requires no special installation except to be on the class path of the Scala or Java application that uses it. This driver is great because it provides connectivity to multiple IBM Data Servers — IBM DB2 for Linux, Unix and Windows, IBM DB2 for z/OS, IBM Big SQL, IBM dashDB and IBM Informix. Therefore, the techniques discussed here apply to all of these data servers, and the "DB2" referenced in the article can refer to any one of the data servers that the IBM Data Server Driver for JDBC and SQLJ provides connectivity to.

In the following sections, I will walk you through how to create a Scala application — DB2Client. This application connects to a DB2 instance and performs some basic SQL operations. There are many different options available for implementing such an application in Scala. I'll show you three different implementations to demonstrate some of your options.

Refer to the Downloadable resources for the link to the Git repository containing the complete code for the application described here.

What you'll need to build your application

  1. http://www.scala-lang.org/ Scala programming language binaries
  2. Simple Build Tool (sbt). sbt is a widely used tool that fits very well with Scala projects and provides build and dependency management similar to Maven. sbt's dependency management abilities include using Maven format repositories.
  3. IBM Data Server Driver for JDBC and SQLJ
  4. Optional: np plugin for sbt. sbt uses a Maven-like directory structure for projects. This plugin makes it easy to create the required directory structure and a basic sbt build definition file for a new project. You can choose to not use this plugin and instead create the necessary directory structure and build file manually.

Step 1. DB2Client project setup

  1. Simple Build Tool (sbt) uses a Maven-like directory structure for a project, as Listing 1 shows.
    Listing 1. Directory structure of an sbt project
    project_home/
      |
      |--- build.sbt        (build definition file)
      |
      +---src
      |   +---main
      |   |   +---resources (resource files for application jar)
      |   |   +---scala     (scala source files)
      |   |   +---java      (java source files)
      |   |
      |   +---test
      |       +---resources (resource files for test jar)
      |       +---scala     (test scala source files)
      |       +---java      (test java source files)
      |
      +---lib (unmanaged library jars)

    For each of the implementations described here, the directory DB2client is the project_home. Inside DB2client, the build definition file build.sbt and the rest of the project directory structure are created.

    To create the project directory structure:

    • If the np plugin for sbt is set up in your environment, change to the DB2client/ directory and issue the command: sbt np. This would create the project directory structure and a default build file build.sbt under DB2client/.
    • If the np plugin for sbt is not set up in your environment, manually create the directory structure inside DB2client/ directory. For this application, you only need to create the following directories and create an employ file named build.sbt under DB2client.
      DB2Client/
        |
        +---src
        |   +---main
        |       +---scala
        |
        +---lib
  2. Once the directory structure is set up, you need to update the build.sbt file with some general properties for the project, including the application's name, version, and Scala language version.

    For more details on sbt build definitions, refer to the sbt documentation.

    With these updates, the build.sbt looks like the one in Listing 2:

    Listing 2. build.sbt
    organization := "com.example"
    
    name := "db2client"
    
    scalaVersion := "2.11.6"
    
    version := "1.0"
  3. Place the driver jar in the lib/ directory under DB2client/.

    Currently, the IBM Data Server Driver for JDBC and SQLJ is an unmanaged dependency for Scala projects since it is not available on public repositories. You can obtain the driver jar in one of two ways:

    • Download the driver jar from the IBM software downloads website. Click the link that says "Install the DB2 driver or client that meets your requirements," and select IBM Data Server Driver for JDBC and SQLJ (JCC Driver).
    • Get it from an existing DB2 server or client installation that has packaged the driver with it. For example, on Linux, Unix, and Windows platforms, the driver (db2jcc.jar or db2jcc4.jar) is present under the DB2 installation's sqllib/java directory.

Step 2. Write and execute application code

After the project setup, let's look at how to implement the application code to access DB2 and do something useful. For this, we will implement a DB2Client.scala class.

In the next sections, I'll show you three of many possible ways you can implement the DB2Client.scala class. The three variations demonstrate how the JDBC driver for DB2 can easily fit with any approach you choose.

Once the code is implemented, you can compile and run your application by changing to the DB2client/ directory and executing the following commands:

  • to compile the code: sbt compile
  • to execute the application: sbt run

DB2Client implementation 1: Using JDBC API

In this implementation, the Scala application uses the standard JDBC API for database access.

Create a Scala class DB2Client.scala. Using the code shown in Listing 3, add connectURL() method to this class. connectURL() acquires a connection to a DB2 instance and returns it to the caller.

Listing 3. Acquire a DB2 connection
  def connectURL(): java.sql.Connection = {

    /*
     * connection properties may be provided on the url,
     * for example to generate a trace, include trace properties on the url:
     *
     * val url = "jdbc:db2://localhost:50000/DBTEST:traceFile=cpds.txt;traceLevel=-1;"
     */
    val url = "jdbc:db2://localhost:50000/dbname"

    val driver = "com.ibm.db2.jcc.DB2Driver"
    val username = "user"
    val password = "password"

    // acquire the connection
    Class.forName(driver) // this step not needed with JRE 1.6+ and db2jcc4.jar
    var connection = java.sql.DriverManager.getConnection(url, username, password)

    connection
  }

Next, we add code to invoke the connectURL() method to obtain a connection object. The code uses standard JDBC API on the connection object to create a table EMP, insert data into the table, and read it back. Listing 4 shows the completed DB2Client.scala code.

Listing 4. DB2Client.scala implemented using JDBC API
package com.example.db2.jdbc

object DB2Client {

  def connectURL(): java.sql.Connection = {

    /*
     * connection properties may be provided on url,
     * for example to generate a trace, include trace properties on the url:
     *
     * val url = "jdbc:db2://localhost:50000/DBTEST:traceFile=cpds.txt;traceLevel=-1;"
     */
    val url = "jdbc:db2://localhost:50000/dbtest"

    val driver = "com.ibm.db2.jcc.DB2Driver"
    val username = "db2admin"
    val password = "db2admin"

    // acquire the connection
    Class.forName(driver) // this step not needed with JRE 1.6+ and db2jcc4.jar
    var connection = java.sql.DriverManager.getConnection(url, username, password)

    connection
  }
     
  def main(args: Array[String]) {
    
    val conn: java.sql.Connection = connectURL()
   
    println("obtained connection ")
    
    try {
      var st = conn.createStatement()
      st.executeUpdate("create table emp (id integer not null primary key, name varchar(30))")
      st.close
      
      var pst = conn.prepareStatement("insert into emp (id, name) values (?, ?)")
      pst.setInt (1, 1)
      pst.setString (2, "name1")      
      pst.executeUpdate()
      
      pst.clearParameters()
      pst.setInt (1, 2)
      pst.setString (2, "name2")      
      pst.executeUpdate()
      
      pst.close

      st = conn.createStatement()
      var rs = st.executeQuery ("select * from emp")
      
      while (rs.next()) {
        println("Emp("+rs.getInt(1)+","+rs.getString(2)+")")
      }
      
      rs.close()
      st.close()
    
    } catch {
      case ex : java.sql.SQLException => println("SQLException: "+ex)
    } finally {
       conn.close()
    }
  }
}

Listing 5 shows the sample output of running this DB2Client application.

Listing 5. Sample output for DB2Client implemented with JDBC API
> sbt run
[info] Running com.example.db2.jdbc.DB2Client
obtained connection
Emp(1,name1)
Emp(2,name2)
[success] Total time: 2 s, completed Apr 8, 2015 3:48:42 PM

In the Scala world, you will likely use an available database access library to avoid dealing with the JDBC API directly. There are multiple libraries available. In in the following sections, I'll go over examples of using two such libraries — Anorm and ScalikeJDBC.

DB2Client implementation 2: Using Anorm

Anorm is a database access library usually consumed as a part of the Play Framework — a high velocity web application framework for Java and Scala applications. However, Anorm may also be used as an independent library for a standalone Scala project.

To use Anorm, update the build.sbt file to specify the application's dependency on the Anorm library:

libraryDependencies += "com.typesafe.play" %% "anorm" % "2.3.6"

The Anorm library is available from a Typesafe repository which is not among the default repositories that sbt uses. To instruct sbt to use the Typesafe repository for resolving library dependencies, add the Typesafe resolver definition to the build file:

resolvers ++= Seq(
 "Typesafe Releases" at "http://repo.typesafe.com/typesafe/releases/")

Listing 6 shows the updated build.sbt.

Listing 6. build.sbt updated for using Anorm
organization := "com.example"

name := "db2scala_anorm"

scalaVersion := "2.11.6"

version := "1.0"

libraryDependencies += "com.typesafe.play" %% "anorm" % "2.3.6"

resolvers ++= Seq(
  "Typesafe Releases" at "http://repo.typesafe.com/typesafe/releases/")

Anorm requires an instance of a java.sql.Connection to be supplied to its APIs. To generate the java.sql.Connection objects, add a connectURL() method identical to what we used in the previous implementation in Listing 3.

Next, we add code that uses the Anorm API to create an EMP table, insert some rows into it, and then query them back.

Listing 7 shows the completed DB2Client.scala code for this implementation. Note the two different ways of executing a query and parsing results in this code, both much more concise than the earlier JDBC implementation.

Listing 7. DB2Client.scala implemented using Anorm
package com.example.db2.anorm

import anorm._

object DB2Client {

  def connectURL(): java.sql.Connection = {

    /*
     * connection properties may be provided on url,
     * for example to generate a trace, include trace properties on the url:
     *
     * val url = "jdbc:db2://localhost:50000/DBTEST:traceFile=cpds.txt;traceLevel=-1;"
     */
    val url = "jdbc:db2://localhost:50000/dbtest"

    val driver = "com.ibm.db2.jcc.DB2Driver"
    val username = "db2admin"
    val password = "db2admin"

    // acquire the connection
    Class.forName(driver) // this step not needed with JRE 1.6+ and db2jcc4.jar
    var connection = java.sql.DriverManager.getConnection(url, username, password)

    connection
  }

  def main(args: Array[String]) {

    implicit val conn: java.sql.Connection = connectURL()

    try {
      println("obtained connection " + conn)

      SQL("create table emp (id integer not null primary key, name varchar(30))").execute()

      SQL("insert into emp (id, name) values ({id}, {name})")
        .on("id" -> 1, "name" -> "name1").executeUpdate()
      SQL("insert into emp (id, name) values ({id}, {name})")
        .on("id" -> 2, "name" -> "name2").executeUpdate()

      // this is one way to query
      val selectAll: SqlQuery = SQL("select * from emp")
      selectAll() foreach println

      // and here's another way where row data is mapped to a case class
      case class Emp(id: Int, name: String)

      val res: Stream[Emp] = SQL("select id,name from emp")().collect {
        case Row(id: Int, Some(name: String)) => Emp(id, name)
      }
      res foreach println
      
    } finally {
      conn.close()
    }
  }
}

Listing 8 shows a sample output of running this application.

Listing 8. Sample output for DB2Client implemented with Anorm
> sbt run
obtained connection
Row('ColumnName(EMP.ID,Some(ID))': 
1 as java.lang.Integer, 'ColumnName(EMP.NAME,Some(NAME))': name1 as java.lang.String)

Row('ColumnName(EMP.ID,Some(ID))': 
2 as java.lang.Integer, 'ColumnName(EMP.NAME,Some(NAME))': name2 as java.lang.String)
Emp(1,name1)
Emp(2,name2)

DB2Client implementation 3: Using ScalikeJDBC

ScalikeJDBC is another Scala data access library that offers features like implicit use of connection pools. Visit http://scalikejdbc.org/ for more details on this library.

To use ScalikeJDBC, update the build.sbt to add dependencies on the scalikejdbc and the logback libraries that ScalikeJDBC requires. Listing 9 shows the updated build.sbt.

Listing 9. build.sbt updated for using scalikejdbc
organization := "com.example"

name := "db2scala_scalikejdbc"

scalaVersion := "2.11.6"

version := "1.0"

libraryDependencies ++= Seq(
   "org.scalikejdbc" %% "scalikejdbc" % "2.2.5",
   "ch.qos.logback"  %  "logback-classic"   % "1.1.2"
 )

In this implementation, the responsibility of acquiring connections and setting up a connection pool is offloaded to the ScalikeJDBC library. This removes the need for a connectURL() method to generate connection instances.

Listing 10 shows the complete code for the DB2Client.scala class that uses the ScalikeJDBC library to acquire a connection to a DB2 instance and execute queries on it. Note how a connection pool gets implicitly created and used here.

Listing 10. DB2Client.scala implemented using ScalikeJDBC
package com.example.db2.scalikejdbc

import scalikejdbc._

object DB2Client {

  def main(args: Array[String]) {

    /*
     * connection properties may be provided on url,
     * for example to generate a trace, include trace properties on the url:
     *
     * val url = "jdbc:db2://localhost:50000/DBTEST:traceFile=cpds.txt;traceLevel=-1;"
     */
    val url = "jdbc:db2://localhost:50000/DBTEST"    
    
    val user = "db2admin"
    val password = "db2admin"
    
    
    // logging settings used by scalikejdbc
    GlobalSettings.loggingSQLAndTime = LoggingSQLAndTimeSettings(
      enabled = true, // set to false to disable logging output
      singleLineMode = true,
      printUnprocessedStackTrace = false,
      stackTraceDepth = 15,
      logLevel = 'debug, // change to 'info, 'warn, 'error or 'off
      warningEnabled = false,
      warningThresholdMillis = 3000L,
      warningLogLevel = 'warn)

    Class.forName("com.ibm.db2.jcc.DB2Driver")

    ConnectionPool.singleton(url, user, password) // builds connection pool

    // connection is implicitly borrowed from connection pool here
    // and closed (returned) where the DB { } block ends
    DB autoCommit { implicit session =>
      SQL("create table emp (id integer not null primary key, name varchar(30))").executeUpdate.apply()
      SQL("insert into emp (id, name) values (?, ?)").bind(1, "name1").update.apply()
      SQL("insert into emp (id, name) values (?, ?)").bind(2, "name2").update.apply()
    }
    
    DB autoCommit { implicit session =>
      sql"select * from emp".foreach { rs =>
        println("Emp("+rs.int("id")+","+rs.string("name")+")")
      }
    }
  }
}

Listing 11 shows the sample output of running this application.

Listing 11. Sample output for DB2Client implemented using ScalikeJDBC
run output:

[info] Running com.example.db2.scalikejdbc.DB2Client
01:55:37.240 [run-main-0] DEBUG scalikejdbc.ConnectionPool$ - Registered connection pool : ConnectionPool(url:jdbc:db2://localhost:50000/DBTEST, user:db2admin) using factory : <default>
01:55:37.282 [run-main-0] DEBUG scalikejdbc.ConnectionPool$ - Registered singleton connection pool : ConnectionPool(url:jdbc:db2://localhost:50000/DBTEST, user:db2admin)
01:55:38.474 [run-main-0] DEBUG s.StatementExecutor$$anon$1 - [SQL Execution] create table emp (id integer not null primary key, name varchar(30)); (713 ms)
01:55:38.526 [run-main-0] DEBUG s.StatementExecutor$$anon$1 - [SQL Execution] insert into emp (id, name) values (1, 'name1'); (14 ms)
01:55:38.537 [run-main-0] DEBUG s.StatementExecutor$$anon$1 - [SQL Execution] insert into emp (id, name) values (2, 'name2'); (2 ms)
01:55:38.587 [run-main-0] DEBUG s.StatementExecutor$$anon$1 - [SQL Execution] select * from emp; (23 ms)
Emp(1,name1)
Emp(2,name2)
[success] Total time: 2 s, completed Apr 26, 2015 1:55:38 AM

Trying out the sample code

If you clone the sample code for this article from the Git project included in the downloads section below, make sure the database url, user, and password values in the code are updated to match the DB2 instance you are running it against.

Each of the sample implementations attempts to create the EMP table before reading and writing to it. If the table already exists in your test database, you can comment out the CREATE queries in the sample code, otherwise the CREATE queries will fail with DB2 SQL Error: SQLCODE=-601, SQLSTATE=42710, indicating that the table can not be created since it already exists in the database.

JDBC support in Spark

Any Scala environment that can consume a JDBC driver to access a RDBMS can use the IBM Data Server Driver for JDBC and SQLJ. Since the article started off noting the emergence of Scala friendly analytics platforms like Spark, it is worth noting here that Spark contains built-in support for accessing RDBMS via a JDBC driver and exposes this function to user applications through its JDBC DataSource support in Spark SQL and the DataFrames API. Refer the Resources section below for a link to a good reference article on this.

Conclusion

We looked at three different Scala implementations that use the IBM Data Server Driver for JDBC and SQLJ to access DB2. One can use the standard JDBC API, or pick an existing data access library to create Scala applications with full-fledged access to DB2. The techniques described here can be used with multiple IBM Data Servers, including IBM DB2 for Linux, Unix and Windows, IBM DB2 for z/OS, IBM DB2 for iSeries, IBM Big SQL, IBM dashDB and IBM Informix.

Acknowledgements

Thank you to Berni Schieffer, IBM Fellow, and Mario D. Briggs, STSM, for their help with reviewing the contents of this article.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Big data and analytics
ArticleID=1016474
ArticleTitle=Building Scala applications that access IBM Data Servers
publish-date=10012015