Stored procedures on IBM i (formerly AS/400) are reusable database routines that can be invoked via SQL. They come in two main types:
- SQL stored procedures: Written entirely in SQL Procedural Language (PSM), compiled into an underlying ILE C program with embedded SQL.
- External stored procedures: Registered SQL wrappers around external programs or service programs (e.g., written in RPG, COBOL, CL, or C). The "source" here is typically the CREATE PROCEDURE statement that registers the procedure, not the underlying program's code.
Retrieving involves extracting the DDL (Data Definition Language) to recreate the procedure, often using system catalogs or tools like Access Client Solutions (ACS). Rebuilding means dropping the existing procedure (if necessary) and recreating it using the retrieved DDL. This is useful for migration, performance optimization, or recovery after restores.
Important Notes:
- Always back up your system before dropping procedures.
- Use tools like IBM i Access Client Solutions (ACS) for a graphical interface, or Run SQL Scripts (STRSQL or ACS equivalent) for command-line execution.
- For SQL procedures created before IBM i 6.1, rebuilding can improve performance by dropping and recreating them.
- Procedures are stored in system catalogs like QSYS2.SYSPROCS (for metadata) and QSYS2.SYSROUTINE (for routine details).
Step 1: Identify the Stored Procedure Type and Details
Open ACS > Run SQL Scripts (or use STRSQL).
Replace 'YOUR_LIBRARY' with the schema (library) name. This lists all procedures in the schema. Note the ROUTINE_TYPE ('PROCEDURE'), EXTERNAL_LANGUAGE (e.g., 'SQL' for SQL procedures, 'RPGLE' for external RPG), and EXTERNAL_NAME (for external procedures).
Alternatively, use ACS: Navigate to Schemas > Expand your database > Schemas > Your library > Procedures.
This displays a list for browsing.
Here you can see the Stored Procedures we created in the earlier lessons of this module:
Step 2: Retrieving the Source (DDL) two ways
Use the QSYS2.GENERATE_SQL procedure to generate the CREATE PROCEDURE statement. This works for both SQL and external procedures, outputting DDL to a source physical file member. Then you
Or we can simply use the IBM i ACS Stored Procedure Tools - This is very much my preferred technique!
Having said that - lets show the good old manual technique first:
(a) Using SQL (Smelly Manual Command-Line Method)
Create a source physical file if needed (e.g., via CRTSRCPF FILE(YOURLIB/QDDLSRC)). Then call the procedure:
OBJECT_NAME => 'YOUR_PROCEDURE_NAME',
OBJECT_SCHEMA => 'YOUR_LIBRARY',
OBJECT_TYPE => 'PROCEDURE',
CREATE_OR_REPLACE_OPTION => 1, -- Use 1 for CREATE OR REPLACE syntax
TARGET_SOURCE_FILE => 'QDDLSRC', -- Target source file name
TARGET_LIBRARY => 'YOURLIB', -- Target library for the source file
TARGET_MEMBER => 'PROC_SRC' -- Target member name for the DDL output
);
For SQL procedures, this retrieves the full body (BEGIN...END block).
For external procedures, it retrieves the registration statement (e.g., CREATE PROCEDURE ... EXTERNAL NAME 'LIB/PGM').
You can now edit those source members and manually CREATE PROCEDURE to rebuild them.
or we can simply use IBM i ACS to do this and sip our cup of tea without any stress!
(b) Using ACS (Saucy Graphical Method)
In ACS, navigate to Schemas > Expand database > Schemas > Your library > Procedures. Select the procedure(s) using Ctrl or Shift for multiple selections. Right-click and choose Generate SQL > DDL.
In the Options tab, select the output format, such as including the schema or using CREATE OR REPLACE. Choose "Run SQL Scripts" as the output destination.
The DDL will open in a new Run SQL Scripts window, where you can save it to a file or copy it for later use.
If the procedure was created without source retention, which is common for external procedures, GENERATE_SQL can still reconstruct the DDL from catalogs. However, for external procedures, you need to retrieve the underlying program's source separately, such as using RTVxxxSRC commands like RTVRPGSRC for RPG programs.
Step 3: Rebuilding the Stored Procedure
Rebuilding involves dropping the existing procedure (optional, but recommended for cleanup or performance) and executing the retrieved DDL to recreate it.
(a) Using SQL (Smelly Manual Command-Line Method)
Dropping a Procedure
For specific overloads (same name, different parameters): Use DROP SPECIFIC PROCEDURE YOUR_LIBRARY.SPECIFIC_NAME (get SPECIFIC_NAME from SYSPROCS view).
Recreating the Procedure
Paste or run the retrieved DDL in Run SQL Scripts:
BEGIN
-- SQL body for SQL procedures
END;
Or for external (aka Program) stored procedures:
LANGUAGE RPGLE
EXTERNAL NAME 'YOUR_LIBRARY/YOUR_PROGRAM'
PARAMETER STYLE SQL;
Run the statement to recompile SQL procedures or re-register external ones. To verify, re-query the SYSPROCS table or call the procedure to ensure it's working as expected.
For external procedures, ensure the underlying program object exists and is not locked or in use. During creation, the system updates the Program Associated Space (PAS) in the program object for ILE programs compiled on V4R4M0 or later. If this fails (e.g., SQL7909 error), address issues like locks (using WRKOBJLCK) or recompile the program. After restore operations (e.g., RSTOBJ), if PAS is missing, recreate it using the provided DDL to update the catalogs and PAS.
(b) Using ACS (Saucy Graphical Method)
We can restore the DDL source into source files and recreate them using the ACS SQL tool, or we can even select all the ones we want to restore direct into the editor if we want to make quick fixes on the fly:
Now you can make all your edits, if needed, and recreate your stored procedures:
Tadaaaaa!