Introduction

For many years now the integrated web services for IBM i has focused on giving IBM i customers the ability to expose Integrated Language Environment (ILE) programs and service programs as REST and SOAP APIs (also known as web services). Using the HTTP Web Administration GUI interface and with a few clicks of the mouse button, you magically have an API that is based on a program or service program that may be written in RPG, Cobol, or C, and even command language (CL)!

In the latest release of the IBM i operating system (as of August 2019), IBM i 7.4, this same interface can now be used to deploy SQL statements as Representational State Transfer (REST) APIs, enabling IBM® Db2® to act as a RESTful service provider.

In this tutorial, we take you through the steps of deploying SQL statements as REST APIs.

Prerequisites

This section lists the software prerequisites and the prior knowledge that you need to possess to create REST APIs using SQL statements.

Software

In order to get all the program temporary fixes (PTFs) required by the integrated web services server in support of SQL, you need to load the latest HTTP Group PTF. Table 1 lists the HTTP group PTFs that are needed for each of the supported releases of the IBM i operating system.

Table 1. Software prerequisites

IBM i release	HTTP Group PTF
IBM i 7.4	SF99662 (level 1 or greater)
IBM i 7.3	SF99722 (level 19 or greater)

Assumptions

Before reading this tutorial, you need to read Part 1: Building a REST service with integrated web services server for IBM iin order to have a basic understanding of the REST principles and the terminology used.

You can find links to various information on REST in the Resources section. You should also be familiar with the fundamental concepts of JavaScript Object Notation (JSON) and XML.

The RESTful application

The example we use in this discussion is a sample Student Registration Application (SRA). This example is used to show how an ILE service program is exposed as a REST API in the tutorial "Part 3: Building a REST service with integrated web services server for IBM i". In this tutorial, we use the same database file to demonstrate how we can achieve the same functionality but without a service program as the interface to the database file.

The student registration management functions we want to provide in this sample SRA application must enable you to:

Register new students
Edit registered student information
List registered students
Get information about a student
Remove student registrations

The only object we have is the database file, STUDENTDB, where student records are stored.

Things to get done before deployment

When deploying a RESTful web service, you should have answers to the following questions at the bare minimum:

How do I want the URIs to look like?
What HTTP methods will the resource support?
What incoming content types should be supported?
What type of data should be returned?
What SQL statements should be used to implement the APIs?

Table 2 shows a summary of the mappings that we want between HTTP methods and Uniform Resource Identifiers (URIs) for the SRA.

Table 2. HTTP method and URI mappings

HTTP method	URI	Description
GET	/context-root/students	Returns all student registrations
GET	/context-root/students/{id}	Returns student registration
POST	/context-root/students	Registers a new student
PUT	/context-root/students	Updates registered student
DELETE	/context-root/students/{id}	Removes registered student

Note: The default context-root for an integrated web services server is /web/services. The context root for a server can be changed.

For each of the URI mappings, we need to identify the SQL statements that will be used and to associate a procedure identifier with the SQL statement. You need this information when deploying services based on SQL statements. Table 3 shows a summary of the mappings that we want between URI and SQL statements for the SRA.

Table 3. URI mapping to SQL mappings

URI	Procedure identifier	SQL statement
/context-root/students	GETALL	`SELECT * from STUDENTDB`
/context-root/students/{id}	GETBYID	`SELECT * from STUDENTDB` `WHERE "studentID" = ?`
/context-root/students	ADD	`INSERT INTO STUDENTDB` `("studentID", "firstName", "lastName", "gender")` `VALUES(?,?,?,?)`
/context-root/students	UPDATE	`UPDATE STUDENTDB SET` `"firstName" = ?, "lastName" = ?,` `"gender" = ?` `WHERE "studentID" = ?`
/context-root/students/{id}	REMOVE	`DELETE FROM STUDENTDB` `WHERE "studentID" = ?`

What you need

The example REST API developed in this tutorial assumes a database of student registrations and focuses on allowing you to retrieve, add, delete, and update these student registrations using normal REST conventions.

Step 1. Set up the application database file

In this example, the STUDENTDB DB file will be created in the STUDENTRSC library. To create the library, issue the following CL command:

CRTLIB STUDENTRSC

To create the table, issue the following SQL command:

CREATE TABLE STUDENTRSC/STUDENTDB
 ("studentID"  CHAR (9) NOT NULL,
  "firstName"  CHAR (50) NOT NULL,
  "lastName"   CHAR (50) NOT NULL,
  "gender"  CHAR (10) NOT NULL,
  PRIMARY KEY ("studentID"))

