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互換機能を使用した実装例