Equivalent of LIMIT for DB2

Question

How do you do LIMIT in DB2 for iSeries    I have a table with more than 50 000 records and I want to return records 0 to 10 000  and records 10 000 to 20 000   I know in SQL you write  LIMIT 0 10000 at the end of the query for 0 to 10 000 and LIMIT 10000 10000 at the end of the query for 10000 to 20 000   So  how is this done in DB2  Whats the code and syntax   full query example is appreciated

User · Answer

Support for OFFSET and LIMIT was recently added to DB2 for i 7.1 and 7.2. You need the following DB PTF group levels to get this support:

SF99702 level 9 for IBM i 7.2
SF99701 level 38 for IBM i 7.1

See here for more information: OFFSET and LIMIT documentation, DB2 for i Enhancement Wiki

User · Answer

You should also consider the OPTIMIZE FOR n ROWS clause  More details on all of this in the DB2 LUW documentation in the Guidelines for restricting SELECT statements topic    The OPTIMIZE FOR clause declares the intent to retrieve only a subset of the result or to give priority to retrieving only the first few rows  The optimizer can then choose access plans that minimize the response time for retrieving the first few rows

User · Answer

elcool s solution is a smart idea  but you need to know total number of rows  which can even change while you are executing the query    So I propose a modified version  which unfortunately needs 3 subqueries instead of 2   select   from       select   from           select   from MYLIB MYTABLE         order by MYID asc          fetch first  last  rows only            I      order by MYID desc     fetch first  length  rows only       II order by MYID asc   where  last  should be replaced with row number of the last record I need and  length  should be replaced with the number of rows I need  calculated as last row - first row   1   E g  if I want rows from 10 to 25  totally 16 rows    last  will be 25 and  length  will be 25-10 1 16

User · Answer

Try this  SELECT   FROM               SELECT T    ROW NUMBER   OVER   R FROM TABLE T           WHERE R BETWEEN 10000 AND 20000

User · Answer

Developed this method   You NEED a table that has an unique value that can be ordered   If you want rows 10 000 to 25 000 and your Table has 40 000 rows  first you need to get the starting point and total rows   int start   40000 - 10000   int total   25000 - 10000   And then pass these by code to the query   SELECT   FROM   SELECT   FROM schema mytable  ORDER BY userId DESC fetch first  start  rows only   AS mini  ORDER BY mini userId ASC fetch first  total  rows only

User · Answer

Theres these available options -  DB2 has several strategies to cope with this problem  You can use the  scrollable cursor  in feature  In this case you can open a cursor and  instead of re-issuing a query you can FETCH forward and backward  This works great if your application can hold state since it doesn t require DB2 to rerun the query every time  You can use the ROW NUMBER   OLAP function to number rows and then return the subset you want  This is ANSI SQL  You can use the ROWNUM pseudo columns which does the same as ROW NUMBER   but is suitable if you have Oracle skills  You can use LIMIT and OFFSET if you are more leaning to a mySQL or PostgreSQL dialect

User · Answer

Using FETCH FIRST  n  ROWS ONLY   http   publib boulder ibm com infocenter dzichelp v2r2 index jsp topic  com ibm db29 doc perf db2z fetchfirstnrows htm  SELECT LASTNAME  FIRSTNAME  EMPNO  SALARY   FROM EMP   ORDER BY SALARY DESC   FETCH FIRST 20 ROWS ONLY    To get ranges  you d have to use ROW NUMBER    since v5r4  and use that within the WHERE clause    stolen from here  http   www justskins com forums db2-select-how-to-123209 html   SELECT code  name  address FROM      SELECT row number   OVER   ORDER BY code   AS rid  code  name  address   FROM contacts   WHERE name LIKE   Bob        AS t WHERE t rid BETWEEN 20 AND 25

User · Answer

There are 2 solutions to paginate efficiently on a DB2 table    1 - the technique using the function row number   and the clause OVER which has been presented on another post   SELECT row number   OVER   ORDER BY          On some big tables  I noticed sometimes a degradation of performances   2 - the technique using a scrollable cursor  The implementation depends of the language used  That technique seems more robust on big tables   I presented the 2 techniques implemented in PHP during a seminar next year  The slide is available on this link    http   gregphplab com serendipity uploads slides DB2 PHP Best practices pdf  Sorry but this document is only in french

User · Answer

Here s the solution I came up with   select FIELD from TABLE where FIELD  gt  LASTVAL order by FIELD fetch first N rows only    By initializing LASTVAL to 0  or    for a text field   then setting it to the last value in the most recent set of records  this will step through the table in chunks of N records

[db2] Equivalent of LIMIT for DB2

Examples related to db2

Examples related to limit

Examples related to ibm-midrange