Warning: Declaration of LJXP_Walker_Category_Checklist::start_lvl(&$output, $depth, $args) should be compatible with Walker::start_lvl(&$output, $depth = 0, $args = Array) in /home/victorsergienko/victorsergienko.com/wp-content/plugins/lj-xp/lj-xp-options.php on line 796

Warning: Declaration of LJXP_Walker_Category_Checklist::end_lvl(&$output, $depth, $args) should be compatible with Walker::end_lvl(&$output, $depth = 0, $args = Array) in /home/victorsergienko/victorsergienko.com/wp-content/plugins/lj-xp/lj-xp-options.php on line 796

Warning: Declaration of LJXP_Walker_Category_Checklist::start_el(&$output, $category, $depth, $args) should be compatible with Walker::start_el(&$output, $object, $depth = 0, $args = Array, $current_object_id = 0) in /home/victorsergienko/victorsergienko.com/wp-content/plugins/lj-xp/lj-xp-options.php on line 796

Warning: Declaration of LJXP_Walker_Category_Checklist::end_el(&$output, $category, $depth, $args) should be compatible with Walker::end_el(&$output, $object, $depth = 0, $args = Array) in /home/victorsergienko/victorsergienko.com/wp-content/plugins/lj-xp/lj-xp-options.php on line 796

Warning: Cannot modify header information - headers already sent by (output started at /home/victorsergienko/victorsergienko.com/wp-content/plugins/lj-xp/lj-xp-options.php:796) in /home/victorsergienko/victorsergienko.com/wp-content/plugins/wp-super-cache/wp-cache-phase2.php on line 60
code name / DB2 supports LIMIT and OFFSET syntax, in MySQL compatibility layer…
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.