Creating and enabling Db2 native REST services

Create and enable Db2 native REST services that use the Db2 sample tables. These services are then used in subsequent scenarios.

About this task

In this scenario, you create three Db2 native REST services:

employeeList - Returns a list of employees.
employeeDetails - Returns the details of an individual employee.
employeeUpdate - Updates the details of an individual employee.

These services are created by using the Db2 service manager native REST service. Alternatively, the BIND SERVICE subcommand can be used to create a new Db2 native REST service. For example,

//FREE EXEC PGM=IKJEFT01,DYNAMNBR=20                        
//STEPLIB  DD DSN=DSN1210.DB2.SDSNEXIT,DISP=SHR             
//         DD DSN=DSN1210.DB2.SDSNLOAD,DISP=SHR             
//SYSTSPRT DD SYSOUT=*                                      
//SYSPRINT DD SYSOUT=*                                      
//SYSUDUMP DD SYSOUT=*                                      
//SYSTSIN  DD *                                             
DSN SYSTEM(DSN2)                                            
FREE SERVICE("zCEEService"."selectEmployee")                
//BIND EXEC PGM=IKJEFT01,DYNAMNBR=20                        
//STEPLIB  DD DSN=DSN1210.DB2.SDSNEXIT,DISP=SHR             
//         DD DSN=DSN1210.DB2.SDSNLOAD,DISP=SHR             
//SYSTSPRT DD SYSOUT=*                                      
//SYSPRINT DD SYSOUT=*                                      
//SYSUDUMP DD SYSOUT=*                                      
//DSNSTMT  DD *                                             
  SELECT EMPNO AS "employeeNumber", FIRSTNME AS "firstName",
        MIDINIT AS "middleInitial", LASTNAME as "lastName", 
        WORKDEPT AS "department", PHONENO AS "phoneNumber", 
        JOB AS "job"                                        
  FROM USER1.EMPLOYEE WHERE EMPNO = :employeeNumber         
//SYSTSIN  DD *                                             
DSN SYSTEM(DSN2)                                            
BIND SERVICE("zCEEService") -                               
NAME("selectEmployee") -                                    
SQLENCODING(1047) -                                         
DESCRIPTION('Select an employee from table USER1.EMPLOYEE') 
/*

For each request in the following procedure, replace <db2_host_name> with the hostname of your Db2 instance and <db2_port> with the port of your Db2 instance. You might also need to include authentication credentials such as the Authorization HTTP header.

The services created in this scenario use the default Db2 collection SYSIBMSERVICE.

Note: The examples in this tutorial use Versioning support. If you do not have versioning, then remove the version from body.

Procedure

Ask your Db2 system administrator to create the Db2 sample tables and enable the creation of Db2 native REST services by following this procedure.
1. Create the Db2 sample tables by running the Db2 installation sample jobs DSNTEJ1 and DSNTEJ7.
  The full procedure to install the Db2 sample tables is documented in the Db2 sample table topic in the Db2 for z/OS® documentation.
2. Enable Db2 native REST service creation.
  Follow the steps documented in the Enabling Db2 REST services topic in the Db2 for z/OS documentation.
  Note: You must apply the PTF for Db2 APAR PI98649: New function update of Db2 native RESTful services support.
Create the employeeList service.
Using a REST client, send an HTTP POST request to https://<db2_host_name>:<db2_port>/services/DB2ServiceManager with header Content-Type: application/json and the following body, modified to match the schema where your EMP table is defined.
```
{
    "requestType": "createService",
    "sqlStmt": "SELECT EMPNO AS \"employeeNumber\", FIRSTNME AS \"firstName\", LASTNAME AS \"lastName\", WORKDEPT AS \"department\" FROM DSN81210.EMP ORDER BY LASTNAME",
    "serviceName": "employeeList",
    "description": "Returns a list of employees.",
    "collectionID": "SYSIBMSERVICE",
    "version": "V1"
}
```
The SQL statement provided in sqlStmt uses SQL aliases to rename columns when they appear in the response JSON. Aliases provide a more natural JSON property name than the Db2 table column names would normally provide.
If the Db2 native REST service is created successfully, an HTTP Created (201) response code is returned.

Create the employeeDetails service.

Using a REST client, send an HTTP POST request to https://<db2_host_name>:<db2_port>/services/DB2ServiceManager with header Content-Type: application/json and the following body, modified to match the schema where your EMP table is defined.

{
    "requestType": "createService",
    "sqlStmt": "SELECT EMPNO AS \"employeeNumber\", FIRSTNME AS \"firstName\", LASTNAME AS \"lastName\", WORKDEPT AS \"department\", PHONENO AS \"phoneNumber\", HIREDATE AS \"hireDate\" FROM DSN81210.EMP WHERE EMPNO = :employeeNumber",
    "serviceName": "employeeDetails",
    "description": "Returns the details of an individual employee.",
    "collectionID": "SYSIBMSERVICE",
    "version": "V1"
}

If the Db2 native REST service is created successfully, an HTTP Created (201) response code is returned.

Create the employeeUpdate service.

{
    "requestType": "createService",
    "sqlStmt": "UPDATE DSN81210.EMP SET FIRSTNME = :firstName, LASTNAME = :lastName, WORKDEPT = :department, PHONENO = :phoneNumber, HIREDATE = :hireDate WHERE EMPNO = :employeeNumber",
    "serviceName": "employeeUpdate",
    "description": "Updates the details of an individual employee.",
    "collectionID": "SYSIBMSERVICE",
    "version": "V1"
}

If the Db2 native REST service is created successfully, an HTTP Created (201) response code is returned.

Results

Db2 native REST services employeeList, employeeDetails, and employeeUpdate are created as part of the SYSIBMSERVICE collection in Db2.

What to do next

Test that the Db2 native REST services are created correctly. See Testing the Db2 native REST services.