Sql – generate_series() equivalent in DB2

auto-generatedb2sql

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)

Best Solution

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