Written by Jack Dunning

Update: Db2 service projects are now available in API toolkit V3.0.7.0 (Aqua 3.1) / V3.2.7.0 (Aqua 3.2) or later. For more information see our blog on Using z/OS Connect EE to create APIs to Db2 on IBM Z.

A new feature, Db2 service project creation is available for testing in the November 2019 refresh of the z/OS Connect EE API toolkit open beta.

This feature allows you to create Db2 service projects which can then be deployed as services to your z/OS Connect EE server to allow connections to Db2. You start by importing the Db2 native REST service definition from Db2.

You can create a Db2 service project in a few short steps. The following video shows all the steps described in this blog:

For more information see the Db2 services section in the IBM Knowledge Center. This shows how to build and deploy a Db2 service that uses the example Employee table supplied with Db2.

Db2 pre-requisites

When Db2 was installed, these requirements might have been done for you.

1. Create the Db2 sample tables. This example uses the Employee table.
2. Enable Db2 REST services.
3. Create a Db2 native REST service. Using a REST client, send a POST request to http://<db2host>:<db2port>/services/DB2ServiceManager. For example, we are using a curl command (with security configuration omitted):

curl -X POST \
    http://db2.example.com:1234/services/DB2ServiceManager \
    -H 'Content-Type: application/json' \
    -H 'Accept: application/json' \
    -d '{
        "requestType": "createService",
        "sqlStmt": "SELECT FIRSTNME AS 'firstName', LASTNAME as 'lastName' FROM DSN81210.EMP WHERE FIRSTNME = :Employee",
        "serviceName": "Employee",
        "description": "Employee service for the blog"
    }'​

Alternatively, you can issue the BIND SERVICE subcommand to create a new Db2 native REST service.

Creating a z/OS Connect EE Db2 service

1. Open the open beta version of the API toolkit.
2. Switch to the z/OS Connect Enterprise Edition perspective and select File > New > Project.
3. Select z/OS Connect Enterprise Edition > z/OS Connect EE Service Project, and click Next.
4. Enter Employee for the project name. Select Db2 service for project type and click Finish.
5. Define the Db2 service by importing the definition from Db2. Click “Import from Db2 service manager…”.
6. This is the first time importing, so you must define a connection to the Db2 service manager
   1. Click the triangle combo button on the connection widget and select “New Db2 Service Manager Connection…”
   2. Enter the hostname and port for Db2. These are the same values you used in pre-requisite step 3 when creating the service in Db2. If Db2 uses a secure connection, select the “Secure connection (TLS/SSL)” checkbox. (Hint: If the connection is secure, the URL starts with https://)
   3. Click “Save and Connect”, and enter the credentials needed to connect to Db2. A list of Db2 native REST services is displayed, which could be just the one Employee service we created in the pre-requisite steps.
7. Select the Employee service and click “Import”.
   The Db2 service details are filled in and some JSON schemas have been specified in the service project editor. The schemas are also created in the project
8. Switch to the configuration tab and enter db2Conn for the connection reference. This value is used in step 10.
9. The service project is complete. Either deploy it to a z/OS Connect EE server, or export it to a .sar file and manually transfer it to the z/OS Connect EE server’s services directory. This is the same directory where you keep your existing .sar files.
10. In server.xml add the following elements. For the zosconnect_zosConnectServiceRestClientConnection element the id attribute is the same as the connection reference configured in step 8 and the host and port values are your Db2 host and port. More information about these elements can be found within the Configuring security for a REST client connections topic of the IBM Knowledge Center.

<zosconnect_zosConnectServiceRestClientConnection id="db2Conn" host="db2.example.com" port="1234" basicAuthRef="authABC" />
<zosconnect_zosConnectServiceRestClientBasicAuth id="authABC" userName="Db2User" password="Db2UserPassword"/>​

Use your REST client to send a POST request to your z/OS Connect EE server using the URL http://<zOSConnectHost>:<zOSConnectPort>/zosConnect/services/Employee?action=invoke. For example, use a curl command (with security configuration omitted):

curl -X POST \
  'http://zosconnect.example.com:1234/zosConnect/services/Employee?action=invoke' \
  -H 'Content-Type: application/json' \
  -d '{	"Employee": "JAMES" }'​

An HTTP OK (200) response code indicates success. The response body contains:

{
    "ResultSet Output": [
        {
            "firstName": "JAMES",
            "lastName": "WALKER"
        },
        {
            "firstName": "JAMES",
            "lastName": "JEFFERSON"
        }
    ],
    "StatusCode": 200,
    "StatusDescription": "Execution Successful"
}​

You can now create a RESTful API which uses this service.