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
 

 

problem in logic for fetching from DB2
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
darakhshan

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Fri Jul 11, 2008 6:47 pm    Post subject: problem in logic for fetching from DB2
Reply with quote

I am facing a problem with the below and i'm just not able to debug it.
i have a cursor from which i need to fetch data, and this i'm performing the para below until end-of-csr is true to achieve it

Code:

 
     **************************                         
          FETCH-FROM-CURSOR.                     
      **************************                     
              SET  NOT-END-OF-CSR TO TRUE.               
              MOVE ID1  TO ID-ONE   
              EXEC SQL                                   
                  FETCH INDVDL                           
                  INTO :VAL1           
              END-EXEC                                   
              MOVE     SQLCODE TO SQLCODE-CHK         
              EVALUATE SQLCODE-CHK                       
              WHEN 0                                     
                  PERFORM DEL-FROM-TABLE               
                     THRU DEL-EXIT                     
                  PERFORM INSERT-INTO-TABLE             
                     THRU INSERT-EXIT                                               
              WHEN +100                                 
                  SET END-OF-CSR TO TRUE                                                     
              WHEN OTHER                                 
                  SET END-OF-CSR TO TRUE                 
                  MOVE SQLCODE OF SQLCA TO SQL-CODE       
                  PERFORM ERROR-ROUTINE-PARA         
                     THRU ERROR-EXIT                       
                  MOVE ERR-DISPLAY   TO SQL-ERROR-DESC
              END-EVALUATE.                               
          FETCH-EXIT.                                     
              EXIT.   


the issue i'm facing here is that the control somehow always enters into the "WHEN 0" part.(i checked that by placing displays) although the cursor query when run separately fetches only 2 rows.
the looping is going on and on infinitely, and i'm not able to figure out why. can anyone help?
Back to top
View user's profile Send private message

Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Jul 11, 2008 7:05 pm    Post subject:
Reply with quote

Please let us know the definition of SQLCODE-CHK.
Back to top
View user's profile Send private message
darakhshan

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Fri Jul 11, 2008 7:11 pm    Post subject:
Reply with quote

defined as s9(3).
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jul 11, 2008 7:27 pm    Post subject:
Reply with quote

when cursor fetches a row it will return 0 only ...so there is nothing wrong in going to WHEN 0 part ...

Now when its 100 u r setting end-of-cursor to true ..but how are you calling this fetch para in the main section ... post that part of the program ...
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Jul 11, 2008 7:50 pm    Post subject:
Reply with quote

The definition is incorrect.

Actually SQLCODE will be defined as S9(09) COMP which is 5 bytes.

But your definition is S9(03).

So when you get SQLCODE 100, it is getting truncated and always goes to WHEN 0 loop.

Please change the same to S9(09) and try.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jul 11, 2008 8:24 pm    Post subject:
Reply with quote

Suresh,

SQLCODE is defined as S9(9) COMP-5 ....

and S9(3) can accomodate 100 ... i dont think it will get truncated ...

the problem is somewhere outside this ....
Back to top
View user's profile Send private message
darakhshan

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Fri Jul 11, 2008 8:29 pm    Post subject:
Reply with quote

PERFORM FETCH-FROM-CURSOR UNTIL END-OF-CSR

and that's defined as

01 END-OF-CSR-ONE PIC X(01) VALUE 'N'.
88 END-OF-CSR VALUE 'Y'.
88 NOT-END-OF-CSR VALUE 'N'.
Back to top
View user's profile Send private message
darakhshan

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Fri Jul 11, 2008 8:32 pm    Post subject:
Reply with quote

also, on displaying the value, i see that only one row is being fetched.. it always fetches the very first row.. and ends up giving an sqlcode of 0.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 11, 2008 8:34 pm    Post subject:
Reply with quote

I agree with ashimer.

Quote:

SET NOT-END-OF-CSR TO TRUE


this does not make much sense.

don't like perform thru's. they always make me suspect.

are you performing sections or paragraphs. do you have goto's?
Back to top
View user's profile Send private message
darakhshan

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Fri Jul 11, 2008 8:52 pm    Post subject:
Reply with quote

performing a para.. and no, i have no gotos here..
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jul 11, 2008 9:04 pm    Post subject:
Reply with quote

As you said that it always fetches the first row can you chk where you are opening and closing the cursor and is there any commit in the insert or delete paras ????

This cannot be a strange behaviour by COBOL instead could be some fault in coding .... review your program once again ..
Back to top
View user's profile Send private message
darakhshan

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Fri Jul 11, 2008 9:38 pm    Post subject:
Reply with quote

i commented out these two

