Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Not reaching End-of-Table.
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
catchyblues

New User


Joined: 28 Dec 2005
Posts: 24

PostPosted: Mon Jul 02, 2007 8:43 pm    Post subject: Not reaching End-of-Table.
Reply with quote

Hi,

I'm facing a wierd problem. I'm reading a view which has 10 test records and I'm using a cursor to read through those records one-by-one. I'm able to read from 1st to 10th records without any problem. But after reading the 10th record, the cursor is still positioned at the last record only and not returning a SQL CODE +100 and hence it keeps reading the last record for ever. Facing the same problem even if i have single record in the View. Can anyone tell me where I might be going wrong ?

I'm pasting the sample code which I have used for this.


PERFORM OPEN CURSOR.

IF SQLCODE = 0
PERFORM EC-PROCESS UNTIL W04-CVR-CSR-FLAG = 'Y'
END-IF


EC-PROCESS SECTION.

EXEC SQL
FETCH CVR_CSR
INTO
:TEST-VAR-1,
:TEST-VAR-2
END-EXEC

IF SQLCODE NOT = 0 AND SQLCODE NOT = 100
MOVE SQLSTATE TO W04-OUT-SQLSTATE
GO TO AA-999-EXIT
ELSE
IF SQLCODE = 100
MOVE 'Y' TO W04-CVR-CSR-FLAG
MOVE SQLSTATE TO W04-OUT-SQLSTATE
PERFORM CLOSE-CVR-CSR
ELSE
IF SQLCODE = 0 THEN
MOVE TEST-VAR-2 TO W04-VAR-CID
MOVE TEST-VAR-2 TO W04-VAR-MC
IF W04-VAR-SCH = 2 OR 3 OR 4 OR 9 OR 35 OR 45 THEN
CALL "RTGFPRPC" USING W04-VAR-CID, W04-VAR-PID,
RTGIXML, W04-END-MESSAGE, W04-OUT-MESSAGE,
W04-OUT-SQLCODE, W04-OUT-SQLSTATE

ELSE
IF W04-VAR-SCH = 70 OR 72 OR 79 OR 82 OR 83
OR 84 OR 85 OR 87 OR 91 THEN
CALL "RTGFOTHC" USING W04-VAR-CID,
RTGIXML, W04-END-MESSAGE,
W04-OUT-MESSAGE, W04-OUT-SQLCODE,
W04-OUT-SQLSTATE
END-IF
END-IF
END-IF
END-IF
END-IF
Back to top
View user's profile Send private message

mainframe_96

New User


Joined: 18 Dec 2006
Posts: 13
Location: usa

PostPosted: Mon Jul 02, 2007 9:55 pm    Post subject: Re: Not reaching End-of-Table.
Reply with quote

Try Reorganizing the code more like this. Also you are moving test var2 to both fields. I am not sure if you intended to do that.

Code:
PERFORM OPEN CURSOR.

You are checking if open cursor was successful

IF SQLCODE = 0
PERFORM Fetch-process until EOF = 'y'
END-IF

Fetch-process.

EXEC SQL
FETCH CVR_CSR
INTO
:TEST-VAR-1,
:TEST-VAR-2
END-EXEC

Checking cursor Fetch sql code

If sqlcode = 0
Perform Move-variables
else
If Sqlcode = 100
Move ?y? to Eof
Display 'end of fetch'
Perform Exit para
else
display 'error and sql codes'


Move-variables.
MOVE TEST-VAR-1 TO W04-VAR-CID .
MOVE TEST-VAR-2 TO W04-VAR-MC .
IF W04-VAR-SCH = 2 OR 3 OR 4 OR 9 OR 35 OR 45 THEN
CALL "RTGFPRPC" USING W04-VAR-CID, W04-VAR-PID,
RTGIXML, W04-END-MESSAGE, W04-OUT-MESSAGE,
W04-OUT-SQLCODE, W04-OUT-SQLSTATE
ELSE
IF W04-VAR-SCH = 70 OR 72 OR 79 OR 82 OR 83
OR 84 OR 85 OR 87 OR 91 THEN
CALL "RTGFOTHC" USING W04-VAR-CID,
RTGIXML, W04-END-MESSAGE,
W04-OUT-MESSAGE, W04-OUT-SQLCODE,
W04-OUT-SQLSTATe
end-if.
Back to top
View user's profile Send private message
catchyblues

New User


