MySQL-Style LIMIT and OFFSET in DB2 Queries

I was recently asked whether DB2 support MySQL-style syntax to page through query result sets. The good news is that DB2 supports LIMIT and OFFSET in addition to its own syntax. The only drawback is that the extra syntax supports needs to be enabled before it can be used. In the following I am going to show you how.

MySQL syntax in DB2

To support features offered by other database systems like Oracle, Microsoft SQL Server, Sybase, or MySQL that are incompatible with existing DB2 features, the DB2_COMPATIBILITY_VECTOR registry variable was introduced in DB2 version 9.7. The vector is used to selectively enable features and there are predefined settings named ORA (enable all Oracle features), SYB (Sybase), and MYS (MySQL). With that background knowledge we can directly get started on enabling LIMIT and OFFSET.



[mymachine]$ db2set DB2_COMPATIBILITY_VECTOR=MYS
[mymachine]$ db2stop
12/07/2015 10:21:14     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[mymachine]$ db2start
12/07/2015 10:21:20     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

With the feature enabled we can connect to an existing database and test it:
[mymachine]$ db2 connect to hltest

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.6
 SQL authorization ID   = HLOESER
 Local database alias   = HLTEST

[mymachine]$ db2 "select tabname from syscat.tables order by tabname limit 5 "

TABNAME                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------
ACTIVITYMETRICS_BETWMON                                                                                                        
ACTIVITYSTMT_BETWMON                                                                                                           
ACTIVITYVALS_BETWMON                                                                                                           
ACTIVITY_BETWMON                                                                                                               
ADMINTABCOMPRESSINFO                                                                                                           

  5 record(s) selected.

[mymachine]$ db2 "select tabname from syscat.tables order by tabname limit 5 OFFSET 5"

TABNAME                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------
ADMINTABINFO                                                                                                                   
ADMINTEMPCOLUMNS                                                                                                               
ADMINTEMPTABLES                                                                                                                
ADVISE_INDEX                                                                                                                   
ADVISE_INDEX                                                                                                                   

  5 record(s) selected.

The first query selects the first five (5) table names from SYSCAT.TABLES, the second query the next five names. If you want to learn more about alternative ways of creating result pagination in DB2, I highly recommend this old blog entry written by Serge Rielau.