{"id":433,"date":"2022-02-05T07:41:35","date_gmt":"2022-02-05T07:41:35","guid":{"rendered":"https:\/\/maboc.nl\/?p=433"},"modified":"2022-02-05T07:41:35","modified_gmt":"2022-02-05T07:41:35","slug":"db2-row-generator","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=433","title":{"rendered":"DB2 Row generator"},"content":{"rendered":"\r\n<p>Sometimes you need a mechanism to generate rows. For example when you need to create a demonstration table .<\/p>\r\n<p>Recursive CTE&#8217;s (Common Table Expression) might be your friend<\/p>\r\n<p>&nbsp;<\/p>\r\n<pre style=\"font-size: 10px;\">WITH engine (rownum) AS \r\n  (select 1 as rownum\r\n   from   sysibm.sysdummy1\r\n   union all\r\n   select rownum + 1 AS rownum\r\n   from   engine\r\n   where rownum&lt;10)\r\nSELECT rownum\r\nFROM   engine;<\/pre>\r\n\r\nLet&#8217;s see how that works out\r\n<pre  style=\"font-size: 10px;\">\r\n[db2inst1@broccoli scripts]$ cat rcte.sql \r\nWITH engine (rownum) AS \r\n  (select 1 as rownum\r\n   from   sysibm.sysdummy1\r\n   union all\r\n   select rownum + 1 AS rownum\r\n   from   engine\r\n   where rownum<10)\r\nSELECT rownum\r\nFROM   engine;\r\n \r\n[db2inst1@broccoli scripts]$ db2 -tf rcte.sql \r\n\r\nROWNUM     \r\n-----------\r\n          1\r\n          2\r\n          3\r\n          4\r\n          5\r\n          6\r\n          7\r\n          8\r\n          9\r\n         10\r\n\r\n  10 record(s) selected.\r\n<\/pre>\r\n\r\nWe can (for example) now construct a table in which we very precisely can determine the distrubtion of the data:\r\n<pre style=\"font-size: 10px;\"> \r\ncreate table t1 (n1 int, n2 int, n3 int, t varchar(100));\r\n\r\ninsert into t1 \r\n  WITH engine (rownum) AS (\r\n                            SELECT 1 AS rownum \r\n                            from   sysibm.sysdummy1\r\n                            UNION ALL\r\n                            SELECT rownum + 1 AS rownum\r\n                            FROM   engine\r\n                            where  rownum<100000\r\n                          )\r\n  SELECT rownum, \r\n         round(rownum\/1000,0), rownum%1000, 't'\r\n  FROM   engine;\r\n\r\ncommit;\r\n<\/pre>\r\n","protected":false},"excerpt":{"rendered":"<p>Sometimes you need a mechanism to generate rows. For example when you need to create a demonstration table . Recursive CTE&#8217;s (Common Table Expression) might be your friend &nbsp; WITH engine (rownum) AS (select 1 as rownum from sysibm.sysdummy1 union all select rownum + 1 AS rownum from engine where rownum&lt;10) SELECT rownum FROM engine; [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[64,49],"tags":[68,65,66,14],"class_list":["post-433","post","type-post","status-publish","format-standard","hentry","category-db2","category-sql","tag-cte","tag-db2","tag-recursion","tag-sql"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/433","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=433"}],"version-history":[{"count":6,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/433\/revisions"}],"predecessor-version":[{"id":441,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/433\/revisions\/441"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=433"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=433"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=433"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}