Guru: Web Service With Pagination
November 16, 2026 Mike Larsen
I spend a lot of time working with web services, and I must admit, I enjoy it. I have always wanted to build a service that allowed for pagination. The consumer of the service would have control over how many rows they wanted to extract, and where they wanted to start. The concept is similar to subfiles in RPG programs.
The source of the data I’m showing is from a sample employee table on my IBM i. Please note that this data is not real and there are 42 rows in the table. While I don’t need pagination for such a small table, my intent is to show the mechanics of how the service works. I’ll show the code section by section, while explaining what each is doing. The complete source code will be available for download.
The first section of code (Figure 1) sets up the program.

Figure 1. Program set up
Here, I define the input and output parameters. The program accepts two input parameters. OffsetRows is used to determine which row to position to in the table. FetchRows is used to determine how many rows to fetch. OutEmployeeInfo returns the rows back to the consumer in JSON format. I realize a CLOB field is quite large for 42 rows, but your program may return a large number of rows, so I wanted to show how to handle that scenario.
This article contains code, which you can download here.
In Figure 2, I set some defaults in case the consumer sends parameters that may be unreasonable. If they send an offsetRows value less than zero, I’m forcing it to be zero. Also, if they try to fetch more than 500 rows, I will limit them to 500 rows. That will help prevent them from requesting too much data.

Figure 2. Set program defaults
Now we’ll start to extract the data to be returned to the consumer (Figure 3).

Figure 3. Extract data
I start by executing two common table expressions (CTE). The first CTE gets the total number of rows in the employee table. The second CTE retrieves employee data starting at the row specified by the consumer (by way of the Offset keyword) for the number of rows they requested. There is a note I’d like to make here. The offset keyword is zero based. For example, if one wants to start with row 1, one would send an offset value of zero.
Figure 4 begins to extract the employee data as JSON.

Figure 4. Build JSON data
I get a page count and I use the json_arrayagg and json_object functions to format the data. Note the use of the coalesce function. I will detail that a bit more in Figure 5.

Figure 5. Use of the coalesce function
I put a lot of comments in the code that explains the use of the coalesce function, but in short, if I don’t use coalesce when retrieving the employee data, the consumer will receive null in the payload. That may not be what they want or expect. Instead, by using the coalesce function and the json_array, I return an empty array, and that may be more desirable to the consumer.
Figure 6 puts all the data together and populates the CLOB variable that will be sent back to the consumer.

Figure 6. Build the payload sent back to the consumer
In this piece of code, I’m selecting data from the CTE’s and building a variable that will tell the consumer if there are more rows to retrieve. It’s a simple calculation and it will send back a ‘y’ or ‘n’ in the hasMore data element.
That’s it for the code. I used IWS to create this RPG as a web service. Let’s see how the service works by examining some examples.
In the first example (Figure 7), I tell the service I want to retrieve the first 20 employees.

Figure 7. Retrieve the first 20 employees
As this is the first time we’re seeing the service in action, let’s examine the parameters and some of the data elements being returned in the payload. I’m passing a zero in the first parameter, offsetRows, and 20 in the second parameter, fetchRows. That tells the service to start retrieving data at row 1 for 20 rows.
The first data element returned, totalCount gives us the number of rows in the employee table. Pagecount tells us how many rows were retrieved. An offset of zero means we started with row 1, and limit tells us that we wanted to select 20 rows. I also let the consumer know that there are more rows to retrieve in the hasMore data element. Finally, I return the array of employees along with their attributes.
In the next example (Figure 8), I’m going to page through the next 20 employees.

Figure 8. Retrieve the next 20 employees
I set the offsetRows parameter to 20 and I left the fetchRows at 20. The only data elements in the payload that changed is the offset, and of course, the employees that were returned. In the third example (Figure 9), I’ll continue to page through as I’ve done before. I’ll attempt to retrieve the next 20 rows.

Figure 9. Retrieve the next 20 employees
I changed the offsetRows parameter to 40. This time, there are two additional data elements in the payload that have changed. Notice that the pageCount is 2. That’s because we have exhausted our employee table, and there were only 2 employees left to retrieve. Also, I see the hasMore data element tells me there are no more employees left to retrieve.
In the final example (Figure 10), I’m going to show what happens when I try to retrieve employees beyond the last row.

Figure 10. Retrieve employees beyond the last employee
I passed in an offset of 42. I know there are 42 rows in the employee table, so this will look for rows starting at row 43. Since there aren’t any employees to retrieve, the service returns an empty array, as expected.
Web services are a great way to make data available to both internal and external consumers, and being able to use pagination, or throttling, could help prevent performance issues and give the consumer a better experience.
Mike Larsen is a director of information technology at Auburn Pharmaceutical and has been working with IBM i systems for over 20 years. He specializes in RPG, CL, and SQL and recently has been working with PHP and Python. Current projects have given Mike the opportunity to work with generating and parsing XML and JSON from SQL and consuming SOAP and REST web services. Although his main area of expertise is on IBM i, Mike has a passion for learning other languages and how he can integrate other platforms with IBM i.
RELATED STORIES
Guru: Analyzing User Session Statistics, Part 2
Guru: Analyzing User Session Statistics, Part 1
Guru: Playing Sounds From An RPG Program
Guru: Creating An SQL Stored Procedure That Returns A Result Set
Guru: Creating An RPG Stored Procedure That Returns A Result Set
Guru: Creating An RPG Stored Procedure With Parameters
Guru: Creating A Web Service With Basic Authentication
Guru: Parsing JSON That Has Spaces In The Key
Guru: Partitioning Result Sets Using SQL
Guru: Comparing IFS Directories Using SQL
Guru: String Manipulation Using SQL
Guru: Regular Expressions, Part 1
Guru: Regular Expressions, Part 2
Guru: Debugging SQL Stored Procedures With ACS

