Programming Zen

Enabling LIMIT and OFFSET in DB2 9.7.2

Antonio Cangiano

In order to enable the new LIMIT and OFFSET syntax in DB2 9.7.2, you’ll need to execute the following commands:

$ db2set DB2_COMPATIBILITY_VECTOR=MYS
$ db2stop
$ db2start

With this enabled, you’ll be able to execute queries such as:

SELECT * FROM users LIMIT 5
SELECT * FROM users LIMIT 5 OFFSET 10

Antonio Cangiano

Antonio Cangiano is an Engineering Manager and AI Specialist at IBM. He authored Ruby on Rails for Microsoft Developers (Wrox, 2009) and Technical Blogging (The Pragmatic Bookshelf, 2012, 2019). He is also the Marketing Lead for Cognitive Class, an educational initiative which he helped grow from zero to over 3 Million students. You can follow him on Twitter.

5 responses to “Enabling LIMIT and OFFSET in DB2 9.7.2”

  1. Leons Petrazickis

    Wow. Finally. This is tremendous. I’ll enable this in the MediaWiki port shortly.:-)

  2. Arun Srini

    So what happens if I had already set the compatibility to Oracle? Will I lose the features I already had? like the pl/sql blocks etc??

  3. Murad Iqbal

    In my DB 9.7, I am getting SQL exceptions for usage of the LIMIT and OFFSET. After setting db2set DB2_COMPATIBILITY_VECTOR=MYS, nothing changed. Still trying to figure out how to get the LIMIT and OFFSET work.

    1. Antonio Cangiano

      Have you restarted the server, Murad?

      $ db2stop 
$ db2start
      1. Murad Iqbal

        Yes I did that. But no change.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.