Skip to content

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


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:
[sourcecode language=’sql’]
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_
[/sourcecode]

Since this summer version – DB2 9.7.2 – you can just append LIMIT and OFFSET:
[sourcecode language=’sql’]SELECT … LIMIT 5 OFFSET 10[/sourcecode]
No wrapping into 2 (!!!) external SELECTs.
One downside is that mighty DB2 does have MySQL compatibility layer mode. Turn it on when starting DB2:
[sourcecode language=”bash”]
$ db2set DB2_COMPATIBILITY_VECTOR=MYS
$ db2stop
$ db2start
[/sourcecode]

Post a Comment

Your email is never published nor shared.