What's an elegant way to retrieve all times of the day (1 hour resolution) in DB2 without a backing table?

Question

I'm querying some data from a table in DB2/z which holds hourly data but occasionally skips some hours (if the clients don't send up details for that hour).

Since I'm feeding the data straight into a charting tool which needs a query to get the axis data, it sometimes has missing values from the time axis and looks ugly:

23.00 |===
22.00 |=====
14.00 |============
01.00 |==
00.00 |=
      +--------------------

We've been verboten from adding any helper tables like a 24-row table holding all the hours so I need a query in DB2 that will return all the hours of the day without a reliable backing table. I've been using the monstrous:

select '00.00' from sysibm.sysdummy1
union all select '01.00' from sysibm.sysdummy1
union all select '02.00' from sysibm.sysdummy1
union all select '03.00' from sysibm.sysdummy1
: : :
union all select '23.00' from sysibm.sysdummy1
order by 1

which is about as kludgy a solution I've ever seen, so I'd rather have something a little cleaner.

I've posted my own solution below but I'm open to anything simpler or more elegant.

Answer 1

The following level-limited recursive call will give the desired range without a backing table. Without the limit on level, DB2 complains that the function may be infinitely recursive.

with trange(lvl, tm) as (
    select 1, time('00:00') from sysibm.sysdummy1
    union all select lvl+1, tm + 1 hour from trange where lvl < 24
) select left(char(tm,iso),5) as hour from trange;

This gives:

HOUR 
-----
00.00
01.00
02.00
03.00
: : :
22.00
23.00

as desired.

Answer 2

VALUES '00.00', '01.00', /* ... */, '23.00'

should work too, at least for me (on LUW 9.1+). :-) Still kludgey, but more compact.

In general, I've found that if you don't need the full power of SELECT, and don't need to name your columns, VALUES is a much simpler alternative.

If you do want to name your column, just throw the VALUES within a SELECT:

SELECT * FROM (VALUES /* ... */) AS foo (hour)

(With some versions of DB2, the foo is optional.)

---

Oh wow, I've just read the DB2 z/OS documentation for VALUES, and it's much wimpier compared to the LUW version. For z/OS, VALUES can only be used in the triggered action of a trigger. Sorry, paxdiablo. :-(