Access dashDB (or DB2) using JDBC from Scala notebook
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";Run the cell above. Create a new cell to create the connection:
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>"
import java.sql.{Connection, DriverManager, ResultSet};Next, execute a query against a sample table to verify everything is working:
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
val query = "select * from samples.cow";Let's print the results.
val resultSet = connection.createStatement.executeQuery(query)
while ( resultSet.next() ) {The results will look like:
val code = resultSet.getString("COW_CODE")
val desc = resultSet.getString("COW_DESC")
println("Cow code = " + code + ", desc = " + desc)
}
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 earlierCool! 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!