Yesterday evening, I had some time on my own and didn't want to read. Why not surf in the Internet? That's why there are services like
YouTube or
Bluemix where you can spend hours without too much thinking... :) I ended up fiddling with
dashDB on Bluemix and building reports with my local
OpenOffice. Here are the details of my evening activity.
 |
| Run query against dashDB in Excel |
When I played with dashDB, a fully managed data warehouse service, I came across the "Run Query in Excel" button. That button shows up in the web-based query interface where you can compose SQL queries and execute them against the dashDB database. I got curious because my Linux machine only has Apache OpenOffice installed. ODC (Office Data Connection) files are not supported (yet) by OpenOffice and LibreOffice, but the programs offer the
component "Base" as database (front-end) similar to Microsoft Access. So why not try to hook up the cloud-based dashDB and my local OpenOffice? This can be done using a JDBC connection.
The first step was to enable Java within OpenOffice. The switch can be found under "Tools->Options" in the OpenOffice configuration (see screenshot below). I chose my IBM Java.
 |
| Enable Java for use in OpenOffice/LibreOffice |
Next I added the classpath (Class Path) to my IBM JDBC driver files that are used to access DB2, Informix, and dashDB. A link to download the drivers is offered in the dashDB dashboard/administration console. Within the classpath menu is the option "Add Archive" which I chose to add the files db2jcc.jar, db2jcc4.jar and db2jcc_license_cu.jar to my OpenOffice environment.
 |
| DB2/dashDB JDBC driver archives |
Once I was done, I needed to restart OpenOffice to pick up the new configuration. Now it was time to configure the database connection to dashDB itself. For it I clicked on "File->New->Database" in the OpenOffice Menu (e.g., from within Calc). This opened up the OpenOffice Database Wizard as shown below. In order to access dashDB you have to pick "Connect to an existing database" and JDBC as the technology.
 |
| OpenOffice Database Wizard |
After pressing "Next" in the wizard the most important configuration dialog shows up. This is where the JDBC connection string, the user name, and options can be entered. The Datasource URL is the based connection string up to the database name. The information to be entered can be found in the dashDB web interface in the "Connection Settings" screen. As can be seen in the picture below, the JDBC driver class needs to be specified. For dashDB (and DB2) it is "com.ibm.db2.jcc.DB2Driver". Using the "Test Class" button I made sure that OpenOffice can load that class.
 |
| JDBC Properties for dashDB within OpenOffice |
Highlighted in the picture above is a part of the Datasource URL which reads "
cursorSensitivity=2;". While accessing some database tables I ran into sqlcode -243 (SQL00243N). By adding the JDBC property to the URL so-called "asensitive" cursors are used and OpenOffice works well with dashDB on Bluemix.
Once the described configuration is done, the data from dashDB can be used within OpenOffice or LibreOffice. It is possible to build reports in OpenOffice Base, view and use data sources in OpenOffice Calc, and more. The screenshot below shows parts of the sample data available in dashDB. It is the table GOSALES.COUNTRY which holds country names in various languages.
 |
| Sample data from dashDB as seen in OpenOffice Calc |
With the work of connecting OpenOffice and dashDB done, I can now dive deep into building dynamic database reports or use result set from SQL database queries for my spreadsheets. But that would be another blog post.
If you have questions or this was useful, please let me know.