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:

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:

SELECT ... LIMIT 5 OFFSET 10

No wrapping into 2 (!!!) external SELECTs.
One downside is that mighty DB2 does have MySQL compatibility layer mode. Turn it on when starting DB2:

$ db2set DB2_COMPATIBILITY_VECTOR=MYS
$ db2stop
$ db2start

Post a Comment

Your email is never published nor shared.