Db2 for z/OS JSON SQL APIs and Native REST Services

In the recent customers’ conference calls, a lot of customers are confused about Db2 for z/OS JSON capability and Native REST services. They think they are the same thing. In fact, they are completely different. In this article, I will describe the difference and share the questions I have been asked many times.

 

Db2 for z/OS JSON SQL APIs

Db2 for z/OS provides JSON SQL support since version 11. These APIs allow you to manipulate the JSON data that do not need to be stored inside a Db2 column. Below are some of common APIs.

  • JSON_VAL: this built-in function extracts a field in a JSON document and converts it to a SQL type you specify as parameter. This function also allows user to create a JSON index (using index on expression).
  • JSON_TABLE: UDF to return the array elements.
  • JSON2BSON and SYSTOOLS.BSON2JSON: these 2 UDFs, as the names imply, are to convert between JSON and BSON (Binary format of JSON). These functions are required since Db2 store JSON data in BSON format in BLOB column and most of JSON functions, like JSON_VAL and JSON_TABLE require input in BSON format.

For usage examples, see https://www.ibm.com/developerworks/data/library/techarticle/dm-1501sql-json-db2/index.html and http://ibm.co/2ejxSDC

 

Db2 Native REST services

Db2 for z/OS provides native REST services support as a post (v11) GA feature. It allows Db2 to act as a RESTful service provider. User can create a REST service from a single SQL statement (INSERT, SELECT, UPDATE, DELETE, CALL) and invoke such service via a POST call with JSON payload over http or https. Db2 will return the result of the invocation in JSON format back to the caller.

See example in second part of article for more usage details.

 

Below are some of the common questions I have been asked many times.

If I store JSON data inside Db2, can I retrieve my data using Db2 native service?

Sure. However, you need to pay attention to the following:

  1. JSON data are stored as BSON format inside Db2 column that your caller/application may not understand, so the SQL statement that is used to create the REST service need to call BSON2JSON function to convert the internal Db2 BSON format to textual format of JSON that your application can understand.
  2. Db2 will return the JSON data (that store inside Db2) as a character string. In other word, the caller application need to convert this character string to JavaScript object, if needed.

