DB2 supports LIMIT and OFFSET syntax, in MySQL compatibility layer…

[singalen]

Originally published at Fiberglass flowers. You can comment here or there.

It hasn’t even been a ten years that DB2, one of the most, er, expensive DBMSes, got a feature needed by every other application – dataset paging.

Before, you had to use window functions rownumber() and fetch first 40 rows only. When used by Hibernate, this resulted ugliness like:

select * from (
  select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_
  from (
    select  ...
    fetch first 40 rows only
    ) as inner2_
  ) as inner1_
where rownumber_ > 20
order by rownumber_

Since this summer version – DB2 9.7.2 – you can just append LIMIT and OFFSET:

Read the rest of this entry »

Comments

[singalen]

Да, соврамши. Но у меня есть оправдание - row_number() ... over() заработал только в DB2 9.

Пример взял из того, что генерирует JDBC для запросов определённого рода.

JDBC тоже равнять и равнять.

[singalen]

Ладно, я опять недочитал - в восьмёрке. Кроме z/OS, там в девятке :)

[singalen]

Понятия не имею - Бог миловал от z/OS. Мы на линоксовой версии.