DB2 Cursor in my cobol code

deeptik · New User Joined: 21 Mar 2006 Posts: 8

Hi,

I have a db2 cursor in my cobol code that fetches some data that needs to be passed to another application. There is a restriction that we cannot send more than say 20 records at a time.

So This program needs to be called again and cursor should start processing from the 21st record in the 2 nd call. Please suggest what would be the most efficient way to do this.

ashimer · Posted: Fri Jul 24, 2009 7:08 pm

How are you passing the data to the second program ?

option 1
----------
declare a session table, put all the data from cursor in this table ( use a mass insert instead of cursor ) and let the second program access this table ..

option 2
---------
declare an array in your first program ... store all data from the cursor in this array and at each call pass 20 records downstream ... remember global variables ..

option 3
----------
open the cursor in your first program and let the second program directly fetch from the cursor ... allocate the opened cursor in your second program ...

Srihari Gonugunta · Posted: Mon Jul 27, 2009 12:41 pm

Deepti,
You can use Order by the key and pass the key value of the 20th row again to your program

Your where clause will be

AND Key > key_value
ORDER BY KEY

For the first call key value will be spaces or zero.

Ketan Varhade · Posted: Mon Jul 27, 2009 5:31 pm

Hi,
I would suggest that you can use the restart logic in the above requirement. Just adding to what Ashimer has said . Dump the complete data in a array and then make a incremental counter and read that array.
For the first time just read the table till 20 rec and then process in the application that you want to do, then we when u are again calling the program again read the table till 20 but do not process that values and start doing this after you encounter the value as 21 .

I hope this will help to solve your query

Thanks
Ketan Varhade

deeptik · New User Joined: 21 Mar 2006 Posts: 8

Thanks all, I think implementing the > key value will work for me.

deeptik · New User Joined: 21 Mar 2006 Posts: 8

Sessions table may not be possible as u need the authorityto declare a sessions table and it may not be possible for the third party application to access this table directly.

the 2nd application is not a mainframe one, but it is a third party java/unix interface so option 2 and 3 will not work out.

I am not sure but is it possible to use scrollable cursors to implement this logic ?