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

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

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

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

# 共通表式・再帰クエリとは
ここでは改めて解説はしませんので、以下の記事等を参考にしてください。

- @ IT
    - 新しい業界標準「SQL99」詳細解説
        - [第一章　高度なデータ操作（2）](http://www.atmarkit.co.jp/fnetwork/tokusyuu/01sql99/sql99_1b.html)


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

# サンプルコード

```sql
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では参照制約が循環しているものは抽出できません。必要であれば、[こちらの例](https://www.ibm.com/developerworks/data/library/techarticle/dm-1301foreignkey/)でストアドプロシージャと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版で実装されました。](http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/)



# 参考資料
- @ IT
    - 新しい業界標準「SQL99」詳細解説
        - [第一章　高度なデータ操作（2）](http://www.atmarkit.co.jp/fnetwork/tokusyuu/01sql99/sql99_1b.html)
- IBM Knowledge Center 
    - DB2 for Linux UNIX and Windows 11.1.0
        - [SYSCAT.REFERENCES](http://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001057.html)
        - [common-table-expression](http://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0059217.html)
        - [再帰の例: 部品表](http://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0059242.html)
- IBM developerWorks
    - [Tips for using SQL to query foreign key relationships](https://www.ibm.com/developerworks/data/library/techarticle/dm-1301foreignkey/)
        - ストアドプロシージャとOracle互換機能を使用した実装例