PERFORM DEL-FROM-TABLE
THRU DEL-EXIT
PERFORM INSERT-INTO-TABLE
THRU INSERT-EXIT

and this is not happening..
i have no commits though.. i tried decalring the cursor with hold.. but still the problem persists..
Back to top
View user's profile Send private message
darakhshan

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Fri Jul 11, 2008 9:46 pm    Post subject:
Reply with quote

i think this is happening because i'm fetching from the table and then performing insert and delete on the same. can that be the problem?
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Jul 11, 2008 11:54 pm    Post subject:
Reply with quote

That means you are fetching a record from the table, then deleting it and then inserting the same record again in to the table is it?
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 12, 2008 12:12 am    Post subject:
Reply with quote

Hello,

It may help if you explain the business requirement that says when a row is "found" you do both a delete and an insert.

More often, i would expect the process to be an insert on a "no hit" and an update or a delete on a "hit".
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Sat Jul 12, 2008 12:16 am    Post subject:
Reply with quote

Quote:
MOVE ID1 TO ID-ONE
EXEC SQL
FETCH INDVDL
INTO :VAL1
END-EXEC


Is ID-ONE the key that is being used in the cursor? If it is then that value has to be set before the cursor is opened!
Back to top
View user's profile Send private message
darakhshan

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Sat Jul 12, 2008 4:13 pm    Post subject:
Reply with quote

This is what i am attempting to do.

My table XYZ has 6 fields- field1, field2, field3, field4, field5, field6. With field1, field2, field3
as inputs, i need to insert a duplicate of the same record into the table XYZ, but i need to change the
value of field6 to 'CURRENT' while inseting this duplicate. However, i am not getting field4, field5 as an input. Also if there are 3 records with this combination in the table, i need to insert 3 duplicate records for each of these.
So in order to insert the duplicate of the said row i need to first fetch field4 and field5
from the same table, and then go ahead with the insert.

My requirement is also that before i insert any record, i should first delete any records with the specified input
having field6 set as 'INVALID'

Example:

TABLE XYZ before operations:

AAA BBB CCC 1 10 OBSOLET
AAA BBB CCC 2 20 OBSOLET
AAA BBB CCC 3 30 OBSOLET
AAA BBB CCC 3 30 INVALID

should now look like

AAA BBB CCC 1 10 OBSOLET
AAA BBB CCC 1 10 CURRENT
AAA BBB CCC 2 20 OBSOLET
AAA BBB CCC 2 20 CURRENT
AAA BBB CCC 3 30 OBSOLET
AAA BBB CCC 3 30 CURRENT

Hence the logic. Hope this explains.
Back to top
View user's profile Send private message
darakhshan

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Sat Jul 12, 2008 4:17 pm    Post subject:
Reply with quote

Quote:
Is ID-ONE the key that is being used in the cursor? If it is then that value has to be set before the cursor is opened!


No, it is not the key that is being used.
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 12, 2008 7:34 pm    Post subject:
Reply with quote

Hello,

Is there more than one cursor used in the code? Is indvdl updatable?

It might be interesting to put a counhter or display in the open cursor code to make sure the open is only executed once.

Please post the cursor declaration and the delete/insert paragraphs.
Back to top
View user's profile Send private message
darakhshan

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Sun Jul 13, 2008 11:47 am    Post subject:
Reply with quote

CURSOR DECLARATION:

EXEC SQL
DECLARE INDVDL CURSOR WITH HOLD FOR
SELECT
FIELD4,
FIELD5
FROM
XYZ
WHERE
FIELD1 = :WS-IN1
AND FIELD2 = :WS-IN2
AND FIELD3 = :WS-IN3
WITH UR
END-EXEC.


DELETE-FROM-TABLE.

EXEC SQL
DELETE FROM XYZ
WHERE
FIELD5 = 'OBSOLET'
AND FIELD1 = :WS-IN1
AND FIELD2 = :WS-IN2
AND FIELD3 = :WS-IN3
END-EXEC.

INSERT-INTO-TABLE.

EXEC SQL
INSERT INTO XYZ
VALUES
(
:WS-IN1,
:WS-IN2,
:WS-IN3,
:VAL1,
:VAL2,
'CURRENT'
)
END-EXEC.

I did put displays.. but the open is happening only once.. only the fetch para is in a loop.. and it fetches the 1st value all the time.. that's why i put a "WITH HOLD" in the cursor, but it did not help..
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 -> COBOL Programming 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 Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm
No new posts Wildcard logic in COBOL dudenithy COBOL Programming 13 Thu Sep 01, 2016 7:36 pm
No new posts Problem with GETMAIN command amitc23 CICS 6 Thu Sep 01, 2016 1:01 pm


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