Edited at

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

More than 1 year has passed since last update.

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


目的

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

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


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

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


実装のポイント


  • 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版で実装されました。


参考資料