DB2 for z/OS Mainframe REST Services Using OData

DB2 for z/OS Mainframe REST Services Using OData

Posted on February 13, 2018 0 Comments
Tutorial DB2 for zOS Mainframe REST Services Using OData_870x450

In this tutorial, learn how to REST enable IBM DB2 for z/OS using OData without requiring any specific patches or upgrade to the mainframe.

We are seeing a growing trend with organizations looking to modernize data access to IBM DB2 for z/OS mainframes. With all the exciting advances in distributed Big Data architectures and NoSQL databases, mainframes remain critical for their ability to process a high volume of transactions required by industries such as financial services, retail and government.

Like many databases born on-premises, enterprise database vendors are adding native REST APIs for a number of reasons. Examples include:

  • Oracle REST Data Services
  • Teradata REST API
  • IBM DB2 Rest Services
  • MarkLogic REST API

Progress DataDirect hosted a webinar with Sanjeev Mohan from Gartner on what’s new in REST APIs for your database to further explore this topic. Below are the reasons cited for REST enabling enterprise databases such as IBM DB2 for z/OS on a live webinar poll that attracted over 980+ registrants; and it extends well beyond just mobile applications.

REST API use cases for DB2 mainframe

Options to REST Enable DB2 on z/OS

  1. Enable native RESTful services IBM DB2 11 and 12.

    Troy Coleman from IBM blogged about the required patch levels which are:

    The support for connecting RESTful web, mobile, and cloud application to DB2 for z/OS is just beginning. New features and functionality are being made available through maintenance for both DB2 12 (see APAR PI70652) and DB2 11 (see APARs ll14827 and PI66828).
  2. Enable OData REST services using Progress DataDirect Hybrid Data Pipeline.

    Below is a chart to help contrast the two options and decide which is best for your organization. Progress DataDirect is an OEM partner for IBM and I endorse both solutions

    z/OS Connect Enterprise Edition

    Progress DataDirect Hybrid Data Pipeline

    REST APIs available by Database Version

    DB2 12 (see APAR PI70652) and DB2 11 (see APARs ll14827 and PI66828).

    • DB2 V12 for z/OS and higher*
    • DB2 V11 for z/OS and higher*
    • DB2 V10 for z/OS and higher*
    • DB2 V9.1 for z/OS and higher*
    • DB2 UDB V8.1 for z/OS

    *Includes day one support which means that we will support newer versions and patches if reported before we are able to certify.

    Mainframe assets exposed by REST APIs

    z/OS Connect Enterprise Edition supports IBM CICS and IBM IMS applications, IBM Db2 data, MQ queues and WOLA-enabled z/OS applications.

    Supports Db2 on z/OS versions specified above.


    REST API specific to IBM DB2.

    REST API with OData V2 and V4 compliance, an ISO standard, for interoperability across any OData resources. Service produces OData from other popular enterprise databases such as wide range of IBM DB2 versions from AS/400 to IBM DB2 Warehouse on Cloud, Oracle, SQL Server, Greenplum and more.

    Creation of REST APIs

    High level steps for creating resources:

    • Create DB2 RESTful services by posting SQL statements for operations you wish to support for a resource, for example:

      { "requestType": "createService", "sqlStmt": "SELECT * FROM USER1.EMPLOYEE", "collectionID": "SYSIBMSERVICE", "serviceName": "selectAllEmployees", "description": "Select all employees from USER1.EMPLOYEE", } 

    • Create Service Archive (SAR) files using z/OS Connect EE Build Toolkit for these services.
    • Create DB2 API project and import SAR file.
    • Compose an API for DB2 REST services.
    • Specify path and path parameter which serves as a key for fetch or CRUD.

    Resources are generated with NO CODE and mapped to Db2 tables or views using intuitive visual editor (see tutorial below).


    Discover and invoke services through a REST HTTP client, including IBM z/OS Connect Enterprise Edition or an IBMCloud API management solution.

    Discover and invoke services through a REST HTTP client, or through rich ecosystem of OData libraries for .NET, Java, JavaScript, C++, Python, NativeScript, Objective-C; SaaS applications such as Salesforce or Dynamics CRM; and visualization tools such as Power BI, Tableau, Qlik, Spotfire and more.


    Support provided by IBM.

    Support provided by Progress with access to IBM via TSANet multi alliance support membership.

Getting Started with Hybrid Data Pipeline for DB2 for z/OS

  1. Install Hybrid Data Pipeline service on a Linux machine. You can review the documentation for additional information.
  2. Login to the installed service from step #1, and click the “Data Sources” tab. Note support for other popular data sources such as Oracle, SQL Server or Hadoop Hive.

    Create New Data Source for OData

  3. Configure the connection to IBM DB2 for z/OS.

    If you do not have the connection information, your system administrator can run the following command to displays the IPADDR (IP address), the SQL DOMAIN (server name), the TCPPORT number, and the LOCATION (database name/location):DB2 -DISPLAY DDF

    Configure IBM DB2 z/OS host, port and location

  4. Click Save and Test the connection.

    Verify successful connection to mainframe

  5. Click on OData tab to select the tables and fields you wish to expose via OData.Select Tables and Columns for OData Endpoint

  6. The OData Access URI can be found on the OData tab. Test connection using postman, or any OData consumer of your choice. For simplicity, I’ll use google chrome.

    Copy OData URI for IBM DB2 Fetch JSON results from IBM DB2

Summing Up

The Hybrid Data Pipeline is a popular choice to REST enable IBM DB2 for z/OS using OData since it’s lightweight and easy to deploy without requiring any specific patches or upgrade to the mainframe. The DataDirect brand is a trusted name across IBM, but you should trust Google over me -> https://www.google.com/search?q=ibm+datadirect&oq=ibm+datadirect

Progress is a proud member of the OData technical committee that is defining the best practices to build and consume REST APIs -alongside IBM, SAP, Microsoft and others.

Try it out today for 90 days (includes evaluation support).

Try Now

Sumit Sakar

Sumit Sarkar

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.


Comments are disabled in preview mode.

Sitefinity Training and Certification Now Available.

Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.

Learn More
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

Loading animation