IBM Mainframe Forum

Hi

Currently in my project COBOL/DB2 Store Procedure in Mainframe is invoked from .NET front end for certain inquiry functionality. Cursor in my SP is defined as WITH RETURN FOR. So SP is opened in MF when invoked. Fetch and Close is taking place in front end. After opening CURSOR in mainframe, entire result is passed to front end which is causing some performance issue.

So i want to change the SP to return rows in pages instead of entire cursor based on the number of rows per page specified in the front end. Please let me know how this can be implemented.

Hi,

this sounds like a scrollable cursor, rowset positioning - or a combination of it

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.