Friday, September 21, 2018

The Two Paths to the Db2 for z/OS Distributed Data Facility

It's been almost two years since the general availability date of Db2 12 for z/OS and, by way of that new version, the availability of Db2's native REST interface (retrofitted to Db2 11 for z/OS via APARs PI66828 and PI70477). While the REST interface to Db2 is relatively new, the foundation on which it was built is not: Db2's native REST interface is an extension of the Db2 distributed data facility, also known as DDF. DDF has been around for more than 25 years, providing data services for high-volume, operational, mission-critical applications deployed by organizations in all kinds of industries, all over the world. Given the industrial-strength underpinnings of Db2's REST interface, it's important that this interface be seen as, essentially, a second path into DDF for applications that access Db2 for z/OS via TCP/IP connections. That's the focus of this blog entry

The two paths into DDF

If the REST interface is one path to DDF, what is the other? Why, the SQL path, of course. Hearing that, a Db2 for z/OS DBA or systems programmer might ask, "Oh, you mean the DRDA path?" Yes, that's technically what I mean, but I think that it would be a good idea to use the words "SQL path" instead of "DRDA path," because the latter term is not relevant to a lot of application developers. DRDA - distributed relational database architecture - is Db2's distributed database protocol, but a client-side developer doesn't see DRDA. The developer sees SQL statements, perhaps in JDBC or ODBC form. Those JDBC or ODBC statements are processed by the IBM Data Server Driver (or Db2 Connect), and are in DRDA form when they get to Db2 for z/OS. So, if an application with a TCP/IP connection to a Db2 for z/OS system wants to access Db2-managed data, it can do that by issuing SQL statements (which, again, might be in the form of JDBC or ODBC statements) or by issuing REST calls.

On the topic of the SQL path to DDF, for a long time that path involved going through a Db2 Connect "gateway" server en route to the target Db2 for z/OS system. Over the past several years, we (IBM) have been recommending to Db2 for z/OS-using organizations that they move away from Db2 Connect gateway servers in favor of the IBM Data Server Driver, which runs on the same server as an application program that is utilizing the SQL path (over TCP/IP) to Db2. The IBM Data Server Driver is lighter weight than Db2 Connect, it has the functionality you want (things like connection pooling, Sysplex workload balancing, etc.), and it delivers performance and system management benefits (both largely owing to the fact that the "hop" formerly required to a Db2 Connect gateway server is eliminated - you go straight in to the Db2 for z/OS system from the application server). Note that the IBM Data Server Driver is not licensed as such: your entitlement to use the IBM Data Server Driver is through your Db2 Connect license. If you have, for example, a Db2 Connect Unlimited Edition for System z license for a Db2 for z/OS system, you are entitled to deploy the IBM Data Server Driver in an unlimited fashion for applications that target said Db2 for z/OS system.

As there are two paths to DDF - SQL and REST - there are also two ways a client program can access Db2's native REST interface: the program could directly access that interface, or get to it through z/OS Connect (when Db2's REST interface is accessed by a client program through z/OS Connect, we say that Db2 for z/OS is acting as a REST provider for z/OS Connect). Given that a client program using the REST path to DDF will be getting to Db2's REST interface regardless of whether or not z/OS Connect is in the picture, you might wonder why your organization would want z/OS Connect to be in the picture. z/OS Connect does, in fact, add a good bit of value when client-side programmers want to go the RESTful route to access z/OS-based data services. Some of the benefits delivered by z/OS Connect:

  • Client-side programmers can code more-intuitive REST calls. When going directly to Db2's native REST interface, you have to use the HTTP verb POST in your REST calls. That doesn't limit what you can do, Db2-wise, in response to a REST call, but a client-side developer might want to use GET in a REST call if the requested service is of a data-retrieval nature, or PUT if the call will cause some data values to be persisted at the data server. With z/OS Connect in the picture, all of the HTTP verbs are available for use in REST calls.
  • Easier creation of RESTful services from SQL statements. Db2's REST interface enables invocation of a single static SQL statement (which could be a data manipulation statement such as SELECT, INSERT, UPDATE or DELETE; or a CALL to a Db2 stored procedure) by way of a REST call. Db2 enables creation of a RESTful service through the Db2 command BIND SERVICE, which can be issued from a batch job, or through DB2ServiceManager, a Db2-provided RESTful service that creates RESTful services from SQL statements. That command and that service certainly work, but creating RESTful services from static SQL statements is even easier using the GUI tooling that comes with z/OS Connect.
  • Swagger-based service description. Client-side developers like to be able to "discover" the RESTful services that are available from a host system. The RESTful service-creation mechanisms provided by Db2 for z/OS (the aforementioned BIND SERVICE command and the DB2ServiceManager RERSTful service) allow a service-creator to provide a description of a service in the form of a comment (e.g., "This service returns information about a customer based on a provided customer number"). That's helpful, but with z/OS Connect, service information can be provided to client-side developers in Swagger format - Swagger being an industry-standard specification for describing RESTful services.
  • More-comprehensive capabilities around the management, monitoring, securing, and auditing of RESTful services. Db2 for z/OS provides functionality that addresses all of these areas. z/OS Connect enriches and enhances that functionality.
  • A single entry point for REST-enablement of all kinds of z/OS-based programmatic assets. With z/OS Connect, not only can you REST-enable Db2 SQL statements (which, again, could be stored procedure calls) - you can also REST-enable CICS transactions, IMS transactions, WebSphere Application Server for z/OS transactions, and batch jobs.

