Example 14: Apply a WHERE clause to a Db2® table

In the following example, File Manager Service Provider REST API calls are issued to limit the result table using a Db2® WHERE clause, then read the first row in the result table.

This example assumes that a service archive has been created and deployed. If you don’t specify a template in the properties when generating the SAR file, the service provider will build a dynamic template and return all table columns and rows. For example, the service archive could be created using the following properties file:

name=fmExample14
description=DFG2:DSN81010:EMP MAPPED BY MY.DB2.TEMPLATE(EMP)
version=1.0
provider=filemanager
host=10.1.1.2
port=9043
userid=admin
passwd=s3cr3t
ssid=DFG2
owner=DSN81010
tableName=EMP
timeout=300
connid=default

Initial request

An initial API call to read data from an Db2® data resource requires an HTTP Basic Authorization header so that the caller can be authenticated for subsequent access to Db2® data resources. For example:

Authorization : Basic dXNlcmlkOnBhc3N3b3Jk

where the string after the Basic keyword represents a Base64 encoding of userid:password.

All requests with a JSON payload also require a Content-Type header with a value of application/json.

The initial request does not need to name the data resource or provide template information because this information is already contained in the service archive associated with the API. Therefore, the API caller only needs to provide the optional operation and position parameters, if required:

{
    "operation" : 
    {
        "numRows" : 1,
        "session" : false,
        "where" : "WORKDEPT = 'B01'"
    }
}

This request reads the first row from the Db2® table EMP, which is owned by DSN81010, in Db2® subsystem DFG2, after applying a WHERE clause of WORKDEPT = 'B01'. The request will build a default template and return all columns in the row in the response.

The service provider will not persist a File Manager/Db2 session because session is set to false.

Successful response

A successful response (HTTP Status Code 200) will be similar to the following:

{
    "rows": 
    [
        {
            "last": true,
            "columns": 
            [
                {
                    "PHONENO": "3476",
                    "EDLEVEL": 18,
                    "FIRSTNME": "MICHAEL",
                    "MIDINIT": "L",
                    "BIRTHDATE": "1948-02-02",
                    "SALARY": 41250,
                    "COMM": 3300,
                    "LASTNAME": "THOMPSON",
                    "WORKDEPT": "B01",
                    "HIREDATE": "1973-10-10",
                    "BONUS": 800,
                    "EMPNO": "000020",
                    "JOB": "Manager"
                }
            ]
        }
    ]
}

The caller does not need to issue a second request to terminate the FM/Db2 session because no session was maintained by the service provider.