db2Dean a.k.a. Dean Compher a.k.a “The Dean of DB2” is here to answer your DB2 Questions !
|
Utah Events None currently Scheduled
Nevada Events None currently Scheduled
|
See my Github Repo:
|
|
|
Online Events
Other Events
|
Welcome to db2Dean’s web site. I’m Dean Compher an IBM IT Specialist who, along with my team, helps customers and prospective customers with Cloud Pak for Data and DB2 on Linux, UNIX and Windows (LUW) technical questions and issues. As this page makes painfully clear, I am a DBA and not a web designer, but I would be happy to get your DB2 questions answered or talk to you about the great features of DB2 or IBM Integration Products. If you are looking at a new database solution or want to compare us to your existing database vendor, please do not hesitate to contact me about getting a presentation or just to ask questions. My e-mail address is dean@db2Dean.com
I am located in Utah and primarily serve CPD and DB2/LUW customers in the Western US, but I can forward requests to my peers in other technology and geographic areas as well. My team also covers Big Data and Informix and products. There are questions that I get on a regular basis, and I will write articles relating to them here. I hope that you find them useful. I also welcome suggestions for future content. Click here for more information about me.
|
|
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
Figure 2. Endpoint for each Database server
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 on Cloud API overview, Db2 on Cloud API details
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.
|
Db2Dean’s Other Interests
|
