IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

problem in logic for fetching from DB2


IBM Mainframe Forums -> COBOL Programming
Post new topic   Reply to topic
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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

Moderator Emeritus


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

PostPosted: Sat Jul 12, 2008 12:12 am
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
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
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
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

Moderator Emeritus


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

PostPosted: Sat Jul 12, 2008 7:34 pm
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
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
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Sun Jul 13, 2008 12:06 pm
Reply with quote

maybe I overlooked something,
from theposted rows and the code snippets it does not look like You need a cursor
You are getting field1,fielsd2,field3 from somewhere else

so just do a select with a condition on field1 field2 field3 and field6
to get all the other values

if OK
delete the 'INVALID'
insert the 'CURRENT'

but as I said maybe I overlooked something
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 12:13 pm
Reply with quote

icon_confused.gif
Quote:
just do a select with a condition on field1 field2 field3 and field6


there can be multiple rows with the same field1, field2, field3 combination.. so if there are n rows, i need to do n inserts of 'CURRENT'..

that is the reason i used a cursor.. to fetch the multiple records in a loop..
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Sun Jul 13, 2008 12:20 pm
Reply with quote

but You did not show it in the sample rows

You showed only
Quote:
AAA BBB CCC 3 30 OBSOLET
AAA BBB CCC 3 30 INVALID


and since I suggested also the fileld6 constraint ..... icon_biggrin.gif
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 12:31 pm
Reply with quote

what i now plan to do to get around with this is fetch the cursor resultset into a table, and then loop separately to insert the same into the database.. i think that will work.. but i am not very good at cobol tables. icon_sad.gif is the below fine? pls help me.

DECLARATION:
01 TABLE-2.
02 INDVDL-TAB OCCURS 1 TO 20 TIMES
DEPENDING ON WS-COUNT
INDEXED BY WS-INDEX.
03 FIELD4 PIC S9(9) USAGE COMP.
03 FIELD5 PIC S9(11)V USAGE COMP-3.

I WILL COUNT THE NUMBER OF RECORDS THAT ARE PRESENT IN THE TABLE WITH FIELD1, FIELD2, FIELD3 WITH FIELD6 AS 'INVALID' AND MOVE IT INTO WS-COUNT.
FETCHING:

SET WS-INDEX TO 1
PERFORM FETCH-INDVDL-CURSOR
THRU FETCH-EXIT UNTIL WS-INDEX > WS-COUNT
OR SQLCODE-CHK = +100.

AND THEN WITHIN THE PARA:

*********************
FETCH-INDVDL-CURSOR.
*********************
SET WS-INDEX UP BY 1
EXEC SQL
FETCH INDVDL
INTO :INDVDL-TAB
END-EXEC.
MOVE SQLCODE TO SQLCODE-CHK.
IF SQLCODE = 0
CONTINUE
ELSE
MOVE SQLCODE OF SQLCA TO SQL-CODE
PERFORM 9999-ERROR-ROUTINE-PARA
THRU 9999-EXIT
MOVE WS-ERR-DISPLAY TO SQL-ERROR-DESC
END-IF.

ADD 1 TO WS-ROW

FETCH-EXIT.
EXIT.


WHILE INSERTING:
PERFORM INSERT-INTO-TABLE UNTIL WS-INDEX > WS-ROW

*****************
INSERT-INTO-TABLE.
*****************
MOVE FIELD1 (WS-INDEX) TO VAL1
MOVE FIELD2 (WS-INDEX) TO VAL2

EXEC SQL
INSERT INTO XYZ
VALUES
(
:WS-IN1,
:WS-IN2,
:WS-IN3,
:VAL1,
:VAL2,
'CURRENT'
)
END-EXEC.
IF SQLCODE = 0
CONTINUE
ELSE
MOVE SQLCODE OF SQLCA TO SQL-CODE
PERFORM 9999-ERROR-ROUTINE-PARA
THRU 9999-EXIT
MOVE WS-ERR-DISPLAY TO SQL-ERROR-DESC
END-IF.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Sun Jul 13, 2008 12:36 pm
Reply with quote

please please, watch Your typing,
You make very difficult for people trying to help to follo w Your line of thought

You started by speaking about &duplicating the rows with the "obsolete" string,

now in the last post You speak about the rows with invalid....
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> COBOL Programming Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
No new posts z/vm installation problem All Other Mainframe Topics 0
No new posts Finding faulty logic Subscript out of... COBOL Programming 5
This topic is locked: you cannot edit posts or make replies. Need assistance in job scheduling logic. Mainframe Interview Questions 2
No new posts Job scheduling problem. JCL & VSAM 9
Search our Forums:

Back to Top