Recursive Select with DB2

[le thuong]

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.

[DSguru2B]

Make it a view and refer the view. This way the logic is externalized as well.

[ray.wurlod]

Or use AS clauses to give your columns names in the user-defined SQL. These column names must match those on the Columns grid.

[le thuong]

Or use AS clauses to give your columns names in the user-defined SQL. These column names must match those on the Columns grid.

I tried the 2 proposed solutions above (the view and the AS clause), unfortunately I got the same error in Datastage, while the SQL is correct in the DB2 user interface.

[DSguru2B]

Once you create a view, its just another database object, just like a table. You import it, load the metadata and do a select * from myrecursive view. How is that giving you the same error?

[soumya5891]

I can give you an idea.Don't know whether it will help or not.

1. Just write a shell script to write with 1,2,........,N in a col say COL1 in a text file
2. Again add a col say COL2 with some default value say 1 using the script.
3. Now call this script in your sequence job.
4. Then in your parallel job with your data set add a dummy column with default as 1.
5. Now join the text file with your record set in your parallel job.
6. You will get N no of records.

Ex.Say N=3

In Text File
-----------------------------

Col1 Col2

1 1
2 1
3 1


In Recors Set
--------------------------
Col1 Col2
A 1
B 1

Result set
-------------------
Col1 COl2
A 1
A 2
A 3
B 1
B 2
B 3

[le thuong]

Thanks to all.
I exported the dsx and sent to IBM. I have a workaround design for the job flow.