Creating a Db2 for z/OS REST service to call a SQLPL

4 min readJan 9, 2020

After publishing a few blogs on Db2 for zOS REST service, I start getting questions on how to create/invoke a REST service to call a SQLPL. In the following sections, I am going to show you how to create and invoke such service.

Prerequisite

The Db2 for z/OS subsystem is enabled for Db2 REST services. See enabling Db2 REST services for details.
curl or a web browser with REST client installed (for sending requests to Db2 for z/OS.

In this blog, I use curl. If you are interested in using a web browser(with REST client installed), please see my other article for reference.

Step 1 : Creating SQLPLs

To make the scenario more interesting, I create 2 SQLPLs, with one calling the other. Our first SQLPL is called JANESP1. Inside JANESP1, we call another SQLPL JANESP2.

We need to create JANESP2 first. Without this, creation of JANESP1 will fail.

Please note I use # as SQL terminator in this blog.

DROP PROCEDURE JANESP2#CREATE PROCEDURE JANESP2
( 
   IN iValue VARCHAR(50),
   OUT oResult VARCHAR(100)
)
DETERMINISTIC
LANGUAGE SQL
DISABLE DEBUG MODE
ISOLATION LEVEL CS
RESULT SETS 1
P1: BEGIN
  
  SELECT SUBSTR(STRING,1,60) as STRING into oResult from SYSIBM.SYSXMLSTRINGS WHERE STRINGID=iValue;END P1 #

JANESP2 is doing a SELECT from a catalog table(SYSIBM.SYSXMLSTRINGS) based on an input parameter, iValue. SYSIBM.SYSXMLSTRINGS contains the mapping of a 4 bytes integer and a string that is used in XML storage. In this example, we are asking the string from a stringid (input).

Below is the content of JANESP1, the top/first SQLPL, which call JANESP2 inside its body with an input parameter, iValue.

DROP PROCEDURE JANESP1#CREATE PROCEDURE JANESP1
(  
   IN iValue VARCHAR(50),
   OUT oResult VARCHAR(100)
)
DETERMINISTIC
LANGUAGE SQL
DISABLE DEBUG MODE
ISOLATION LEVEL CS
RESULT SETS 1
P1: BEGINCALL JANESP2(iValue, oResult);
  -- CALL STLEC1B.SYSADM.JANESP2(iValue, oResult);END P1 #

Step 2: Create a Db2 native REST service

As mentioned above, I use curl to send requests to Db2.

Please customize the following command according to your environment (i.e. replace <userid>:<password>, <hostname>:<port> with your actual Db2 info)

curl -u <userid>:<password> -X POST -H 'Content-Type: application/json' -i 'http://<hostname>:<port>/services/DB2ServiceManager' --data '{
      "requestType": "createService",
       "sqlStmt": "CALL JANESP1(?, ?)",
       "serviceName": "callJaneSP1",
        "description": "Call JaneSP1 which will call JaneSP2"
 }'

The above command creates a REST service called callJaneSP1 from a CALL statement (CALL JANESP1(?, ?)).

For a successful creation, you will see something like :

HTTP/1.1 201 Created
Location: http://dtec222.vmec.svl.ibm.com:446/services/SYSIBMSERVICE/callJaneSP1
Connection: close
Content-Length:                 808
Content-Type: application/json; charset=UTF-8
Date: Wed,  8 Jan 2020 22:29:26 GMT
X-Powered-By: DB2 for z/OS
Server: DB2 DDF Native REST, STLEC1, DSNLJEMG 01/04/20 UI67243
Content-Language: en-US
X-Correlation-ID: G91E2A7B.E164.D74B8B59578C{"StatusCode":201,"StatusDescription":"DB2 Rest Service SYSIBMSERVICE.callJaneSP1 was created successfully.","URL":"http://dtec222.vmec.svl.ibm.com:446/services/SYSIBMSERVICE/callJaneSP1","StatusOptions":{"Applied":[{"ACTION":"ADD"},{"VALIDATE":"RUN"},{"EXPLAIN":"NO"},{"ISOLATION":"CS"},{"RELEASE":"COMMIT"},{"OWNER":"SYSADM"},{"QUALIFIER":"SYSADM"},{"APREUSE":""},{"APCOMPARE":""},{"BUSTIMESENSITIVE":"YES"},{"SYSTIMESENSITIVE":"YES"},{"ARCHIVESENSITIVE":"YES"},{"APPLCOMPAT":"V11R1"},{"DESCSTAT":"YES"},{"SQLERROR":"NOPACKAGE"},{"CURRENTDATA":"NO"},{"DEGREE":"1"},{"NODEFER":"PREPARE"},{"REOPT":"NONE"},{"IMMEDWRITE":"NO"},{"DBPROTOCOL":"DRDA"},{"OPTHINT":""},{"ENCODING":"UNICODE(01208)"},{"CONCURRENTACCESSRESOLUTION":""},{"PATH":""},{"QUERYACCELERATION":""},{"GETACCELARCHIVE":""},{"ACCELERATOR":""}]}}

