Configuring the Db2 Big SQL interpreter for Apache Zeppelin

Apache Zeppelin is a one-stop notebook, designed by the Apache open source community, that is available in Hortonworks Data Platform (HDP).

This web-based notebook can help you with:
  • Data ingestion
  • Data discovery
  • Data analytics
  • Data visualization and collaboration

What sets Zeppelin apart from other similar tools are its interpreters. Interpreters allow code written in any language to be plugged into Zeppelin, with support for Apache Spark, R, Hive, Shell, Cassandra, and more.

This topic describes how to use Apache Zeppelin with Db2 Big SQL's JDBC driver. You can follow the same process using any other JDBC driver.

Before you begin

As a prerequisite, ensure that you have installed Zeppelin notebook and IBM Db2 Big SQL. Confirm that the status for both services is green in the Ambari dashboard.

Configuring the Db2 Big SQL JDBC interpreter in Zeppelin

  1. From the Ambari dashboard, click on Zeppelin Notebook. From the Quick Links drop-down menu select Zeppelin UI to open a notebook in a new browser tab.
    screen capture of Ambari user interface
  2. In your browser, navigate to http://<Zeppelin host>:9995/. If you have connected successfully you should see the welcome screen below.
    screen capture of Ambari user interface
  3. In the navigation bar, click on your username and select the option Interpreter. A new page will open showing all of the existing interpreters that have been configured.
  4. Click the Create button.
    screen capture of Ambari user interface
  5. Choose a name for your interpreter, and for interpreter group choose JDBC. A new form will appear with default values already specified. For driver-specific details, the required fields are:
    Default.url: jdbc:db2://<bigsql_head_host>:<bigsql_port>/bigsql 
Default.driver: com.ibm.db2.jcc.DB2Driver 
default.user: bigsql
default.password:<password>
    Note: If using SSL, edit the JDBC URL accordingly to add SSL parameters. For example: 
    sslConnection=true;sslTrustStoreLocation=/path/to/truststore.jks;Password=<password>
    You can remove all the other properties by clicking the x button under Actions.
  6. Add the following Dependency:
    Dependencies:artifact: /usr/ibmpacks/bigsql/<current>/db2/java/db2jcc.jar
    Note: The library path should exist on the node where the Zeppelin server is running.
  7. Save these settings and your interpreter will be created. The newly configured interpreter appears in Ambari as shown:
    screen capture of Ambari user interface
You are now ready to query Db2 Big SQL using Zeppelin. You can also update the Zeppelin interpreter via REST API. For details see Apache Zeppelin Interpreter REST API.

For an example of using a Zeppelin notebook with Db2 Big SQL see Visualizing data in a Zeppelin notebook.