• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Guru: Creating PDF Documents With Python

    Guru: Creating Excel Spreadsheets With Python

    Share this:

    • Share on Reddit (Opens in new window) Reddit
    • Share on Facebook (Opens in new window) Facebook
    • Share on LinkedIn (Opens in new window) LinkedIn
    • Share on X (Opens in new window) X
    • Email a link to a friend (Opens in new window) Email

    Tags: Tags: 400guru, FHG, Four Hundred Guru, IBM i, JSON

    Sponsored by
    ARCAD Software

    AI is opening new possibilities for IBM i modernization, but for business-critical applications, performance alone is not enough: managing risks remains essential.

    How do BOB, Claude, and ARCAD Transformer RPG compare in converting RPG code to free-form RPG?

    Join Alan Ashley, Sr. Solutions Architect, as he presents a hands-on benchmark comparing these three solutions.

    Beyond conversion performance, discover how to minimize modernization risks through automated regression testing, validating application behavior and detecting unintended changes before they impact business operations.

    During this session, you’ll discover:

    • Compare code conversion performance: BOB vs. Claude vs. ARCAD Transformer RPG
    • Understand the role of deterministic automation alongside AI in a comprehensive IBM i modernization strategy
    • Accelerate RPG code transformation with predictable, rule-based processes
    • Reduce business risks through automated regression testing with ARCAD Verifier
    • Build a controlled, auditable modernization process that protects business-critical applications

    Register now for this webinar to discover how ARCAD Software’s deterministic modernization solutions complement AI initiatives, providing a structured, repeatable, and controlled approach to application transformation.

    Share this:

    • Share on Reddit (Opens in new window) Reddit
    • Share on Facebook (Opens in new window) Facebook
    • Share on LinkedIn (Opens in new window) LinkedIn
    • Share on X (Opens in new window) X
    • Email a link to a friend (Opens in new window) Email

    The GenAI Revolution And Product Shortages Send IT Budgets Skyward Big Blue Says AI Is Not Behind The Big Uptick In IBM i Security Alerts

    Leave a ReplyCancel reply

TFH Volume: 36 Issue: 40

This Issue Sponsored By

  • Maxava
  • FalconStor
  • ARCAD Software
  • Raz-Lee Security
  • WorksRight Software
  • WorksRight Software

Table of Contents

  • IBM i Delivers New Development Tooling With 2026 Fall Tech Refreshes
  • Big Blue Says AI Is Not Behind The Big Uptick In IBM i Security Alerts
  • Guru: Web Service With Pagination
  • The GenAI Revolution And Product Shortages Send IT Budgets Skyward
  • IBM i PTF Guide, Volume 28, Number 40
  • IBM i PTF Guide, Volume 28, Number 41

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM i Delivers New Development Tooling With 2026 Fall Tech Refreshes
  • Big Blue Says AI Is Not Behind The Big Uptick In IBM i Security Alerts
  • Guru: Web Service With Pagination
  • The GenAI Revolution And Product Shortages Send IT Budgets Skyward
  • IBM i PTF Guide, Volume 28, Number 40
  • IBM i PTF Guide, Volume 28, Number 41
  • Bob Code Assistant Gets Better Understanding Of IBM i Applications
  • IBM i In The Cloud And The Case For Reversing Replication
  • Guru: From Hiking Boots To Long-Term Memory
  • Inside IBM’s New PowerHA Cloud Offering, Full System Manager

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle