Automatically generate rows in DB2 with SQL

Question

I'm trying to search the DB2 equivalent of generate_series() (the PostgreSQL-way of generating rows). I obviously don't want to hard-code the rows with a VALUES statement.

select * from generate_series(2,4);

    generate_series
    -----------------
                    2
                    3
                    4
    (3 rows)

Answer 1

I managed to write a recursive query that fits :

with dummy(id) as (
    select 2 from SYSIBM.SYSDUMMY1    
    union all
    select id + 1 from dummy where id + 1 between 2 and 4
)
select id from dummy

The query can be adapted to whatever for(;;) you can dream of.

Answer 2

The where clause needs to be a bit more explicit about the bounds of the recursion in order for DB2 to suppress the warning. Here's a slightly adjusted version that does not trigger the warning:

with dummy(id) as (
    select 2 from SYSIBM.SYSDUMMY1    
    union all
    select id + 1 from dummy where id < 4
)
select id from dummy