A RESTful Approach for Working with Db2, by George Baklarz

By George Baklarz posted Apr 12, 2018 05:09 AM


A RESTful Approach for Working with Db2

Customers have a wealth of data in their databases (not just Db2) and publishing it to different devices is often fraught with many challenges. Db2 requires the use of client code to communicate between the application and the database itself. Many of APIs that are used are well known: JDBC, .Net, ODBC, OLE-DB, ADO, CLI and so on. Most programming languages have some sort of connector that maps from the programming language syntax to the database driver. When a new language gets developed it always needs this driver code to talk to the database. For instance, a Python application or Jupyter notebook can communicate to Db2 natively using the ibm_db package. Without some specialized coding and database drivers, there would be no way to communicate with Db2.

A number of years ago, a consortium of vendors worked together to develop a standard protocol for communicating to database servers using RESTful API calls. Rather than paraphrase what OData does, here is the official statement from the OData home page:


OData (Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs. OData helps you focus on your business logic while building RESTful APIs without having to worry about the various approaches to define request and response headers, status codes, HTTP methods, URL conventions, media types, payload formats, query options, etc.



OData tries to remove much of the complexity of communicating with the database. There are no drivers required, no configuration file, nor any administration required on the client that is communicating with the database. All communication is done using RESTful API calls, which are available on all browsers and all operating systems. The calls to the database are replaced with standard RESTful POST, GET, DELETE, PUT and PATCH requests.

OData goes one step further and removes the syntactical differences between SQL vendors. The INSERT, DELETE, UPDATE and SELECT statements are converted to a canonical form that should be interpreted by all vendors. Of course, interoperability depends on how much of the standard a vendor has implemented.

The end result is that an IPhoneTM, AndroidTM phone, tablet, browser or any application will be able to access the database without having any code installed locally. This simplifies the ability to access the database and makes development considerably easier.

The downside to this approach is that the richness of a particular SQL dialect will not be available through OData. Complex SQL with aggregation functions and moving result windows are not currently supported with OData. However, OData covers much of the query spectrum that traditional applications will use, so it makes it a good choice for agile development.

This article will explain some of the features that are available in the IBM Data Server Gateway for OData Version 1.0.0. (OData Gateway). The OData Gateway enables you to quickly create OData RESTful services to query and update data in IBM Db2 LUW. An introduction to the OData gateway is found in the following developerWorks article:

IBM Data Server Gateway for OData Version 1.0.0

The OData Gateway code can be obtained through the following link:

Fix Central

When clicking on the link you will be give two choices of files to download.



Figure 1: Download of WebSphere Liberty Server and OData Gateway

The OData Gateway OData is released in two formats:

  • Integrated with WebSphere Liberty Profile Application server : this version is configured with a default server that enables you to quickly start the server and be productive.
  • Web Application Resource (WAR) file, which can be deployed to any Java servlet container (Application server) of your choice.

There is no difference from an application development perspective whether you use the integrated version or the WAR version.

The OData Gateway sits between the calling application and the database engine. The figure illustrates this relationship. Note that the OData Gateway can be co-located with the Db2 engine to reduce network latency.


Figure 2: OData Gateway Configuration and Communication Flows

Connecting through OData requires a different approach than a Db2 client. There are two steps required to get access to the data

  1. A Service URL needs to be generated that maps the required Db2 tables to the OData Service The OData Gateway needs connection and schema information in order to build the service URL. This is not a connection string that you might be familiar with when using Db2. The URL encapsulates all of the authentication and schema information required for the RESTful calls to work. The end result is that the RESTful calls require no userids or passwords to be passed as part of the RESTful call.
  2. Client Authentication : as mentioned in the previous paragraph, the service URL already has everything it needs to access the data. This creates a security exposure since the URL by itself is sufficient to access the database and the tables that are defined as part of the service. The device must have sufficient security procedures in place (password, biometrics, two-factor authentication, VPN) before the application even attempts to use the URL. There is an option within the OData service to dynamically create a service URL when the application attempts to make a connection. This approach guarantees a unique URL and authentication for each user, but it will result in higher overheads on the OData Gateway since it will need to generate a new URL for every connection.

The next section describes how a RESTful service URL is generated using the OData gateway.

Creating a Service URL with the OData Gateway Console

The OData Gateway Console can be accessed by using a web browser and connecting to the port that is exposed by the WebSphere Application Liberty server (if using the integrated install), or the port exposed by your J2EE server (If you installed the WAR file version).

In the case of WebSphere, the default port is 9000 when running natively on a server. If the service is running in a Docker environment, port 9000 may be mapped to another port. The following screen shows the default display when connecting to the WebSphere Liberty service.


Figure 3: WebSphere Application Server Startup Screen

To access the OData Gateway, append the service name /ODataOne after the port number.


Figure 4: OData Console

The console gives you some details of how to create a service dynamically, but for this example the dialog will be used to create the service URL. Clicking on the Create OData Service button will display the configuration panel.


Figure 5: OData Creation Dialog

Connecting through OData requires a different approach than a Db2 client. You still need to answer a number of questions required to connect to the database, but this doesn't create a connection from the client. Instead what we end up creating is a service URL.

The OData Server take the URL request and maps it to a Db2 resource, which could be one or more tables. The RESTful API needs this URL to communicate with the OData service, which in turn communicates with Db2. No authentication (userids, passwords, etc...) are sent with the OData request.

The Configure Db2 Datasource panel requires the following information to build a service URL.

  • Database Name – The database name that you will connecting to.
  • Hostname – This is hostname or the IP address of the server that the Db2 resides on.
  • Port Number – 50000 is the usual Db2 port number.
  • Db2 Administrator User – This is a userid on the Db2 system that has DBA administrative privileges. Only a user with this privilege can create a service URL.
  • Db2 Administrator Password – The password for the administrative userid.
  • Db2 User for OData Service – This is the userid that will be used to connect to the database to issue the SQL commands. This is separate from the administrative user which is required to generate the URL. This userid must have the proper credentials to access the table(s) that are defined as part of the service URL.
  • Db2 Password for OData Service – The password for the Db2 User.
  • Use SSL – This flag will ensure that any communications to the OData service will use SSL (to encrypt the transmissions).

 After successfully connecting to the database, the OData service will display the available schemas that you can select from.


Figure 6: OData Schema Selection

Selecting a schema will result in a list of tables that you can access. You are able to select more than one table to be part of the service URL.


Figure 7: Table Selection

Once the tables are selected, the display needs to be scrolled to the bottom to access the Submit button. After a short period of time, the OData service will return a Service URL.


Figure 8: OData Service URL Completion

This URL needs to be used in conjunction with the RESTful calls and the OData syntax to access the tables that are associated with this service. The green button below the URL will display the metadata associated with the service URL.


Figure 9: Service URL Metadata

In this case, the metadata shows that there are two collections: EMPLOYEES and DEPARTMENTS. The OData service generator always generates a service URL name that is a combination of the table name with the letter "S" appended to it. This is an important point to remember since OData will be using the entity set name, not the table name when writing queries with the OData syntax.

The metadata contains information about the database (SAMPLE), the table (EMPLOYEE), the primary key (EMPNO), and the columns that make up the table. Included in the column information is the OData specification for the data type.

The datatypes are not the same as what one would expect from a relational database. You get generic information on the character columns (String), and the numbers (Int16, Decimal). The decimal specification contains the number of digits and decimal places but none of the other data types have lengths associated with them.


Table 1: OData Data Types

Data Type                    Contents

Binary                          Binary data

Boolean                       Binary-valued logic

Byte                             Unsigned 8-bit integer

Date                             Date without a time-zone offset

Decimal                        Numeric values with fixed precision and scale

Double                          IEEE 754 binary64 floating-point number (15-17 decimal digits)

Duration                        Signed duration in days, hours, minutes, and (sub)seconds

Guid                              16-byte (128-bit) unique identifier

Int16                              Signed 16-bit integer

Int32                               Signed 32-bit integer

Int64                               Signed 64-bit integer

SByte                             Signed 8-bit integer

Single                             IEEE 754 binary32 floating-point number (6-9 decimal digits)

String                              Sequence of UTF-8 characters

TimeOfDay                     Clock time 00:00-23:59:59.999999999999


Normally an application would be aware of what data types the Db2 table contains, but in the event that the URL is generated through the application then the metadata can be used to determine how the returned data should be handled.

RESTful Programming

Almost every programming language has support for RESTful API calls. If your favorite language doesn't support RESTful APIs then it might be time to look for an alternative language! RESTful support usually requires the inclusion or import of a RESTful library. The following examples demonstrate the use of RESTful API calls using the Python programming language, but the concepts are similar in other languages.

Python includes support for RESTful calls by importing the requests package:

import requests


The RESTful API supports the five types of RESTful calls used in OData:


  • PUT (UPDATE a full row)
  • PATCH (UPDATE selected columns in a row)
  • DELETE (DELETE a row)


All of these functions except perhaps PUT are self-explanatory. PUT is used to replace the contents of an entire row. You don't typically do that in Db2, but then anything is possible!

RESTful calls to OData use the following call format:

RESTful.call(host+service+entityset+OData, headers=
, json=)


  • Call – One of the RESTful calls: get, post, put, patch or delete.
  • Host – The Host field is the IP address and port of the machine that is hosting OData, not the database. OData and Db2 can be co-located on the same server, but the Host field always refers to the server that OData is running on.
  • Service – The service field is the OData service URL that is generated by the OData console (or programmatically). This service is used to connect to the Db2 database and retrieve information from the table that you defined. The service name must be used in conjunction with the Entity Set name that was generated for the table.
  • Entityset – The entity set name refers to the name that was generated by the OData service when the URL was created. The entity set name is the name of the table (EMPLOYEE) with the letter S appended to it (EMPLOYEES). The entity set must always be used in conjunction with the service URL.
  • OData – An optional OData command for GET requests that allows you to filter the rows based on different criteria.
  • Headers – The header instructs the RESTful API how the parameters should be sent to OData and how the data will be received from OData.
  • JSON – The JSON field needs to be supplied If data needs to be sent to OData (PATCH, PUT, POST). For GET and DELETE commands the JSON command is not required.


The host, service, and entityset values are concatenated to create a valid URL. The following example illustrates how these fields are used.


host = ""
service = "/ODataOne/ODataService/SAMPLE-9632a626226a4fa594b88e9ee27b3c42"
entityset = "/EMPLOYEES('000010')" 
URL = host + service + entityset


A RESTful call requires a header field to determine what method of handshaking should be used between the application and the service. The header has the following format:


header = {
    "Content-Type": "application/json",
    "Accept": "application/json"


The header information is a JSON value that tells the OData service how to send and receive instructions and data. These two fields can be set to either XML or JSON, but JSON is a more convenient method to use when programming with Python.


At this point we would have sufficient information to issue a RESTful GET request to retrieve all of the information about employee '000010' from the EMPLOYEE table:


One could also use the URL with one additional field appended to it (?$format=json) to test the results in a browser window instead of using application code.


Figure 10: Using a Browser to View the Results


A URL provides a lot of flexibility from a development perspective. You can use a browser to test the results of your OData query, write applications without any database drivers, and use other tools and applications that support URLs as a data source.

The next section explores the use of the RESTful OData calls to query and modify a Db2 table.

RESTful GET (SELECT) Request

A RESTful call to OData will always start with the name of the entity set we are retrieving from. The previous examples used the EMPLOYEES entity set (which represents the EMPLOYEE table). Any request to OData would start with this entity set name:


There are two forms of GET that can be used when accessing a record. The first method uses the primary key of the table to retrieve a record with optional arguments.


The second method use keywords after the entity set name to retrieve records and restrict results:


The keywords that follow the service name are:

  • $select Select which columns to retrieve (default is all)
  • $filter Filter the rows based on some logic
  • $top Retrieve only a portion of the rows
  • $count Retrieve the count of records and the rows
  • $format=json Format the result as json records

Forming OData Commands

To form an OData command, the following rules must be followed:

  • An OData command always starts with the entity set (i.e. EMPLOYEES)
  • At the end of the entity set name is a question mark ? followed by the list of keywords
  • Keywords are always specified $keyword=values
  • Keywords can only be used once, so they cannot be repeated in the sequence of keywords
  • Keywords can be in any order
  • Multiple keywords are separated by an ampersand & character
  • The last keyword should be $format=json if you want to be able to test the query in a browser

 $SELECT Keyword

The $select keyword is used by OData to limit which columns should be retrieved from the table. Each column that you want returned must be placed into a comma-separated list with no spaces between column names. In addition, the column names must be in the correct case for the database and cannot be a computed value.

OData does not fold table and column names to uppercase (as is the case with Db2 commands) so you must use the uppercase name of a column in the $select option or else the column will not be found. If you used double quotes to create the column names in Db2 (i.e. "lastname") then you must use the exact spelling of the name.

The following OData retrieves three fields from employee '000020' using the primary key to index into the table.


The columns are always returned in alphabetical order, not in the order specified in the $select option.


$FILTER Keyword

The $filter keyword is used to filter out the rows that you want to retrieve from the table. The $filter clause allows the following operators:


  • ge (>=), gt (>), lt (<), le` (<=)**, eq (=), ne (!=)
  • and, or
  • contains
  • Parenthesis to override order of operators


The $format keyword does not allow for mathematical operators at this time (\*, -, +, /) nor the unary not or "-" operators.


The contains operator is similar to the Db2 LIKE clause but it searches the entire column and does not anchor its search at the beginning of the string.


The $filter keyword has the following rules:

  • A space must be placed between any keywords and operators
  • Do not place a space between a bracket () and the column name or constant (on either side). For instance, ( EMPNO ...) will cause an error.
  • Arithmetic operations are not allowed. You can't create complex conditions like SALARY > BONUS * 10.
  • The contains clause requires a column name and a pattern in a string (contains(column,'value')). Contains is case sensitive so you need to be aware of that in your searches.


Example 1: Return the LASTNAME and SALARY of all employees who make more than 100000 in salary.

EMPLOYEES?$select=LASTNAME,SALARY&$filter=SALARY gt 100000

Example 2: Return the LASTNAME of all employees in department E21 or E11 and have a salary less than 40000.

/EMPLOYEES?$select=LASTNAME&$filter=SALARY lt 40000 and (WORKDEPT eq 'E21' or WORKDEPT eq 'E11')

Example 3: Return the LASTNAME of all employees that have a last name with the characters 'AA' in it.



$TOP Keyword

The $top keyword is used to reduce the number of rows that are returned in the answer set. The $top clause is similar to FETCH FIRST x ROWS ONLY in Db2. The rows are always taken from the beginning of the answer set so there is no way to skip "x" rows before getting results.

The following OData request will retrieve the LASTNAME of the first 5 employees in the  EMPLOYEE table.



$COUNT Keyword

You may think that the $count keyword will return the number of records that in the answer set. You would be partially correct. Setting $count=true will return the number of rows in the answer set, but it will also return all of the data in the rows. This behavior is completely different from what you would expect from Db2! The following OData will return the top 5 records in the EMPLOYEE table and a count of 5.



The count information is included in the first block of the result set. This would allow an application to decide whether or not the remainder of the answer set should be retrieved or if the process should be canceled.  The recommendation is not use this feature to determine the number of rows in an answer set unless you make sure that the smallest column of information is returned.

$FORMAT Keyword

The header used by OData already indicates that the data should be returned to the application as a JSON record. If you are using the OData command from a browser, then you need to append the $format=json keyword to ensure that the result set is returned as JSON back to the browser. Normally you would add this to the end of the OData command. Figure 10 contains an example of the browser displaying the answer set.

Depending on what version of Firefox you have, you may not get the JSON record to be properly formatted. To use the built-in JSON formatter, issue the following command in a separate browser window:




Search for devtools.jsonview.enabled


Right click on the jsonview setting and enable it. This will result in the JSON being easier to view.

Processing OData GET Result Sets

Retrieving the result set from OData depends on the type of call you made. The format using /EMPLOYEES('key') will return an OData header (@odata.context) and all of the key:value pairs that you requested.



    "@odata.context": "$metadata#EMPLOYEES(FIRSTNME,LASTNAME)",


The code below illustrates how you can retrieve the data from the JSON record.


r = requests.get(host + service + odata, headers=header)
data = json.loads(r.text)
for key, value in data.items():
    print(key, "=", value)

@odata.context = $metadata#EMPLOYEES(FIRSTNME,LASTNAME)


The /EMPLOYEES?$select... will return the same header, but all of the values will be in the value key. The data associated with the value key will be an array of JSON records, one for each row returned from Db2.


/EMPLOYEES?$select=FIRSTNME,LASTNAME$filter=EMPNO eq '000010'

    "@odata.context": "$metadata#EMPLOYEES(LASTNAME,SALARY)",
    "value": [
            "LASTNAME": "HAAS",
            "SALARY": 152750.0


We need two loops to get the data out of this array. The first loop will iterate through the array to get each JSON record (which is made up of many key/value pairs). The second loop will take each JSON record and then iterate through the key/value pairs.


data = json.loads(r.text)

for result in data['value']:
    for key,value in result.items():
        print( key, '=', value)

SALARY = 152750.0

If you only retrieve single records, then using the /EMPLOYEES('key') format will require less logic to process. However, if you are going to issue get requests that return a variable number of rows, then you will have to iterate through the array of JSON records.


OData allows you to insert data into a table through the use of the RESTful POST command and a JSON document that contains the field names and contents of those fields.

The table that is being modified should have a primary key defined, but there is no requirement to have one for an insert. However, you will need a primary key if you intend to update or delete any rows at another time. The column list and value list must match (i.e. there must be a value for every column name). If you do not supply the list of all columns in the table, the missing columns will have null values assigned to them. The insert will fail if any of these missing columns requires a value (NOT NULL).

The format of a POST call is similar to a GET, except that one additional field is required:

RESTful.post(host + service + entityset, headers=header, json=data)

The host, service, entityset, and headers are identical to what is required with the GET command. The difference is that the post command needs to supply the entire contents of the row in a json record. A sample JSON record is shown below.

new_row = {
  'EMPNO'    : '99999',
  'LASTNAME' : 'Fred',
  'SALARY'   : 67000

This example will insert a single row into a table. Assume that the table has the following structure:


A couple of things to note about the table design. The salary is NOT NULL, while the BONUS allows for nulls. The JSON record needs to ensure that all columns must have values except BONUS. The first thing that needs to be done is create a JSON record that contains the row that we want to insert into the table.

row = {
    'EMPNO' : 1,
    'LASTNAME' : 'Flintstone',
    'SALARY' : 54000,
    'BONUS' : 1000

A new service URL may need to be created if this table was not part of the original set of tables defined using the OData console.

host = ""
service = "/ODataOne/ODataService/sample-e2ab125b43344192b06d2fe99266be20"
header = {
    "Content-Type": "application/json",
    "Accept": "application/json"

All of the information we need for an insert is now in place. The final portion is the entity set name (EMPSHORTS) which was produced by the OData service.

entityset = "/EMPSHORTS"

The final post request to the OData service would be:

requests.post(host + service + entityset, headers=header, json=row)

If the RESTful call returns successfully (True) then it will also return the contents of the data that was just inserted. In the event of an error, you will get a description of what caused the failure. An insert failure can occur because of a duplicate primary key or because of column values not being suppled for non-null columns.


The PATCH/PUT command requires a primary key to update a row, and the name of the fields that you want changed. The PATCH format is identical to the POST command. The PATCH command is what you would normally use since it lets you update individual columns. The PUT command replaces the entire contents of the row, which isn't something that you would typically do.

Note that you can only change one row at a time. There is no ability to specify multiple rows at this time (searched update).

The format of the PATCH command is:

RESTful.patch(url + service + entityset(key), headers=header, json=data)

The JSON field contains the columns that you want to modify. For instance, the following structure would replace the SALARY and BONUS fields of the EMPSHORT table.

update = {
  'SALARY'   : 67000,
  'BONUS'    : 1000

Here is the current record that is found in the EMPSHORT table.

row = {
    'EMPNO' : 1,
    'LASTNAME' : 'Flintstone',
    'SALARY' : 54000,
    'BONUS' : 1000

The OData call is going to update the SALARY and BONUS values for this record. The host, service, and header remain the same, but a primary key must be added to the end of the entity set:

entityset = "/EMPSHORTS(1)"

All updates and deletes must be done through a key so you cannot use the $filter clause that is used in GET requests.

Here is the final set of statements required to do the update.

update_row = {
  'SALARY'   : 67000,
  'BONUS'    : 1000

r = requests.patch(host+service+entityset, headers=header, json=update)

When the update completes, a status will be returned that shows either success (True) or failure (False). There is no text returned from the patch command unless there is an error.


The DELETE command only takes one parameter and that is the key value for the record that will be deleted from the table. The format of the command is:

RESTful.delete(host + service + entityset(key), headers=header)

The key refers to the primary key in the table we are deleting from. Unless you have a primary key, the DELETE command will not work.

The following code will delete one employee from the EMPSHORT table

entityset = '/EMPSHORTS(1)'

requests.delete(host + testodata + entityset, headers=header)



The IBM Data Server Gateway for OData Version 1.0.0. (OData Gateway) gateway removes much of the complexity of communicating with the database. There are no drivers required, no configuration file, nor any administration required on the client that is communicating with the database. All communication is done using RESTful API calls, which are available on all browsers and all operating systems. The calls to the database are replaced with standard POST, GET, DELETE, PUT and PATCH requests. Enabling OData support to Db2 opens up the types of applications that you can write and clients that you can connect to Db2 with.

Future releases of the OData gateway will focus on enriching the support OData syntax, improving URL administration, and allow for in-depth monitoring of URL usage. RESTful APIs are powerful way of developing agile applications and the IBM Data Server Gateway for OData Version 1.0.0. is key component of supporting RESTful APIs in Db2.


George Baklarz, BMath, MSc, Ph.D. Comp Eng.


George is part of the Digital Technical Enablement Team in IBM Cloud and is responsible for creating content that helps customers embrace Db2 and other technologies. In his spare time, he presents at numerous conferences and works with customers to help adopt new Db2 features.

Explore the new IBM DTE (Digital Technical Engagement) site at https://ibm-dte.mybluemix.net for self-service technical assets on Db2 and other product offerings.





May 16, 2018 07:53 AM

IBM Data Server Gateway for OData Version 1.1


Db2Connect has released new version of IBM Data Server Gateway for OData Version 1.1 having many new features

A. Dashboard- The dashboard provides a web interface to manage services. Below administrative tasks are supported:
1. List all the services in tabular format with client side pagination. User can sort each column in the table.
2. Allowed to update the database credentials (host name, database name, port, User name, Schema, Use SSL) in server.
3. Allowed to Add/Remove tables
4. Allowed to change the service password
5. Allowed to change/rename the Service Id
6. Allowed to restart the service. [This is useful when a table's metadata is changed and service need to have updated table's metadata ]
7. Allowed to enable or disable a service.
8. Allowed to delete a service
B. Provided APIs to update the database credentials, delete, change service Id, change service password, add/remove tables, enable, disable and restart the service.
C. Allowed user to specify the service Id while creating a service.
D Support for $skip ()
E. Support for $orderBy




May 09, 2018 04:30 PM

Use Views for More Complex SQL

While Complex SQL with aggregation functions are not currently supported with OData directly, it seems that you should be able to create views that incorporate those functions and complex SQL in the database and query the view through your application. 

Apr 12, 2018 10:00 AM

OData and DB2 z/OS

Is it possible to update data on DB2 z/OS with OData currently?