1

I'd like to use the LIMIT clause to do paging on DB2. The DB2 is hosted on AS400 and I'm accessing it via JDBC over JTOpen.

In Paging through result sets using LIMIT and OFFSET or ROWNUM I learned that I can put DB2 into an MySql compatibility by calling

DB2_COMPATIBILITY_VECTOR=MYS

This allows to use a LIMIT clause to do paging. Since my queries should run on MySql too this would be very helpull.

Unfortunaltely I did not find a way to enable this via JDBC and I'm not really sure this is supported at all on DB2 on AS400.

Hope somebody can shed some more light on this.

BetaRide
  • 16,207
  • 29
  • 99
  • 177
  • It's not possible on AS400 (System i) and you cannot enable it via JDBC on LUW. You can use one of the standard-compliant methods, such as the `ROW_NUMBER()` OLAP function, if it is available in your version of DB2. – mustaccio May 08 '14 at 11:28
  • Thanks a lot for your answer! Is there some (ibm) documentation pointing this out? – BetaRide May 08 '14 at 12:12
  • Pointing what out? There are online information centres for each version of DB2, which you can find by googling for your particular version. – mustaccio May 08 '14 at 13:15
  • I found some blogs and entries here on so about the limit clause, but non of them clearly sates where this is suported. – BetaRide May 08 '14 at 18:00
  • Read about MySQL compatibility: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.porting.doc/doc/r0052867.html – mustaccio May 08 '14 at 18:20
  • Note that the MySQL compatibility only works for the Linux/Unix/Windows version of DB2, and not the AS400 (or z/OS) version. – bhamby May 08 '14 at 19:16
  • YES, MySQL compatibility is supported on IBM i (f.k.a. OS/400) by Zend (in cooperation with IBM) http://www-01.ibm.com/support/docview.wss?uid=nas8N1013084 for starters – WarrenT May 09 '14 at 16:32
  • @mustaccio That's what I was looking for. Feel free to add your comments as answer in order you get the credits. – BetaRide May 15 '14 at 12:43
  • possible duplicate of [Equivalent of LIMIT for DB2](http://stackoverflow.com/questions/3885193/equivalent-of-limit-for-db2) – Clockwork-Muse Jul 11 '14 at 04:25
  • This is not a duplicate since it asks about *how* to enable the feature on different OS versions of DB2. The question is not asking about the LIMIT clause or how to achieve the same functionality on DB2. – BetaRide Jul 14 '14 at 05:51

1 Answers1

0

DB2/400 has native support for this out of the box (so do not fiddle with MySQL compatability!)

See Equivalent of LIMIT for DB2 for details.

Community
  • 1
  • 1
Thorbjørn Ravn Andersen
  • 73,784
  • 33
  • 194
  • 347
  • I'm not asking about how to achieve LIMIT functionality. I'm asking about how to enable the feature. – BetaRide Jul 14 '14 at 05:52