Joined: 28 Dec 2005
Posts: 24

PostPosted: Mon Jul 02, 2007 10:36 pm    Post subject:
Reply with quote

I don't think the problem is with the restructuring.....I have even restructured the code but wit no effect....

Because the fetch works fine and also the processing further to it. The problem starts when I reach the last record in the view. After reading the last record, the cursor position is still positioned at tht record only and hence it's fetching the last record infinitely. I using IBM Debugger to debug the program.



Cheers
CB
Back to top
View user's profile Send private message
vijayamadhuri

Active User


Joined: 06 Apr 2005
Posts: 180

PostPosted: Mon Jul 02, 2007 11:42 pm    Post subject: Re: Not reaching End-of-Table.
Reply with quote

Can u tell us the SQL CODE U R getting after the last record is read?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jul 03, 2007 1:12 am    Post subject:
Reply with quote

Hello,

You might add a line of code to display the sqlcode on each iteration.

You might also post the other code involved.

How sure are you that you are actually reading the last row repeatedly or simply re-processing the values already in the variables.
Back to top
View user's profile Send private message
catchyblues

New User


Joined: 28 Dec 2005
Posts: 24

PostPosted: Tue Jul 03, 2007 10:17 pm    Post subject:
Reply with quote

The sqlcode on the fetch is returning Zero instead of +100. Even after the last record has been reached, when the control comes to that section, the Fetch still retrieves the last record(with SQL Code 0) instead of returning a SQL code +100.

It's only the Fetch on the last record which is a problem. For ex:- if I have 10 matching records in the View, the cursor retrieves each of the 10 records one-by-one. But after the last record has been fetched, the cursor is still positioned at the last recrod and hence fetching that record only. And If i have only one record in the view, the cursor keeps on fetching the same record. Hope I'm making myself clear here....

Repasting the code for your reference

PERFORM OPEN CURSOR.

IF SQLCODE = 0
PERFORM EC-PROCESS UNTIL W04-CVR-CSR-FLAG = 'Y'
END-IF


EC-PROCESS SECTION.

EXEC SQL
FETCH CVR_CSR
INTO
:TEST-VAR-1,
:TEST-VAR-2
END-EXEC

IF SQLCODE NOT = 0 AND SQLCODE NOT = 100
MOVE SQLSTATE TO W04-OUT-SQLSTATE
GO TO AA-999-EXIT
ELSE
IF SQLCODE = 100
MOVE 'Y' TO W04-CVR-CSR-FLAG
MOVE SQLSTATE TO W04-OUT-SQLSTATE
PERFORM CLOSE-CVR-CSR
ELSE
IF SQLCODE = 0 THEN
MOVE TEST-VAR-1 TO W04-VAR-CID
MOVE TEST-VAR-2 TO W04-VAR-MC
IF W04-VAR-SCH = 2 OR 3 OR 4 OR 9 OR 35 OR 45 THEN
CALL "RTGFPRPC" USING W04-VAR-CID
ELSE
IF W04-VAR-SCH = 70 OR 72 OR 79 OR 82 OR 83
OR 84 OR 85 OR 87 OR 91 THEN
CALL "RTGFOTHC" USING W04-VAR-CID,
END-IF
END-IF
END-IF
END-IF
END-IF
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 04, 2007 12:32 am    Post subject:
Reply with quote

Hello,

When you do this
Quote:
You might also post the other code involved.
we may be able to offer suggestions.

You need to at least post the cursor declaration and all of the cursor processing and the variable definitions referenced by the cursor and other sql code.

You might also check for other references to sqlcode and make sure that nothing is moving zeros to it. After each iteration, you might want to move some constant value to the test-vars to see if the code really reads the last row repeatedly or is running on whatever was left in the variables.
Back to top
View user's profile Send private message
catchyblues

New User


Joined: 28 Dec 2005
Posts: 24

PostPosted: Thu Jul 05, 2007 1:49 am    Post subject:
Reply with quote

attaching the text file(rtg010.txt) containing most of the code.......

btw, I'm checking for the sqlcode at the end of the fetch and if it's zero, i'm doin further processing. Also checking for sqlcode 100, to exit the processing......And I'm pretty sure that the fetch is failing at the last record. If it were repeatedly running on the values left-over in the variables, then it wld happen for each and every record in the view. But that's not case as the fetch very much retrieves all the relevant record until the last one.....

