Wednesday, February 24, 2021

Are You Using the REST Interface to Db2 for z/OS?

Db2 for z/OS got a built-in REST interface about four years ago. Have you put it to work? Perhaps you should.

Some information about REST

REST is short for representational state transfer, an architectural style that enables invocation of services through a straightforward, consistent mechanism. It is increasingly popular among application developers in large part because it is lightweight, easy to use, and provides a high degree of abstraction with respect to the particulars of a service-providing system. That can be really helpful when an application's client-side programs have to interact with a number of disparate back-end systems - if the service requests your programs issue look largely the same in form, even though the responding systems are quite different from each other, that is likely to be a big boost to your coding productivity, versus a scenario in which you have to interact in different ways (maybe very different) with the various systems that provide services your programs consume.

Let me show you what I mean. A REST request to get information related to account ID 123456789, issued by a client program and fielded by a Db2 for z/OS system, might look something like this:


Body: { “ID”: 123456789 } 


The response received by the client from the Db2 for z/OS system could look like this:



  “FIRSTNAME” : “John”,

  “LASTNAME”  : “Smith”,

  “BALANCE”   : 1982.42,

  “LIMIT”     : 3000.00


There is nothing in the REST request issued by the client program, and nothing in the request response received by the program, that gives any indication that the request was processed by a Db2 for z/OS system, and that's the point. This type of programming model, sometimes referred to as data-as-a-service, eliminates the need for a client-side developer to know the technical particulars of a request-processing system. Is it a z/OS system? A Linux or a Windows system? Will a relational database management system be involved? Will data be retrieved from flat files, or maybe from a Hadoop data store or a NoSQL database? For the developer of the REST request-issuing program, the answer to these questions can be, "Don't know, don't care - all that stuff's plumbing, as far as I'm concerned." What matters to the developer is that a requested service - retrieval of data for a given account ID, in the example above - worked as expected.

When Db2 for z/OS is the service-providing system, what happens when the REST request is received, and how did that REST service come to be, in the first place? Read on.

What Db2 does with a REST request

So, let's say that the first part of the example REST request seen above,, resolves to a Db2 for z/OS system (in that case, 4711 would be the Db2 system's SQL listener port - it could be the system's secure SQL port, and if that is true then Db2 will require SSL encryption for the transaction). The request would be received by the Db2 distributed data facility (DDF), as Db2's REST interface is an extension of DDF functionality (that brings a cost-of-computing advantage: as many Db2 people know, when a SQL statement is executed via DDF, up to 60% of the CPU cost of SQL statement execution can be offloaded to a zIIP engine). Db2 parses the HTTP and JSON associated with the request (JSON is short for JavaScript Object Notation, and I'm referring to the request's input of { "ID": 123456789 } - more on that in a moment), and then does security checks. Security check number one is for user authentication purposes. Db2 requires that a REST request be associated with either an ID and a password or an ID and a certificate (if a password is supplied, it will be in the HTTP header of the request). The ID and password (or certificate) will be validated by RACF (or an equivalent z/OS security manager), and if authentication is successful then we move on to security check number two (note that, additionally, a RACF profile for REST access to the target Db2 subsystem can be defined, giving you the ability to permit an ID to access the Db2 subsystem via the REST interface but not through another interface such as CICS, or vice versa).

That second security check is of an authorization nature: does the ID associated with the REST request have the EXECUTE privilege on the Db2 package associated with the requested service? If that required Db2 privilege has indeed been granted to the ID (or to a group ID to which the ID associated with the REST request is connected), it's package execution time: the package is allocated for execution to the REST request's Db2 thread (a DDF thread, also known as a DBAT - short for database access thread), and the corresponding SQL statement is executed (with the input that accompanied the request, if input is required and supplied).


The Db2 REST service example I have used involves execution of this SQL statement:









The input value supplied with the REST request, account ID 123456789, is substituted for the parameter marker (the question mark) in the query text, and the result set is sent, in JSON format, to the REST client. That output document, as we saw before, looks like this:



  “FIRSTNAME” : “John”,

  “LASTNAME”  : “Smith”,

  “BALANCE”   : 1982.42,

  “LIMIT”     : 3000.00



This JSON document shows what a one-row query result set would look like when sent to the REST client. If it were a multi-row result set, the output JSON document would contain several sets of name-value pairs - one such set for each result set row.


A word about these JSON documents used for Db2 REST service input and output "payloads": this is another aspect of the REST model that appeals to a lot of developers. Though it is not technically required that input and output data associated with REST requests be in the form of JSON documents, that tends to be the case, and with good reason: JSON documents, as you can see, are really easy to form and to parse (by programs and by people). That's not always true for, say, XML documents. JSON also tends to be "lighter weight" versus XML - fewer bytes are transmitted between client and server.


