March 3, 2021 By Ute Schuerfeld
Andreas Wagener
5 min read

How to set up ODBC connectivity for IBM Cloud SQL Query using the Progress DataDirect Hybrid Data Pipeline product.

IBM Cloud SQL Query is an IBM Cloud service that allows users to access and process data on IBM Cloud Object Storage using standard SQL. IBM Cloud SQL Query provides a Web UI, a REST API, SDKs for Python, Go, Node.js and a JDBC driver for access.

The JDBC driver enables BI Tools to integrate data on IBM Cloud Object Storage into business reports. But, there are a couple of BI tools out there that do not provide JDBC connectors — instead, they have ODBC connectors. In this blog post, we’re going to describe an easy way to set up ODBC connectivity for IBM Cloud SQL Query using the Progress DataDirect Hybrid Data Pipeline product.

Progress DataDirect Hybrid Data Pipeline

Progress DataDirect offers Hybrid Data Pipeline (HDP) as a connectivity solution that comes with several connectors, including a JDBC connector to which you can plug in a custom JDBC driver, such as the one provided by IBM Cloud SQL Query. Additionally, it provides an ODBC driver that applications with ODBC connectors can use to connect to a Hybrid Data Pipeline server. Hybrid Data Pipeline acts as a middleman to map ODBC requests to JDBC requests:

Setting up Progress DataDirect Hybrid Data Pipeline

The easiest way to set up ODBC connectivity to IBM Cloud SQL Query with Progress DataDirect Hybrid Data Pipeline is to use the Docker image provided by the free Hybrid Data Pipeline trial. It contains a Hybrid Data Pipeline server pre-installed on a Linux 64-bit system. A prerequisite for using it is that you have downloaded and installed Docker on your machine (Windows, Linux or Mac). Once you have done so, download the Hybrid Data Pipeline Docker image from the Progress DataDirect download page:

To download the Hybrid Data Pipeline Docker image, load it into your Docker repository and start the Hybrid Data Pipeline server, see the instructions in the Progress DataDirect tutorial.

Setting up JDBC connectivity from HDP to IBM Cloud SQL Query 

The next step is to plug the custom JDBC driver provided by IBM Cloud SQL Query into the Hybrid Data Pipeline and  create a JDBC data source for connectivity for your IBM Cloud SQL Query instance. Download the latest JDBC driver ibmcloudsql-jdbc-version.jar.

To make the driver available to the Hybrid Data Pipeline server, copy the driver into the Docker container using the following command:

docker cp ibmcloudsql-jdbc-.jar:/opt/Progress/DataDirect/Hybrid_Data_Pipeline/Hybrid_Server/ddcloud/keystore/drivers

Next, log in to the Docker container, stop and start the server and exit the container again, as follows:

docker exec -it <containerID> /bin/bash
cd /opt/Progress/DataDirect/Hybrid_Data_Pipeline/Hybrid_Server/ddcloud
./stop.sh
./start.sh
exit

You are now all set to create a JDBC data source. In order to do so, start a web browser and connect to http://<hostname>:8080/hdpui/. You should see the following log-on page:

Log on as user d2cadmin using the password you set up when starting the Docker container:

Next, you will see the following page:

Navigate on the left sidebar to Manage Data Sources:

Create a JDBC data source to connect to your IBM Cloud SQL Query instance. On the top of the page, click the New Data Source button. This brings up the following page where you can select the data store to which you want to connect. As you want to connect to an IBM Cloud SQL Query instance using the custom JDBC driver, select JDBC:

This opens up a page to collect the information needed to connect to your IBM Cloud SQL Query instance: 

Fill in the following information:

  • Data Source Name: A name of your choice.
  • Driver Class: com.ibm.cloud.sql.jdbc.Driver
  • User ID: Either the word apikey or blank.
  • Password: Your IBM Cloud apikey.
  • Connection URL: The connection URL for the JDBC Connection to IBM Cloud SQL Query has the general form  jdbc:ibmcloudsql:{instance-crn}[?{key1}={value1}&{key2}={value2}…].

The instance-crn is the Cloud Resource Name of your IBM Cloud SQL Query instance and can be obtained from the service dashboard in the IBM Cloud console. The following is an example URL: 

jdbc:ibmcloudsql:crn:v1:bluemix:public:sql-query:eu-de:a/290ec9931c0737248f3dc2aa57187d14%3AAf86d20c4-7646-441e-ad4b-182c57008471::?targetcosurl=cos://us-geo/sample-result-bucket/hdpdatasets/&password=*******

