SQL

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; …

Recursive SQL in DB2

Problem Playing around in DB2 I wanted to know how to create a hiearchical query. This because I wanted to create view for explain plans, a bit resembling the explain plans which oracle provides. First step is understanding how to make the recursive SQL’s The setup: drop table relations; create table relations (id int, parent …

Session statistics

You can have a look at v$sesstat (joined with v$staname) to see the value of some statistics at this moment. What is a little more difficult to see is how statistics change over time. So I wrote a script for it. What it does: Gather all statistics, of one or all sessions, and put them …