Learn more >
Nadir K. Amra | Published August 23, 2019
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.
This section lists the software prerequisites and the prior knowledge that you need to possess to create REST APIs using SQL statements.
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
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.
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:
The only object we have is the database file, STUDENTDB, where student records are stored.
When deploying a RESTful web service, you should have answers to the following questions at the bare minimum:
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
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
SELECT * from STUDENTDB
SELECT * from STUDENTDB
WHERE "studentID" = ?
INSERT INTO STUDENTDB
("studentID", "firstName", "lastName", "gender")
UPDATE STUDENTDB SET
"firstName" = ?, "lastName" = ?,
"gender" = ?
WHERE "studentID" = ?
DELETE FROM STUDENTDB
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.
In this example, the STUDENTDB DB file will be created in the STUDENTRSC library. To create the library, issue the following CL command:
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:
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.
Now we deploy the SQL statements that make up the SRA as a RESTful 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
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.
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
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:
Click Next at the bottom of the page.
The wizard shows a page requiring various database properties, as shown in Figure 3.
Figure 3. Deploy web service – step 3
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):
SELECT * FROM MYTABLE
You can specify the following naming convention to use when referring to tables:
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.
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
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)
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)
After adding all the SQL statements, click Next.
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)
Looking at Figure 7, you can find that:
Click Next to process the UPDATE procedure (shown in Figure 8).
Figure 8. Deploy web service – step 5 (UPDATE)
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)
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)
Looking at Figure 10, you can find that:
Click Next to process the GETALL procedure (Figure 11).
Figure 11. Deploy web service – step 5 (GETALL)
Looking at Figure 11, you can find that:
At this point, we are done with setting SQL-related information for each procedure. Click Next.
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
First procedure to be processed is the REMOVE procedure.
Figure 12. Deploy web service – step 6 (REMOVE)
Looking at Figure 12, you can find that:
Click Next to process the UPDATE procedure (Figure 13).
Figure 13. Deploy web service – step 6 (UPDATE)
Looking at Figure 13, you can find that:
Click Next to process the CREATE procedure (Figure 14).
Figure 14. Deploy web service – step 6 (ADD)
Looking at Figure 14, you can find that:
Click Next to process the GETBYID procedure (Figure 15).
Figure 15. Deploy web service – step 6 (GETBYID)
Looking at Figure 15, you can find that:
Click Next to process the GETALL procedure (Figure 16).
Figure 16. Deploy web service – step 6 (GETALL)
Looking at Figure 16, you can find that:
At this point, we have completed setting REST information. Click Next.
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
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.
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)
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
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
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
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
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
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.
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.
September 12, 2019
August 28, 2019
July 1, 2019
Back to top