Monday, August 12, 2019

Get some REST with Db2

Db2 has a REST API
Many of the IBM Cloud services have a REST API, an overview of REST APIs is here in the IBM Cloud docs. Recently, I realized that I had not yet tried the Db2 on Cloud API and the API for Db2 Warehouse on Cloud. Thus, last Friday I took some time to test out some code using one of my Db2 instances on IBM Cloud. Here is what you need to get started....


After taking a quick look at the documentation for the REST API offered for Db2 on Cloud and Db2 Warehouse on Cloud you notice that both seem to be the same  - great, less work and simpler development. The API offers functionality to manage users and their authentications, work with schemas and database objects, load and export data, and - most importantly - to create SQL jobs and retrieve their results.

Authentication to all API calls is per bearer token. That token needs to be obtained by an initial POST request with a user name and password. If you are familiar with using curl or request libaries, everything is straight-forward.

Executing SQL statements

As mentioned, SQL processing is performed in two steps. First, you send one or multiple statements to Db2 for execution. This returns a job ID. Second, to retrieve results, Db2 is polled with the job ID to retrieve available results. This works well. I found it important (and recommend it for performance anyway) to specify "read only" access when querying the system catalog. This is shown in the following command. It makes use of my small test script available as Gist (which should be shown thereafter):
python3 db2creds.json "select schemaname,owner from syscat.schemata for read only"


The REST API for Db2 is easy to use and provides access to basic SQL querying as well as to administrative functionaliy. A big benefit of using the API is that you don't need to install any Db2 SDK or libraries when developing an app. If you want to learn more, check out my upcoming Db2 app development talks at the IDUG 2019 EMEA conference or German Db2 Aktuell conference.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.