Recursive Select with DB2
Posted: Tue Mar 15, 2011 10:17 am
I need to generate N records when reading 1 record in a DB2 table (N = value in a column of the table). In this forum, I have seen solutions with Transformer loop (unfortunately with Datastage 8.5), or with Oracle Connect by (while it is DB2 in my case).
WITH N (LEVEL, FREQ_SKEY, FREQ_STARTTIME) AS
(SELECT 1 AS LEVEL, FREQ_SKEY, FREQ_STARTTIME
FROM DSADM.F_REQUEST WHERE FREQ_SKEY = 7151
UNION ALL
SELECT N.LEVEL + 1, NPLUS1.FREQ_SKEY, NPLUS1.FREQ_STARTTIME
FROM DSADM.F_REQUEST AS NPLUS1, N
WHERE LEVEL < FREQ_MAX_RUNTIME
AND N.FREQ_SKEY = NPLUS1.FREQ_SKEY)
SELECT LEVEL, FREQ_SKEY, FREQ_STARTTIME
FROM N
For example, this query returns 6 records (= FREQ_MAX_RUNTIME).
The Select works with the DB2 Control Center user interface, but when copying it to Datastage in a DB2/UDB Enterprise stage in a User-defined SQL, I got an error with View data, and when running the job.
DB2Driver Embedded SQL message: SQL0347W The recursive common table expression "DSADM.N" may contain an
infinite loop. SQLSTATE=01605
;
sqlcode = 347;
sqlstate = 01605
##W IIS-DSEE-TNDB-00000 17:12:34(002) <F_REQUEST> Prepare failed
>##E IIS-DSEE-TOPK-00007 17:12:34(006) <_PEEK_IDENT_> Input dataset does not have field: "LEVEL".
>##E IIS-DSEE-TOPK-00007 17:12:34(007) <_PEEK_IDENT_> Input dataset does not have field: "FREQ_SKEY".
>##E IIS-DSEE-TOPK-00007 17:12:34(008) <_PEEK_IDENT_> Input dataset does not have field: "FREQ_STARTTIME".
>##E IIS-DSEE-TFSR-00019 17:12:34(010) <main_program> Could not check all operators because of previous error(s)
##W IIS-DSEE-TFIP-00093 17:12:34(011) <F_REQUEST> When checking operator: The modify operator has a binding for the non-existent output field "LEVEL".
##W IIS-DSEE-TFIP-00093 17:12:34(012) <F_REQUEST> When checking operator: The modify operator has a binding for the non-existent output field "FREQ_SKEY".
##W IIS-DSEE-TFIP-00093 17:12:34(013) <F_REQUEST> When checking operator: The modify operator has a binding for the non-existent output field "FREQ_STARTTIME".
##W IIS-DSEE-TFIP-00096 17:12:34(014) <F_REQUEST> When checking operator: The modify operator keeps field "LEVEL" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 17:12:34(015) <F_REQUEST> When checking operator: The modify operator keeps field "FREQ_SKEY" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 17:12:34(016) <F_REQUEST> When checking operator: The modify operator keeps field "FREQ_STARTTIME" which is not present in the output interface.
>##E IIS-DSEE-TCOS-00029 17:12:34(017) <main_program> Creation of a step finished with status = FAILED.
WITH N (LEVEL, FREQ_SKEY, FREQ_STARTTIME) AS
(SELECT 1 AS LEVEL, FREQ_SKEY, FREQ_STARTTIME
FROM DSADM.F_REQUEST WHERE FREQ_SKEY = 7151
UNION ALL
SELECT N.LEVEL + 1, NPLUS1.FREQ_SKEY, NPLUS1.FREQ_STARTTIME
FROM DSADM.F_REQUEST AS NPLUS1, N
WHERE LEVEL < FREQ_MAX_RUNTIME
AND N.FREQ_SKEY = NPLUS1.FREQ_SKEY)
SELECT LEVEL, FREQ_SKEY, FREQ_STARTTIME
FROM N
For example, this query returns 6 records (= FREQ_MAX_RUNTIME).
The Select works with the DB2 Control Center user interface, but when copying it to Datastage in a DB2/UDB Enterprise stage in a User-defined SQL, I got an error with View data, and when running the job.
DB2Driver Embedded SQL message: SQL0347W The recursive common table expression "DSADM.N" may contain an
infinite loop. SQLSTATE=01605
;
sqlcode = 347;
sqlstate = 01605
##W IIS-DSEE-TNDB-00000 17:12:34(002) <F_REQUEST> Prepare failed
>##E IIS-DSEE-TOPK-00007 17:12:34(006) <_PEEK_IDENT_> Input dataset does not have field: "LEVEL".
>##E IIS-DSEE-TOPK-00007 17:12:34(007) <_PEEK_IDENT_> Input dataset does not have field: "FREQ_SKEY".
>##E IIS-DSEE-TOPK-00007 17:12:34(008) <_PEEK_IDENT_> Input dataset does not have field: "FREQ_STARTTIME".
>##E IIS-DSEE-TFSR-00019 17:12:34(010) <main_program> Could not check all operators because of previous error(s)
##W IIS-DSEE-TFIP-00093 17:12:34(011) <F_REQUEST> When checking operator: The modify operator has a binding for the non-existent output field "LEVEL".
##W IIS-DSEE-TFIP-00093 17:12:34(012) <F_REQUEST> When checking operator: The modify operator has a binding for the non-existent output field "FREQ_SKEY".
##W IIS-DSEE-TFIP-00093 17:12:34(013) <F_REQUEST> When checking operator: The modify operator has a binding for the non-existent output field "FREQ_STARTTIME".
##W IIS-DSEE-TFIP-00096 17:12:34(014) <F_REQUEST> When checking operator: The modify operator keeps field "LEVEL" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 17:12:34(015) <F_REQUEST> When checking operator: The modify operator keeps field "FREQ_SKEY" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 17:12:34(016) <F_REQUEST> When checking operator: The modify operator keeps field "FREQ_STARTTIME" which is not present in the output interface.
>##E IIS-DSEE-TCOS-00029 17:12:34(017) <main_program> Creation of a step finished with status = FAILED.