Getting -501 sqlcode from in a db2 CICS program....

peek_deep · New User Joined: 02 Jan 2007 Posts: 13 Location: india

Hi all,

Need your help to solve this issue. I have an issue as follows:

1. I have set some flags as follows

01 CURSOR-STATUS PIC X
88 CURSOR-OPEN VALUE 'O'
88 CURSOR-CLOSE VALUE 'C'
88 CURSOR-FETCH VALUE 'F'
88 CURSOR-NOTOPEN VALUE ' ' It is a space.

these above fields are part of my commarea which I pass and get it back after each call to subprogramss.

2. In my first step I am setting the CURSOR-OPEN to true and calling a sub program, inside the subprogram I have a condition if OPEN is successful set the FETCH flag to true and will fetch the first record as per the logic. Infact it is doing the same. this cursor is CSR1.

3. Now my control comes back to the mainprogram and by using some of the recenly fetched fields from the above subprogram I have to call an another subprogram which is again a cursor CSR2. For this I am again setting the CURSOR-OPEN to true and Now I fetch the first matched row and came back to the main program.

4. Here as per the requirement If there is a match I need to close the cursor i.e. CSR2 (You may think I could use single select in place of this cursor. Even I had the same thought, but unfortunately this subprogram which I am using is a existing one and I can not modify it). So to close the cursor in the mainprogram I set the CURSOR-CLOSE to true and again called the existing module. This 2nd call to the existing module with the CURSOR-CLOSE flag set to true closes the cursor and immediately sets the CURSOR-NOTOPEN to true.

5. So finally I have the cursor status in my main program as ' '.

6. Now I wanted to fetch the next record from my first subprogram. Since the corsor is in CURSOR-NOTOPEN status I set it again to CURSOR-FETCH to true and called the 1st subprogram.

7. It went through the fetch para but throw a SQLCODE of -501. I checked the logic. As per my knowledge the logic should work, but I am not sure where the 1st cursor is getting close.

Can anyone please help me in this regard

Thanks in advance
Deepak

vv · New User Joined: 06 Jan 2008 Posts: 10 Location: bangalore

what ever u r declere the cursor name is worng or mismatch.

check it clearly.

venkat.

ascelepius · New User Joined: 16 Jun 2008 Posts: 35 Location: bangalore

Oops I couldnt complete that,

I Main program
a. Open cursor CSR1
b. Call Sub-program 1
II Sub-program 1
a. fetch record
b. return data
III Main program
a. Open cursor CSR2
b. Call Subprogram 2
IV Sub-program2
a. fetch record
b. return data
V Main program
a.Compare data from CSR1 and CSR2
b. close CSR2
c.Call Sub-program 1

And you have a problem while fetching CSR1 again. Am I correct?

thanks,
asci(i)

peek_deep · New User Joined: 02 Jan 2007 Posts: 13 Location: india

Yes, The flow you understood is correct and I am getting -501 when trying to fetch the second record from the CSR1.

ascelepius · New User Joined: 16 Jun 2008 Posts: 35 Location: bangalore

It could help if you could share your cursor declaration...
Meanwhile, since I have some time to kill let me try to replicate this situation

regards,
asci(i)

peek_deep · New User Joined: 02 Jan 2007 Posts: 13 Location: india

Hi ASCI,

any cursor you can take. The only doubt I am having is why the first cursor is getting close, where as I am closing the cursor2. Even I crossed check the program again and I feel the logic should go through...

peek_deep · New User Joined: 02 Jan 2007 Posts: 13 Location: india

I am using a Goback in the sub program. Is is causing the problem... I mean is it closing the cursor.

ashimer · Posted: Thu Jun 19, 2008 4:56 pm

Did any other sqlerror occur before your second fetch ??? .... if not try declaring your cursor with option WITH HOLD ...

ashimer · Posted: Thu Jun 19, 2008 4:58 pm

If its a CICS psuedo conversational transaction you may have to convert it to conversational to keep the cursor open ...