Find objects used by specific stored procedure in Db2 database

Query below return all objects used by procedures in Db2 database.

Query

select r.routineschema as schema_name, 
       r.routinename as procedure_name,
       bschema as ref_object_schema, 
       bname as ref_object_name,
       case btype
            when 'A' then 'Table alias'
            when 'B' then 'Trigger'
            when 'F' then 'Routine'
            when 'G' then 'Global temporary table'
            when 'H' then 'Hierarchy table'
            when 'K' then 'Package'
            when 'L' then 'Detached table'
            when 'N' then 'Nickname'
            when 'O' then 'Privilege dependency'
            when 'Q' then 'Sequence'
            when 'R' then 'User-defined data type'
            when 'S' then 'Materialized query table'
            when 'T' then 'Table'
            when 'U' then 'Typed table'
            when 'V' then 'View'
            when 'W' then 'Typed View'
            when 'X' then 'Index extension'
            when 'Z' then 'XSR object'
            when 'q' then 'Sequence alias'
            when 'u' then 'Module alias'
            when 'v' then 'Global variable'
            when '*' then 'Anchored to the row of a base table'
            end as ref_object_type
from syscat.routinedep rd
join syscat.routines r
     on rd.routineschema = r.routineschema
     and rd.specificname = r.specificname
where r.routinetype = 'P'
      and r.routineschema not like 'SYS%'
      --and r.routineschema = 'schema_name' -- put schema name here
      --and r.routinename = 'procedure_name' -- put procedure name here
order by schema_name,
         procedure_name;

Columns

  • schema_name - schema name
  • procedure_name - provided procedure name
  • ref_object_schema - schema name of the referenced object
  • ref_object_name - name of the referenced object
  • ref_object_type - type of referenced object

Rows

  • One row represents one referenced object by provided procedure
  • Scope of rows: all objects that are used by procedure in database
  • Ordered by schema name and name of referenced object

Sample Result

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.