Query Cloud Db2 Using REST APIs
Dean Compher
22 March, 2018
REST API calls are quite popular with many developers and you can query and administer all flavors of Db2 with them. There are a variety of reasons you may want to do this. The first one that comes to mind is developers’ preference for using them over other APIs like SQL. Another reason for doing this is that it allows you to query and administer your databases from servers or other devices where you cannot- or do not wish- to install the Db2 Client. With the cURL command you can even run REST commands from the command line and imbed cURL rest calls in shell scripts if you don’t wish to write Java or other applications to call them.
While all of the variants of Db2 allow you to use REST APIs, different ones use different interfaces and a different syntax. The public and private cloud flavors of Db2 including Db2 Warehouse on Cloud, Db2 Warehouse, and the IBM Integrated Analytics System (IIAS) use one implementation of the REST API. Db2 (formerly DB2/LUW) uses a different implementation that you install separately, and DB2/zOS uses yet a different one, but it is installed by default. In this article I’ll discuss the “Db2 Warehouse” APIs that are also good for IIAS. I discuss the Db2 software API commands in a future article. Please see this page for more information on the Db2/zOS API Commands.
All of the examples I show in this article will use the cURL command to call DB2. It is a lightweight utility that is already installed on most Linux and Mac machines and can be easily downloaded if you do not already have it. There really isn’t an install process for cURL. You just unzip it and start using the command. I have additional information and useful links on cURL and REST APIs in my past articles including Put the Weather in your Databases and API Data in your Datbase.
Db2 Warehouse (private cloud), Db2 Warehouse on Cloud and Db2 on Cloud (public cloud), and the IBM Integrated Analytics System all provide REST APIs and have a lightweight web servers running that listen for- and process- REST API calls. Most of the commands for this API have to do with administering the database, but there are a few that also allow you to run SQL. The documentation for these is pretty basic but useful.
I’ll now discuss how to use the main document called IBM Db2 Warehouse on Cloud REST API. The main part of the document shows the categories of commands with the individual commands shown in each category and which REST activity it is used with such as GET, POST, etc. Categories include Monitoring where you can monitor activity, storage and connections, Users where you administer and monitor and list users, and Database Objects where you can administer and list tables, and SQL that allows you to run queries. There are several others and you can review them at your leisure.
The next category section is the “Models” section. This section describes input parameters and output records. Unfortunately, there is not a cross reference between the commands at the top and the Models at the bottom. I figured it out by trial and error, but it wasn’t too hard to do. I show examples later so you can query the database without having to go through this. One example of this is where the /sqlJobs and /sql_quiery_export commands both take as input various parameters including the query you want to execute. They do not explain the input parameters. However, in the Models section I found the SQLJob Create model that describes the “commands” parameter and the SQLStatement model that describes the “command” parameter. Fortunately, based on error output, I was able to determine which model is associated with which API command. I’ll show both in the examples below.
You can see more information about the API commands with error code information here.
Before you can run any other commands you have to get an authorization token that provides a key you use on all other calls. You need an administrator userid and password to get this token. This token expires in less than a couple of hours, so if you write any scripts you will need to call this first and use the output in subsequent steps. In this example my Db2WoC user id is dash000555 and my password is 16yoPinso.
Example 1. Get the token for subsequent commands
curl -k -X POST 'https://awh-yp-small03.services.dal.bluemix.net/dashdb-api/v2/auth' -d '{"userid":"dash000555", "password":"16yoPinso"}'
Executing this scrip gives me this output.
{
"userid": "dash000555",
"token": "eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJVc2VyIjoiZGFzaDEwMDIwMCIsIlR5cGUiOiJibHV1c2VycyIsIk9yaWdpbiI6ImF3aC15cC1zbWFsbDAzLnNlcnZpY2VzLmRhbC5ibHVlbWl4Lm5ldDo0NDMiLCJleHAiOjE1MTg2NTA4MTIsImp0aSI6IjczMzdjZTQwLTVmODQtNGQ2NS05Njg4LWZhZWNjOTNhNDhmMCJ9.kGzUqAWXGDdkXkbTYTgsRwQdHVf84wKMzuJh-UN0qC8MfbLsgb5PRue9-jDOj0T2eRlWBS4T7W6vcFFBSdDsKUeXGfvQQpR5b_CSBj9RkEpgudt41go13BU53hRdrFYAnJjzHGzXMPJeB0tmHygB1x05DpMwKqGIZ5EH34rb6S4"
}
As you can see the token was quite long so to keep the following examples to a reasonable length I’ll shorten the token. In all following examples that use the token, I pretend that I really got the following output:
{
"userid": "dash000555",
"token": "db2DeanIsGreat25"
}
Example 2. Query the database and return delimited data.
In this example I want to execute the following query:
select city, prov_state from gosales.branch
where prov_state = ‘California’ order by prov_state
There are a few things to note
- “Bearer” is a key word and is placed after the Authorization parameter. It does not need to be changed.
- Make sure to the token after Bearer is the token from the auth command
- To pass a single quote to the Db2, you need to use the string '\'' as shown in the example below around the string “California”.
curl -k -X POST 'https://awh-yp-small03.services.dal.bluemix.net/dashdb-api/v2/sql_query_export' -H 'Authorization: Bearer db2DeanIsGreat25' -d'{"command": "select city, prov_state from gosales.branch where prov_state = '\''California'\'' order by prov_state"}'
An excerpt of the output looks like this
Los Angeles,California
Burbank,California
Anaheim,California
Example 3. Run Administrative Commands using the ADMIN_CMD stored procedure.
If you want to run administration commands that are not provided with the API then you may be able to run them using the ADMIN_CMD stored procedure since stored procedures can be executed from SQL. In this case I call the DESCRIBE TABLE command as follows. See the ADMIN_CMD page for the commands that can be executed by it. There are quite a few other built-in routines and views for administering and monitoring Db2. Note that this procedure requires that the input be quoted so I had to use the '\'' syntax again:
curl -k -X POST 'https://awh-yp-small03.services.dal.bluemix.net/dashdb-api/v2/sql_query_export' -H 'Authorization: Bearer db2DeanIsGreat25' -d'{"command": "call sysproc.admin_cmd ('\''DESCRIBE table gosales.branch'\'')"}'
Example 4. Create a new user.
As I noted earlier there is an extensive list of administrative API commands. Here is an example of the one that creates a new user:
curl -k -X POST 'https://awh-yp-small03.services.dal.bluemix.net/dashdb-api/dashdb-api/v2/users' -H 'Authorization: Bearer db2DeanIsGreat25' -d '{ "id": "testid1", "name": "Rick Grimes", "password": "I_love_michonne23", "role": "leader", "email": "walker543@gmail.com", "locked": "no" }'
Example 5. Run multiple SQL commands using the sql_jobs command
The sql_jobs command allows you to run multiple sql queries with one command. Its output contains an id field that you use in the sql_jobs/<id> as shown in this example.
curl -k -X POST 'https://awh-yp-small03.services.dal.bluemix.net/dashdb-api/v2/sql_jobs' -H 'Authorization: Bearer db2DeanIsGreat25' -d'{"commands": "select * from gosales.order_header fetch first 2 rows only; select * from gosales.order_details fetch first 2 rows only;","limit": 10, "separator": ";", "stop_on_error": "no"}'
The above command provides the output below that contains the id that is used to query the output of your SQL.
{
"id": "eec8ac0d-3cfe-4a0b-b712-31c8220bb627",
"commands_count": 2,
"limit": 10
}
Using the id in the sql_jobs query, you can see the output of the SQL as follows:
curl -k -X GET 'https://awh-yp-small03.services.dal.bluemix.net/dashdb-api/v2/sql_jobs/eec8ac0d-3cfe-4a0b-b712-31c8220bb627' -H 'Authorization: Bearer db2DeanIsGreat25'
For these queries I got the following output:
{"id":"eec8ac0d-3cfe-4a0b-b712-31c8220bb627","status":"completed","results":[{"command":"select * from gosales.order_header fetch first 2 rows only","columns":["ORDER_NUMBER","RETAILER_NAME","RETAILER_NAME_MB","RETAILER_SITE_CODE","RETAILER_CONTACT_CODE","SALES_STAFF_CODE","SALES_BRANCH_CODE","ORDER_DATE","ORDER_CLOSE_DATE","ORDER_METHOD_CODE"],"rows":[["808308","Edge Mountainwear","Edge Mountainwear","20742","3761","10354","40","2011-12-13 00:00:00","2011-12-19 00:00:00","5"],["808309","Kurashiro Golf Ten","TBD","20591","3610","10358","40","2011-05-13 00:00:00","2011-05-18 00:00:00","5"]],"rows_count":2,"limit":10,"runtime_seconds":0.016500802000000002},{"command":" select * from gosales.order_details fetch first 2 rows only","columns":["ORDER_DETAIL_CODE","ORDER_NUMBER","SHIP_DATE","PRODUCT_NUMBER","PROMOTION_CODE","QUANTITY","UNIT_COST","UNIT_PRICE","UNIT_SALE_PRICE"],"rows":[["1000001","100001","2010-01-19 00:00:00","41110","0","256","15.62","35.09","33.69"],["1000002","100001","2010-02-17 00:00:00","69110","0","92","49.69","110","102.3"]],"rows_count":2,"limit":10,"runtime_seconds":0.020583179}]}
Example 6. Error output
When creating cURL commands there are three major types of errors you can make. You can make an error in the format of the curl command itself and curl will complain about that. The other two error types that are of most interest here are having an error in the SQL sent or a badly formatted call to the API. Here are two examples. The first is of an SQL error where the column MISSPELLING does not exist and the second is an error in the curl command:
{
"trace": "4b778e56",
"errors": [
{
"code": "database_error",
"message": "Error running command, SQL0206N \"MISSPELLING\" is not valid in the context where it is used. SQLSTATE=42703\n",
"target": {
"type": "",
"name": ""
}
}
]
}
In the following output was caused by me using the keyword “commands” instead of “command”. The sql-query_export API expects to get the parameter “command”.
{
"trace": "1b9ade2a",
"errors": [
{
"code": "invalid_parameters",
"message": "Parameter command is missing.",
"target": {
"type": "",
"name": ""
}
}
]
}
These are just a few examples of using the APIs to query the database. I think that they will be helpful in translating the documentation into actual API Calls.
***
I hope you find this information useful especially in cases where you need to query or administer your database, but don’t have a Db2 Client handy. If you have other interesting ways of using these commands, please share them my Facebook Page or my db2Dean and Friends Community along with any other comments you may have.