Db2 REST Endpoint

Dean Compher

25 May 2021

 

Db2 has a new REST Endpoint that you can use to query and administer your database using a REST API.  This way developers and other who prefer to interact with your Db2/LUW databases using a REST API can do so.  You can either format new queries as part of dynamic API Calls or build RESTful services around individual SQL Statements that allow developers to use the queries without having to know SQL.  They can pass parameters to these services so that you can query different rows or update different values.  This also has some good security implications.  The endpoint is downloaded as a container image and can be deployed in Docker, Kubernetes or RedHat Openshift.  You can deploy one endpoint for each database or have one endpoint that queries multiple databases.  In this article I provide some guidance on deploying and using the end points.   

 

Figures 1 and 2 show two different possible deployments:

 

Figure 1.  One Endpoint and Multiple Databases

 

Diagram

Description automatically generated

 

 

Figure 2. Endpoint for each Database server

 

Graphical user interface

Description automatically generated

 

The Db2 database server can be any flavor of Db2 including your on-premises databases, Db2 Warehouse on Cloud, Db2 on Cloud and the various deployments of DB2 in Cloud Pak for Data.  It can also connect to Data Virtualization, but that is not officially supported as of the writing of this article.

 

Download and Configuration of the Service

 

So that I could experiment with this endpoint I downloaded the container image and deployed it under Docker on my Mac where I also have Db2 11.5 running in a container.  I connected it to the local Db2 databases as well as Db2 servers in other places.  I found the instructions for downloading and deploying the container image in the Knowledge Center easy to use.  However, for simplicity, I didn’t use the example Docker Run command shown in this page because I wanted to call the endpoint with HTTP instead of HTTPS, so I used the example provided on the  Running the IBM Db2 REST server without HTTPS page.  Here is the command that I ran:

 

docker run -it --hostname Deans-MacBook-Pro-3.local -p 50050:50050 -e LICENSE=view -e DB2REST_USE_HTTP=true --name=db2rest icr.io/obs/hdm/db2rest:latest-amd64

 

The next step is to configure the service to use any database that you want to query.  When you run these commands you need to run them using a database user and password with a fairly high database authority as the configuration creates a schema, a table and two stored procedures in each database you configure the service to use.  This process only needs to be done once for each database.  The credentials used for this configuration step are NOT used querying the databases through the end point. Here are the commands that I ran to do the configuration:

 

·       docker exec db2rest /opt/ibm/dbrest/scripts/db2rest-stop.sh

·       docker exec db2rest /opt/ibm/dbrest/scripts/db2rest-setup.sh 192.168.0.11 sample 50000 N db2rest db2inst1 db2inst1Password

·       docker exec db2rest /opt/ibm/dbrest/scripts/db2rest-start.sh

 

The “db2-rest-setup.sh” command configured my database called sample whose instance, db2inst1, listens on port 50000.  Running this command created a schema called db2rest in the sample database and created a table and two stored procedures under it.  The parameter N indicated that the connection from the service to the database is not SSL.  I ran these commands for each of the three databases I used in my experiments, with different parameter values. 

 

Authenticating and running Queries

 

The REST Endpoints Knowledge Center page has documentation on authenticating, running queries, creating REST SQL services and other good information.  I looked that over, but mostly tried the examples that Peter Kohlmann provides in his Jupyter notebook on GitHub.  I will now highlight a few portions of the notebook. 

 

First, to run a query, you need to get a token from the service, giving it the database connection information and the database user id and password.  This user id only needs authority to run the queries on the tables that use the token you generate.  You can use the token as many times in queries as you like until it expires.  This way you can generate a token and give it to others, allowing them to query the database without knowing the database user id and password.  See the cell labeled Authentication in Peter’s GitHub notebook to view the information needed.  The cells under the API Service heading actually generate the token.  Here is an example of using cURL to do the same thing, except here I’m using the credentials for my local database.

 

curl --header "Content-Type: application/json" -d '{"dbParms":{"dbHost": "192.168.0.11","dbName": "SAMPLE","dbPort": 50000,"isSSLConnection":false,"username": "db2dean","password": "db2deanPassword"},"expiryTime": "10m"}' http://192.168.0.11:50050/v1/auth

 

In this cURL example the IP address for my database container and endpoint container is the same since they are both running on my workstation, but they will be different if you have your endpoint installed on a separate system. 

 

Once you generate the token you can now query the database.  Peter provides examples of doing this in the sections of the notebook starting with the Reusing the token in the standard header section.  Here is a shell script I tested that both generates the token an executes a query using that token:

 

token=`curl --header "Content-Type: application/json" \

             -d '{"dbParms":{"dbHost": "192.168.0.11","dbName": "SAMPLE","dbPort": 50000,\

                  "isSSLConnection":false,"username": "db2dean","password": "db2deanPassword"},"expiryTime": "1m"}' \

             http://192.168.0.11:50050/v1/auth`

 

token2=`echo $token | awk 'BEGIN {FS = "\""} ; {print $4}'`

echo $token2

 

curl --header "authorization: $token2" --header 'Content-Type: application/json' \

      -d '{"isQuery": true,"sqlStatement": "SELECT * FROM GOSALES.BRANCH","sync": true}' \

     http://192.168.0.11:50050/v1/services/execsql

 

Please feel free to copy and paste these commands, but since I wrote this article in Word, the forward quotes and backwards quotes may get changed.  Another thing to note is that the equivalent commands in the notebook use the Boolean values True and False with the first letters capitalized.  However, that gives an error when using them in cURL where you must use all lower case.

 

Create and use a Unique RESTful Service

 

One of the features of this endpoint is that it allows you to create and store a parameterized query and then call it with different values for the parameters.  In organizations where many developers aren’t that great at SQL, this allows for a single person to write all of the SQL and allows many others to use it with different input parameters.  In the notebook, Peter shows how to create the service in the Create a Unique RESTful Service section and then shows how to call it in the next section called Call the new RESTful Service.  The subsequent sections show useful things you can do with these services.

 

Other Useful Information

 

Other Db2 implementations including Db2 Warehouse, Db2 Warehouse in Cloud Pak for Data, Db2 on Cloud and Db2 Warehouse on Cloud have their own endpoints that can optionally be deployed and used.  Further Db2 on z/OS has an endpoint.  As of the time that I am writing this article these endpoints can only be used with the associated database instance, but if you deploy the Db2 Endpoint I discuss above, it can be used to provide an endpoint for any of them, but can’t be installed in the container with those database implementations.  Here are the links to those other endpoints. 

 

·      DB2 WAREHOUSE on Cloud Pak for Data

·      DB2 Warehouse

·      Db2 on Cloud API overviewDb2 on Cloud API details

·      Db2 Warehouse on Cloud

 

There is also an open source REST endpoint service you can use.  You can see my earlier article about OData for more information about that. 

 

***

 

In this article I wanted to introduce you to the new Db2 REST endpoint for querying the database.  This technology is being developed rapidly, so if notice an interesting update, please post it to the db2Dean Facebook Page and share your thoughts about them.

HOME | Search