SQL Query Limit for DB2 AS/400 Version 4

Question

I know the version is way too old (yea version 4!), but I have no choice.

How to limit my query for example 100 rows only for DB2 AS400?

FETCH FIRST n ROWS ONLY

and

ROW_NUMBER()

don't work.

Any ideas or workaround?

Here is a sample SQL query (does not work):

SELECT POLNOP FROM ZICACPTF.POLHDR FETCH FIRST 10 ROWS ONLY

It says

[SQL0199] Keyword FETCH not expected. Valid tokens: FOR WITH ORDER UNION OPTIMIZE.

Earliest reference I find for a _fetch-first-clause_ is V5R1, similarly for `ROW_NUMBER()`. Can you give an example of **how** you want to use it? A FETCH statement may fetch a block of, e.g., 100 rows. You could FETCH once and process only those rows, but that implies embedded SQL. Also, there were five separate releases of `version 4` -- which is yours? — user2338816, Apr 14 '15 at 23:43
V4R4, what I only what is to fetch a table but limited for example 10 rows only. in mysql it's like `select * from table1 limit 10` — Kevin, Apr 15 '15 at 11:04
Are you running a MySQL version from 2001? That's (apparently) when the _fetch-first-clause_ showed up in DB2 on iSeries, so a similar comparison should be done for MySQL. Not much can be done about V4R4 that is now more than 15 years old. IMO, the only reasonable possibility is by embedded SQL where you FETCH the first 10 rows. I can't recall requirements for SQL stored procs on V4R4. You might create one that returns a result set containing only 10 rows. If a SQL stored proc isn't reasonable, an external stored proc could be used. — user2338816, Apr 15 '15 at 23:06
Hi! Did you try this? http://blog.zanclus.com/2008/07/emulating-limit-and-offset-on-db2-for.html — Ilia Maskov, Apr 17 '15 at 16:53
@agent5566 As already noted, ROW_NUMBER() didn't exist back at V4R4. — user2338816, Apr 22 '15 at 07:06
It's _possible_ that ...FROM myfile a WHERE RRN(a) < 11 would suffice. The RRN() function returns the physical Relative Record Number. As long as there are no deleted record slots at the beginning of the file, that would return the first ten physical rows. You could increase "11" until enough deleted records are bypassed if needed. — user2338816, Apr 22 '15 at 08:24

score 2 · Accepted Answer · answered Apr 19 '15 at 17:09

2

There is no dbms support for this operation, check Version 4 DB2 UDB for AS/400 SQL Reference: No Limit, Top, First, ... reserved words.

You can try to limit rows via where clause, where sequence between 100 and 200. But this is an unreal scenario.

First work around is via cursor:

DECLARE ITERROWS INTEGER;
...
SET ITERROWS = 0;
DO WHILE (SUBSTR(SQLSTATE,1,2) = '00' and ITERROWS < 100
DO
    ...
    SET ITERROWS = ITERROWS + 1;

second one, in your middleware language.

I hope someone post a clever workaround, but, in my opinion, they are not.

answered Apr 19 '15 at 17:09

dani herrera

48,760
8
117
177

Thanks, it seems that there's really no way to use LIMIT like clause for V4R4. Sadly, I'm using Java + Hibernate, and we may not change (yet) anything inside AS400 – Kevin Apr 20 '15 at 07:44
Hibernate is not able to deal with your issue through [Query.setMaxResults()](http://stackoverflow.com/a/1239745)? ( disclaimer, not tested ) – dani herrera Apr 20 '15 at 07:55
Perhaps there is a way to overwrite [hibernate setMasResults behavior](https://github.com/hibernate/hibernate-orm/blob/4615ae1018b0d83b7bc6f890a3287870a2d0dd86/hibernate-core/src/main/java/org/hibernate/dialect/DB2400Dialect.java#L103) Digging repository there are other RDBMS without limit support. You can deal with your issue at hibernate level instead at RDBMS level ( I know, not the best for performance ). Good luck. – dani herrera Apr 20 '15 at 08:52

score 0 · Answer 2 · edited May 23 '17 at 11:51

Solution only for > V4R4

Using FETCH FIRST [n] ROWS ONLY:

SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY
  FROM EMP
  ORDER BY SALARY DESC
  FETCH FIRST 10 ROWS ONLY;

Reference: publib.boulder.ibm.com

The difference I can see from your query to this example is that here we are using a ORDER BY clause - do you have the possibility to add a ORDER BY - it should do the trick. Referencing to: https://stackoverflow.com/a/16858430/1581725

To get ranges or also only the first 10 rows, you'd have to use ROW_NUMBER() (since v5r4):

SELECT 
    * 
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY {{table field}}) AS ROWNUM, * {{yourtable}}
) AS {{yourcursor}}
WHERE 
    {{yourcursor}}.ROWNUM>0 AND 
    {{yourcursor}}.ROWNUM<=10

Reference: blog.zanclus.com

Had only a v5r4 - didn't want to let you done with no solution. I will add this in first line - maybe someone needs this for later versions. Thank for your feedback @Kevin — DominikAngerer, Apr 20 '15 at 09:22

SQL Query Limit for DB2 AS/400 Version 4

2 Answers2