DB2DBA: 再帰クエリで参照制約の階層構造を出力する

イマドキのSQLでDB管理を楽にしましょう。

目的

DB2 for LUWでは参照制約に関する情報はカタログビューSYSCAT.REFERENCESから確認できますが、そのままでは1対1の親子関係の羅列なので3階層以上の親子関係を把握するのが大変です。

そこで、共通表式の再帰クエリでSYSCAT.REFERENCESから全ての表の親子関係を出力するSQLを作成してみました。

共通表式・再帰クエリとは

ここでは改めて解説はしませんので、以下の記事等を参考にしてください。

• @ IT
  • 新しい業界標準「SQL99」詳細解説
    • 第一章　高度なデータ操作（2）

実装のポイント

• SYSCAT.REFERENCESのREFTABNAME（親表名）とTABNAME（子表名）を参照する
• REFTABNAMEに存在し、TABNAMEに存在しないものを探索のROOTとする
  • ただし、自己参照のケースが除外されないようにする
• 子表のテーブル名を連結していくことでを階層構造を表現する
• インクリメントされる定数をチェックすることで無限ループを防止する
  • 無限ループの可能性がある書き方の場合、DB2では警告が出力される
• DB2では再帰クエリの場合でもRECURSIVEの指定は不要

サンプルコード

WITH cte
(
    degree
    ,reftabschema
    ,reftabname
    ,tabschema
    ,tabname
    ,path
)
AS
(
    SELECT DISTINCT
        1 AS degree
        ,reftabschema
        ,reftabname
        ,tabschema
        ,tabname
        ,RTRIM(reftabschema) || '.' || RTRIM(reftabname) || ' -> ' || RTRIM(tabschema) || '.' || RTRIM(tabname) AS path
    FROM
        syscat.references
    WHERE
        reftabschema NOT IN                          -- システムオブジェクトを除外
        ( 
            'SYSIBM'
            ,'SYSIBMADM'
            ,'SYSIBMINTERNAL'
            ,'SQLJ'
            ,'SYSCAT'
            ,'SYSFUN'
            ,'SYSPROC'
            ,'SYSPUBLIC'
            ,'SYSSTAT'
            ,'SYSTOOLS'
            ,'NULLID'
            ,'NEWTON'
            ,'OBSIEGER'
        )
        AND (reftabschema, reftabname) NOT IN       -- 子表とならないものをROOTとする
        (
            SELECT
                tabschema
                ,tabname
            FROM
                syscat.references
            WHERE
                (tabschema, tabname) <> (reftabschema, reftabname) -- 自己参照を除外しない
        )

    UNION ALL

    SELECT
        cte.degree + 1 AS degree
        ,ref.reftabschema
        ,ref.reftabname
        ,ref.tabschema
        ,ref.tabname
        ,cte.path || ' -> ' || RTRIM(ref.tabschema) || '.' || RTRIM(ref.tabname) AS path
    FROM
        cte
        ,syscat.references AS ref
    WHERE
        cte.degree <= 8                                                        -- 深さ上限での打ち切り
        AND (ref.reftabschema, ref.reftabname) = (cte.tabschema, cte.tabname)
        AND (cte.reftabschema, cte.reftabname) <> (cte.tabschema, cte.tabname) -- 自己参照の場合打ち切り

)


SELECT
    substr(path, 1, 128) AS foreign_key_path
FROM
    cte
ORDER BY
    path
;

なお、このSQLでは参照制約が循環しているものは抽出できません。必要であれば、こちらの例でストアドプロシージャとOracle互換機能で実現しているので参考にしてください。

出力イメージ

FOREIGN_KEY_PATH
--------------------------------------------------------------------------------------------------------------------------------
SVC34.CUSTOMER -> SVC34.ORDER
SVC34.CUSTOMER -> SVC34.ORDER -> SVC34.ORDER_DETAIL
SVC34.CUSTOMER -> SVC34.ORDER -> SVC34.SHIPMENT
SVC34.PRODUCT -> SVC34.CATALOG
SVC34.PRODUCT -> SVC34.ORDER_DETAIL
SVC34.DEPARTMENT -> SVC34.DEPARTMENT
SVC34.DEPARTMENT -> SVC34.EMPLOYEE

  7 レコードが選択されました。

このようなSQLは、共通表式およびメタデータを提供する管理ビューが提供されていれば他のRDBMSでも同じように実装することが可能です。これまで共通表式がサポートされていなかったMySQLでも、MySQL 8.0のLAB版で実装されました。

参考資料

• @ IT
  • 新しい業界標準「SQL99」詳細解説
    • 第一章　高度なデータ操作（2）
• IBM Knowledge Center
  • DB2 for Linux UNIX and Windows 11.1.0
    • SYSCAT.REFERENCES
    • common-table-expression
    • 再帰の例: 部品表
• IBM developerWorks
  • Tips for using SQL to query foreign key relationships
    • ストアドプロシージャとOracle互換機能を使用した実装例