Query below return all stored procedures and information about it in Db2 database.
Query
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,
language,
text
from syscat.routines
where routinetype = 'P'
and routineschema not like 'SYS%'
order by schema_name,
procedure_name;
Columns
- 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
Rows
- One row - represents one procedure
- Scope of rows: - all procedures in database
- Ordered by - schema name and procedure name