Working with SQL Stored Procedures on IBM i becomes much easier when you use IBM Data Studio. It gives you a modern interface, simple navigation, and a smooth way to create and manage SQL procedures. In this blog, we will walk through why SQL stored procedures are useful, how IBM Data Studio connects with the IBM i server, and how you can create and use these procedures—even from RPGLE programs.
Why We Use IBM Data Studio for SQL Stored Procedures
IBM data studio is used for SQL stored procedures primarily because it provides a dedicated, IDE purpose-built for IBM database like DB2 for i.
If offers features like syntax highlighting, auto-completion, and a built-in SQL editor that makes writing stored procedure code faster.
SQL stored procedures on IBM i can return result sets that can be easily accessed by languages like .NET, Java, or even external applications connected to IBM i. Stored procedures on IBM i are defined as:
- Object type: PGM
- Attribute: CLE
- Cataloged in system table: QSYS2/SYSPROCS
Connecting IBM Data Studio to IBM i
You can download IBM Data Studio from IBM’s official site. Once the installation is complete, launch IBM Data Studio and select a workspace before proceeding.
To connect to IBM i:
1. Go to the Data Source Explorer.
2. Right-click Database Connections → New Connection.
3. Choose DB2 for i.
4. Enter the server’s IP address, user ID, and password.
5. In Options, set:
· naming = system
· libraries = *LIBL
6. Test the connection.
7. Once successful, you are connected to your IBM i server.
Now you can view libraries, tables, stored procedures, etc. directly in IBM Data Studio.
How to Write an SQL Stored Procedure in IBM Data Studio
To create a stored procedure:
1. Select a Database Project.
2. Choose Stored Procedure.
3. A blank editor screen will open where you can type your SQL code.
You can also define how many results sets for the procedure will be returned. These results behave like temporary tables.
When you write the code, IBM Data Studio gives you helpful features like autocomplete, syntax coloring, and validation.
Example: Stored Procedure Implementation
The following example demonstrates how to implement a stored procedure on IBM i (Db2 for i).
This procedure accepts input parameters, performs a cursor-based retrieval, returns a result set to the caller, and invokes an error-handling routine when required.
Creating the Stored Procedure Object on IBM i
Once your code is ready, simply compile it using IBM Data Studio.
During compilation:
· Choose the library where the object should be stored.
· Include any required system libraries.
After the object is created, you can check it on IBM i using:
The system table QSYS2/SYSPROCS also stores information like:
· Procedure name
· Parameters (IN/INOUT)
· Library where it exists
This helps you verify that your stored procedure was created successfully.
Using the Stored Procedure
The stored procedure can be invoked from several sources, including:
- External systems
- Application Servers
- Web Services
- From RPGLE / SQLRPGLE Programs
SQLRPGLE programs can also call the stored procedure using:
This lets you combine RPGLE business logic with SQL power.
Conclusion
IBM Data Studio is a powerful yet simple IDE that can streamline stored procedure development with guided wizards, a syntax-aware SQL editor, and one click build & deploy. It reduces coding effort, improves efficiency, and supports modern development practices.
By connecting SQL Stored Procedures with RPGLE programs, organizations can:
· Improve data handling
· Speed up processing
· Simplify complex logic
· Work smoothly with external systems
IBM Data Studio also supports Git integration, function creation, and SQL file management. And the best part? You can have multiple database connections and assign different SQL files to different connections.