Example 7: 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.

Initial request

An initial API call to read data from a 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 also requires the name of the Db2® subsystem, the Db2® table owner, the Db2® table name, and optionally a File Manager/Db2 template to map the data. If you don’t specify a template, the service provider will build a dynamic template and return all table columns and rows.

A WHERE clause is only valid on an initial API request that starts the Db2® session and builds the result table for the session.

{
    "resource" :
    {
        "ssid" : "DFG2",
        "owner" : "DSN81010",
        "tableName" : "EMP"
    },
    "template" :
    {
        "templateName" : "DB2.TEMPLATE(EMPTMPL)"
    },
    "operation" : 
    {
        "numRows" : 1,
        "session" : false,
        "where" : "WORKDEPT = 'B01'"
    }
}

This request reads the first row from the beginning of Db2® table EMP, which is owned by DSN81010, in Db2® subsystem DFG2, after applying a WHERE clause of WORKDEPT = 'B01'. The request will use FM/Db2 template DB2.TEMPLATE(EMPTMPL) to map out the row columns 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":
    [
        {
            "columns": 
            [
                {
                    "column": "EMPNO",
                    "type": "CH(6)",
                    "value": "000020"
                },
                {
                    "column": "FIRSTNME",
                    "type": "VC(12)",
                    "value": "MICHAEL"
                },
                {
                    "column": "LASTNAME",
                    "type": "VC(15)",
                    "value": "THOMPSON"
                },
                {
                    "column": "WORKDEPT",
                    "type": "CH(3)",
                    "value": "B01"
                },
                {
                    "column": "JOB",
                    "type": "CH(8)",
                    "value": "Manager"
                },
                {
                    "column": "SALARY",
                    "type": "DEC(9,2)",
                    "value": 41250
                }
            ]
        }
    ] 
}

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.