Connect to DB2 from ClicData via OData

ClicData is a cloud based Business dashboard creator. It includes data manipulation tools and automatic data refresh features that enable the dashboards to update automatically once you create them. With regard to data import, ClicData offers several out of the box connectors, and we are excited to see one of these connectors is for OData, a standards based REST API.

To help users get started with bringing in data into ClicData via OData, we have put together a tutorial which will walk you through on how to import your data from DB2 into ClicData using the OData API that is generated using Progress DataDirect Cloud for your DB2 data source, all  in just couple of clicks.

1. Register for Progress DataDirect Cloud here for a 15-day trial account.
2. Login to DataDirect Cloud using your credentials that you just used to sign up.
3. On the Landing Page, you should see two options. One of them will be Progress DataDirect Cloud and it will have a button "Connect". Click on the "Connect" button to open the DataDirect Cloud dashboard.
4. On the dashboard, click on the Data Sources tab that is present on left side as shown below.

    

   

    

5. Click on the New Data Source button. You should now see the list of Data Stores as shown below.

    

   

    

6. Click on the data store that you want to use, in this case, choose DB2 as your data store. You should now see a new view where you can configure connection to your data source like Data source name (Unique name for the connection), hostname, port, username, password etc., as shown below.

    

   

    

7. If you are connecting to a publicly accessible DB2 instance, choose “Cloud”.
8. If you are trying to access a Db2 instance that is on premises behind a firewall, choose On-premise and follow the below steps before proceeding further to configure Progress On-Premise connector.
   • On the left side of the DataDirect Cloud console, under quick links you will find a Downloads link. Click on that to reveal the downloads page.
   • On the Downloads Page you will find the On-Premise Connector Installer for Windows. Download the relevant architectural version of installer according to your OS.
   • Run the installer to start installing the On-Premise Connector. Follow the instructions on the installer and proceed ahead as most of the installation is pretty straight forward.
   • While installing the On-premise Connector, you will come across the form where it asks for your DataDirect username, password and a connector label that you would like to have. Provide your DataDirect Cloud credentials and provide any name for connector label as you wish. Below is screenshot for the form that I am talking about for your reference.

    

   

    

   • Once the installation completes, exit the installer and in a few seconds you should see a configuration tool that opens and automatically tests for the connection to DataDirect Cloud. You should see that On-premise connector successfully connects with all the DataDirect Cloud services as shown below.

    

   

    

9. Fill in your DB2 connection information. If connecting to an On-Premises DB2 database, you will see a Connector ID value having the same name that was specified when installing the On-Premise connector behind the firewall in the previous steps. Choose that connector as a part of configuring the connection.
10. Click on ‘Test Connection’ button and it should return a message saying Connection was successfully as shown in the below screenshot.

     

    

     

11. Now go to OData tab that you see on the same page to enable OData access to entities that you would like to use. Click on the OData tab you should see a view as shown in below screenshot.

12. Click on the Configure Schema button, which reveals a page where you would have to select the schema of the database that you would like to configure. On selecting the Schema, you will be redirected to page where you will see all the tables that you have in your schema as shown below.
13. Select all the tables that you would like to access through OData. Note that you would have to choose a primary key if the table doesn’t have any on the above dashboard. After you have chosen the tables click on Save and Close which will update the settings and you should be back on the OData page as shown below.

     

    

     

14. Copy the OData Access URI and Save the data source.
15. To test the API, Paste the OData Access URI for your data source and paste it in your browser. When you browse that URL, it will prompt for your DataDirect Cloud credentials and when you provide them and proceed ahead, you should see the list of tables returned by OData API.

1. Login into ClicData and click on Data tab. You should see a dropdown button ‘Add New’, click on that and choose ‘Add Connection’.
2. You should now see a list of data sources that ClicData supports. Scroll down and Choose OData as your data source as shown below.

    

   

    

3. That should open a new view as shown below. Fill in as following.
   • Protocol: HTTPS
   • Host: service.datadirectcloud.com
   • Port: 443

      

     

      

4. Click on Save Changes and you should be directed to Add Data form as shown below. Fill in as following
   • URL: api/odata/<Your Data Source name> (The name that you have defined in Progress DataDirect Cloud for your database)
   • Format: ATOM
   • Version: 2.0
   • Headers:
     • Authorization: “Basic ” + Base 64 encoding of DataDirect Cloud “<user>:<password>”

        

                                      

        

5. Click on ‘Test’ button to check if you are able to connect. If everything goes well, ‘Next’ button should be enabled. Click on ‘Next’.
6. On the next screen, select the table you would like to import and provide a name for this dataset under which this data will be stored in ClicData as shown below.

    

   

    

7. Click on Next, and wait for the sync operation to complete as shown below.

    

     

    

8. That’s it, you have just imported your DB2 data into ClicData and it is now available for your analytics purposes.

We hope you found this tutorial helpful. Note that you can use similar process to import your data from Eloqua, Oracle Service Cloud, Marketo, Sybase, Greenplum etc., by using DataDirect Cloud to generate OData API through a clicks-not-code approach.