In the following example, we create a table to store the employee data in JSON format (note: I use # as SQL terminator).

DROP TABLE EMPLOYEE#
CREATE TABLE EMPLOYEE(id INT, name BLOB)#

 

INSERT INTO EMPLOYEE VALUES(1, SYSTOOLS.JSON2BSON('{ "name": "Jane", "age":18, "isManager": false}'))#
INSERT INTO EMPLOYEE VALUES(2, SYSTOOLS.JSON2BSON('{ "name": "John", "age":58, "isManager": true}'))#

 

To create a REST service to SELECT the employee info from id, we can send a POST request to Db2. Note: in the following example, dtec207.vmec.svl.ibm.com is the hostname, and 446 is the port number.

POST http://dtec207.vmec.svl.ibm.com:446/services/DB2ServiceManager
     {
          "requestType": "createService",
          "sqlStmt": "SELECT SYSTOOLS.BSON2JSON(name) FROM EMPLOYEE WHERE ID= ?",
          "serviceName": "selectNameFromId",
          "description": "Select name from id"
     }

The service URL will be:  "http://dtec207.vmec.svl.ibm.com:446/services/SYSIBMSERVICE/selectNameFromId"

To get more info of this service (e.g. input, output), you can send a GET request to
http://dtec207.vmec.svl.ibm.com:446/services/SYSIBMSERVICE/selectNameFromId

You may see the following output:

    "RequestSchema": {
      …
      "properties": {
        "P1": {
          "type": ["null", "integer"],
          "multipleOf": 1,
          "minimum": -2147483648,
          "maximum": 2147483647,
          "description": "Nullable INTEGER"
        }
      },

    },
    "ResponseSchema": {
      "$schema": "http://json-schema.org/draft-04/schema#",
      "type": "object",
      "properties": {
        "ResultSet Output": {
          "type": "array",
          "items": {
             "type": "object",
            "properties": {
              "C1": {
                "type": ["null", "string"],
                "maxLength": 16777216,
                "description": "Nullable CLOB(16777216)"
              }
            },
            "required": ["C1"],

 

Under RequestSchema and ResponseSchema, you can find the info of input and output parameters respectively. 

Below is how you can invoke such service from a node.js application. We use a node.js application as example here, but the same logic also applies to other programming languages.

var Client = require('node-rest-client').Client;
var client = new Client();

//create a base64 encoding of userid:password , need to fill out with actual userid and password
var userpw = "userid:password";
var buffer = new Buffer.from(userpw);
var userPwBase64 = buffer.toString('base64');

var args = {
    data: { "P1": 1 },
    headers: {  "Content-Type": "application/json",
                "Authorization": "Basic " + userPwBase64,
               "Accept": "application/json"     }
};

client.post("http://dtec207.vmec.svl.ibm.com:446/services/SYSIBMSERVICE/selectNameFromId", args, function (data, response) {
    console.log(data);
    var ResultSetOutput = data["ResultSet Output"];
    var nameInfo = ResultSetOutput[0].C1;
    
    console.log(nameInfo);//print: {"name":"Jane","age":18,"isManager":false}
    console.log(nameInfo.name); //print: undefined (incorrect)
    console.log(JSON.parse(nameInfo).name); //print: Jane (correct)
   });

 

From the example above, nameInfo.name will give you “undefined”. JSON.parse() is required here to convert the JSON string to a JavaScript object.

 

Can I do multi-row insert using Db2 Native Services?

At the time of writing this article, Db2 Native Services do not support array datatype yet. In other word, the input parameter to a Db2 Native Service cannot be an array datatype.

As you may already know, array datatype allows us to do multi-row insert using UNNEST function easily. For examples:

DROP TABLE RESULTTABLE#
CREATE TABLE RESULTTABLE(name varchar(20))#

DROP TYPE namesArray#
CREATE TYPE namesArray AS VARCHAR(12) ARRAY[3]#

--Following can be inside a SQLPL:
DECLARE names namesArray;
SET names=ARRAY['Jane','Tom','Michael'];
INSERT INTO RESULTTABLE
  (
    SELECT *
    FROM UNNEST(names)
   );

 

After the INSERT statement, RESULTTABLE will be populated with ‘Jane’, ‘Tom’, ‘Michael’.

Since Db2 native REST services currently don’t support array datatype, is there an easy way to do multi-row insert using REST services? Yes, we can use the Db2 JSON SQL APIs to bypass this limitation.

Step 1: Create a SQLPL to take CLOB (or VARCHAR) as parameter

CREATE PROCEDURE JANEPROC(IN iJSON CLOB(16M))
DETERMINISTIC
LANGUAGE SQL

P1: BEGIN 
      INSERT INTO RESULTTABLE  ( 
          SELECT X.VALUE  
          FROM       
          TABLE(SYSTOOLS.JSON_TABLE(
               SYSTOOLS.JSON2BSON(iJSON), 'names', 's:20')) X  
       );
END P1 #

 

I will explain this after all I describe all the steps.

Step 2: Create a Db2 native REST service to invoke the SQLPL created in step 1

CREATE a REST service called callSQLPL with following statement:
CALL JANEPROC(?)

This REST service will invoke the SQLPL we created in Step 1.

 

Step 3: Invoke the REST service created in Step 2

POST http://dtec207.vmec.svl.ibm.com:446/services/SYSIBMSERVICE/callSQLPL
with following input parameter*
{
    "P1": "{                         
              \"names\": [\"Jane\",
                        \"Tom\",
                        \"Michael\"
                      ]
             }"
}

 

Inside the SQLPL body in step #1, we first convert the input (iJSON) to BSON using JSON2BSON. Then we use JSON_TABLE function to extract the array elements of “names” (highlighted above). Finally, we insert each array element into RESULTTABLE using INSERT from SELECT.

Using this approach, we can invoke a native REST service to do multi-row insert.

*Note: you need to “escape” the input parameter to the REST service properly.

 

Summary

JSON is one of the most commonly used exchange format while REST is one of most commonly used architecture in modern applications. From Db2 perspective, they are somehow different (e.g. different pre-requisites), but they are closely related to each other in term of usage (e.g. input and output of Db2 Native REST service are in JSON format).  No matter how you use these two features, Db2 for z/OS provide support for both. Personally, I see they play important roles in modernizing the Db2 for z/OS applications.

Recent Stories
Db2 for z/OS – New Encryption Capabilities to Protect Your Data

Transparent Data Encryption

August Content Recap