For better readability, you may want to use a JSON reader/validator.

From the output above, service url of the service we created is :

http://dtec222.vmec.svl.ibm.com:446/services/SYSIBMSERVICE/callJaneSP1

(Optional) Step 3: Get detail for the service created

You can get the details of the service you just created. Again, please customize the command according to your environment.

curl -u <userid>:<password> -X GET -H 'Content-Type: application/json' -i '<hostname>:<port>/services/SYSIBMSERVICE/callJaneSP1'

Output:

HTTP/1.1 200 OK
Connection: close
Content-Length:                1474
Content-Type: application/json; charset=UTF-8
Date: Wed,  8 Jan 2020 22:31:58 GMT
X-Powered-By: DB2 for z/OS
Server: DB2 DDF Native REST, STLEC1, DSNLJEMG 01/04/20 UI67243
Content-Language: en-US
X-Correlation-ID: G91E2A7B.E9D2.D74B8BEA992E{"callJaneSP1":{"serviceName":"callJaneSP1","serviceCollectionID":"SYSIBMSERVICE","serviceProvider":"db2service-1.0","serviceDescription":"Call JaneSP1 which will call JaneSP2","serviceURL":"http://dtec222.vmec.svl.ibm.com:446/services/SYSIBMSERVICE/callJaneSP1","serviceStatus":"started","RequestSchema":{"$schema":"http://json-schema.org/draft-04/schema#","type":"object","properties":{"P1":{"type":["null","string"],"maxLength":50,"description":"Nullable VARCHAR(50)"}},"required":["P1"],"description":"Service callJaneSP1 invocation HTTP request body"},"ResponseSchema":{"$schema":"http://json-schema.org/draft-04/schema#","type":"object","properties":{"Output Parameters":{"type":"object","properties":{"P2":{"type":["null","string"],"maxLength":100,"description":"Nullable VARCHAR(100)"}},"required":["P2"],"description":"Output Parameters"},"ResultSet 1 Output":{"description":"Stored Procedure ResultSet 1 Data","type":"array","items":{"description":"ResultSet Row","type":"object"}},"Anonymous ResultSets":{"type":"integer","multipleOf":1,"minimum":0,"maximum":1,"description":"Number of Anonymous ResultSets"},"StatusDescription":{"type":"string","description":"Service invocation status description"},"StatusCode":{"type":"integer","multipleOf":1,"minimum":100,"maximum":600,"description":"Service invocation HTTP status code"}},"required":["StatusDescription","StatusCode","Output Parameters"],"description":"Service callJaneSP1 invocation HTTP response body"}}}

We need to find out the info of input parameter. After reformat, check under RequstSchema.From what we have below, P1 is the name of input parameter of string type.

...
"RequestSchema": {
   "$schema": "http://json-schema.org/draft-04/schema#",
   "type": "object",
   "properties": {
    "P1": {
     "type": ["null", "string"],
     "maxLength": 50,
     "description": "Nullable VARCHAR(50)"
    }
   },
   "required": ["P1"],
   "description": "Service callJaneSP1 invocation HTTP request body"
  },
...

Step 4: Invoke the REST service

Let’s invoke the REST service. Please customize the command according to your environment before executing it.

curl -u <userid>:<password> -X POST -H 'Content-Type: application/json' -i '<hostname>:<port>/services/SYSIBMSERVICE/callJaneSP1' --data '{
    "P1": 1006
}'

We are invoking our service, callJaneSP1, with input "P1": 1006. I hope you know why we use P1 here ;-)

Output:

HTTP/1.1 200 OK
Connection: keep-alive
Content-Length:                 153
Content-Type: application/json; charset=UTF-8
Date: Wed,  8 Jan 2020 22:33:24 GMT
X-Powered-By: DB2 for z/OS
Server: DB2 DDF Native REST, STLEC1, DSNLJEMG 01/04/20 UI67243
Content-Language: en-US
X-Correlation-ID: G91E2A7B.O05E.D74B8C3C714B{"Output Parameters":{"P2":"space                                                       "},"StatusCode": 200,"StatusDescription": "Execution Successful"}

From output above, we find out space is the string for stringid 1006 in our catalog table.

Summary

We have discussed how to create a REST service to invoke a SQLPL. I hope you enjoy reading this.