DB2中几个递归语句的案例

DB2递归算法：
1、查找某个作业的下游作业：
WITH GET_STREAM_JOB(LEVEL,ETL_SYSTEM,ETL_JOB) AS (
SELECT 0，ETL_SYSTEM,ETL_JOB FROM PETL.ETL_JOB_STREAM WHERE ETL_JOB='XXX' 
UNION ALL
SELECT LEVEL+1,A.STREAM_SYSTEM,A.STREAM_JOB FROM PETL.ETL_JOB_STREAM A,GET_STREAM_JOB B
WHERE A.ETL_SYSTEM=B.ETL_SYSTEM AND A.ETL_JOB=B.ETL_JOB
)SELECT * FROM PETL.ETL_JOB WHERE (ETL_SYSTEM,ETL_JOB) IN (SELECT ETL_SYSTEM,ETL_JOB FROM GET_STREAM_JOB)
;
2、查找某个作业的的所有下游作业
WITH GET_STREAM_JOB(LEVEL,ETL_SYSTEM,ETL_JOB) AS (
SELECT 0，ETL_SYSTEM,ETL_JOB FROM PETL.ETL_JOB_DEPENDENCY WHERE DEPENDENCY_JOB='XXX' AND DEPENDENCY_SYSTEM='XXX'
UNION ALL
SELECT LEVEL+1，A.ETL_SYSTEM,A.ETL_JOB FROM PETL.ETL_JOB_DEPENDENCY A,GET_STREAM_JOB B WHERE A.DEPENDENCY_SYSTEM=B.ETL_SYSTEM 
AND A.DEPENDENCY_JOB =B.ETL_JOB
)SELECT MIN(T2.LEVEL),T1.ETL_SYSTEM,T1.ETL_JOB,T1.DESCRIPTION,T1.LAST_JOBSTATUS,T1.JOBTYPE FROM PETL.ETL_JOB T1,GET_STREAM_JOB T2
WHERE T1.ETL_SYSTEM=T2.ETL_SYSTEM AND T1.ETL_JOB=T2.ETL_JOB AND T1.JOBTYPE<>'M'
GROUP BY T1.ETL_SYSTEM,T1.ETL_JOB,T1.DESCRITTION,T1.LAST_JOBSTATU,T1.JOBTYPE
ORDER BY MIN(T2.LEVEL)
;