Connection properties can be specified as part of the URL, as key=value pairs separated by an ampersand (&). Specify the following properties:

  • Targetcosurl: Cloud Object Storage URL in SQL query style, where the results should be stored. Ensure that the specified Cloud Object Storage bucket exists.
  • Password: Your IBM Cloud API key.

Click on Test in the bottom right corner to validate that you can connect to your IBM Cloud SQL Service instance. You should see the message “connection established successfully.”

Setting up ODBC connectivity to IBM Cloud SQL Query through HDP 

When you run the Hybrid Data Pipeline Docker image, four configuration and certificate files are generated. These files are located in the following Hybrid Data Pipeline installation directory:

/opt/Progress/DataDirect/Hybrid_Data_Pipeline/Hybrid_Server/redist

Before installing a component, such as the ODBC driver, these files must be copied to the installer directory of the component you are installing (the ODBC client installer). Create the temporary installer directory with the following command:

/<path-to-odbc-client-installer>.

Copy the files using the following command:

docker cp <container_name>:/opt/Progress/DataDirect/Hybrid_Data_Pipeline/Hybrid_Server/redist /<path-to-odbc-client-installer>

The four configuration and certificate files are as follows:

  • config.properties
  • OnPremise.properties
  • ddcloud.pem
  • ddcloudTrustStore.jks

Next, download the ODBC driver installer (your trial version) from the Progress DataDirect Hybrid Data Pipeline ODBC Driver download page.

Download the ODBC driver installer appropriate for your platform into the <path-to-odbc-client-installer> folder and install it. The creation of an ODBC data source is described by a sample for the Windows version. Go to the Windows start menu and look for the Progress DataDirect Hybrid Data Pipeline folder. Open the folder and select ODBC Administrator. This will open up the following tool to configure an ODBC Data Source:

Click on the Add button. The following selection menu opens:

Select the ODBC Driver DataDirect HDP 4.6 and click Finish. This will bring up the configuration menu:

Provide the following information:

  • Data Source Name: Name of your choice for the ODBC Data Source.
  • Hybrid Data Pipeline Source: Name of the JDBC Data Source you created with the Progress HDP Administrator to connect to the IBM Cloud SQL Query service.
  • Service: IP address or the hostname of the host.
  • Port Number: 8080 for the port on which the Hybrid Data Pipeline server listens for requests.

Click on Test Connect to validate the configuration. You should see the message “Connection Established.”

Now you are ready to use the ODBC data source to connect from an application to your IBM Cloud SQL Query instance. 

Learn more

Learn more about IBM Cloud SQL Query  and Progress DataDirect Hybrid Data Pipeline.

Was this article helpful?
YesNo

More from Cloud

Enhance your data security posture with a no-code approach to application-level encryption

4 min read - Data is the lifeblood of every organization. As your organization’s data footprint expands across the clouds and between your own business lines to drive value, it is essential to secure data at all stages of the cloud adoption and throughout the data lifecycle. While there are different mechanisms available to encrypt data throughout its lifecycle (in transit, at rest and in use), application-level encryption (ALE) provides an additional layer of protection by encrypting data at its source. ALE can enhance…

Attention new clients: exciting financial incentives for VMware Cloud Foundation on IBM Cloud

4 min read - New client specials: Get up to 50% off when you commit to a 1- or 3-year term contract on new VCF-as-a-Service offerings, plus an additional value of up to USD 200K in credits through 30 June 2025 when you migrate your VMware workloads to IBM Cloud®.1 Low starting prices: On-demand VCF-as-a-Service deployments begin under USD 200 per month.2 The IBM Cloud benefit: See the potential for a 201%3 return on investment (ROI) over 3 years with reduced downtime, cost and…

The history of the central processing unit (CPU)

10 min read - The central processing unit (CPU) is the computer’s brain. It handles the assignment and processing of tasks, in addition to functions that make a computer run. There’s no way to overstate the importance of the CPU to computing. Virtually all computer systems contain, at the least, some type of basic CPU. Regardless of whether they’re used in personal computers (PCs), laptops, tablets, smartphones or even in supercomputers whose output is so strong it must be measured in floating-point operations per…

IBM Newsletters

Get our newsletters and topic updates that deliver the latest thought leadership and insights on emerging trends.
Subscribe now More newsletters