One of the challenges many skilled DB2 enthusiasts are fighting with is to return the number of the actual row as part of the result. The first row must be assigned the number 1, the next row number 2 and so on. In an application program you can code your own counter, but it is much harder to solve when you use any of the different tools executing dynamic SQL.
Recently I discovered a so-called SEQUENCE in DB2 (one of many functionalities which ORACLE introduced many years before DB2, just like recursive SQL). The easiest way to create a SEQUENCE is to use a CREATE SEQUENCE MYSEQ. Of course you replace MYSEQ with the name you prefer. Next you can try something like:
The first column of the result will be an ever increasing number. It is that easy. But already at the next SQL statement execution a flaw occurs. The first row will be assigned the next number after the last returned row from the previous SQL statement. Luckily enough the simple cure is to issue an ALTER SEQUENCE MYSEQ RESTART WITH 1. Now the numbering is back on track. The next challenge occurs when you add ORDER BY, GROUP BY or DISTINCT. Then you receive a SQLCODE -348, which describes all the cases where you cannot use this tip.
If you want to create a function working like NEXT VALUE FOR MYSEQ without its limits I will suggest you look into the possibilities of a User Defined Function (UDF). In this tip I have used the most simple version of a SEQUENCE. You can read more in SQL Reference. Please note the revision marks in the left margin indicating that SEQUENCE was introduced in DB2 version 8.