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.

现在我一直在谷歌搜索，发现您可以使用 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?

推荐答案

DB2 for Linux Unix Windows (LUW) 和 DB2 for iSeries 是不同的产品.很可能，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.

除了 LIMIT 和 OFFSET，您应该能够使用带有 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屋！