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.
|
|
to Change DB Data 27 September 2022 Dean Compher
In this article I discuss examples of creating Db2 REST services with input parameters to insert, update and delete rows in a database. When creating the insert service, I realized that it was quite tedious to define a parameter for each column in even a moderately wide table, so I pass a single JSON document with the values for the columns. The built-in Db2 JSON_TABLE function makes it rather painless to place the column values into their correct place in the insert statement.
For this article I created two Jupyter Notebooks using Python to show in detail what I am discussing in the following examples. These notebooks and the “grants” script can be found in the Db2 REST Services to Change Db Data article directory on my github repo. Even if you don’t use Jupyter Notebooks or Python, you can click on these links and see the syntax for these services in your browser along with the output from various steps. The notebooks are:
· Db2 RESTful Endpoint Get Token Notebook – Creates a token used by other notebooks to authenticate to the REST Endpoint and return a token to the calling notebook. Depending on the user id used, the token will have different levels of access. This is the only place database user ids are needed.
· Db2 RESTful Endpoint Create DB Changes Services – Creates and executes services for inserting updating and deleting rows from a database. The insert service takes as input a single JSON document containing values for all columns in the database table.
One improvement I made in these example notebooks over the process described in the Db2 Rest End Point and Database Security article, is that the notebook that creates the services calls the notebook that creates the token for authentication to the REST endpoint, eliminating the need to copy and paste the tokens. I continue with the security practices in this article whereby I create each service with a higher level of authority and then test them with a lower level of authority.
The tables I use in these examples are the GSDB sample database tables that you can also download and implement if you want to try this yourself. The only additional permission I granted over what I did in the previous security article was to allow the SERVICE_ADMIN role to insert, update and delete in the GOSALES schema. You can see the entire file of grants in my github repo for this article. Here is the line I added:
GRANT INSERTIN, UPDATEIN, DELETEIN ON SCHEMA gosales TO ROLE SERVICE_ADMIN;
I think that the most interesting of the examples in this article is the use of a single JSON document to pass many values into the service. It would have been tiresome to create a parameter for each column in even a rather narrow table. For the insert I used the JSON_TABLE Db2 built-in function to extract the column values from the document. If you needed to pass a lot of values to the later update service this could have been done as well.
Using Python in my notebook I built the query and put it into a string variable I then used when creating the insert service. You can see all of the steps in creating and executing the service in the Db2 RESTful Endpoint Create DB Changes Services notebook in my github repo. For more information on the JSON_TABLE function see my Query JSON in Db2 article. Examples 1 & 2 below show two of the strings being used to build the body of the request to create the services.
Example 1. Snippet of SQL used to build the query variable part service body
insert into gosales.order_header (ORDER_NUMBER, RETAILER_NAME, … ORDER_CLOSE_DATE, ORDER_METHOD_CODE) select U."order_no", U."retailer_name", … U."order_close_dte", U."order_meth_code" FROM sysibm.sysdummy1 as E , JSON_TABLE(@ORDERHEADJSON,'strict $' COLUMNS ("order_no" INTEGER, "retailer_name" VARCHAR(150),… "order_close_dte" TIMESTAMPs, "order_meth_code" INTEGER)ERROR ON ERROR) AS U
Example 2. Create the body of the service to be called ins_order_header
body = {"isQuery": False, "parameters": [ { "datatype": "CLOB", "name": "@ORDERHEADJSON" } ], "schema": "REST_SERVICES", "serviceDescription": "Insert data from values in JSON doc into the ORDER_HEADER table", "serviceName": "ins_order_header", "sqlStatement": query, "version": "1.0" }
Here are a few points I would like to make about the above examples · Example 1 shows the string I built to use in the service. To get all of the quoting right, I needed multiple steps to build the “query” string used to build the body dictionary shown in Example 2. Click the notebook link above to see all the steps I used. · @ORDERHEADJSON is the input variable that will contain the JSON document sent to the service. In this case I’m defining it as a CLOB, but other data types could be used. Please note though that Db2 does not have a “dict” or “JSON” data type. · The value of the “isQuery” key needs to be False for insert, update and delete statements that don’t return query results. You will get an error if the value is wrong.
To execute the service in my notebook, I first built a Python dictionary to hold the JSON data, then converted it to a string data type and manipulated the quote types used in the string and finally built the body variable that will be used as input when calling the ins_order_header service. Note that the order of the keys in the dictionary does not matter, but the key names like “order_no” must exactly match the key names used when the service was created. Example 3 shows some of the code used to build a call to the update service.
Example 3. Build dictionary of values to be inserted and then use it to call the insert service.
# Create dict with values to be inserted raw_order_header= {'order_close_dte': '2004-02-17 00:00:00', 'order_dte': '2004-01-12 00:00:00', 'order_meth_code': 6, 'order_no': 834937, 'retailer_contact_cd': 3549, 'retailer_name': 'Kavanagh Sports', 'retailer_name_mb': 'Kavanagh Sports', 'retailer_site_cd': 20530, 'br_cd': 21, 'ss_code': 10798}
# Convert the dict to string and switch quote types sorder_header=str(raw_order_header) order_header= sorder_header.replace("'", '"')
# Finally build the body of the call to the service using the JSON order_header string. body = { "parameters": { "@ORDERHEADJSON": order_header }, "sync": True }
After executing the insert service, I show an example of creating and executing an update service in the Db2 RESTful Endpoint Create DB Changes Services notebook. Generally, you will need to give the service a key or keys to tell it which rows to update as well as the values of the columns you wish to change in those rows. Example 4 shows an example of building and executing a service to update rows in the order_header table.
Example 4. Create and execute an update service.
# Create the query for the service body query="update gosales.order_header \ set RETAILER_NAME = @RETAILERNAME \ ,RETAILER_NAME_MB = @RETAILERNAMEMB \ where ORDER_NUMBER = @ORDERNOKEY"
# Create the service body using query string body = {"isQuery": False, "parameters": [ { "datatype": "INTEGER", "name": "@ORDERNOKEY" }, { "datatype": "VARCHAR(150)", "name": "@RETAILERNAME" }, { "datatype": "VARCHAR(150)", "name": "@RETAILERNAMEMB" } ], "schema": "REST_SERVICES", "serviceDescription": "Update the Retailer names", "serviceName": "update_order_header", "sqlStatement": query, "version": "1.0" }
# Execute the service passing the key and columns to update orderno = 834937 name = "db2Deans Discount Software" namemb = "Ask about our DBA specials" body = { "parameters": { "@ORDERNOKEY": orderno, "@RETAILERNAME": name, "@RETAILERNAMEMB": namemb }, "sync": True }
After executing the update service, I show an example of creating and executing a service to delete rows in the Db2 RESTful Endpoint Create DB Changes Services notebook. Generally, you will need to give the service a key or keys to tell it which rows delete. Example 5 shows an example of building and executing a service to delete rows in the order_header table.
Example 5. Create and execute a service to delete rows
# Create the delete query string for service query="delete from gosales.order_header \ where ORDER_NUMBER = @ORDERNOKEY"
# Create the service body using the query string body = {"isQuery": False, "parameters": [ { "datatype": "INTEGER", "name": "@ORDERNOKEY" } ], "schema": "REST_SERVICES", "serviceDescription": "Delete a row from ORDER_HEADER", "serviceName": "delete_order_header", "sqlStatement": query, "version": "1.0"
# Execute the service passing the key of the row to delete. orderno = 834937 body = { "parameters": { "@ORDERNOKEY": orderno }, "sync": True }
You can use various things I show in many different ways when you create services in your endpoint. Passing many variables as a JSON document can be used in any of service. Don’t forget that you can insert entire JSON documents into Db2 columns and query those documents using their keys as discussed in my Query JSON in Db2 article.
*** If you have ideas about how the REST Endpoint can be used in interesting ways or to make life easier, please share them to my Facebook Page.
|
Db2Dean’s Other Interests
|