/*
SYSIBM.SYSDEPENDENCIES 可以來找出 Stored Procedure 使用了哪些 Tables.
但遇到 dynamic sql 當然就沒辦法這樣找了.
*/
-- 建立測試 Tables & 資料
CREATE TABLE A1
(
COL1 INTEGER,
COL2 VARCHAR(32)
) IN USERSPACE1;
CREATE TABLE F1
(
COL1 INTEGER NOT NULL PRIMARY KEY,
COL2 VARCHAR(32)
) IN USERSPACE1;
INSERT INTO A1 VALUES (10,'測試1'),(10,'測試2'),
(10,'測試3'),(20,'測試4'),
(20,'測試5'),(20,'測試6'),
(30,'測試7'),(30,'測試8'),
(30,'測試9'),(30,'測試10'),
(30,'測試11');
INSERT INTO F1 VALUES (10,'代碼10'),
(20,'代碼20'),
(30,'代碼30');
-- 建立測試 Procedure
CREATE OR REPLACE PROCEDURE SP_TEST
SPECIFIC SP_TEST
LANGUAGE SQL
BEGIN
IF EXISTS (SELECT 1 FROM A1
WHERE COL1 NOT IN (SELECT COL1 FROM F1)) THEN
INSERT INTO F1
SELECT COL1,'代碼' || RTRIM(CHAR(COL1))
FROM A1
WHERE COL1 NOT IN (SELECT COL1 FROM F1);
END IF;
END
-- 檢查關聯性
SELECT T.DNAME,A.BNAME
FROM SYSCAT.PACKAGEDEP A,
(SELECT BNAME,DNAME FROM SYSIBM.SYSDEPENDENCIES
WHERE DSCHEMA = 'ADMINISTRATOR'
AND DNAME = 'SP_TEST') T
WHERE BTYPE = 'T' AND PKGNAME = T.BNAME;
DNAME BNAME
------------------------------- -----------------
SP_TEST F1
SP_TEST A1
已選取 2 個記錄。
-- 再建立另一測試 Procedure
CREATE OR REPLACE PROCEDURE SP_TEST2
SPECIFIC SP_TEST2
LANGUAGE SQL
BEGIN
DECLARE SZSTR VARCHAR(1024);
SET SZSTR = 'INSERT INTO A1 ' ||
'SELECT COL1,''代碼'' ||
RTRIM(CHAR(COL1)) ' ||
'FROM (SELECT MAX(COL1) + 10 COL1 FROM A1) T';
EXECUTE IMMEDIATE SZSTR;
END
-- 檢查關聯性，SP_TEST2 並未被 SELECT 出來
SELECT T.DNAME,A.BNAME
FROM SYSCAT.PACKAGEDEP A,
(SELECT BNAME,DNAME FROM SYSIBM.SYSDEPENDENCIES
WHERE DSCHEMA = 'ADMINISTRATOR'
) T
WHERE BTYPE = 'T' AND PKGNAME = T.BNAME;
DNAME BNAME
------------------------------- -----------------
SP_TEST F1
SP_TEST A1
已選取 2 個記錄。
-- 只好先用 Procedure 的程式內容來查
SELECT ROUTINENAME FROM SYSIBM.SYSROUTINES
WHERE ROUTINESCHEMA = 'ADMINISTRATOR'
AND TEXT LIKE '%A1%'
ROUTINENAME
------------------------------------------------
SP_TEST
SP_TEST2
已選取 2 個記錄。
-- 案子小也就算了，大案子就沒辦法這樣查了....
