Example 6: Read a Db2® database sequentially

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

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 do not specify a template, the service provider will build a dynamic template and return all table columns and rows.

{
    "resource" :
    {
        "ssid" : "DFG2",
        "owner" : "DSN81010",
        "tableName" : "EMP"
    },
    "template" :
    {
        "templateName" : "DB2.TEMPLATE(EMPTMPL)"
    },
    "operation" : 
    {
        "numRows" : 1,
        "session" : true,
        "timeout" : 300,
        "maxRows" : 0
    }
}

This request reads one row from the beginning of Db2® table EMP, which is owned by DSN81010, in Db2® subsystem DFG2. The request will use FM/Db2 template DB2.TEMPLATE(EMPTMPL) to map out the row columns in the response.

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.

The maxRows parameter specifies 0, which means all rows in the table. The default is 10000 if maxRows is not specified.

Note: Reading all rows in a large table may take a long time.

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": 
            [
                {
                    "column": "EMPNO",
                    "type": "CH(6)",
                    "value": "000010"
                },
                {
                    "column": "FIRSTNME",
                    "type": "VC(12)",
                    "value": "CHRISTINE"
                },
                {
                    "column": "LASTNAME",
                    "type": "VC(15)",
                    "value": "HAAS"
                },
                {
                    "column": "WORKDEPT",
                    "type": "CH(3)",
                    "value": "A00"
                },
                {
                    "column": "JOB",
                    "type": "CH(8)",
                    "value": "PRES"
                },
                {
                    "column": "SALARY",
                    "type": "DEC(9,2)",
                    "value": 52750
                }
            ]
        }
    ],
    "token": "1zcuItFg8fX18Pjx+PXw9GD29/Tx+Pk="
}

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": "1zcuItFg8fX18Pjx+PXw9GD29/Tx+Pk="
    }
}

When passing a token, the caller does not need to pass resource or template parameters as these are already known by the session associated with the token.

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": "1zcuItFg8fX18Pjx+PXw9GD29/Tx+Pk="
    }
}

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.