10

I am developing a Java Web service allow paging when fetching big data set from a DB2 Database on a IBM Mid Range Machine (AS400).

For example; if there are 10000 records in a data set, I want to fetch them in 1000 blocks at a time.

I found this article that explains that I can use LIMIT, and OFFSET. But I need to set the DB2_COMPATIBILITY_VECTOR variable to MYS.

Now I have been googling and saw you can use the db2set to set this variable. But I have not been able to find out where to type this command in?

I am developing on a windows machine, and I have the iSeries installed, and I have access to the IBM Mid Range Machine via the iSeries 5250 emulator.

I know this must be a real noob question, but How do I go about changing DB2_COMPATIBILITY_VECTOR variable to MYS?

ZioN
  • 550
  • 2
  • 11
  • 35
  • [Here is the documentation](http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/db2/rbafzintro.htm) for DB2 for i (V6R1). As Leons has noted you will need to use the `ROW_NUMBER` function – Benny Hill Jan 21 '14 at 14:48
  • You should be using the [Toolbox for Java and JTOpen](http://www-03.ibm.com/systems/power/software/i/toolbox/index.html) JDBC driver with the IBM i. – James Allman Jan 21 '14 at 16:26
  • @JamesA, yes I am using the JTOpen. But I am not use how one use it to allow paging? – ZioN Jan 22 '14 at 06:36
  • AFAIK, the actual answer is "You don't, not for DB2 for i at version 6.1. But if you want to enter the command anyway, do it at the same command line where you enter other db2* commands, e.g., db2stop and db2start." – user2338816 Jun 29 '16 at 05:05

2 Answers2

13

As of IBM i 7.1 TR11 or IBM i 7.2 TR3, normal modern paging with LIMIT / OFFSET is now supported:

SELECT SalesOrderId,OrderDate,DueDate,ShipDate,
       Status,CustomerId,SubTotal,TaxAmt
  FROM SalesOrderHeader SOH
 WHERE CustomerId=@CustomerId
ORDER BY SalesOrderId DESC
 LIMIT @ROWS_PER_PAGE      -- Variable = 10
OFFSET @PAGE_START_ROW     -- Variable = 10 * Page Number

See this article for details...

sventechie
  • 1,859
  • 1
  • 22
  • 51
12

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
Leo
  • 1,493
  • 14
  • 27
  • 1
    I come a cross this solution, but the performance is very slow, I was hoping to find faster solution. – ZioN Jan 21 '14 at 12:33
  • 1
    Sadly I am starting to believe the this may be the only way: http://stackoverflow.com/questions/3885193/equivalent-of-limit-for-db2 – ZioN Jan 21 '14 at 12:53
  • The scrollable cursor mentioned in one of the answers will likely have better performance, but you may not be able to use it in some cases. – Leo Jan 21 '14 at 14:54
  • 3
    Many 'this SQL is too slow' problems can be addressed with appropriate indexes. Run the index adviser in IBM i Navigator and then create the recommended indexes. – Buck Calabro Jan 21 '14 at 16:22
  • @BuckCalabro That is something I will have to do then. – ZioN Jan 22 '14 at 06:37
  • @Leons I do not understand scrollable cursor well. But from what I understand that they only exist in the same job. Each time I call the web service it creates a new job is created, and when the web service is done. It finishes the job. Correct me if I am wrong, but then scrollable cursor will not work in this situation? – ZioN Jan 22 '14 at 06:40
  • 1
    Its good to note, that ROW_NUMBER() function has a few restrictions, and one of them is it does not allow you to use views. – ZioN Feb 25 '14 at 13:25
  • 1
    @ZioN i'm using IBM i v6r1 and i can use ROW_NUMBER() applied to a view without problems. – Mirco Apr 08 '14 at 14:09
  • @Mirco then I assume you have applied some path to your machine that allows you to use it with views. – ZioN Apr 30 '14 at 12:39
  • 1
    We write the year 2021 and I do have to apply this solution to my problem at work? - oh no... I am in the wrong twilight zone... ok they give me money for that...Do I have to be scared by AI and job loss. I guess not but it won't make me happy either :-( – Dirk Schumacher Jul 28 '21 at 13:25