Query Db2 with REST API
Dean Compher
27 April, 2018
Updated 11 May 2018
With a free download and easy install, you can add a REST API interface to your Db2 (formerly DB2 /LUW) databases. Being able to query Db2 using a REST API will delight many of your web and mobile developers because this is their preferred way of interacting with things outside of their application. It will also allow users to query the Db2 database without installing any clients or drivers. Another useful thing is that it allows you to easily get query output into JSON format. Most development environments provide a way of calling REST APIs or you can also just use a browser to query your database or copy cURL to your machine and do command line REST API calls as well. I’ll show examples of these in this article.
The IBM Data Server Gateway for OData is the freely available software that you install to allow this RESTful access to Db2 (formerly known as DB2/LUW). This links describes it, provides a link to download it and gives instructions for installation and configuration. It provides a web server, application and driver to connect to your Db2 databases. It can be installed on your database server or another server. An OData server can be configured to talk to several Db2 Database servers. It takes requests in the standard OData REST API format and translates those into Db2 queries and then responds with the query results in JSON format. Since this product makes client calls to Db2, you could also use it as a front end to Db2 Warehouse and Warehouse on Cloud, but those already have a built-in REST interface as described in Query Cloud Db2 Using REST APIs. You can get even more details on George Baklarz’s A RESTful Approach for Working with Db2 blog post.
OData is an open standard for making RESTful calls to databases – not just Db2. Here is the official statement from the OData web site. http://www.odata.org/
“OData (Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs. OData helps you focus on your business logic while building RESTful APIs without having to worry about the various approaches to define request and response headers, status codes, HTTP methods, URL conventions, media types, payload formats, query options, etc. OData also provides guidance for tracking changes, defining functions/actions for reusable procedures, and sending asynchronous/batch requests.”
Once you install the gateway, you will need to configure it. Specific instructions can be found on the IBM Data Server Gateway for OData page. The gateway establishes JDBC connections to the database, so you will need all of the normal connection parameters to your Db2 database including host/IP, database name, port, Db2 instance owner id and a Db2 database user’s user id plus associated passwords. As part of the initial configuration you will create at least one “DB2 Resource”. A DB2 Resource is an object on the OData server that associates a specific URL to a Db2 Database user and a set of tables and views on the database. Generally, you will not want to use the instance owner id for both the administrative connection and the user’s DB2 Resource id. This way when an application or user queries the database they are connecting with just the authority they need and not the administrator id. There must also be at least one table in the database with a schema name that is the same as this Db2 User and the user must have at least select privileges on it at the time you are creating the DB2 Resource.
Figure 1. Example of OData Connections:
Figure 1 is just a simple example. The OData server and Db2 Servers could be on the same machine and the OData server could be connected to multiple Db2 Servers.
When the OData administrator creates a DB2 Resource, the OData server shows the URL that can then be used to query the database. This URL can then be given to the developers or others who will query the database through the RESTful API. This URL should be kept relatively private as it does provide access to your database, but only to the tables defined when the DB2 Resource is created. You can create as many DB2 Resources as needed.
The user who only has an OData URL that you created can only query the database through the URL and only access the tables defined in the DB2 Resource. Once someone has the URL, they do not need a database userid or password to query the tables in that resource group. Also it’s not a bad idea to only provide authorities necessary in the database to the user id used in DB2 Resource user id. You could create a different Db2 Role for each DB2 Resource.
You could create one big Db2 Resource with all of the tables and views in a schema or a subset of the tables and views in a schema. A Db2 Resource can have only tables from one schema. For databases that support multiple applications you would likely want to create a Db2 Resource for each application with only that application’s tables. This also allows each different application to use a different URL to contact the gateway. To get around the limitation of only having tables from one schema in a DB2 Resource, an application could use multiple Db2 Resources or you could create aliases or views in one database schema that are built on tables from various other schemas and include the aliases or views in one resource group. You should also note that OData does not allow you to join tables, but if you need to do that you could create a view that joins the tables and use that in your DB2 Resource.
When you create a Db2 Resource group on the OData Server, it will give you a URL for the DB2 Resource that looks something like this:
http://172.16.0.4:9080/ODataOne/ODataService/DEVDB-a1f1f0475e51428eb93ebf550f4035e9/
You can check the tables in your DB2 Resource and the way OData views them any time by appending the $metadata parameter to the URL and connecting to in in a browser on your workstation. When I created the DB2 Resource URL above that I called DEVDB, I selected the EMPLOYEE related tables in the standard Db2 Sample database. Figure 2 shows an example of what I pasted into my browser an example of the output:
Figure 2. Resource group call and output
http://172.16.0.4:9080/ODataOne/ODataService/DEVDB-a1f1f0475e51428eb93ebf550f4035e9/$metadata
Once you have created one or more DB2 Resources on the OData server you are ready to start using REST API calls to query it. There are 5 RESTful calls available:
· GET (SELECT)
· POST (INSERT)
· PUT (UPDATE a full row)
· PATCH (UPDATE selected columns in a row)
· DELETE (DELETE a row)
You can read more about how to interact and develop with ODATA RESTful APIs on the odata.org website. I show a few examples below. As I noted earlier, this is generally useful to developers, but if you ever have a need to query your database from a machine that only has a browser or you can copy cURL to it, then you can query your database.
Here is a query that could be executed in the sample database:
SELECT FIRSTNME,LASTNAME,BONUS,WORKDEPT
FROM EMPLOYEES
WHERE SALARY > 10000
I was able to execute the above query by pasting the following URL into my Firefox browser:
http://172.17.0.4:9080/ODataOne/ODataService/DEVDB-a1f1f0475e51428eb93ebf550f4035e9/EMPLOYEES?$select=FIRSTNME,LASTNAME,BONUS,WORKDEPT&$filter=SALARY gt 10000
The output looked like this:
For some reason I wasn’t able to get the “select” or “filter” to work when doing a call through cURL (probably user error), but I was able to execute the following commands successfully:
curl -k -X GET "http://172.17.0.4:9080/ODataOne/ODataService/DEVDB-a1f1f0475e51428eb93ebf550f4035e9/EMPLOYEES('000010')"
curl -k -X GET "http://172.17.0.4:9080/ODataOne/ODataService/DEVDB-a1f1f0475e51428eb93ebf550f4035e9/EMPLOYEES"
Both of the examples above query the EMPLOYEES table and return all columns. The second one returns all rows, where the first one only returns the row for EMPNO=000010. This syntax can only be used for the primary key of the table. Notice that OData recognizes the key in Figure 2.
***
I think that the ability to call the database with these APIs will come in handy in a variety of situations. I am definitely interesting in hearing about how you use them, so please post your ideas on my Facebook Page or my db2Dean and Friends Community along with any other comments you may have.