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
Scroll to view full table

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
Scroll to view full table

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=*******
Scroll to view full table

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
Scroll to view full table

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>.
Scroll to view full table

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>
Scroll to view full table

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.

More from Cloud

Clients can strengthen defenses for their data with IBM Storage Defender, now generally available

2 min read - We are excited to inform our clients and partners that IBM Storage Defender, part of our IBM Storage for Data Resilience portfolio, is now generally available. Enterprise clients worldwide continue to grapple with a threat landscape that is constantly evolving. Bad actors are moving faster than ever and are causing more lasting damage to data. According to an IBM report, cyberattacks like ransomware that used to take months to fully deploy can now take as little as four days. Cybercriminals…

2 min read

Integrating data center support: Lower costs and decrease downtime with your support strategy

3 min read - As organizations and their data centers embrace hybrid cloud deployments, they have a rapidly growing number of vendors and workloads in their IT environments. The proliferation of these vendors leads to numerous issues and challenges that overburden IT staff, impede clients’ core business innovations and development, and complicate the support and operation of these environments.  Couple that with the CIO’s priorities to improve IT environment availability, security and privacy posture, performance, and the TCO, and you now have a challenge…

3 min read

Using advanced scan settings in the IBM Cloud Security and Compliance Center

5 min read - Customers and users want the ability to schedule scans at the timing of their choice and receive alerts when issues arise, and we’re happy to make a few announcements in this area today: Scan frequency: Until recently, the IBM Cloud® Security and Compliance Center would scan resources every 24 hours, by default, on all of the attachments in an account. With this release, users can continue to run daily scans—which is the recommended option—but they also have the option for…

5 min read

Modernizing child support enforcement with IBM and AWS

7 min read - With 68% of child support enforcement (CSE) systems aging, most state agencies are currently modernizing them or preparing to modernize. More than 20% of families and children are supported by these systems, and with the current constituents of these systems becoming more consumer technology-centric, the use of antiquated technology systems is archaic and unsustainable. At this point, families expect state agencies to have a modern, efficient child support system. The following are some factors driving these states to pursue modernization:…

7 min read