Introduction
In the previous blogs in this series, we’ve seen that the process of creating and consuming REST services with DB2 for z/OS V11 and V12 can be managed easily with PHP. In this blog, we’ll look at how we access LOB data, and finally how we use stored procedures to drive business logic.
LOB Data and REST
Availability
LOB data types were not initially supported when the REST API was released. They didn’t become available until PI70477 (V11 – closed 2016-11-16) / PI74515 (V12 – resolves PE on original – closed 2017-03-01).
An Example Application
Back to that old favourite of DBAs, the Spiffy Computer Company, who’s data makes up the set of sample data delivered with DB2. It seems that they need a solution to access employee photos and resumes, conveniently already held in one of these tables:
DB2 V11 : DSN81110.EMP_PHOTO_RESUME
DB2 V12 : DSN81210.EMP_PHOTO_RESUME
The REST interface handles the LOB locators and the implicit cursor to send a stream of data back to the client. As we’ll see in the following example, this greatly simplifies the coding effort but try and keep in mind that the network is a shared resource!
The table fields that we’re interested in are:
Note that the table holds data for a small subset of the employees in the EMP table – these are as follows:
Because I’ve only got four rows of data, I’m going to break my first rule and use an unqualified query:
SELECT EMPNO, BMP_PHOTO, RESUME
FROM EMP_PHOTO_RESUME
ORDER BY EMPNO
To create the REST service, we use the following PHP code (crGetEmployeesResumes.php):
To drive this new service, we need to turn the LOB data that is returned back into something that we can display on our web service / portal / microsite (delete as appropriate).
Of interest in this code section (possibly!) is the parseResume() function and the tagging of the image data to get HTML clients to display it as a BMP image (last line). The parseResume() function hasn’t been included in the blog because it’s 130 lines of regular expressions and string manipulation which even I fall asleep reading. If anyone is an insomniac, please drop me a line and I’ll share it.
The following image is a grab of the output for EMPNO = 000190:
REST and Stored Procedures
Having spent a little time enthusing about DevOps recently, we thought that we’d make the example REST API stored procedure call something that could be used to provision test environments. Like the other examples, this is not a complete solution, but provides a bit of the journey!
When doing DBA work, one of the time-consuming challenges revolves around creating and tearing down unit test environments. The DB2 V11 example we’ll look at is a microservice to do exactly this. In V12, we’d probably just CREATE SCHEMA LIKE…
The example uses DB2 tables to hold versioned DDL as “templates” that can be deployed under a new schema. The procedure itself is written in REXX and makes use of the SYSIBM.SYSPRINT global temporary table to return statement progress information to the caller in a result set. The CREATE PROCEDURE statement looks like this:
Note that the named parameters provide the name of the schema to create (SCHEMA), the name of the environment template to base it on (ENVNAME) and the output return code (STATUS).
The following is an example of driving this from DB2 Connect on a local mid-range box:
In this example call, we’ve requested the creation of the new schema ADDU001 based on the “V11.1 EMP SAMPLE” template DDL – here’s a bit of the output:
To create the REST service, we’ll use the (by now, hopefully) familiar PHP code, with the SQL statement for this being:
CALL CREATE_ENV(:schema,:template,:status)
like this:
Which delivers the following response:
If we look at the resulting URL to investigate the configuration for this new service, we can see that we have two input parameters:
And one output parameter and a result set:
Using the curl tool, we can test our new REST service:
Which produces the following (slightly reformatted for readability):
Conclusions
The real power in the DB2 for z/OS REST API lies in its capability to drive business logic through stored procedures. This allows DB2 to work as the provider of microservices – both internally within the business and externally to partner organisations. By removing the requirement for DB2 Connect licensing to access data and business logic in DB2 for z/OS by mid-range solutions, this represents a cost effective portal for the enterprise data server.
Access all of James Gill’s Native REST API blogs here.