How to query range of data in DB2 with highest performance?

My requirement have been added into DB2 9.7.2 already.

DB2 9.7.2 adds new syntax for limit query result as illustrate below:

SELECT * FROM TABLE LIMIT 5 OFFSET 20

the database will retrieve result from row no. 21 - 25

Not sure why you are creating the TMP table. Isn't RUNNING_NO aready in ascending sequence? I would think:

SELECT SMALLINT(RANK() OVER(ORDER BY NAME DESC)) AS RUNNING_NO,
       DATA_KEY_VALUE,
       SHOW_PRIORITY
  FROM EMPLOYEE
 WHERE NAME LIKE 'DEL%'
 ORDER BY NAME DESC
 FETCH FIRST 10 ROWS ONLY

would give the same results.

Having an INDEX over NAME on the EMPLOYEE table will boost performance of this query.

It's very difficult, it is depends which database do you have.

for example:

SELECT * FROM ( 
    SELECT 
      ROW_NUMBER() OVER (ORDER BY ID_USER ASC) AS ROWNUM,  
      ID_EMPLOYEE, FIRSTNAME, LASTNAME 
    FROM EMPLOYEE 
    WHERE FIRSTNAME LIKE 'DEL%' 
  )  AS A WHERE A.rownum
BETWEEN 1 AND 25