select * from SYSCAT.ROUTINEDEP r
where r.BSCHEMA = 'имя схемы'
and r.ROUTINENAME = 'имя процедуры'

select p.*
from SYSCAT.ROUTINEDEP r
join syscat.packagedep p on r.bschema=p.pkgschema and r.bname=p.pkgname
where r.BSCHEMA = 'имя схемы'
and r.ROUTINENAME = 'имя процедуры'
and r.btype='K'

select r.TEXT
from SYSCAT.ROUTINES r
where r.ROUTINESCHEMA = 'схема'
and r.ROUTINENAME = 'имя процедуры'

create table MY_SCHEMA.TEST_PROC_TAB (id smallint);

insert into MY_SCHEMA.TEST_PROC_TAB values  1 , 2 , 3 ;


CREATE PROCEDURE MY_SCHEMA.TEST_PROC()
    SPECIFIC TEST_PROC
    RESULT SETS  1 
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL
BEGIN
  DECLARE OUT_CURSOR CURSOR WITH RETURN FOR
  select * from MY_SCHEMA.TEST_PROC_TAB;
  
  OPEN OUT_CURSOR;
END
;

CREATE PROCEDURE MY_SCHEMA.TEST_PROC_1()
    SPECIFIC TEST_PROC_1
    RESULT SETS  1 
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL
BEGIN
   IF ( 1  =  2 ) THEN
     DECLARE GLOBAL TEMPORARY TABLE SESSION.t_temp(i smallint) NOT LOGGED ;
   END IF;

 P: BEGIN
  DECLARE OUT_CURSOR CURSOR WITH RETURN FOR
  select a.* from MY_SCHEMA.TEST_PROC_TAB a
  left join SESSION.t_temp t
  on a.id = t.i;

  OPEN OUT_CURSOR;
 END P; 
END
;


select p.* from SYSCAT.ROUTINEDEP d
join SYSCAT.ROUTINES r on d.ROUTINENAME = r.SPECIFICNAME
join syscat.packagedep p on d.bschema=p.pkgschema and d.bname=p.pkgname
and d.ROUTINESCHEMA = r.ROUTINESCHEMA
where r.ROUTINESCHEMA = 'MY_SCHEMA'
and  r.ROUTINENAME = 'TEST_PROC';

select p.* from SYSCAT.ROUTINEDEP d
join SYSCAT.ROUTINES r on d.ROUTINENAME = r.SPECIFICNAME
join syscat.packagedep p on d.bschema=p.pkgschema and d.bname=p.pkgname
and d.ROUTINESCHEMA = r.ROUTINESCHEMA
where r.ROUTINESCHEMA = 'MY_SCHEMA'
and  r.ROUTINENAME = 'TEST_PROC_1';

drop procedure MY_SCHEMA.TEST_PROC;

drop procedure MY_SCHEMA.TEST_PROC_1;

drop table MY_SCHEMA.TEST_PROC_TAB;