List stored procedures in Db2 database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-30

Table of Contents:


    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

    Sample results

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