January 26, 2016 | Written by: Alexander Pogue
Categorized: Data Analytics | How-tos
Share this post:
Streaming Analytics can now communicate with additional Bluemix database services such as SQL Database, dashDB, and many others. The new Streams JDBC Toolkit allows Streams apps to read from and write to any database that can be accessed using JDBC. This post shows how to integrate with SQL Database and dashDB using the streamsx.jdbc toolkit.
If using SQL Database:
- Create a SQL Database service in Bluemix
- Create a Bluemix Application using the Liberty for Java buildpack – follow the first four steps in Creating Cloud Foundry apps
- Bind your SQL Database service to your Bluemix application
- Find service credentials in the “Environment Variables” tab of your Bluemix application’s dashboard.
If using dashDB:
- Create a dashDB service in Bluemix
- Find service credentials in the “Service Credentials” tab of your dashDB service dashboard.
Configuring the sample Streams application
The JDBC toolkit comes with a sample Streams application called JDBCSample:
JDBCSample into Streams Studio from the toolkit’s sample directory
JDBCRunSample.spl file in Streams Studio.
This application creates a table, inserts a record, queries the record, writes the record to the console, and finally drops the table.
In order for Streams to talk to SQL Database, you will need a JDBC driver jar file:
- Download the DB2 driver and extract it
- Create an
opt directory inside the imported
- Move the extracted
db2jcc4.jar file to the new
Before running the JDBCSample application, enter the following information into the parameters of each JDBCRun operator:
||the jdbc driver library (use
opt/db2jcc4.jar for this sample)
||the class name for jdbc driver (use
com.ibm.db2.jcc.DB2Driver for this sample)
||the database URL. (
jdbcurl from the service credentials)
||the database user. (
username from the service credentials)
||jdbcUser’s password. (
password from the service credentials)
jdbcClassName come from the driver you added, and the values given in the table are correct.
jdbcUrl correspond with the
jdbcurl fields in the service credentials retrieved in the last steps of the Setup section above.
Running the sample Streams application
After entering the values to each JDBCRun operator, run the Streams app and view the result:
printer operator should print the retrieved database record to its PE console:
1,Mike ,Ward ,31,M,33.3,912.3
We have now shown that we can successfully store and retrieve data using a Bluemix database service.
To show the database growing, remove the
drop JDBCRun operator. Now each time you run JDBCSample an extra row will be added to the database and printed by
View the database contents from the Bluemix service dashboard:
- Run JDBCSample a few times after removing the `drop` operator
- Navigate to the SQL Database or dashDB service dashboard
- Click “Launch” to go to the service console
- Click “Work with Tables” for SQL Database or “Tables” for dashDB
- Select the `JDBCRUN_SAMPLE` table and select the “Browse Data” tab.
SQL Database table view
dashDB table view
Deploying the sample application to work with Streaming Analytics
JDBCSample and its use of the JDBCRun operator will work unmodified on the Streaming Analytics Bluemix service.
- Create a Streaming Analytics service in Bluemix – see “Finding the service” in Introduction to Bluemix Streaming Analytics
- Press “Launch” from the Bluemix service dashboard to launch the Streams console
- Press the “play” icon to submit a job
- Browse for the .sab file in your workspace’s
/JDBCSample/output/com.ibm.streamsx.jdbc.sample.jdbcrun.JDBCRunSample directory and click Submit
- The Streams app will start. If the operators in the “Streams Graph” panel show green circles, the Streams app is running successfully
- View the logs in the log viewer as shown below
This article has shown how to use Bluemix database services from the Streaming Analytics service. You can use parts of JDBCSample in your own projects to easily integrate with Bluemix databases.