To populate the table with sample student registration data, issue the following SQL command:

INSERT INTO STUDENTRSC/STUDENTDB
 ("studentID", "firstName", "lastName", "gender")
     VALUES('823M934LA', 'Nadir', 'Amra', 'Male'),
           ('826M660CF', 'John', 'Doe', 'Male'),
           ('747F023ZX', 'Jane', 'Amra', 'Female')

You must ensure that the user profile that will be running the service has authority to the library and database file. In this example we will be using the default user profile for the server, QWSERVICE. So issue the following CL command:

CHGAUT OBJ('/qsys.lib/studentrsc.lib/studentdb.file')
       USER(QWSERVICE)
DTAAUT(*RWX)

Step 2. Create the integrated web services server

To deploy an ILE program object as a REST service, you need to have an integrated web services server created, and it must be version 2.6 or later. If you have one already created, you can skip this section. If you need to create one, see "Part 2: Building a REST service with integrated web services server for IBM i" to learn how to create a server.

Step 3. Deploy the SQL statements as a RESTful web service

Now we deploy the SQL statements that make up the SRA as a RESTful web service.

Step 3-1. Deploy SQL statements as a web service

Click the Deploy New Service wizard link that is located in the navigation bar. You should see the page in Figure 1.

Figure 1. Deploy web service - step 1 alt

This page gives you the option to either deploy a SOAP or REST web service, and to choose whether the web service will be based on an ILE program/service program or SQL statements. Select *SQL to indicate that the web service will be based on SQL statements and click Next.

Step 3-2. Specify a name for the resource (web service)

Now we need to give the web service (that is, the resource) a meaningful service name and description. The resource name has been changed to students(see Figure 2).

Figure 2. Deploy web service - step 2 alt

You have the ability to set a URI path template for the resource. For this example, we do not need to specify anything because the path to the resource after changing the resource name is what we want:

/context-root/students

Click Next at the bottom of the page.

Step 3-3. Specify database properties

The wizard shows a page requiring various database properties, as shown in Figure 3.

Figure 3. Deploy web service - step 3 alt

By default, the database system that processes the SQL statements is the local host (the system hosting the integrated web services server). You may specify a remote system if the database files reside on a remote server.

In this example, we have specified the default schema to be studentrsc. The system uses the default SQL schema to resolve unqualified names in the SQL statements. For example, in the statement SELECT * FROM MYTABLE, the system looks only in the default SQL schema for MYTABLE. The following conditions apply, depending on whether the naming convention is set to *SQL (SQL naming) or *SYS (system naming):

For SQL naming, if *LIBL is specified for default schema, then the first or second entry (whichever is not *LIBL) in the library list becomes the default schema. If the first and second entry in the library list is *LIBL, then the user profile becomes the default schema.
For system naming, if *LIBL is specified for default schema, no default SQL schema is set, and the system uses the specified libraries in the library list to search for unqualified names.

You can specify the following naming convention to use when referring to tables:

*SQL indicates that SQL naming (as in schema.table) should be used.
*SYS indicates that system naming (as in schema/table) should be used.

Finally, you may specify one or more libraries that you want to add to or replace the library list of the server job. The system uses the specified libraries to resolve unqualified stored procedure names, and stored procedures use them to resolve unqualified names. To specify multiple libraries, use commas or spaces to separate individual entries. You can use *LIBL as a placeholder for the current library list of the server job.

Click Next at the bottom of the page.

Step 3-4. Specify SQL statements

The wizard shows a page that allows you to add SQL statements (see Figure 4). SQL statements are associated with a procedure so you also have to specify a meaningful procedure name (because it will be used as an identifier when returning result sets in response to a client request).

Figure 4. Deploy web service - step 4 alt

Click Add to add a SQL statement. Figure 5 shows the page after we added the SQL statement for removing a student record.

Figure 5. Deploy web service - step 4 (remove student record) alt

In Figure 5, we specify REMOVE for procedure name and the SQL statement to be used to remove a record from the database. Notice that we use a parameter marker for student ID that will be removed. When you click Continue after adding the SQL statement, the page will display a parameter corresponding to the parameter marker. You have the opportunity to change the identifier associated with the parameter by selecting the SQL statement. You want to change the parameter identifier for parameters if the identifier is going to be part of the HTTP payload of the client request (payloads are normally associated with the POST or PUT HTTP methods).

In Figure 6, you can see the rest of the SQL statements. Note that the parameter identifiers have been changed for all the SQL statements.

Figure 6. Deploy web service - step 4 (all SQL statements added) alt

After adding all the SQL statements, click Next.

