List stored procedures in Db2 database

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


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,
from syscat.routines
where routinetype = 'P'
    and routineschema not like 'SYS%'
order by schema_name,


  • 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


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

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.