List all IBM i stored procedures

Where do IBM i Stored Procedures live?

Stored procedures on IBM i are stored in libraries (schemas) as part of the system's database objects. SQL stored procedures are cataloged in the QSYS2.SYSROUTINES view.

To list all stored procedures on IBM i, run this SQL query against the system catalog, this pulls from QSYS2.SYSROUTINES, which is the catalog view for procedures.

select ROUTINE_SCHEMA as SCHEMA_NAME,
       ROUTINE_NAME as PROCEDURE_NAME,
       ROUTINE_TYPE,
       ORIGIN,
       IN_PARMS,
       OUT_PARMS,
       ROUTINE_CREATED
  from QSYS2.SYSROUTINES
  where ROUTINE_TYPE like 'PROCEDURE'
  order by SCHEMA_NAME,
PROCEDURE_NAME;

What this does:

ROUTINESCHEMA: shows the library where the procedure is stored.
ROUTINENAME: shows the procedure name.
ORIGIN: tells you if it's SQL-bodied or external.
IN_PARMS: how many input parameters.
OUT_PARMS: how many return parameters.
ROUTINE_CREATED: creation date (duh!)

If you are using green screen the results might look like this:

List all IBM i stored procedures 1

List all IBM i stored procedures 2

Deep down, even you stick-in-the-mud green screen developers know that this kind of sucks!

Let's do the same thing with IBM i ACS SQL screen:

sample ACS SQL screen listing all ibm i sql procedures

Simply looking this list makes a lot of sense right?

Do you want to know how to program your own Stored Procedures? Check out the Stored Procs Programming Module here

Done?

Mark lesson complete

Previous Lesson