SQL交叉引用

Question

如果我要对一个对象(可能是一个表)进行更改，我想通过系统编目表找出该对象被引用/使用的位置。那么用什么CatLog系统表来检查存储过程呢?函数，触发器，约束。我错过了什么吗?

提前感谢您的帮助，JemRug

Answer 1

要查找引用表的函数和过程，可以扫描sysibm.routines视图的routine_definition列以查找表名。使用regexp_instr函数查找表名后面的模式FROM|UPDATE|INSERT INTO

with t1 as (                                                       
SELECT char(ROUTINE_SCHEMA,10) libname,                            
char(ROUTINE_NAME,30) routine_name,                         
cast(a.routine_definition as varchar(9999)) routine_defn    
FROM   sysibm.routines a                                           
where  routine_schema = 'YOURLIB'                                   
),  t2 as (                                                      
select  a.routine_name,                                            
regexp_instr(a.routine_defn,                                    
'(FROM|UPDATE|INSERT INTO)s+YOUR_TABLE',1,1) pos,             
a.routine_defn                                                    
from t1 a )                                                        
select  a.routine_name,  a.pos, substr(a.routine_defn,pos,20) text 
from      t2 a                                                     
where    a.pos > 0                                                 

Answer 2

可以使用IBM函数RELATED_OBJECTS SQL

https://www.ibm.com/docs/en/i/7.3?topic=services-related-objects-table-function