Developing Scala applications that access IBM Db2

Scala is a concise, expressive and powerful Java Virtual Machine (JVM) programming language. Scala code gets compiled into bytecode that runs on a JVM allowing Scala code to consume Java libraries.

To access IBM Data Servers using Scala, use the IBM Data Server Driver for JDBC and SQLJ. You can write applications using the JDBC API or third-party data access libraries which are built as abstractions on top of the JDBC API. All features and API interfaces provided by the driver can be used in Scala applications.

Note: The current releases of Scala compiler 'scalac' has deprecated support for creating Java 1.5 compatible binaries. Java 1.6 and above is recommended for running Scala applications.

Example

The following is an example of a Scala application written 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 include trace properties on the url:     
*     
* val url = "jdbc:db2://localhost:50000/SAMPLE:traceFile=cpds.txt;traceLevel=-1;"     
*      
* all other variants of java.sql.DriverManager.getConnection( ) APIs may     
* also be used, for example:        
*      
*java.sql.DriverManager.getConnection(String url, Properties info)     
*java.sql.DriverManager.getConnection(String url, String user, String password)     
*/
val url  =  "jdbc:db2://localhost:50000/SAMPLE"
val username =  "db2admin"    
val password =  "db2admin"
// load the driver and acquire connection.    
// Note, the Class.forName( ) way of loading a driver is not needed     
// when using the JDBC 4 compliant JCC driver db2jcc4.jar 
 
Class. forName ( "com.ibm.db2.jcc.DB2Driver" )   // needed   only for db2jcc.jar, not db2jcc4.jar
var connection = java.sql.DriverManager. getConnection ( url ,  username ,  password )
     connection  
}
 def main ( args : Array[String]) {
println ( "obtaining connection..." )
val con : java.sql. Connection  =  connectURL ()
     println ( "connection obtained." ) 
try {
println ( "retrieving data..." )       
con . setAutoCommit (false)

var st  =  con . createStatement ()
var rs  =  st . executeQuery ( "SELECT EMPNO, FIRSTNME, LASTNAME, SALARY FROM EMPLOYEE WHERE SALARY > 50000" )
       while( rs . next ()) {
          printf ( "Employee (%s, %s, %s, %8.2f) \n " ,  rs . getString ( 1 ),  rs . getString ( 2 ),
          rs . getString ( 3 ),  rs . getDouble ( 4 ))
          }

rs . close ()       
st . close ()

println ( "updating data..." )      
var pst =  con . prepareStatement ( """INSERT INTO EMPLOYEE (                                     
                                    EMPNO,
                                    FIRSTNME,                                    
                                    LASTNAME,                                    
                                    EDLEVEL,                                    
                                    BIRTHDATE,                                     
                                    SALARY)
                                    VALUES (?, ?, ?, ?, ?, ?)""" )
pst . setString ( 1 ,  "300000" )       
pst . setString ( 2 ,  "JOHN" )       
pst . setString ( 3 ,  "DOE" )       
pst . setInt ( 4 ,  18 )       
pst . setDate ( 5 , java.sql.Date. valueOf ( "1980-01-01" ))        
pst . setDouble ( 6 ,  50500.00 )       
pst . executeUpdate ()

pst . clearParameters ()       
pst . setString ( 1 ,  "300100" )       
pst . setString ( 2 ,  "JANE" )       
pst . setString ( 3 ,  "DOE" )       
pst . setInt ( 4 ,  19 )       
pst . setDate ( 5 , java.sql.Date. valueOf ( "1980-01-01" ))       
pst . setDouble ( 6 ,  60750.00 )       
pst . executeUpdate () 

pst . close

println ( "retrieving data again..." )       
st =  con . createStatement ()
rs  =  st . executeQuery ( "SELECT EMPNO, FIRSTNME, LASTNAME, SALARY FROM EMPLOYEE WHERE SALARY > 50000" )

while( rs . next ()) {        
      var emp  =  printf ( "Employee (%s, %s, %s, %8.2f)" ,  rs . getString ( 1 ),  rs . getString ( 2 ),
           rs . getString ( 3 ),  rs . getDouble ( 4 ))         
            println ( emp )
            }        
            rs . close ()       
            st . close () 
   } catch{ 
      case ex : java.sql.SQLException =>  println ( "SQLException occurred: " + ex )
    } finally{       
      println ( "closing connection." )
      con . close ()    
      }  
   }
}

Other ways of accessing Db2 in Scala

Most Data Access libraries and frameworks in Scala are built on top of the JDBC standard and allow a JDBC driver to be configured for database access. You can access Db2 providing the IBM Data Server Driver for JDBC and SQLJ as the JDBC driver when using these frameworks. Here are examples of some such data access frameworks available for Scala applications: Refer to the documentation accompanying these libraries and frameworks on how to supply the IBM Data Server Driver for JDBC and SQLJ driver for accessing Db2.

The Spark SQL module of the Spark big data processing system allows access to databases through JDBC. Using the IBM Data Server Driver for JDBC and SQLJ, Db2 can be accessed using Spark SQL. The following is a code snippet from a Spark SQL application written in Scala that uses Spark's DataFrame API and IBM Data Server Driver for JDBC and SQLJ to access Db2:

import org.apache.spark.sql.SQLContext
import org.apache.spark.SparkConf
import org.apache.spark.SparkContext
.
.
.
// Obtain Sparkcontext
val sc = new SparkContext(conf)

// Obtain SQLContext
val sqlContext = new SQLContext(sc)
// Construct DataFrame from Employee table,
// passing the Database URL and Driver class
// to the sqlContext.load( ) API
val employeeDF = sqlContext.load("jdbc", Map(
"url" -> "jdbc:db2://localhost:50000/SAMPLE:user=db2admin;password=db2admin;",
"driver" -> "com.ibm.db2.jcc.DB2Driver",
"dbtable" -> "EMPLOYEE"))

// show the DataFrame contents
employeeDF.show();

Scala Development Resources