Db2 Using Limit and Offset

DB2 Using LIMIT and OFFSET

DB2 for Linux Unix Windows (LUW) and DB2 for iSeries are different products. Likely, DB2 for iSeries does not support DB2_COMPATIBILITY_VECTOR. I'm not able to find mention of it in the iSeries Information Center.

Instead of LIMIT, you can use the FETCH FIRST 10 ROWS ONLY clause.

Instead of LIMIT and OFFSET, you should be able to use a subselect with the ROW_NUMBER olap function. Something like this:

 SELECT emp.EMPNO, emp.SALARY
 FROM (

     SELECT EMPNO, SALARY, 
            ROW_NUMBER() OVER(ORDER BY SALARY DESC) as row_number
     FROM EMPLOYEE

 ) emp
 WHERE emp.row_number > 10
 AND emp.row_number <= 20

Equivalent of LIMIT for DB2

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

Creating a pagination with db2

Well, depending on what platform of DB2 you are using, you didn't read the full story. DB2 LUW has support for LIMIT and OFFSET, but you have to turn it on (don't forget to restart DB2 after setting the flag). If you want to use DB2 with ROW_NUMBER() as you asked for, you could write the query as follows:

SELECT * 
  FROM (SELECT ROW_NUMBER() OVER() AS rn, 
               items.* 
          FROM items)
  WHERE rn BETWEEN computelowerboundaryhere AND computeupperboundaryhere;

There is also an overview article describing the different ways of doing the LIMIT/OFFSET work in DB2.

Getting top n to n rows from db2

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061832.html

db2 "select row_number() over(order by tabschema, tabname)
,    tabschema::char(10), tabname::char(30)
from syscat.tables
order by tabschema, tabname 
offset 10 rows 
fetch first 10 rows only"

1                    2          3                             
-------------------- ---------- ------------------------------
                  11 SYSCAT     COLCHECKS                     
                  12 SYSCAT     COLDIST                       
                  13 SYSCAT     COLGROUPCOLS                  
                  14 SYSCAT     COLGROUPDIST                  
                  15 SYSCAT     COLGROUPDISTCOUNTS            
                  16 SYSCAT     COLGROUPS                     
                  17 SYSCAT     COLIDENTATTRIBUTES            
                  18 SYSCAT     COLLATIONS                    
                  19 SYSCAT     COLOPTIONS                    
                  20 SYSCAT     COLUMNS                       

  10 record(s) selected.

SQL query for second largest value with a where condition

If the step ids are distinct, you can just use OFFSET and FIRST FIRST 1 ROW ONLY:

SELECT step_action_id
FROM step_table 
WHERE resident_id = 219 
ORDER BY step_action_id DESC
OFFSET 1 ROWS
FETCH FIRST 1 ROW ONLY;

If they are not distinct, just add a GROUP BY or SELECT DISTINCT:

SELECT step_action_id
FROM step_table 
WHERE resident_id = 219 
GROUP BY step_action_id
ORDER BY step_action_id DESC
OFFSET 1 ROWS
FETCH FIRST 1 ROW ONLY;

How to optimize a UNION with a final ORDER BY/OFFSET

Make sure you have indexes on all three tables with the columns (titi, toto).

create index ix1 on s1 (titi, toto);
create index ix2 on s2 (titi, toto);
create index ix3 on s3 (titi, toto);

Then the query could look like:

select * 
  from (
    select toto, titi from s1 order by titi, toto 
      fetch next (50 + 50) rows only
    union all
    select toto, titi from s2 order by titi, toto
      fetch next (50 + 50) rows only
    union all
    select toto, titi from s3 order by titi, toto
      fetch next (50 + 50) rows only
  ) x
  order by titi, toto
  offset 50 rows fetch next 50 rows only

In any case, your pagination strategy is quite inefficient for high values of OFFSET. My query above can make a difference nevetheless.

The key improvements over your query are:

• Using indexes on each table separately.
• Use UNION ALL instead of UNION since it more efficient.

Do I have to use FETCH FIRST n ROWS ONLY or LIMIT when selecting an unique id in where clause

You can use both the FETCH FIRST 1 ROWS ONLY as well as LIMIT in Db2, check the DB2 compatibility settings.

If only one row is returned, it does not matter if that syntax is specified. However, if you or the system is not sure, then it is an extra safeguard. Depending on the optimizer settings (or mood or statistics or metadata), the additional syntax may help with performance. The reason is that the database system knows that only 1 row should be returned and it can optimize for that case.

If there is a unique index on id, then it should be obvious, but is there an index...?

DB2 SQL Pagination with a variable BETWEEN

As it is currently written, the subselect with the correlation name P contains only one column, ROWNUM:

SELECT PALAVRA.CODPAL, PALAVRA.NMPAL, PALAVRA.NMLIBTRANS 
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY PALAVRA.NMPAL ASC) AS ROWNUM
    FROM MAXADM.PALAVRA WHERE PALAVRA.NMPAL LIKE '$busca%'
) AS P 
WHERE P.ROWNUM BETWEEN $offset AND $offsetend

so you obviously cannot select PALAVRA.CODPAL, PALAVRA.NMPAL, PALAVRA.NMLIBTRANS from it.

Try rewriting the query as

SELECT P.CODPAL, P.NMPAL, P.NMLIBTRANS 
FROM (
  SELECT 
    PALAVRA.CODPAL, PALAVRA.NMPAL, PALAVRA.NMLIBTRANS,
    ROW_NUMBER() OVER (ORDER BY PALAVRA.NMPAL ASC) AS ROWNUM
  FROM MAXADM.PALAVRA WHERE PALAVRA.NMPAL LIKE '$busca%'
) AS P 
WHERE P.ROWNUM BETWEEN $offset AND $offsetend

How to query range of data in DB2 with highest performance?

My requirement have been added into DB2 9.7.2 already.

DB2 9.7.2 adds new syntax for limit query result as illustrate below:

SELECT * FROM TABLE LIMIT 5 OFFSET 20

the database will retrieve result from row no. 21 - 25

---

Related Topics