Using Couchbase JDBC with Tibco BusinessWorks

Couchbase JDBC and Tibco ActiveMatrix Business Works 6.3

Summary

Establish rapid application workflows with Tibco ActiveMatrix Business Works by using Couchbase Server third-party drivers provided by SimbaTechnologies and CData.

Table of Contents

• Third Party Tools
• Query
• Data Integration
• Tibco Business Works
• Setup a Custom Driver

In recent years NoSQL has had a steep increase in use within many areas of industry and most notably within the enterprise. Many enterprise customers develop tools, use tools to develop application. They face a build or buy decisions throughout the lifecycle of an application. Because NoSQL is very developer friendly it is often at the forefront of data centric application development. This is especially true in the digital economy. NoSQL databases and document stores are used for development and often require integration with existing tools.

This creates a challenge for developers, managers, and executives when planning to adopt NoSQL systems. Primarily due to extra overhead of the learning curve for end-users who use tools to introspect data which is now in a new format. Couchbase has a very powerful set of standards built features which allows the re-use of existing knowledge thereby reducing the learning curve and ultimately the barrier to adoption.

Third Party Tools

Couchbase has several partners and there are two in particular who provide data integration technologies for existing applications in the form of ODBC and JDBC drivers. This allows off-the-shelf software and existing applications to connect to popular NoSQL systems like Couchbase.

Query

Accessing data in any database often requires some level of querying. Also a common, hopefully native, query language to execute those queries. NoSQL systems traditionally have been used for what is known as a Key/Value access pattern. Possibly, indexed query through the use of map-reduce.

Later advents, such as search technology, allowed new queries against NoSQL systems and are often  proprietary though SQL-like language. Eventually tools were employed, like Hadoop and and Spark integrations, to scale MapReduce queries. These access methods have challenges though. Lack of ad-hoc query, poor performance, no automatic joins, and missing features which requires additional application code. These are all reasons why the need for a query language exists, even when proprietary, for NoSQL data.

One key feature of Couchbase Server is a complete ad-hoc query language called N1QL, pronounced Nickel. This language is a standards oriented purpose-built query language based on ANSI SQL92 standard. Because N1QL is standards based it allows customers to use Couchbase Server Query service and built-in query workbench to perform ad-hoc queries with complex logic, joins, subqueries and much more with their now rich JSON document data stored within Couchbase.

Managing data may be a task … but it doesn’t have to be a difficult one.

SQL: Developed in 1970s to deal with first wave of data storage applications

NoSQL: Developed in 2000s to deal with limitations of Relational Databases, particularly concerning scale, replication, developer agility, and unstructured data storage

Data Integration

To handle the challenges NoSQL might present to developers Couchbase has many partners. One set of partners, in particular, enables end-users to easily access NoSQL data with tools such as Excel, Tibco, Informatica, Tableau, or anything that can use ODBC or JDBC.

Simba Technologies and CData.com provide ODBC and JDBC drivers for Couchbase Server query service to allow applications to use standard SQL queries with Couchbase server. Using these drivers you can enable popular applications like Tibco BusinessWorks (BW) to use data stored within Couchbase natively.

Tibco Business Works

This document was written using Tibco BusinessWorks 6.3 on Mac OS X El Capitan but the configuration described will work on Windows systems as well.

The steps used to configure the environment are:

1. Install Tibco ActiveMatrix BW 6.3 with the java installer (Windows, Linux or Mac)
2. Complete any necessary BW 6.3 configuration steps
3. Download a Couchbase Server JDBC driver
   • http://www.simba.com/drivers/couchbase-odbc-jdbc/
   • http://www.cdata.com/drivers/couchbase/jdbc/
4. Install the JDBC driver with administrative privileges
5. Record the installation directory for later use
6. Configure a Tibco Data source as Custom/JDBC

The following section will discuss how to setup a “Custom JDBC” connection in Tibco BusinessWorks with Couchbase JDBC Drivers.

Setup a Custom Driver

Start TIbco BW 6.3 and then right click under the project explorer and choose “New->Application Module” and step through the interfaces until a default package is listed, like below:

To add a custom JDBC, right-click the Resources object and choose “JDBC Connection” to open the JDBC configuration screen and change the Resource Name as desired.

On the following screen you will need to configure the following options:

Once configured the explorer menu should look like to this:

As shown in the screenshot above there is a process resource listed as “process.bwp” and this is used to define the application workflow. To test queries, double-click on the process object and then drag-n-drop a JDBC->JDBCQuery object from the menu on the right:

Click on the JDBCQuery object and enter a query to test. For example

This will yield a result similar to the following table.

Enter the query in the “Statement” section as shown in the dialog like the diagram below:

Upon executing the query results appear in the “SQL Results” tab:

You can test the same way with the other Couchbase JDBC drivers as well. For more information please see the partner web sites for additional documentation about the capabilities of each driver and compatibility with your application.