Finally together: Db2 and Zeppelin

United: Db2 and Zeppelin

If you followed my blog, you may have noticed that I wrote about Db2 and about Zeppelins in the past - but not together. Today, I am going to discuss how I configured a JDBC interpreter in an Apache Zeppelin notebook to connect to a Db2 on Cloud database. So, finally, within a single blog post, I can talk about both of them. Let's get started.

Apache Zeppelin

Similar to Jupyter notebooks which I have used in the past, Apache Zeppelin is a web-based notebook for data science. It enables data-driven,
interactive data analytics and collaborative documents and supports SQL, Scala, Python and more. It comes with built-in JDBC support and allows to easily configure your database of choice.

JDBC setup for Db2

Following the guide, enabling access to Db2 is pretty straightforward. I started by creating a new JDBC interpreter. Then, I filled in few settings:

• default.URL: The JDBC URL for my Db2 on Cloud database, except the user and password information,
• default.user and default.password with my test user and password,
• default.driver as "com.ibm.db2.jcc.DB2Driver".

In the dependency section I configured "com.ibm.db2:jcc:11.5.8.0" as Maven artifact dependency. After saving the new interpreter as "Db2", I was done and ready for some action.

Visualize Db2 data in a Zeppelin notebook 

As you can see in the screenshot below, I started with a Markdown cell to provide a description. Then, I used "%Db2" to use have a notebook cell with an SQL statement executed in my Db2 on Cloud database. The statement is a simple "group by" to retrieve the tables by schema.

After the query finished, the notebook shows the result table (left icon below the statement). Clicking on the icon for bar chart generated the visualization as shown below.

Zeppelin notebook with Db2 data visualized as bar chart

For more complex queries and results, you can adapt the visualization via the settings menu. It allows to configure the columns for keys, groups and values which I tried with some data from statistics I collect. The labels are not shown for privacy reasons.

Statistics as line chart

Conclusions

Access to Db2 from Zeppelin notebooks is easy to set up. The built-in visualization allows you to quickly get results to show off. And working on it provided some Friday fun.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.