This is achievable with following code.
1) Declare 2 cursors.
a) For executing the query: DECLARE C1 ASENSITIVE SCROLL CURSOR WITH ROWSET POSITIONING WITH HOLD FOR. The result of this query will be inserted into a Decalre Global Temp Table.
b) For returning data from Temp table: DECLARE TEMP_C1 ASENSITIVE SCROLL CURSOR WITH ROWSET POSITIONING WITH RETURN FOR
2) Open C1 cursor.
3) fetch data using following syntax:
FETCH ROWSET STARTING AT ABSOLUTE :WS-ABSOLUTE-ROW FROM C1 FOR :WS-IN-ROWS-PER-PG ROWS
INTO :<<Host Variable array>>:<<Null indicator array>>
Here you can compute WS-ABSOLUTE-ROW depending upon page no and number of rows per page. For example, if page no is 5 and number of rows per page is 10, WS-ABSOLUTE-ROW = 41. Page no and number of rows must be passed from front end. If page no is 0, it will retrieve last N rows.
4) Insert above rows in Declare Global Temp Table 1 by 1.
5) Open cursor Temp_C1.
Following problems were encountered in my project:
1) What kind of .Net standard are you using? Is it IBM DB2 data provider or system ODBC provider. We faced a looping issue with above type of cursor structure when IBM DB2 data provider was used. i.e. the call to SP was going into an infinite loop. But same SP was executing successfully for ODBC provider. Finally we had to change above logic and remove pagination from SP as we didn't get timely response from any of the service provider.
2) Threads!!!: Please use ON COMMIT DROP TABLE while declaring Temp table. Otherwise it may result into creation of threads into DB2 subsystem and hamper performance.
So please take this into consideration before getting started.