Step 3-5. Specify SQL information

Procedures contain SQL statements. For each procedure, you must specify how the output from SQL statements is to be handled.

The first procedure to be processed is REMOVE.

Figure 7. Deploy web service - step 5 (REMOVE) alt

Looking at Figure 7, you can find that:

We have taken the default value for including SQL state information in response (1), which is only if the SQL operation fails to run successfully.
We have also taken the default value for treating SQL warnings as errors (2). This option only applies to SQL warnings related to getting DB connections, preparing SQL statements, and running the SQL statements. If a warning happens in any of these cases, it will be treated as a SQL error.
We have indicated that a 204 (No Content) HTTP status code (3) is to be returned on a SQL statement that has run successfully. We have taken the default HTTP status code of 500 (Server Error) if the SQL operation fails to run successfully.

Click Next to process the UPDATE procedure (shown in Figure 8).

Figure 8. Deploy web service - step 5 (UPDATE) alt

Looking at Figure 8, you will find that we have taken the default values for the fields except for where we have indicated that a 204 (No Content) HTTP status code (1) is to be returned on a SQL statement that has run successfully.

Click Next to process the ADD procedure (Figure 9).

Figure 9. Deploy web service - step 5 (ADD) alt

Looking at Figure 9, you will find that we have taken the default values for the fields except for where we have indicated that a 201 (Created) HTTP status code (1) is to be returned on a SQL statement that has run successfully.

Click Next to process the GETBYID procedure (Figure 10).

Figure 10. Deploy web service - step 5 (GETBYID) alt

Looking at Figure 10, you can find that:

The SQL operation returns a single-row result set (1). This ensures that the response returned is not an array of objects, but a single object. If you wanted the response to return an array of objects, you would specify a multi-row result set. Note that if you specify a single-row result set, then only one row will be returned, even in the case where the result set has multiple rows.
The default trim mode (2) for output character fields is to remove trailing blanks from fields. You also have the option to remove leading blanks, both leading and trailing blanks, and to not remove blanks. Using the trim function within the SQL statement will perform better than setting this option to a value other than None (not to remove blanks).
We have taken the default values for HTTP status codes (3): 200 (OK) for success and 500 (Server Error) for failure.

Click Next to process the GETALL procedure (Figure 11).

Figure 11. Deploy web service - step 5 (GETALL) alt

Looking at Figure 11, you can find that:

The SQL operation returns a multi-row result set (1). This ensures that the response returned is an array of objects.
We have taken the default values for HTTP status codes (2): 200 (OK) for success and 500 (Server Error) for failure.

At this point, we are done with setting SQL-related information for each procedure. Click Next.

Step 3-6. Specify resource method information

Before discussing this step, it is a good idea to summarize the REST information for the RESTful application that is to be deployed. Table 4 summarizes REST information for each of the resource methods (that is, procedures) of the SRA.

Table 4. REST information for each procedure


REMOVE	URL	/context-root/students/{id}
	Method	DELETE
	Request body	None
	Returns	204 No content
		500 Server error
UPDATE	URL	/context-root/students
	Method	PUT
	Request body	JSON
	Returns	204 No content
		500 Server error
ADD	URL	/context-root/students
	Method	POST
	Request body	JSON
	Returns	201 Created
		500 Server error
GETBYID	URL	/context-root/students/{id}
	Method	GET
	Request body	None
	Returns	200 OK and JSON
		500 Server error
GETALL	URL	/context-root/students
	Method	GET
	Request body	None
	Returns	200 OK and JSON
		500 server error

First procedure to be processed is the REMOVE procedure.

Figure 12. Deploy web service - step 6 (REMOVE) alt

Looking at Figure 12, you can find that:

The HTTP request method (1) is set to DELETE.
Recall from Table 3 that the URI has the following format: /context-root/students/{id} That is, the student identifier information is passed in as part of the URI. So, we specify a URI path template (2) so that any HTTP DELETE request that matches the URI is passed to the REMOVE procedure.
There is no entity body returned by the procedure. However, we needed to specify a value and therefore, retained the default which is JSON (3).
We want to inject the URI path variable id into the studentID parameter. We do this by specifying the input source as *PATH_PARAM (4) and selecting the identifier to be inserted.

Click Next to process the UPDATE procedure (Figure 13).

Figure 13. Deploy web service - step 6 (UPDATE) alt

Looking at Figure 13, you can find that:

The HTTP request method (1) is set to PUT.
The format of the input data is JSON (2).
There is no entity body returned by the procedure. However, we need to specify a value, and therefore, we retained the default which is JSON (3).
Because the request is in the payload of the client request, we specify that the parameters should be wrapped (4).

