If you are working with stored procedures on your IBM i system, you know they are handy for encapsulating business logic and making your SQL calls more efficient. But what happens when you need to back them up or restore them after a hiccup? Today, I am walking you through the process in a straightforward way. We will cover the basics, the steps, and some tips to avoid common pitfalls.
First off, a quick refresher. Stored procedures on IBM i come in two flavors: SQL stored procedures, which are written in SQL and compile down to an ILE C program with embedded SQL, and external stored procedures, which wrap around existing programs or service programs in languages like RPG or COBOL. In both cases, they tie into system objects like *PGM or *SRVPGM, and their definitions live in the SQL catalogs in QSYS2.
Backing Up Stored Procedures
Backing up is pretty simple because stored procedures are linked to those underlying objects. The key is to save the program or service program objects, which carry the procedure details in something called the Program Associated Space or PAS. This space holds the external procedure info, so when you save the object, the definition comes along for the ride.
Step 1: Save the Objects
Use the SAVLIB command to back up the entire library where your procedures live. For example:
SAVLIB LIB(YOURLIB) DEV(TAP01)If you want to be more selective, use SAVOBJ for specific objects:
SAVOBJ OBJ(YOURPROC) LIB(YOURLIB) OBJTYPE(*PGM) DEV(TAP01)This grabs the *PGM or *SRVPGM and includes the PAS data. For external procedures, make sure the program existed when you created the procedure, or the PAS might not have the full info.
Step 2: Retrieve the DDL Source
To have a recoverable source for rebuilding, extract the CREATE PROCEDURE statements. This is gold for migrations or if things go sideways during restore.
My go-to method is IBM i Access Client Solutions (ACS) because it’s graphical and easy. Open ACS, go to Schemas, expand your database, then Schemas > Your Library > Procedures. Select your procedure, right-click, and pick Generate SQL > DDL. It pops the code into Run SQL Scripts for you to save or tweak.
If you prefer command-line, use the GENERATE_SQL procedure:
CALL QSYS2.GENERATE_SQL(
OBJECT_NAME => 'YOURPROC',
OBJECT_SCHEMA => 'YOURLIB',
OBJECT_TYPE => 'PROCEDURE',
CREATE_OR_REPLACE_OPTION => 1,
TARGET_SOURCE_FILE => 'QDDLSRC',
TARGET_LIBRARY => 'YOURLIB',
TARGET_MEMBER => 'PROC_SRC'
);This dumps the DDL into a source member. For SQL procs, you get the full body. For external, it’s the registration statement like CREATE PROCEDURE … EXTERNAL NAME ‘LIB/PGM’.
Remember – don’t be silly, just go and back up this DDL source separately. It’s your safety net.
Restoring Stored Procedures
Restoring flips the script. Use RSTLIB or RSTOBJ to bring back the objects. But watch out, the procedure might not auto-register if the PAS is missing or outdated. In that case, you will need to re-run the CREATE PROCEDURE from your retrieved DDL.
Step 1: Restore the Objects
For a full library:
RSTLIB SAVLIB(YOURLIB) DEV(TAP01) RSTLIB(YOURLIB)Or for a specific object:
RSTOBJ OBJ(YOURPROC) SAVLIB(YOURLIB) DEV(TAP01) OBJTYPE(*PGM)During restore, the system processes the PAS and updates the catalogs. If the library names differ between save and restore, the schema and external names might adjust automatically if they match the object’s library.
Step 2: Verify and Re-Register if Needed
After restore, check if the procedure shows up:
SELECT * FROM QSYS2.SYSPROCS WHERE SPECIFIC_SCHEMA = 'YOURLIB';If it’s missing or borked, drop any remnants with DROP PROCEDURE YOURLIB.YOURPROC and re-run your DDL in Run SQL Scripts.
Common issues? If you see SQL7909, it means the PAS was not updated properly during creation. Fix by ensuring the program is not locked (use WRKOBJLCK), is an ILE *PGM or *SRVPGM, and not in QSYS. Recompile if it’s from an old release like before V4R4M0.
For external procs, if you recompiled the program, drop and re-create the procedure to relink it.
Things to remember to avoid stormy weather
- Keep procedures and their programs in the same library. This makes restores cleaner and avoids name mismatches.
- Always create procedures when the external program exists to populate the PAS right.
- Test your backups. Restore to a test library and verify with a CALL statement.
- If you are dealing with overloads (same name, different parms), use specific names in your DROP and CREATE.
- For older SQL procs from before IBM i 6.1, rebuilding can boost performance.
- Back up regularly with a full system strategy, but focus on user libraries for your custom stuff.
There you have it, folks. Backing up and restoring stored procs on IBM i is not rocket science, but paying attention to the PAS and having that DDL handy saves headaches.
If you run into snags, mutter, moan, take a breath and double check everything. It’s easy to panic with stored procs but they really are quite straight forward.