I hope i'm clear wit the problem i'm facing.... n thnx a lot for ur patience

cheers
CB

dick scherrer wrote:
Hello,

When you do this
Quote:
You might also post the other code involved.
we may be able to offer suggestions.

You need to at least post the cursor declaration and all of the cursor processing and the variable definitions referenced by the cursor and other sql code.

You might also check for other references to sqlcode and make sure that nothing is moving zeros to it. After each iteration, you might want to move some constant value to the test-vars to see if the code really reads the last row repeatedly or is running on whatever was left in the variables.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jul 05, 2007 2:13 am    Post subject:
Reply with quote

Hello,

I'll try to look thru the source later this evening, but i believe we are not in sync with this
Quote:
And I'm pretty sure that the fetch is failing at the last record. If it were repeatedly running on the values left-over in the variables, then it wld happen for each and every record


It is my guess that the fetches all work until the end. After all of the rows have been fetched, the program continues to run and is either not getting or is "clearing" the 100 and acting like a row was fetched. If code is put in to force some constant into the ws variables where the database data is placed and those variables are displayed on each iteration, it can be deterined if the last row is repeatedly being fetched/returned somehow or if the code is simply using the values from the last successful read.

You haven't moved anything to the sql control info have you? There's no FETCH CURRENT by chance? Just guesses without having looked at the code. . .
Back to top
View user's profile Send private message
catchyblues

New User


Joined: 28 Dec 2005
Posts: 24

PostPosted: Fri Jul 06, 2007 3:31 pm    Post subject:
Reply with quote

Hi Scherrer,


Could you get some time to look through my code please ?


cheers
CB
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Fri Jul 06, 2007 5:22 pm    Post subject:
Reply with quote

Quote:
IF SQLCODE = 100 THEN
MOVE 'Y' TO W04-CVR-CSR-FLAG
MOVE 'NO VALID COVERS FOUND ON THIS POLICY' TO
W04-OUT-MESSAGE
MOVE SQLSTATE TO W04-OUT-SQLSTATE
MOVE 'Y' TO W04-CVR-CSR-FLAG
EXEC SQL
CLOSE CVR_CSR
END-EXEC



catchyblues,

If the SQLCODE =+100, you are closing the cursor which is executing successfully and returning SQLCODE=0, so your original SQLCODE from fetch which was +100 is now over written by 0. I hope this is enough to make you fetch one more time.

Think it over and let me know if my observation helped
Back to top
View user's profile Send private message
catchyblues

New User


Joined: 28 Dec 2005
Posts: 24

PostPosted: Fri Jul 06, 2007 9:32 pm    Post subject:
Reply with quote

hi,

My cursor wasn't returning a SQLCODE 100 for the fetch and that's the main problem.... If the control comes to the piece of code u've mentioned, then CVR-CSR-FLAG will then set to be 'Y' and the control will not return to the FETCH para as I'm doing the perform process until the flag is set to Y.... i hope i'm rite here....


prasadvrk wrote:
Quote:
IF SQLCODE = 100 THEN
MOVE 'Y' TO W04-CVR-CSR-FLAG
MOVE 'NO VALID COVERS FOUND ON THIS POLICY' TO
W04-OUT-MESSAGE
MOVE SQLSTATE TO W04-OUT-SQLSTATE
MOVE 'Y' TO W04-CVR-CSR-FLAG
EXEC SQL
CLOSE CVR_CSR
END-EXEC



catchyblues,

If the SQLCODE =+100, you are closing the cursor which is executing successfully and returning SQLCODE=0, so your original SQLCODE from fetch which was +100 is now over written by 0. I hope this is enough to make you fetch one more time.

Think it over and let me know if my observation helped
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 06, 2007 10:00 pm    Post subject:
Reply with quote

Hello,

Quote:
After each iteration, you might want to move some constant value to the test-vars to see if the code really reads the last row repeatedly or is running on whatever was left in the variables.

Have you done this yet? It will be interesting to see the outcome.
Back to top
View user's profile Send private message
catchyblues

New User


Joined: 28 Dec 2005
Posts: 24

PostPosted: Fri Jul 06, 2007 10:20 pm    Post subject:
Reply with quote

Hi,

I couldn't get to test it again as i had further updations to make on other programs......

Btw did u get time to read through the code for any obvious mistakes ?

dick scherrer wrote:
Hello,

