Will a called DB2 routine close the cursor automatically?

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 103

Hi

Program A calls program B. A is a non-DB2 program and B is a DB2 one.
A will call B in a loop. I open a cursor in the pgm B and suppose say it fetches 50 recs, then pgm A will call pgm B 50 times to give back each one record. Now my Q is, will the cursor be closed when it goes back to A every time ?

or Is there any other any solution to transfer all the records to pgm A that are fetched in pgm B. Here we will not be knowing how many records will be fetched from the cursor?

Thanks & Regards
Prabs

William Thompson · Posted: Mon Dec 11, 2006 6:59 pm

Just guessing, but unless B closes the cursor, the second visit should still be part of the same LUW. Of course, some of the types of call can wreak havic on that.

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 103

What is LUW?

William Thompson · Posted: Mon Dec 11, 2006 7:07 pm

Logical unit of work

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 103

Not too sure. DB2 experts any take on this?

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

Agree with William.

- A Cursor has to be explicitly closed.
- If a commit is issued and cursor has not being declared WITH HOLD, cursort gets closed.

Also you say 'Cursor is opened in Pgm B', so next call when A calls B, probably it is calling with different parameters otherwise B will give you error 'Cursor already opened'.

To transfer more than record to PGM A, you can declare a linkage area copybook with Occurs ( give a reasonable limit, say 20) and populate occurs in a FETCH/LOOP. You will have to code cursor SQL so that next time program A call B incase there are more than 20 rows then SQL starts with 21 row.