DB2 Native REST API – Creating Services from TSO / Batch
Introduction
One of the challenges that we’ve faced working with the DB2 for z/OS native REST API is the mid-range nature of the interface when creating services. This doesn’t sit so well with the existing mainframe landscape and administration processes and the fact that Data Studio (at least up to V4.1.3) doesn’t work with it means that DBAs have to become developers and write code (see previous blogs) to get services deployed.
To answer this problem, IBM have extended the BIND processor to provide support for REST service creation and removal.
Enabling Maintenance
The following maintenance is required to enable the BIND and FREE SERVICE functions:
- DB2 for z/OS V11 : PI86867 (UI51795)
- DB2 for z/OS V12 : PI86868 (UI51748)
Note that this includes additional problem fixes – including the need to restart DDF once you’ve created the SYSIBM.DSNSERVICE control table as part of the REST enablement.
Creating a New Service
Using this new feature is as simple as running a slightly enhanced BIND, passing the service SQL in through a separate DD. The new command format is:
Where:
- collection is the service collection ID for the package that will be created and that is used as part of the service URL (defaults to SYSIBMSERVICE)
- service-name is the name of the resulting REST service (see example, below)
- ddname is the name of the JCL DD name that holds the single SQL statement that will form the REST service
- SQLENCODING is used to tell DB2 what encoding is used in the SQLDDNAME file – i.e. is it in EBCDIC, ASCII, UNICODE or some other code page
- description-string is saved in the DSNSERVICE table and reported as ServiceDescription on the Service Discovery response (https://my.db2.addr:port/services/)
The full documentation reference for DB2 for z/OS V11 is here, and for V12 here.
Example Service Creation
The following is an example batch job which creates service GetEmpList in collection GILLJSRV and makes it available to user ADCDA
Note that we have to use double-quotes (“) around identifiers, and single quotes (‘) around strings.
When ADCDA reviews the REST API service discovery URL, he can now see:
In this example, s0w1.zpdt.local is the address of our z/OS LPAR and port 2050 is the standard DB2 DDF port (TCPPORT in the “-DIS DDF DETAIL” output).
To test the new service we can drive it using CURL (see blog post #7):
And we get:
Removing a Service
To remove a service, we can use the new FREE SERVICE command, which looks like this:
Where:
- collection is the service collection ID that was used when it was created (cf the service URL)
- service-name is the name of the REST service. Note that this is case sensitive and may need to be quoted
Example Service Drop
To drop the service that we just created – ccollection GILLJSRV, service name GetEmpList – use the following command:
Note the use of double quotes on the mixed case identifier for the service name.
Driving a Service on z/OS
There are actually a couple of options for doing this, without having to resort to bespoke socket programming:
- Using the z/OS Client Enablement Toolkit, which provides support to high level languages (e.g. COBOL and C) and REXX to callable services to support HTTP requests and JSON handling.
- Using the DB2 for z/OS (from V11) DB2XML sample user defined functions (UDFs).
Whilst option 1 is interesting – I built REXX and C examples whilst researching it – it is much easier to use the supplied UDFs.
DB2XML Sample UDFs
Installing
If you haven’t used these before, it may be that they are not installed on your service as the job that creates them is not tailored by the installation CLIST. They are all Java based, so a Java WLM application environment will be required – see installation job DSNTIJRW step DSNWLMJ.
The UDFs are created by the sample job <smphlq>.SDSNSAMP(DSNTIJRF) – where <smphlq> is the SMP/E target library high level qualifier – e.g. DSNB10 (default for DB2 for z/OS V11). You will need to take a copy of this, follow the instructions in the comments to edit and run it.
Using
Once available, you can use them to drive the REST API like this:
Where:
- serviceURL is the fully qualified URL for the REST service, e.g. as used in previous blogs: https://s0w1.zpdt.local:2050/services/GILLJSRV/GetEmployeesByDepartment
- headerXML is a set of XML stanzas that define the HTTP request headers – i.e. Content-Type and Authorization. More on this below
- requestBody is the JSON encoded request parameters – see the example, below
The headerXML for our DB2 for z/OS REST calls looks like this:
This establishes the two HTTP headers:
- Content-Type – required by DB2 for z/OS and must be set to “application/json”
- Authorization – used to set the userid and password to authenticate the request
And this is our first challenge: The server (DB2 for z/OS REST) is expecting to see the userid and password in ASCII, with base64 encoding – i.e. it will do this with the string
- Base64decode
- Split the resulting ASCII string into userid:password
So, before we put authstr in the header, we need to construct it like this:
Where MYUSER is the userid and xxxxxxxx is the password.
Here’s a complete example using the GetEmployeesByDepartment (used in previous examples) being run through SPUFI – note the CURRENT PATH:
Before running it, remember to make the maximum character data field width something useful – e.g. 4000.
Here’s the output from our SQL, above:
Whilst SPUFI is not the best way to look at CLOB results (lots of PF11 to see it all), it does make the point that we can drive the service from z/OS quite easily and verify that it is working.
Authorisation Errors
When the service returns an authorisation error (user or password is wrong / invalid) we get an SQLCODE -4302 (Java stored procedure / UDF error), with a reported HTTP response code of 401 (unauthorised).
Conclusions
With the delivery of the BIND and FREE SERVICE support, we can now create, verify and drop DB2 for z/OS REST services from within TSO and batch z/OS. This allows us to integrate the delivery of REST services into the traditional mainframe deployment pipelines and support a coordinated delivery of database and code / service changes.
Access all of James Gill’s Native REST API blogs here.
« Previous | Next »