Introduction
With DB2 for z/OS customers expanding their business presence into the mobile client world, solution developers are seeking more efficient ways to leverage existing data and business logic to support this.
The REST (Representational State Transfer) native API implemented in DB2 is a light weight interface using HTTP POST / GET request handling to drive SQL and stored procedures, with the result sets being returned in JSON (Java Script Object Notation) format.
REST has been around for a number of years – it was first proposed in a PhD thesis in 2001 - and DB2 support for initially provided through the z/OS Connect product.
The z/OS Connect solution is based on Websphere Liberty Server, and provides a means for z/OS solutions (DB2, IMS, CICS and MQ) to provide a public REST interface to business function and application data. This was positioned as a temporary solution until such time as the supported products could provide native support.
Enter DB2 for z/OS V12, with headline support for a native REST API as part of DDF. Unfortunately, it was not mature enough to make it into the QPP program, and was therefore not delivered on GA. It did, however, arrive with maintenance (continuous delivery!) at the end of 2016, having been back ported to V11 for release in November.
As well as the simplicity of the solution, one of the most notable elements is that this is a distributed connection point into DB2 for z/OS which does not require licensed software to access it.
In this article, we’ll look at how we:
- Install and configure this feature
- Create services using JavaScript / node.js
- How we drive these services with JavaScript / node.js
The article is based on a series of blogs (which was more PHP based) delivered via the Triton DB2 Geek blog:
http://db2geek.triton.co.uk/tag/rest/
Installation
The REST API has been delivered to DB2 V11 and V12 through the maintenance stream, and to support clarity on when function has become available (DB2 V11 support was delivered in two parts), IBM have an informational APAR running which holds the current position – see II14827:
http://www-01.ibm.com/support/docview.wss?uid=isg1II14827
At the time of writing (early January 2018), the following maintenance is required:
DB2 Version APAR (PTF)
DB2 V11 PI66828 (UI41625) – Base REST API support in DDF
PI70477 (UI42683) – LOB support
PI80087 (UI48082) – Client Certificate support
DB2 V12 PI70652 (UI43239) – REST API support in DDF
PI80088 (UI48178) – Client Certificate support
As new maintenance becomes available, it is currently being tagged with the text “DB2REST”, which makes tracking it down on the IBM support portal more straightforward.
Note that whilst deploying the maintenance delivers the capability, there are additional actions that need to be taken to enable it – and these were not originally documented in the ++HOLD. The key points are covered below, and are not covered in the IBM online DB2 documentation as well.
Actions
- Before rolling your maintenance out, create the new REST configuration control table SYSIBM.DSNSERVICE. This is delivered in (untailored) xxx.SDSNSAMP(DSNTIJRS). You will need to copy the job from your SMP/E target SDSNSAMP and tailor it for your intended environment. This ensures that the control table exists when you restart DB2 as part of your maintenance delivery, and REST will then be enabled.
- Before rolling your maintenance out, create a new RACF DSNR class profile to cover REST API connection security. This takes the form:
REST
and is like the other DSNR profiles – if the userid attempting a connection has READ auth, the connection may proceed, otherwise it is rejected. Again, having this in place ahead of the maintenance roll ensures that REST will function following the maintenance restart.
- If you intend using REST for real world data, enable the DDF SSL connection (SECPORT), and route all REST requests this way. As REST depends on HTTP POST / GET plain text processing, it is a good idea to encrypt the stream from prying eyes, even if it is just to protect the user authentication process.
Installation Verification
So, you’ve created the control table and defined the DSNR ssid.REST and got READ auth permitted to your userid. You’ve applied and rolled out the maintenance and you’ve restarted DB2. Did it work?
The quickest way to verify this is to point the web browser of your choice (we’ve used IE and Chrome) at your DB2 DDF address:
http://my.db2.ip.addr:port/services/
Where:
- db2.ip.addr is the hostname / TCP/IP address of the z/OS service hosting DB2
- port is the standard DDF port for DB2 (cf TCPPORT in “-DIS DDF DETAIL”)
Note that you could also use:
https://my.db2.ip.addr:secport/services/
if the secure port (cf SECPORT in “-DIS DDF DETAIL”) is already configured in DB2, TCPIP and RACF.
If it’s all working, you will be prompted for a userid and password to make the connection with, and then you should see something like this:
{"DB2Services": [
{"ServiceName": "DB2ServiceDiscover",
"ServiceCollectionID": null,
"ServiceDescription": "DB2 service to list all available services.",
"ServiceProvider": "db2service-1.0",
"ServiceURL": "http://s0w1.triton.co.uk:2046/services/DB2ServiceDiscover"
},
{"ServiceName": "DB2ServiceManager",
"ServiceCollectionID": null,
"ServiceDescription": "DB2 service to create, drop, or alter a user defined service.",
"ServiceProvider": "db2service-1.0",
"ServiceURL": "http://s0w1.triton.co.uk:2046/services/DB2ServiceManager"
}
]}
Note that if you’re using Internet Explorer, it doesn’t really understand JSON very well and assumes that it’s a file to be downloaded. We used Google Chrome to look at this output with the free JSON Formatter extension (by Callum Locke) from the Chrome Store.
Getting Information About a Service
As seen above, when we point the web browser at the …/services/ path, one of the services listed is the DB2ServiceManager. This is the pre-defined REST service for creating and dropping our own services. We can get more information on the parameters used with this by pointing the web browser at the listed “ServiceURL” (…services/DB2ServiceManager).
When a GET request – which is what a web browser uses for general page requests – is driven against the listed ServiceURL, the API returns information about the service parameters and returned JSON field formats. This self-documenting information is useful, and applies equally to the DB2ServiceManager service as to the services that you create:
{
"DB2ServiceManager": {
"serviceName": "DB2ServiceManager",
"serviceCollectionID": null,
"serviceProvider": "db2service-1.0",
"serviceDescription": "DB2 service to create, drop, or alter a user defined service.",
"serviceURL": "http://s0w1.zpdt.local:2050/services/DB2ServiceManager",
"serviceStatus": "started",
"RequestSchema": {
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"description": "Service DB2ServiceManager invocation HTTP request body",
"properties": {
"sqlStmt": {
"type": "string",
"maxLength": 2097152,
"description": "Service SQL statement for the createService request type"},
"collectionID": {
:
snip
:
},
"ISOLATION": {
"enum": ["CS",
"RS",
"RR",
"UR",
"NC"
],
"description": "ISOLATION bind option for the createService request type"
},
:
snip
:
The input parameters are listed as the “RequestSchema” “properties” and the output JSON format is listed as the “ResponseSchema” “properties”.
Creating a Service
As mentioned above, the examples that we’ll use are JavaScript – actually node.js – based, and were mostly put together for the sixth blog entry. Whilst you may have a tool that works seamlessly with the DB2 REST API, I have yet to find one which works with both the standard and secure (AT-TLS encrypted) ports on DB2 for z/OS. Because of this, we’re going to show how to create and drop services in code.
Before we do – what is a REST service, from a DB2 point of view? It’s actually quite a simple thing:
- Single Unit of Recovery
- Single SQL statement (although this can be a procedure CALL)
- Static SQL (it’s bound when created)
- Stateless – i.e. context (security, cursors, etc) is not maintained between calls
Security and Creating a Service
To ensure that we can create it, we need to have some permissions in:
- RACF (or the security product of your choice):
- DSNR class, READ access to ssid.REST. As covered in the installation section, above, the REST API, like other DB2 for z/OS interfaces, is protected by the RACF DSNR class. Any thread attempting a connection using REST will need READ access to the subsystem REST profile (ssid.REST – e.g. DC01.REST for subsystem DC01).
- DB2:
- BINDADD to bind the package
- CREATE IN to create a package within a nominated collection. When services are created, it is usually within a collection name specified, but if none is supplied the service manager will attempt to create it in the default collection, SYSIBMSERVICE. Whichever way you go, you will need authority to create the package in the collection.
- DML access to support the SQL for the service (e.g. SELECT on table DSN81210.DEPT in the following example)
Service Creation Parameters
To create a new service from the DB2ServiceManager, we need to author a POST HTTP/HTTPS request and supply at least the following parameters:
- requestType: what sort of request is being made – “createService” in this case
- serviceName: the name of the service to be created or dropped
- sqlStmt: the SQL statement to create the service with
Other parameters are available to support the implicit BIND process – e.g.
- collectionID: the name of the collection to bind the package into (default is SYSIBMSERVICE)
- qualifier: schema name to apply to unqualified objects in the SQL
- owner: package owner to apply
- isolation: SQL isolation to run with
- description: text associated with the service (cf ServiceDescription)
All of the typical BIND parameters are available – to get a list of the parameters to use for each and what keywords to apply, refer to the output from the DB2ServiceManager (see Getting Information About a Service, above).
Making the Request
The example service uses the sample tables delivered with DB2 (cf installation / IVP job DSNTEJ1) and is intended to return a list of employees and their phone numbers who are in a specific department. It takes one parameter – the three character department ID:
SELECT FIRSTNME,
MIDINIT,
LASTNAME,
PHONENO
FROM EMP
WHERE WORKDEPT = :dept
ORDER BY LASTNAME,MIDINIT,FIRSTNME
By giving the parameter a name in the SQL, it will be named in the service as well – if we just used the ‘?’ parameter marker, the service manager will supply a name (P1, P2, P3, etc).
When forming the POST request, we will use the following settings:
Parameter
Value
requestType
createService
serviceName
GetEmployeesByDepartment
sqlStmt
SELECT FIRSTNME,
MIDINIT,
LASTNAME,
PHONENO
FROM EMP
WHERE WORKDEPT = :dept
ORDER BY LASTNAME,MIDINIT,FIRSTNME
collectionID
GILLJSRV
qualifier
DSN81210
isolation
UR
description
Retrieve list of employees in a department
The HTTP request is comprised of the parameter data, and a number of HTTP headers. These are used to share information with the server (DB2 for z/OS) about the nature of the request. The specific ones that we will use are:
- Content-Type – tells the server what format the data in the request body (our parameters) are being passed in. DB2 for z/OS requires this to be “application/json” – i.e. the data is passed in JSON form
- Content-Length – how long the data in the request body is
- Accept – tells the server what format we’d like to get the data back in. This should also be set to “application/json”
- Authorization – user context for the connection with DB2. Of the many different options supported by HTTP, DB2 just supports the Basic form – i.e. userid and password encoded in base64 form
The Basic Authorization header is a rather open way of establishing mainframe credentials. This can be partially secured by encrypting the conversation (HTTPS), or you may rather dispense with this and use Client Certificate authentication (also over HTTPS).
Here’s the code to create the service (GetEmployeesByDept) using the Basic Authorization header:
var https = require('http');
var host = 's0w1.zpdt.local';
var port = 2050;
var user = 'MYUSER';
var pwd = 'xxxxxxxx';
// create the base64 user:pwd auth string
var auth = user + ':' + pwd;
var authstr = new Buffer(auth).toString('base64');
// setup the request parameters
var parms = {
'requestType' : 'createService',
'sqlStmt' : ' SELECT FIRSTNME,' +
'MIDINIT, ' +
'LASTNAME,' +
'PHONENO ' +
'FROM EMP ' +
'WHERE WORKDEPT = :dept ' +
'ORDER BY LASTNAME,MIDINIT,FIRSTNME',
'qualifier' : 'DSN81210',
'isolation' : 'UR',
'serviceName' : 'GetEmployeesByDept',
'description' : 'Retrieve list of employees in a department'
};
var postData = JSON.stringify(parms);
// setup the HTTP headers
var headers = {
'Content-Type' : 'application/json',
'Content-Length': postData.length,
'Accept' : 'application/json',
'Authorization' : 'basic ' + authstr
};
// setup the HTTP POST request
var options = {
host: host,
port: port,
path: '/services/DB2ServiceManager',
method: 'POST',
headers: headers
};
// make the request and spool the response
var req = https.request(options, function(res) {
res.setEncoding('utf-8');
var responseString = '';
res.on('data',function(data) {
responseString += data;
});
res.on('end',function() {
console.log('Response from DB2ServiceManager:');
console.log(responseString);
var object = JSON.parse(responseString);
console.log('\nFormatted:');
console.dir(object,{depth: null, colors: true});
});
});
req.write(postData);
req.end();
The request (req) has two handlers – one which manages returning data (‘data’) and one managing what to do when the response finishes (‘end’).
The dir() method provides a formatted (and coloured) way of looking at the JSON response back.
As with other node applications, run it like this:
node crGetEmployeesByDept.js
Here’s the formatted part of the output:
Driving a Service
When driving the service, we use much the same code structure as already demonstrated in the service creation example above – because we were using REST to create the new REST service. Again, we are formatting an HTTP POST request, passing JSON formatted parameters in the request body and requesting a JSON response. The security is again managed via base64 encoded plain text userid and password:
var http = require('http');
var host = 's0w1.zpdt.local';
var port = 2050;
var user = 'MYUSER';
var pwd = 'xxxxxxxx';
var auth = user + ':' + pwd;
var authstr = new Buffer(auth).toString('base64');
var parms = {
'dept': 'A00'
}
var postData = JSON.stringify(parms);
var headers = {
'Content-Type' : 'application/json',
'Accept' : 'application/json',
'Content-Length': postData.length,
'Authorization' : 'basic ' + authstr
};
var options = {
host: host,
port: port,
path: '/services/SYSIBMSERVICE/GetEmployeesByDept',
method: 'POST',
headers: headers
};
var req = http.request(options, function(res) {
res.setEncoding('utf-8');
var responseString = '';
res.on('data',function(data) {
responseString += data;
});
res.on('end',function() {
console.log('Response from GetDepartments:');
console.log(responseString);
var object = JSON.parse(responseString);
console.log('\nFormatted:');
console.dir(object,{depth: null, colors: true});
});
});
req.write(postData);
req.end();
And the formatted response:
If the service that you are driving doesn’t take any parameters, then remove the elements in the example code that refer to postData – i.e.
- The ‘Content-Length’ header
- The send of the data (req.write(postData))
HTTPS, AT-TLS and Client Certificate Authentication
The previous code examples are interesting, but unlikely to make your security administrators happy! Passing a plain text userid and password over an unencrypted connect to the corporate data store is okay if the data store is the sysprog test LPAR, but in real world environments will warrant further securing.
Two methods are supported to assist with this:
- HTTPS conversations, where the client (node.js) and the server (DB2 for z/OS, TCP/IP policy agent and RACF) agree an encryption algorithm and key and then encrypt the conversation
- HTTPS with Client Certificate authentication, where as well as encrypting the conversation, we use a registered client certificate to authenticate the session and provide userid association
Both methods rely on AT-TLS encryption, which is enabled by the following elements:
- RACF registers a server certificate for the DDF service. This is used to verify the identity of the DDF service to the client and seed the encryption. Both RACF and the client must agree that the signing service (certificate authority, CA) is trusted.
- TCP/IP Policy Agent (PAGENT) performs the negotiations and encryption based on the policy settings. These include the cyphers that may be used and which methods (SSL V1/2/3, AT-TLS V1/1.1/1.2/1.3) as well as whether just encryption or SAF based client certificate authentication is to be performed
- DB2 for z/OS by enabling the SECPORT DDF setting in the DDF stanza of DSNJU003
HTTPS and AT-TLS Conversation Encryption
If you are just enabling HTTPS encryption of the conversation, then the only code changes required are to amend the first line of code to require the “https” module and to set the TCP/IP port to the SECPORT value, rather than the standard (PORT) one:
var https = require('https');
var host = 's0w1.zpdt.local';
var port = 2052;
var user = 'MYUSER';
var pwd = 'xxxxxxxx';
:
var req = https.request(options, function(res) {
:
});
req.write(postData);
req.end();
Things become a bit more interesting if you are signing your certificates yourself – e.g. creating them in RACF - rather than acquiring them from one of the well-known, and therefore already trusted, certificate vendors. In this instance, you will need to deliver the public CA certificate – the one you’re using to sign all of the others - to your node.js client, so that the application can trust the DB2 DDF server certificate when it is presented. Packaging this CA certificate with your node application using npm (the Node Package Manager) ensures that it is available at run-time.
The CA certificate being extracted from RACF should be exported in CERTB64 (DER in base64 format). It is then referenced in the https request options (‘ca:’) as a filesystem object:
var https = require('https');
var fs = require('fs');
var path = require('path');
var host = 's0w1.zpdt.local';
var port = 2052;
var user = 'MYUSER';
var pwd = 'xxxxxxxx';
// CA certificate in ./servTLS/zpdt:
var cacert = path.join(__dirname,'servTLS','zpdt','zpdtca.pem');
var auth = user + ':' + pwd;
var authstr = new Buffer(auth).toString('base64');
:
:
var options = {
host: host,
port: port,
path: '/services/SYSIBMSERVICE/GetEmployeesByDept',
method: 'POST',
headers: headers,
ca: fs.readFileSync(cacert)
};
var req = https.request(options, function(res) {
:
});
req.write(postData);
req.end();
As a result of this, our node application now trusts the certificate that our DB2 for z/OS DDF SECPORT presents to initiate the encryption of the conversation.
Client Certificate Authentication
To make use of a client (node) delivered certificate to drive authentication with DB2 for z/OS and RACF, RACF needs to know about the certificate and trust its signer. Vendor produced certificates can be imported into RACF and associated with a RACF userid, or can be generated within RACF itself – e.g.:
RACDCERT GENCERT ID(ADCDA) +
SUBJECTSDN(CN('WebServers1 Cert') +
OU('DB2APP') +
O('TRITON') +
C('UK')) +
NOTAFTER(DATE(2050-12-31)) +
WITHLABEL('ZPDT WebClients Tier1') +
SIGNWITH(CERTAUTH LABEL('ZPDT CA Cert'))
In this example, we create a certificate (‘ZPDT WebClients Tier1’) that is associated with RACF userid ADCDA. When it is used by the client application to authenticate the connection, the authorisation associated with the executed work will be ADCDA.
To make it available to node to use requires exporting it in PKCS#12 format:
RACDCERT EXPORT(LABEL('ZPDT WebClients Tier1')) +
ID(ADCDA) +
FORMAT(PKCS12DER) +
DSN('GILLJ.RACF.WEBCLNT.PKCS12') +
PASSWORD('SAUSAGE')
Then downloading it in binary format and splitting it into the public keys/certificate and the private keys. This last part can be done with the openssl command line tool – available for Windows and Linux hosts:
openssl pkcs12 -in zpdt_webclient.p12 -out zpdt_webclient.keys.pem
-nocerts -nodes
openssl pkcs12 -in zpdt_webclient.p12 -out zpdt_webclient.cert.pem
-clcerts -nokeys
The private keys and public certificate are then referred to in the https request options (‘key:’ and ‘cert:’) as filesystem objects:
var https = require('https');
var fs = require('fs');
var path = require('path');
var host = 's0w1.zpdt.local';
var port = 2052;
var certpath = path.join(__dirname,’servTLS’,’zpdt’);
var cacert = path.join(certpath,'zpdtca.pem');
var clkeys = path.join(certpath,'zpdt_webclient.keys.pem');
var clcert = path.join(certpath,'zpdt_webclient.cert.pem');
var parms = {
'dept': 'A00'
}
var postData = JSON.stringify(parms);
var headers = {
'Content-Type' : 'application/json',
'Accept' : 'application/json',
'Content-Length': postData.length
};
var options = {
host: host,
port: port,
path: '/services/SYSIBMSERVICE/GetEmployeesByDept',
method: 'POST',
headers: headers,
ca: fs.readFileSync(cacert),
key: fs.readFileSync(clkeys),
cert: fs.readFileSync(clcert)
};
var req = https.request(options, function(res) {
res.setEncoding('utf-8');
var responseString = '';
res.on('data',function(data) {
responseString += data;
});
res.on('end',function() {
console.log('Response from GetDepartments:');
console.log(responseString);
var object = JSON.parse(responseString);
console.log('\nFormatted:');
console.dir(object,{depth: null, colors: true});
});
});
req.write(postData);
req.end();
And here’s the formatted output:
A more complete sample using the express framework is available on GitHub at:
https://github.com/db2dinosaur/db2-rest-node-sample
Other samples based on PHP are available as well.
Conclusions
The advent of the microservices architecture and the availability of a simple interface like the REST API help maintain DB2 for z/OS as the core data store for many enterprises. By delivering this solution without the requirement for additional licensed middleware, IBM have simplified the delivery of business solutions that utilise DB2 for z/OS.
This is a new (to DB2 for z/OS) technology, and is receiving enhancements on a regular basis from IBM through their continuous delivery maintenance strategy. As new features become available, we will continue to blog about them on the Triton DB2 Geek blog:
http://db2geek.triton.co.uk/tag/rest/
At the time of writing, PI86867/8 (BIND SERVICE) maintenance has become available, so expect something on this shortly.