Query below return all User Defined Routines and information about it in IBM DB2 database.

Query

select routineschema as routine_schema,
       routinename as routine_name,
       case routinetype
            when 'F' then 'Function'
            when 'M' then 'Method'
            when 'P' then 'Procedure'
            end as routine_type,
       case origin
            when 'E' then 'User-defined, external'
            when 'F' then 'Federated procedure'
            when 'U' then 'User-defined, based on a source'
            when 'M' then 'Template function'
            when 'Q' then 'SQL-bodied'
            end as origin,
       parm_count as parameters,
       language,
       case functiontype
            when 'C' then 'COLUMN OR AGGREGATE'
            when 'R' then 'ROW'
            when 'S' then 'SCALAR'
            when 'T' then 'TABLE'
            end as function_type,
       return_typename,
       text
from syscat.routines
where 
    routineschema not like 'SYS%'
order by routine_schema,
         routine_name;

Columns

• routine_schema - name of the routine's schema
• routine_name - name of the routine
• routinetype -
  • Function
  • Method
  • Procedure
• origin -
  • User-defined, external
  • Federated procedure
  • User-defined, based on a source
  • Template function
  • SQL-bodied
• parameters - number of function parameters
• language - language in which function is written
• function_type -
  • COLUMN OR AGGREGATE
  • ROW
  • SCALAR
  • TABLE
• return_typename - routine return type for a scalar function or method.
• text - full text of routine definition if language is SQL, otherwise NULL

Rows

• One row: represents one function
• Scope of rows: all user defined functions in database
• Ordered by: routine schema, routine name

Sample results