Calling DB2 Store procedure from .NET front End



IBM's flagship relational database management system

Calling DB2 Store procedure from .NET front End

Postby trnagarajan » Thu Dec 16, 2010 10:19 am

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.
trnagarajan
 
Posts: 1
Joined: Thu Jan 21, 2010 8:23 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Calling DB2 Store procedure from .NET front End

 

Re: Calling DB2 Store procedure from .NET front End

Postby alexm » Fri Dec 17, 2010 7:19 pm

Hi,

this sounds like a scrollable cursor, rowset positioning - or a combination of it
User avatar
alexm
 
Posts: 33
Joined: Wed Oct 13, 2010 6:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Calling DB2 Store procedure from .NET front End

Postby upendra_water » Sun Dec 19, 2010 1:45 am

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.
upendra_water
 
Posts: 33
Joined: Wed Nov 25, 2009 10:58 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post