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.

Answer 1

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.