List stored procedures in Db2 database

Query below return all stored procedures and information about it in Db2 database.

Query

select routineschema as schema_name,
       routinename as procedure_name,
       case origin
            when 'E' then 'User-defined, external'
            when 'F' then 'Federated procedure'
            when 'U' then 'User-defined, based on a source'
            when 'Q' then 'SQL-bodied'
            end as origin,
       parm_count as parameters,
       language,
       text
from syscat.routines
where routinetype = 'P'
    and routineschema not like 'SYS%'
order by schema_name,
         procedure_name;

Columns

  • schema_name - name of the database (schema)
  • procedure_name - name of the function/procedure
  • origin -
    • User-defined, external
    • Federated procedure
    • User-defined, based on a source
    • SQL-bodied
  • parameters - name of parameters with their data type separated by comma ','
  • language - implementation language for the procedure body
  • text - definition of the SQL statement executed by the function/procedure

Rows

  • One row - represents one procedure
  • Scope of rows: - all procedures in database
  • Ordered by - schema name and procedure name

Sample results