With regard to the name-value pairs seen in the JSON documents associated with a Db2 REST service, let's consider first the input. We saw, for my example, { "ID": 123456789 }. Where did that "ID" come from? It's the name of the Db2 table column referenced in the query predicate (referring to the SELECT statement associated with the REST service) that is coded with a parameter marker. "ID" is a pretty intuitive column name. What if that were not the case? What if you wanted to have a Db2 REST service for retrieval of information related to a product ID, and the table column had an obtuse name like PRID? In that case, the predicate of the query to be REST-enabled could be coded with a host variable instead of a parameter marker, and the host variable name could then be used in the input document for the REST request instead of the column name (so, if the query predicate were of the form WHERE PRID = :PRODUCT_ID, the input JSON document could be { "PRODUCT_ID": 56789 } instead of { "PRID" 56789 }).


For an output JSON document that contains data from a query result set, the names in the name-value pairs are, by default, the names of the columns in the query's select-list. As noted above, you could have a table column with a not-very-intuitive name. That situation can be addressed by renaming the column in the query select-list. If, for example, you want to return a mobile phone number from a column named MONUM, you could code the query that you're going to REST-enable in this way: SELECT MONUM AS MOBILE_NUMBER, ... Having done that, you'll have an output JSON document with "MOBILE_NUMBER" : "111-222-3333" instead of "MONUM" : "111-222-3333".


How Db2 for z/OS REST services are created


I've referred to packages associated with Db2 REST services, and that might cause you to think that what you're REST-enabling is static SQL, and you'd be right about that: the Db2 capability I'm talking about enables invocation of a pre-coded, server-side, static SQL statement in response to receipt of a properly coded (and security-checked) REST request. And yes, I do mean a single statement. Sometimes that's all you need for a given Db2 REST service, and in such a case the single SQL statement can be a SELECT, INSERT, UPDATE, DELETE or TRUNCATE. What if you want a Db2 REST service to involve execution of several SQL statements? Can that be done? Absolutely, because the single static SQL statement for which you create a REST service can be a CALL of a Db2 stored procedure, and the called stored procedure could issue any number of SQL statements (if you REST-enable a CALL of a stored procedure, I'd recommend going with a native SQL procedure because execution of such a stored procedure, when the call is through DDF, is up to 60% zIIP-offload-able).


With a SQL statement that you want to REST-enable, you have two service-creation options. One option is to use the Db2-supplied REST service called DB2ServiceManager (a REST service that can be used to create a REST service from a SQL statement). Information on using DB2ServiceManager to create a REST service from a Db2 SQL statement (including an example) can be found in the Db2 for z/OS Knowledge Center on IBM's Web site.

Another option for creating a REST service from a SQL statement is the Db2 command BIND SERVICE, which can be used in a batch job such as the one shown below:



//         DD  DSN=DB2A.SDSNLOAD,DISP=SHR      

//SYSTSPRT DD  SYSOUT=*                        

//SYSPRINT DD  SYSOUT=*                        

//SYSUDUMP DD  SYSOUT=*                        


//             DISP=SHR                        

