DBオブジェクトの依存関係確認方法

DBオブジェクトの依存関係確認方法について説明します。
DBオブジェクトの依存関係は「SYSCAT.ROUTINEDEP」カタログビューに格納されています。
正し、ストアドプロシージャ、ストアドファンクション等は、「SPECIFIC」句を指定しないと、ルーチンの名称がわかりにくくなるので指定することをお勧めします。
今回は、「SPECIFIC」句を指定したケースでの説明をします。

DBオブジェクトの作成

依存関係の表示を確認するため、テーブル、プロシージャ、ファンクションを作成します。
以下がサンプルSQLです。

CREATE TABLE TBL1
(
    COL1 INT
)
;

CREATE TABLE TBL2
(
    COL1 INT
)
;

CREATE OR REPLACE FUNCTION SF_FUNC1()
SPECIFIC SF_FUNC1
RETURNS INTEGER
LANGUAGE SQL
BEGIN

    DECLARE IN_COL1     INTEGER;

    SELECT COL1 INTO IN_COL1
      FROM TBL2
     FETCH FIRST 1 ROWS ONLY;

    RETURN IN_COL1;

END;
/

CREATE OR REPLACE PROCEDURE SP_PROC3()
SPECIFIC SP_PROC3
LANGUAGE SQL
BEGIN
END;
/

CREATE OR REPLACE FUNCTION SF_FUNC2()
SPECIFIC SF_FUNC2
RETURNS INTEGER
LANGUAGE SQL
BEGIN

    DECLARE IN_RET     INTEGER;

    SET IN_RET = SF_FUNC1();

    RETURN IN_RET;
END;
/


CREATE OR REPLACE PROCEDURE SP_PROC2()
SPECIFIC SP_PROC2
LANGUAGE SQL
BEGIN

    DECLARE IN_CNT     INTEGER;
    DECLARE IN_RET     INTEGER;

    SELECT COUNT(*) INTO IN_CNT
      FROM TBL2;

    SET IN_RET = SF_FUNC2();

    CALL SP_PROC3();

END;
/


CREATE OR REPLACE PROCEDURE SP_PROC1()
SPECIFIC SP_PROC1
LANGUAGE SQL
BEGIN
    DECLARE IN_CNT     INTEGER;
    DECLARE IN_RET     INTEGER;

    SELECT COUNT(*) INTO IN_CNT
      FROM TBL1;

    CALL SP_PROC2();

    SET IN_RET = SF_FUNC1();
END;
/ 

SQLの実行結果は以下の通りです。
これで準備が整いました。

SQL> CREATE TABLE TBL1
   2 (
   3     COL1 INT
   4 )
   5 ;

DB250000I: コマンドは正常に完了しました。

     : (省略)

SQL> CREATE OR REPLACE PROCEDURE SP_PROC1()
   2 SPECIFIC SP_PROC1
   3 LANGUAGE SQL
   4 BEGIN
   5     DECLARE IN_CNT     INTEGER;
   6     DECLARE IN_RET     INTEGER;
   7     SELECT COUNT(*) INTO IN_CNT
   8       FROM TBL1;
   9     CALL SP_PROC2();
  10     SET IN_RET = SF_FUNC1();
  11 END;
  12 / 

DB250000I: コマンドは正常に完了しました。
  

依存関係の取得

依存関係を取得するサンプルSQLです。

WITH RTN_HIERARCHY (ROUTINESCHEMA, SPECIFICNAME, BSCHEMA, BNAME, HIE_LV) AS (
     SELECT ROUTINESCHEMA, SPECIFICNAME, BSCHEMA, BNAME, 0 AS HIE_LV
       FROM SYSCAT.ROUTINEDEP
      WHERE ROUTINESCHEMA     = 'SMPLUSR'
        AND SPECIFICNAME      = 'SP_PROC1'    -- 親のプロシージャを指定
        AND BTYPE             IN ('F', 'T') -- F:ルーチン, T:テーブルを対象とする
      UNION ALL
     SELECT DEP.ROUTINESCHEMA, DEP.SPECIFICNAME, DEP.BSCHEMA, DEP.BNAME, HIE.HIE_LV + 1 AS HIE_LV
       FROM SYSCAT.ROUTINEDEP DEP, RTN_HIERARCHY HIE
      WHERE DEP.SPECIFICNAME  =  HIE.BNAME
        AND DEP.ROUTINESCHEMA =  'SMPLUSR'
        AND BTYPE             IN ('F', 'T') -- F:ルーチン, T:テーブルを対象とする
)
SELECT HIE.SPECIFICNAME AS SCHEMA, 
       HIE.BNAME        AS DEP_SCHEMA,
       HIE.HIE_LV
  FROM RTN_HIERARCHY HIE
; 

SQLの実行結果は以下の通りです。
依存関係が表示されました。
今回はここまで、SQLはもう少し改良の余地があるので機会があったら改良します。

SCHEMA             DEP_SCHEMA                HIE_LV
------------------ ------------------ -------------
SP_PROC1           SF_FUNC1                       0
SP_PROC1           SP_PROC2                       0
SP_PROC1           TBL1                           0
SF_FUNC1           TBL2                           1
SP_PROC2           SF_FUNC2                       1
SP_PROC2           SP_PROC3                       1
SP_PROC2           TBL2                           1
SF_FUNC2           SF_FUNC1                       2
SF_FUNC1           TBL2                           3

DB250000I: コマンドは正常に完了しました。