DB2查询全表外键子表及关联关系SQL报错求助

Hey there, let's get this foreign key relationship query working in DB2! The errors you're seeing (SQLCODE=-104 and SQLCODE=-727) are happening because you're using Oracle-style recursive syntax (CONNECT BY, PRIOR) which DB2 doesn't support. DB2 relies on recursive Common Table Expressions (CTEs) for hierarchical queries instead.

What's Wrong with the Original SQL? #

Your query mixes a CTE (root_parents) with Oracle's CONNECT BY clause—this is invalid in DB2. DB2 requires all recursive logic to be contained within the CTE definition using a UNION ALL between the base case and recursive case.

Corrected DB2-Compatible SQL #

Here's the rewritten query that uses DB2's recursive CTE syntax to get the foreign key parent-child chains:

WITH root_parents (root_parent_schema, root_parent_name) AS (
    -- Base case: tables with no parents but have children
    SELECT tabschema, tabname 
    FROM syscat.tables 
    WHERE parents = 0 AND children > 0
),
recursive_refs (root_schema, root_table, lvl, parent_schema, parent_table, child_schema, child_table, constname, chain) AS (
    -- Initial recursive step: link root parents to their direct children
    SELECT 
        rp.root_parent_schema,
        rp.root_parent_name,
        1 AS lvl,
        r.reftabschema,
        r.reftabname,
        r.tabschema,
        r.tabname,
        r.constname,
        VARCHAR(r.reftabname || '->' || r.tabname, 100) AS chain
    FROM syscat.references r
    JOIN root_parents rp 
        ON r.reftabschema = rp.root_parent_schema 
        AND r.reftabname = rp.root_parent_name
    UNION ALL
    -- Recursive step: traverse down to next level of children
    SELECT 
        rr.root_schema,
        rr.root_table,
        rr.lvl + 1 AS lvl,
        r.reftabschema,
        r.reftabname,
        r.tabschema,
        r.tabname,
        r.constname,
        VARCHAR(rr.chain || '->' || r.tabname, 100) AS chain
    FROM syscat.references r
    JOIN recursive_refs rr 
        ON r.reftabschema = rr.child_schema 
        AND r.reftabname = rr.child_table
)
-- Final select to format output
SELECT
    SUBSTR(root_table, 1, 11) AS root,
    SUBSTR(lvl, 1, 5) AS lvl,
    SUBSTR(parent_table, 1, 10) AS parent,
    SUBSTR(child_table, 1, 10) AS child,
    SUBSTR(constname, 1, 5) AS cnst,
    SUBSTR(chain, 1, 42) AS chain
FROM recursive_refs
ORDER BY root, lvl;

Key Changes Explained: #

• Recursive CTE (recursive_refs): Replaces the CONNECT BY logic. It starts with the root tables and recursively joins to syscat.references to build the parent-child chain.
• Level Tracking: Uses lvl to track the depth of each relationship.
• Chain Construction: Builds the relationship path incrementally in each recursive step instead of using SYS_CONNECT_BY_PATH.
• Schema Matching: Ensures we match both schema and table names (critical in environments with multiple schemas, like your 300+ DB setup).

How to Test: #

Run this query in your DB2 environment—you should no longer get the -104 or -727 errors, and it will return the hierarchical foreign key relationships you need.

内容的提问来源于stack exchange，提问作者Zhixian Chin