List stored procedure parameters in Db2 database

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


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,
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,


  • 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


  • One row - represents one function/procedure parameter
  • Scope of rows: - all parameters for routines
  • Ordered by - schema name, 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.