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