ratnalein, can you show the SQL for the recursive query you are having problems with? here is an example that doesn't use a real table while doing recursion.
WITH DATE_TAB (DATE_COL)
AS (SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1)
, REC_DATE_TAB (COUNTER, DATE_COL)
AS (SELECT 1, DATE_COL
SELECT COUNTER + 1, DATE_COL + 1 DAY
WHERE COUNTER + 1 <= 10
I create a CTE that contains CURRENT DATE. I do use SYSIBM.SYSDUMMY1 (a real table) to generate this CTE.
The second CTE is where the Recursion is processed. I used the first CTE and increment the date by 1 day and stop after 10 iterations.
Other than SYSIBM.SYSDUMMY, there isn't a 'real' table in the query.
,base as (
, rec (...) as (
select * from rec;
I run exactly this code, and it never stopped. As we can see, in the recursive part, I took BASE B as "table". I would not suggest that. But it could be okay, if there is no group by in the base clause(not sure about that). As I later on created a real table for base, then it worked with no problem. Please take note, it might be only a problem for me, because the datas I had to recursive are about 200 million rows. And the result is around 290 millions.
Hope, I didnt create missunderstanding to you all.