//SYSTSIN  DD  *                               

 DSN SYSTEM(DB2A)                              



  NAME("simpleSelect1") -                      

  SQLENCODING(1047) -                          

  DESCRIPTION('return a list of deptname-      

 based on input location') 


More information about BIND SERVICE can be found in the Db2 Knowledge Center. Also in the Knowledge Center is additional information on invoking a Db2 REST service. And, check out the series of helpful videos on creating and invoking Db2 REST services (in - yes - the Db2 Knowledge Center).

Db2 REST service capabilities, made even better

As I noted at the beginning of this blog entry, the REST interface is built into Db2 for z/OS: if you have Db2, you have what you need to create and use Db2 REST services. With that said, if you want to have even greater ease of use in creating Db2 REST services, have your Db2 REST services described using Swagger (a standard specification with which many developers are familiar), have more flexibility in invoking Db2 REST services, and have more options for formatting the JSON output document associated with a Db2 REST service, you'll want to take a look at an IBM product called z/OS Connect. In an entry I'll post to this blog in the near future, I'll expand on what z/OS Connect can do to make Db2 REST services even more valuable for your organization.


  1. Robert,

    Sorry i could not appropriate thread to post my below query.

    The minimum hardware for Db2 12 is z196/z114. I got this information from From

    Does this mean that DB2 12 load modules(binaries) are compiled on z196/z114 so that it can run on latest machines z14, z15 machines using backward compatibility ?

    Does Db2 12 make use of new instructions introduced in z14, z15 machines ?


    1. Sorry about the delay in responding.

      As for what these IBM Z server requirements for Db2 12 for z/OS mean, this is what I can tell you: Db2 12 is not supported on an IBM Z model prior to the z196. Would Db2 12 come up on a mainframe model prior to the z196? Perhaps, but that would not be a supported configuration.

      With regard to newer mainframe models, what I can say is that Db2 12 takes advantage of new hardware features that are beneficial in a Db2 context. An example there would be the Huffman compression algorithm introduced with the z14: that's a newer IBM Z server feature of which Db2 12 can take advantage.


  2. Hi Robert,

    I would like to get clarity on BRF & RRF.why is RRF more preferred and how storing the variable column at end of row is an advantage with some example.

    1. There are a couple of efficiency advantages that come with reordered row format (RRF). One has to do with reducing the volume of data written to the Db2 for z/OS transaction log. When data in a varying-length column is changed by an update operation, Db2 writes to the log the portion of the associated row from the varying-length column to the end of the row. That being the case, moving the varying-length columns physically (not logically) to the end of the row (as is done when RRF is in effect, reduces logging volume when varying-length columns are updated.

      The other efficiency benefit has to do with navigation to a varying-length column - a benefit that is realized particularly when a row has more than one varying-length column. Let's say that a table has three varying-length columns, and am application process is going to update the third of those columns. With basic row format (BRF), to get to the third varying-length column of the row, Db2 has to determine the length of the values in the first and second varying-length columns of the row. With RRF, Db2 knows where the offset indicators are that show the starting point of each varying-length column value (they are located after the fixed-length columns), and Db2 knows that it will be going to the third offset indicator (each of those is a two-byte field), and that indicator tells Db2 where the third varying-length column value starts; so, with one direct access to length indicator number three, Db2 knows right where to go to get to the third varying-length column of the row. The more varying-length columns a table has, the greater the efficiency gaoin delivered by this navigational advantage of RRF (assuming that the varying-length column values are sometimes updated).


  3. Robert,

    I would also like to know about the preferences for pctfree and freepage and when it comes into major part of play

    1. In my experience, FREEPAGE is useful mostly in certain cases involving indexes. If an index is defined on a key that is not continuously-ascending in nature, there will be inserts of new key values in the "middle" of the index, and that will likely lead to index leaf page splits. When an index page is split, some of its entries will be moved to another leaf page, and that other leaf page will have to be empty at the time of the index entry relocation. To help maintain the physical organization of the index (for performance reasons), you'd like for the entries moved from a split leaf page to go to a page not very far from the page that was split. In general that will require having some empty pages spread throughout the index, and that is what FREEPAGE will accomplish for you: for example, with a setting of FREEPAGE 5, when the LOAD or REORG utility is executed for the table space (or for the index, in the event of an index-only REORG) then after every 5 pages in which there are entries Db2 will leave an empty page which can receive index entries in the event of a page split. If an index on a key that is not continuously ascending has no empty pages among the populated pages, when a leaf page split occurs the entries moved out of the split page will have to be relocated to an empty page at the very end of the index - not good for matching index scan performance.

      PCTFREE can also be helpful for indexes defined on continuously-ascending keys, by providing space in index pages to accommodate new entries, thereby reducing the number of page splits that would otherwise occur between index REORGs. In choosing a PCTFREE value, go with a percentage large enough to allow at least a few new entries in an index page before a split occurs.

      For table spaces, PCTFREE can be helpful for tables clustered on a key that is not continuously-ascending - by allowing for more inserts into or near a row's target page (per the table's clustering index), a non-zero PCTFREE value helps to maintain good table space organization between table space (or partition) REORGs. As true for an index, the PCTFREE value for a table space should be enough to allow at least one more row to go into a page following a REORG, and if row length is short relative to table space page size then maybe a PCTFREE value that would allow for a few additional rows in a page after a REORG would be helpful.

      Db2 11 for z/OS introduced PCTFREE FOR UPDATE, which allows you to reserve space in table space pages for row length increases that would occur as a result of UPDATE operation. You can find information about PCTFREE FOR UPDATE in section 13.3.2 of the IBM redbook titled, "IBM DB2 11 for z/OS Technical Overview," download-able from


  4. Replies
    1. Good to know that, Akil - thanks for the feedback.


  5. Hello Robert,
    very interesting. I am following you trying to do the BIND SERVICE from a batch job, but I always receive a -103.
    I just write a SELECT from LOCATIONS table at the DSNSTMT member. Could be here the problem?
    Do you have an idea what I could be doing wrong?

    1. Sorry, my fault. First I had the beginning of the SQL statement not at the right column, and then was using reusing a member who had the numbering at the right and was receiving a -104 who drove me crazy.
      Finally I succeed, thank you!

    2. Well, Soledad, when you answer your own question that certainly makes things easy for me!


  6. Hello Robert,

    Thank you so much for the valuable information on DB2 REST.
    I wanted to check if we can invoke a Cobol Stored procedure from DB2 REST API?

    I know that the recommended approach is to use a Native SQL stored procedure but curious to know other alternatives

    Best Regards,

    1. Hello, Purusottam.

      Absolutely you can REST-enable a Db2 for z/OS stored procedure written in COBOL. When it comes to REST-enabling a Db2 stored procedure, the technical capability is there regardless of whether the stored procedure is written in SQL PL (i.e., a native SQL procedure) or another language, such as COBOL (making it an external stored procedure). What you're actually turning into a REST service is the CALL statement that invokes the stored procedure. From the client perspective, there is no difference in accessing a native versus an external Db2 stored procedure via a REST request - the very fact that the REST request invokes a stored procedure is of course invisible on the client side (a key aspect of the REST architectural style is its abstraction of the particulars of the server-side process that provides a requested service). In a REST context, native SQL procedures have a cost-of-computing benefit versus external stored procedure programs (the difference there is zIIP offload for native SQL procedures), but otherwise external stored procedures can be REST-enabled just as readily as can native SQL procedures.