List user defined routines in Db2 database

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

0
There are no comments. Click here to write the first comment.