What is an IBM i Stored Procedure?
A stored procedure is a program stored in the IBM i database (DB2 for i). You call it from applications or SQL to perform tasks. It runs on the server, processes data, and returns results.
In more detail: An IBM i stored procedure is a user-defined program or routine stored in the IBM i system's database (DB2 for i) that can be called from a client application, another program, or a database interface to perform a specific task or set of operations. It encapsulates business logic or database operations, allowing for modularity, reusability, and improved performance in applications running on the IBM i platform.
Why Use Stored Procedures?
- A stored procedure is saved SQL code you can reuse.
- Use it when you run the same query often.
- It works like a function. You call it instead of rewriting the query.
- They are efficient and easy to execute: Save time by storing repeat queries.
- Use CREATE PROCEDURE to define it.
Types of Stored Procedures
SQL Stored Procedures: Use SQL with logic like loops and conditionals.
External Stored Procedures: Use languages like RPG or COBOL, linked to the database.
What Does a Stored Procedure Look like?
An SQL Stored procedure that will always return a list of all employees in the employee master file might need and SQL statement like this:
To create an SQL stored procedure on IBM i, use the CREATE PROCEDURE statement inside a SQL script or through a tool like ACS Run SQL Scripts. Here's a clean example:
LANGUAGE SQL
BEGIN
SELECT * FROM EMPLOYEE;
END;
Procedures with parameters might look like this:
LANGUAGE SQL
BEGIN
SELECT * FROM EMPLOYEE WHERE CITY = CITY_NAME;
END;