Guru: SQL Sequences In RPG Let Db2 Handle The Counting

June 1, 2026 Gregory Simmons

There is something deeply satisfying about letting the database do the counting for you. In a world where we have spent decades hand-rolling identifiers, guarding them with locks, and hoping no job collides with another, SQL sequences feel like discovering a patch of mushrooms that quietly regenerate overnight. You stop worrying about scarcity and start focusing on what matters.

In a procedure driven RPG system, this is exactly the kind of responsibility we want to isolate. Generating a new identifier is not business logic. It is not validation. It is not formatting. It is a single, well-defined action that deserves a single, well-defined home.

Let’s build that home.

We will start in the database, where the responsibility truly belongs. A mushroom catalog needs a unique identifier for each specimen we record. Rather than storing and incrementing a value manually, we create a sequence object that guarantees uniqueness and concurrency safety.

Here is a simple example of creating a sequence named Msh_Cat_Id in your library:

CREATE SEQUENCE MYLIB.Msh_Cat_Id
    AS BIGINT
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO CYCLE
    CACHE 20;

This definition tells Db2 to generate a never-ending stream of numbers, starting at one and increasing by one each time it is requested. The cache improves performance by reducing trips to disk, which matters when your application grows and many jobs are harvesting identifiers at the same time.

Now that the database owns the responsibility of generating identifiers, we turn to RPG. Our goal is not to embed this logic everywhere it is needed. That would scatter responsibility and make future change painful. Instead, we wrap access to the sequence in a single procedure whose only job is to retrieve the next value.

This is where procedure driven RPG shines. The procedure does one thing. Its name makes that thing obvious. And every caller benefits from the same consistent behavior.

Below is a minimal SQLRPGLE example of such a procedure:

dcl-proc Get_Next_Mushroom_Catalog_Id export;


  dcl-pi Get_Next_Mushroom_Catalog_Id packed(15:0);
  end-pi;

  dcl-s next_Id packed(15:0);

  Exec sql
    VALUES NEXT VALUE FOR MYLIB.Msh_Cat_Id
    INTO :next_Id;

  return next_Id;

end-proc Get_Next_Mushroom_Catalog_Id;

There is no extra logic here because none is needed. The procedure is a thin, intentional wrapper over the sequence. It does not validate, transform, or interpret. It simply retrieves. This simplicity is not a lack of design. It is the result of good design. By resisting the urge to do more, we preserve clarity. If requirements change tomorrow, perhaps the identifier needs a prefix or comes from a different source, we know exactly where to go. One procedure. One responsibility.

Using the procedure is equally straightforward.

dcl-s catalogId packed(15:0);

catalogId = GetNextMshCatId();

That single line replaces what used to require a data area, a lock, error handling, and a quiet sense of unease about concurrency. Now the database guarantees correctness, and the procedure guarantees consistency of access.

This pairing of SQL sequences with small, focused RPG procedures is a perfect example of how modern capabilities fit naturally into a procedure driven approach. The database handles what it is best at. RPG orchestrates through clear, intention revealing procedures.

Much like a healthy mushroom network beneath the forest floor, the strength of the system is not in any one piece, but in how cleanly each piece connects to the next.

Until next time, happy coding.

Gregory Simmons is a Project Manager with PC Richard & Son. He started on the IBM i platform in 1994, graduated with a degree in Computer Information Systems in 1997 and has been working on the OS/400 and IBM i platform ever since. He has been a registered instructor with the IBM Academic Initiative since 2007, an IBM Champion and holds a COMMON Application Developer certification. When he’s not trying to figure out how to speed up legacy programs, he enjoys speaking at technical conferences, running, backpacking, hunting, and fishing.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search