Quote:
After each iteration, you might want to move some constant value to the test-vars to see if the code really reads the last row repeatedly or is running on whatever was left in the variables.

Have you done this yet? It will be interesting to see the outcome.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 06, 2007 11:39 pm    Post subject:
Reply with quote

Hello,

When this bubbles back near the top of your priorities, run the test and post what the results are. A few additional testing thoughts follow:

Something i do not see in the code is some kind of test to see if you are in a loop and if so, stop the run. I would think that checking for 1000 (or some better number) and terminating would be a good idea while you are working on this situation. I also do not see any diagnostic displays - without them, how did you determine you looping at end of set?

The code sometimes moves info to "OUT_MESSAGE" and sometimes to "END MESSAGE". Might this cause any confusion?

After this sql
Code:
           EXEC SQL
              FETCH CVR_CSR
                  INTO
               :M-COVR-COVER-ID:W04-VAR-CVRID-NULL-IND,
               :W04-MODCODE:W04-VAR-MODCODE-NULL-IND,
               :W04-CVRCODE:W04-VAR-CVRCODE-NULL-IND,
               :W04-SCHDNO:W04-VAR-SCHDNO-NULL-IND
           END-EXEC

it may help to add a display to show the sqlcode and the "fetched" fields. The display should be placed before the next statement
Code:
           IF SQLCODE = 0 THEN
              MOVE M-COVR-COVER-ID TO W04-VAR-CID


This test should demonstrate whether a 100 is ever returned or if something else is going on. When you have time to test, someone will be here.

It may also help if you post your sqlca copybook.
Back to top
View user's profile Send private message
Bitneuker

CICS Moderator


Joined: 07 Nov 2005
Posts: 1105
Location: The Netherlands at Hole 19

PostPosted: Sat Jul 07, 2007 12:30 am    Post subject:
Reply with quote

Why not test the SQLCODE for +100 instead of 100? You also test for -8xx. Couldn't find in your download how SQLCODE is defined.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jul 07, 2007 12:41 am    Post subject:
Reply with quote

Hi George,

It is in the copybook that maybe we'll see. . .
Back to top
View user's profile Send private message
catchyblues

New User


Joined: 28 Dec 2005
Posts: 24

PostPosted: Sat Jul 07, 2007 12:48 am    Post subject:
Reply with quote

hi,

i'll try putting display statements for the records fetched and also will check the count at the last fetched record...... will post the test results tomm......

thanks a lot for ur time spent on this.....

thnx once again....

cheers
CB

dick scherrer wrote:
Hello,

When this bubbles back near the top of your priorities, run the test and post what the results are. A few additional testing thoughts follow:

Something i do not see in the code is some kind of test to see if you are in a loop and if so, stop the run. I would think that checking for 1000 (or some better number) and terminating would be a good idea while you are working on this situation. I also do not see any diagnostic displays - without them, how did you determine you looping at end of set?

The code sometimes moves info to "OUT_MESSAGE" and sometimes to "END MESSAGE". Might this cause any confusion?

After this sql
Code:
           EXEC SQL
              FETCH CVR_CSR
                  INTO
               :M-COVR-COVER-ID:W04-VAR-CVRID-NULL-IND,
               :W04-MODCODE:W04-VAR-MODCODE-NULL-IND,
               :W04-CVRCODE:W04-VAR-CVRCODE-NULL-IND,
               :W04-SCHDNO:W04-VAR-SCHDNO-NULL-IND
           END-EXEC

it may help to add a display to show the sqlcode and the "fetched" fields. The display should be placed before the next statement
Code:
           IF SQLCODE = 0 THEN
              MOVE M-COVR-COVER-ID TO W04-VAR-CID


This test should demonstrate whether a 100 is ever returned or if something else is going on. When you have time to test, someone will be here.

It may also help if you post your sqlca copybook.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jul 07, 2007 1:39 am    Post subject:
Reply with quote

You're welcome icon_smile.gif

As i mentioned, someone will be here. . .
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sat Jul 07, 2007 3:12 am    Post subject:
Reply with quote

Bitneuker:
SQLCODE is defined in the DB2 manual.
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/DSNSQJ10/5.1.5.2?SHELF=&DT=20040216135741&CASE=
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Getting soc4 on internal table in cobol abdulrafi COBOL Programming 13 Fri May 06, 2016 3:39 pm
No new posts Difference space showed for TS and it... autobox DB2 1 Thu Apr 14, 2016 1:07 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us