Which path to DDF? The SQL path, or the REST path?

Keep in mind that this is not an either/or choice for a Db2 for z/OS system. I expect that, going forward, at many sites you'll see a mix of SQL and REST access to Db2 systems. The question is more relevant, then, in the context of appropriateness/attractiveness for a given application that will access Db2-managed data via TCP/IP connections. Here are some factors that might cause you to lean towards the SQL path:

  • You have client-side developers with a lot of SQL coding experience and expertise, and you want to leverage that capability. In particular, lots and lots of client-side developers know JDBC and/or ODBC (and/or ADO.NET) very well.
  • You want to take advantage of scalability and/or workload management capabilities provided by the IBM Data Server Driver (or Db2 Connect). These capabilities include connection pooling and Sysplex workload balancing functionality.
  • You want client-side programs to be able to dynamically form SQL statements that are then sent to Db2 for z/OS for execution. Oftentimes, SQL statements are hard-coded in client-side programs; sometimes, that is not the case. While it would be technically possible to dynamically form a SQL statement and then pass it as input to a REST-enabled Db2 stored procedure for preparation and execution, issuing dynamically formed SQL statements targeting a Db2 for z/OS system is more easily done via the SQL path to DDF.
  • You need or want client-side control over the scope of transactions. If you want a client-side program to be able to do something like issue SQL | issue SQL | issue SQL | commit, the SQL path is the way to go. When using the REST path, every interaction with the server is, from Db2's perspective, a separate unit of work. That's OK for some applications, not OK for others.

How about factors that might cause you to favor use of the REST path to DDF? Among those could be:

  • There is no need for Db2 client code on the application requester side. In some cases, it may not be desireable or feasible to have the IBM Data Server Driver (or Db2 Connect) installed on the client-side application server.
  • There is no need for client-side programmers to know anything about the particulars of a back-end data server. When a client-side programmer codes SQL statements, he or she knows that the back-end data server is a relational database management system (or something that at least looks like a relational DBMS). Maybe the client-side developers for a given application project don't have much in the way of SQL skills; or, maybe they do have SQL skills, but they prefer the high level of back-end system abstraction provided by the REST architectural style (in other words, they like the data-as-a-service programming model).

Next time a development team is getting ready to build a new application that will access Db2 for z/OS-managed data via TCP/IP connections, or when an existing Db2 for z/OS-based application is going to be reengineered along client-server lines, keep in mind that two paths to the Db2 distributed data facility from such applications are available: the SQL path and the REST path. Work with the development team and determine which path would be best for the project at hand.


  1. Hello Robert, I have a sql statement with IN, Like select * from EMP where ENO in(:ENO), when Invoked it is only accepting just one value for ENO, The GET on the service shows the MAX length of the Variable ENO is limited to column ENO length from the table. Is there a way I can specify the length of the input variable is Unkonwn and it should accept what ever the number of values I throw at it?.


    1. Hello, Nagarjuna.

      Are you sure that the Db2 for z/OS query that has been REST-enabled is of the form SELECT * FROM EMP WHERE ENO IN (:ENO)? I ask because that would be (from my perspective) a rather odd use of an IN predicate - a query coded in that way would be equivalent to the following query with an "equals" predicate instead of an IN predicate, as the query form you referenced in your question could include only one value in the IN-list: SELECT * FROM EMP WHERE ENO = :ENO;

      If the the Db2 REST service you are wanting to use is in fact coded with an IN-list predicate of the form WHERE ENO IN (:ENO), let me know and we'll go from there.

      (I am thinking about the possibility that the query uses the supplied :ENO value in a predicate that builds an IN-list of values generated via a sub-select.)