List stored procedure parameters in Db2 database

Query below return all parameters of stored procedures and details about them in Db2 database.

Query

select proc.routineschema as schema_name,
       proc.routinename as procedure_name,
       case proc.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,
       param.parmname as parameter_name,
       typename as data_type,
       length,
       scale
from syscat.routines proc
left join syscat.routineparms param
          on proc.routineschema = param.routineschema
          and proc.specificname = param.specificname
where proc.routinetype = 'P'
order by schema_name,
         procedure_name;

Columns

  • schema_name - name of the database (schema) containing routine
  • procedure_name - name of the function/procedure
  • origin -
    • User-defined, external
    • Federated procedure
    • User-defined, based on a source
    • SQL-bodied
  • parameter_name - parameter name
  • data_type - data type of parameter
  • length - maximum length of a datatype
  • scale - scale of numeric data type or number of digits of fractional seconds for timestamp data type

Rows

  • One row - represents one function/procedure parameter
  • Scope of rows: - all parameters for routines
  • Ordered by - schema name, procedure name

Sample results