Access dashDB (or DB2) using JDBC from Scala notebook

IBM dashDB is a fully managed cloud data warehouse, purpose-built for analytics. In this article we will explore how to connect to it from a Scala notebook and perform data access using JDBC.

To get started you will need credentials for a dashDB database. If you don't already have an instance of dashDB you can get one for free by following steps in this article.

Next, create a new Jupyer notebook in Data Scientist Workbench. Set it to use Scala.



Enter the connection details for your instance of dashDB (or DB2 database).
val dsn_driver = "com.ibm.db2.jcc.DB2Driver";
val dsn_database = "BLUDB";          
val dsn_hostname = "<Enter Hostname>";
val dsn_port = "50000";              
val dsn_protocol = "TCPIP";          
val dsn_uid = "<Enter UserID>";      
val dsn_pwd = "<Enter Password>"    
Run the cell above. Create a new cell to create the connection:
import java.sql.{Connection, DriverManager, ResultSet};
java.sql.DriverManager.registerDriver(new com.ibm.db2.jcc.DB2Driver);

val url = List("jdbc:db2://", dsn_hostname, ":", dsn_port, "/", dsn_database).mkString("");

val connection = java.sql.DriverManager.getConnection(url, dsn_uid, dsn_pwd)
connection
Next, execute a query against a sample table to verify everything is working:
val query = "select * from samples.cow";
val resultSet = connection.createStatement.executeQuery(query)
Let's print the results.
while ( resultSet.next() ) {
    val code = resultSet.getString("COW_CODE")
    val desc = resultSet.getString("COW_DESC")
    println("Cow code = " + code + ", desc = " + desc)
}
The results will look like:
Cow code = b, desc = Under 16 years/Never worked
Cow code = 1, desc = Private for-profit company Employee
Cow code = 2, desc = Private non-profit company Employee
Cow code = 3, desc = Local government employee
Cow code = 4, desc = State government employee
Cow code = 5, desc = Federal government employee
Cow code = 6, desc = Self-employed in own not incorporated
Cow code = 7, desc = Self-employed in own incorporated
Cow code = 8, desc = Working without pay in family business or farm
Cow code = 9, desc = Unemployed and last worked 5 years ago or earlier 
Cool! You've accessed data in a dashDB data warehouse using JDBC connection from a Scala notebook. Finally, as a best practice we should close the database connection once we're done with it.
connection.close()
Good luck!

Feedback and Knowledge Base