DB2使用LIMIT和OFFSET [英] DB2 Using LIMIT and OFFSET

问题描述

我正在开发Java Web服务，当从IBM中型机(AS400)上的DB2数据库中获取大数据集时，允许分页.

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).

例如；如果数据集中有10000条记录，我想一次以1000个块的形式获取它们.

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

我发现了这个文章，该文章解释了我可以使用LIMIT和OFFSET.但是我需要将DB2_COMPATIBILITY_VECTOR变量设置为MYS.

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.

现在，我一直在使用Google搜索，发现可以使用db2set设置此变量.但是我无法找出在何处键入此命令?

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?

我正在Windows机器上进行开发，并且已经安装了iSeries，并且可以通过iSeries 5250模拟器访问IBM Mid Range Machine.

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.

我知道这一定是一个真正的菜鸟问题，但是如何将DB2_COMPATIBILITY_VECTOR变量更改为MYS?

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

推荐答案

Linux上的DB2 Unix Windows(LUW)和iSeries上的DB2是不同的产品.可能，DB2 for iSeries不支持DB2_COMPATIBILITY_VECTOR.我无法在iSeries信息中心找到它.

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.

您可以使用FETCH FIRST 10 ROWS ONLY子句代替LIMIT.

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

您应该能够将子选择与ROW_NUMBER olap函数.像这样:

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

这篇关于DB2使用LIMIT和OFFSET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！