Click Next to process the CREATE procedure (Figure 14).

Figure 14. Deploy web service - step 6 (ADD) alt

Looking at Figure 14, you can find that:

The HTTP request method (1) is set to POST.
The format of the input data is JSON (2).
There is no entity body returned by the procedure. However, we need to specify some value, and therefore, we retained the default which is JSON (3).
Because the request is in the payload of the client request, we specify that the parameters should be wrapped (4).

Click Next to process the GETBYID procedure (Figure 15).

Figure 15. Deploy web service - step 6 (GETBYID) alt

Looking at Figure 15, you can find that:

The HTTP request method (1) is set to GET.
Recall from Table 3 that the URI has the following format: /context-root/students/{id} That is, the student identifier information is passed in as part of the URI. So, we specify a URI path template (2) so that any HTTP GET request that matches the URI is passed to the GETBYID procedure.
There is no input data. So we can simply consider the default values to accept all input media types (3).
The format of the output data is JSON (4).
We want to inject the URI path variable id into the studentID parameter. We do this by specifying the input source as *PATH_PARAM (5) and selecting the identifier to be inserted.

Click Next to process the GETALL procedure (Figure 16).

Figure 16. Deploy web service - step 6 (GETALL) alt

Looking at Figure 16, you can find that:

The HTTP request method (1) is set to GET.
There is no input data. So, we can simply consider the default values to accept all input media types (2).
The format of the output data is JSON (3).

At this point, we have completed setting REST information. Click Next.

Step 3-7. Specify user ID for this service

We now need to specify the user ID to run the service. As shown in Figure 17, you can run the service using the server's user ID, specifying an existing user ID, or using an authenticated user ID (this would require you to enable basic authentication in the associated HTTP server - see the Security chapter in the Integrated Web Services Server Administration and Programming Guide for details on how to do this).

Figure 17. Deploy web service - step 7 alt

In order for the web service to run correctly, the user ID status must be set to *ENABLED and the password must be set to a value other than *NONE. If a user ID that is disabled or has a password of *NONE is specified, a warning message is displayed, and the service may not run correctly. In addition, ensure that the specified user ID has the proper authorities to any resources and objects that the web service needs, such as libraries, databases, and files.

In this example, we accept the default values. Click Next.

Step 3-8. Deploy web service - step 8

The web service deployment wizard shows you a summary page (see Figure 18), giving you a chance to see the details relating to the web service being deployed.

Figure 18. Deploy web service - step 8 (Summary) alt

Click Finish at the bottom of the summary page to complete the installation process. When the web service is deployed, the deployed service becomes active (indicated with a green dot to the left of service name) as in Figure 19:

Figure 19. Successfully deployed RESTful web service alt

Congratulations, you have now successfully deployed SQL statements as a RESTful web service.

You can easily test the resource methods that are bound to the HTTP GET method using a browser. Figure 20 shows the results of the request that returns all registered students.

Figure 20. Testing web service - return all registered students alt

Figure 21 shows the result of a request for a student record with a student ID of 823M934LA.

Figure 21. Testing web service - return a registered student alt

To test the other resource methods, an external tool (such as SoapUI) must be used. Figure 22 shows the result of a request to create a new student registration using SoapUI.

Figure 22. Testing web service - create a new student registration alt

Looking at Figure 22, as we are attempting to create a new student registration, the HTTP method is POST (1). The sub pane numbered (2) is the new student registration data in JSON format that will be sent to the server as part of the HTTP POST request. After submitting the request, the server response did not return any JSON data (3). Because the create request succeeded, the REST service returned the HTTP status code of 201 (Created).

Figure 23 shows the results when we ran the same request again.

Figure 23. Testing web service - create a student registration error alt

Looking at Figure 23, as we are attempting to create a new student registration with a student ID that already exists in the database, the server returned an HTTP status code of 500 (Server Error) (1) with information regarding the error (2), which includes the SQL state, SQL code, and the error message.

Summary

In this tutorial, you learned how to deploy a REST API based on SQL statements using the integrated web services server support for IBM i.

The integrated web services server REST support provides a solid foundation for creating and deploying REST APIs based on ILE programs, service programs, and SQL statements on the IBM i platform. Add the highly-intuitive IBM Web Administration for i GUI for deploying web services, and you've got everything you need to quickly prototype and deploy your own custom REST API.

Resources

For detailed information about the integrated web services support on IBM i, see the product web page.
See RESTful Web services: The basics to learn what REST is all about.

Create REST APIs based on SQL statements

Create REST APIs based on SQL statements