Create an IBM i RPGLE Stored Procedure

How I Create an IBM i RPGLE Stored Procedure

Welcome to a quick lesson on building an IBM i RPGLE stored procedure, this time using modern RPG code with native DB2 IO access.

Step 1: Plan Your Procedure

Let's start by outlining inputs, outputs, and result sets.

Just like our SQL example, we take an employee number as input and return a name and a city. If the employee hides, we handle that gracefully.

Step 2: Write the RPGLE Code

This RPGLE (Report Program Generator Language Extended) code demonstrates a simple stored procedure on IBM i (AS/400) systems. It's written in free-format RPG, which removes fixed-column constraints for better readability. The program acts as a database stored procedure to fetch employee details (name and city) from a file based on an input employee ID. If no match is found, it returns default messages.

This SQLRPGLE program is a clean, modular example of an IBM i stored procedure written in free-format RPG that retrieves employee details based on a given employee ID.

Think of this as a beginner-friendly example of using native I/O (input/output) in RPG to interact with physical files, similar to querying a database table but without SQL. 

For this example, we are reading all the rows in the file to find the one that matches the employee id - it's inefficient but good for this example! In real life you would use a keyed access path, a logical file, to chain the file.

Header and Control Options:

• **free: Enables free-format mode.
• Comments describe the program, purpose, and history (e.g., created in 2025).
• ctl-opt: Sets program-wide options:
  • main(mainline): Defines the entry point procedure.
  • optimize(*full): Optimizes for performance.
  • option(*nodebugio:*srcstmt:*nounref): Controls debugging and unused variable handling.
  • pgminfo(*pcml:*module): Generates PCML (Program Call Markup Language) for calling as a stored procedure.
  • actgrp(*new): Runs in a new activation group for isolation.
  • indent('| '): Sets indentation for generated listings.
  • alwnull(*usrctl): Allows user control over null values.
  • copyright(...): Adds a copyright notice.

  These options configure how the program compiles and runs, emphasizing it's designed as a callable module.

Procedure Definition:

• Dcl-Proc mainline;: Declares the main procedure.
• Dcl-Pi mainline;: Procedure interface (like parameters in other languages):
  • emp_id_in int(10) const;: Input parameter (constant integer for employee ID).
  • emp_name_out varchar(50); and city_out varchar(30);: Output parameters (variable-length strings).

This setup allows the procedure to be called like: CALL PROCEDURE_NAME(123, ?, ?)

where ? are output placeholders.

File and Data Declarations:

•  dcl-f EMPLO00001 usage(*input) rename(EMPLO00001:EMPLREC) usropn;: Declares a physical file (like a table) for input only. usropn means the program controls opening/closing. Renames the record format to EMPLREC.
• dcl-ds employee_master likerec(EMPLREC:*INPUT);: Defines a data structure matching the file's record layout for input fields (e.g., emp_id, emp_name, city).
• dcl-s success ind inz(*off);: An indicator (boolean-like) initialized to false.

Files in RPG are treated like datasets; this one assumes EMPLO00001 contains employee records.

Main Logic:

•  open EMPLO00001;: Opens the file for reading.
• dou %eof();: "Do Until End of File" loop.
  • read EMPLO00001 employee_master;: Reads the next record into the data structure.
  • if %found() and emp_id_in = employee_master.emp_id;: Checks if a record was read and if IDs match.
    • Sets success = *on; and leave; the loop if matched.
• After loop: If success, copies matched data to outputs; else, sets error messages.
• close EMPLO00001;: Closes the file.
• return;: Ends the procedure, passing outputs back.

Best Practices

RPG I/O Basics: RPG excels at file handling. Operations like open, read, close mimic database cursors. Built-ins like %eof() (end of file) and %found() (record found) simplify checks.

Stored Procedures in RPG: By using pgminfo(*pcml), this can be registered as an SQL stored procedure (e.g., via CREATE PROCEDURE). It's great for encapsulating business logic.

Error Handling: Basic here (just a success flag and defaults). Add exceptions or SQLSTATE for production.

Improvements: For efficiency, add keys to the file or switch to embedded SQL (e.g., EXEC SQL SELECT ... INTO ...)

Step 3: Compile the Code

We should compile this - Run CRTBNDRPG

If errors pop up, we fix them before blaming the copy/paste, me or your keyboard. 

So, now we have an RPGLE program that can be called from anywhere. It will consume that single parameter of employee id number and return the name and city of the employee, if found. The obvious question is - how do we register this program as a stored procedure?

Step 4: Register in SQL

Now we need to use SQL, and the CREATE PROCEDURE statement to add our stored procedure and to make it official.

NOTE I am qualifying the schema name (NICKLITTEN) against the long STORED PROCEDURE NAME (GET_EMPLOYEES_SQL) and the RPGLE Program name (STOREPRCS):

Step 5: Call the Procedure

From SQL, we run CALL NICKLITTEN.GET_EMPLOYEES_RPG ( 12345 , ? , ? ) 

The ? grabs the output like a catcher’s mitt.

Let's test this using a similar script, we generated for the testing lesson previously. This is a little more friendly - if you want to test with different employee number simply change the value of the V_EMP variable.

Remember to use our new procedure name: GET_EMPLOYEES_RPG

If I paste this into my IBM i ACS screen, I see this:

Excellent!