DB2 Row generator

Sometimes you need a mechanism to generate rows. For example when you need to create a demonstration table .

Recursive CTE’s (Common Table Expression) might be your friend

 

WITH engine (rownum) AS 
  (select 1 as rownum
   from   sysibm.sysdummy1
   union all
   select rownum + 1 AS rownum
   from   engine
   where rownum<10)
SELECT rownum
FROM   engine;

Let’s see how that works out

[db2inst1@broccoli scripts]$ cat rcte.sql 
WITH engine (rownum) AS 
  (select 1 as rownum
   from   sysibm.sysdummy1
   union all
   select rownum + 1 AS rownum
   from   engine
   where rownum<10)
SELECT rownum
FROM   engine;
 
[db2inst1@broccoli scripts]$ db2 -tf rcte.sql 

ROWNUM     
-----------
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10

  10 record(s) selected.

We can (for example) now construct a table in which we very precisely can determine the distrubtion of the data:

 
create table t1 (n1 int, n2 int, n3 int, t varchar(100));

insert into t1 
  WITH engine (rownum) AS (
                            SELECT 1 AS rownum 
                            from   sysibm.sysdummy1
                            UNION ALL
                            SELECT rownum + 1 AS rownum
                            FROM   engine
                            where  rownum<100000
                          )
  SELECT rownum, 
         round(rownum/1000,0), rownum%1000, 't'
  FROM   engine;

commit;