Example 13: Read a Db2® database sequentially

In the following example, File Manager Service Provider REST API calls are issued to start a persistent session to read rows from a Db2® database table, using a template to map columns in each row.

This example assumes that a service archive has been created and deployed that maps a Db2® table with a matching template. For example, the service archive could be created using the following properties file:

name=fmExample13
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
template=MY.DB2.TEMPLATE(EMP)
timeout=300
connid=default

This example maps a Db2® database table EMP owned by DSN81010 in subsystem DFG2. The table is mapped by a File Manager template found in MY.DB2.TEMPLATE(EMP). This example assumes that the generated SAR file and APIs built in association have been deployed to your z/OS® Connect WLP server.

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" : true
    }
}

This request will read one row from the beginning of the Db2® table as filtered by the FM/Db2 template. Any rows read will be mapped in the response by the template.

The service provider will persist a File Manager/Db2 session because session is set to true. The session will remain active until a subsequent request is made with session set to false, or a timeout period of 300 seconds (5 minutes) elapses with no activity on the session. This timeout period is preset in the service archive.

The caller can expect a token to be returned on an initial request when session is set to true, and can use the token on subsequent calls without the need to pass resource or template parameters, or a Basic Authorization header.

Successful response

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

{
    "rows": 
    [
        {
            "columns": 
            [
                {
                    "PHONENO": "4978",
                    "EDLEVEL": 18,
                    "FIRSTNME": "CHRISTINE",
                    "MIDINIT": "I",
                    "BIRTHDATE": "1933-08-14",
                    "SALARY": 52750,
                    "COMM": 4220,
                    "LASTNAME": "HAAS",
                    "WORKDEPT": "A00",
                    "HIREDATE": "1965-01-01",
                    "BONUS": 1000,
                    "EMPNO": "000010",
                    "JOB": "PRES"
                }
            ]
        }
    ],
    "token": "1NC6BNJg8fX18fH2+fP58GDy9/f39/Y="
}

Subsequent read requests

The API caller can now make subsequent requests using the response token and reuse the FM/Db2 session maintained by the service provider. For example:

{
    "operation" :
    {
        "numRows" : 1,
        "session" : true,
        "token": "1NC6BNJg8fX18fH2+fP58GDy9/f39/Y="
    }
}

The API caller can continue to read one or more rows at a time until a response row object has last set to true, indicating that the last row has been read. For example:

{
    "rows" :
    [
        {
            "last" : true,
    … other response fields
        }
    ]
}

Ending the session

At this point, the API caller sends a request to terminate the active FM/Db2 session.

{
    "operation" :
    {
        "numRows" : 0,
        "session" : false,
        "token": "1NC6BNJg8fX18fH2+fP58GDy9/f39/Y="
    }
}

Sending a request with session set to false will always terminate the FM/Db2 session, providing the token is valid. Since the caller has already read the last row, setting "numRows" : 0 tells the service provider not to attempt to read any more rows.