problem in logic for fetching from DB2

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

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

Suresh Ponnusamy · Posted: Fri Jul 11, 2008 7:05 pm

Please let us know the definition of SQLCODE-CHK.

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

defined as s9(3).

ashimer · Posted: Fri Jul 11, 2008 7:27 pm

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 ...

Suresh Ponnusamy · Posted: Fri Jul 11, 2008 7:50 pm

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.

ashimer · Posted: Fri Jul 11, 2008 8:24 pm

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 ....

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

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'.

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

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.

dbzTHEdinosauer · Posted: Fri Jul 11, 2008 8:34 pm

I agree with ashimer.

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

performing a para.. and no, i have no gotos here..

ashimer · Posted: Fri Jul 11, 2008 9:04 pm

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 ..

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

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..

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

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?

Suresh Ponnusamy · Posted: Fri Jul 11, 2008 11:54 pm

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?

dick scherrer · Posted: Sat Jul 12, 2008 12:12 am

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".

Craq Giegerich · Posted: Sat Jul 12, 2008 12:16 am

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

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.

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

dick scherrer · Posted: Sat Jul 12, 2008 7:34 pm

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.

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

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..

enrico-sorichetti · Posted: Sun Jul 13, 2008 12:06 pm

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

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

enrico-sorichetti · Posted: Sun Jul 13, 2008 12:20 pm

but You did not show it in the sample rows

You showed only

darakhshan · New User Joined: 03 Mar 2008 Posts: 32 Location: India

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.

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.

enrico-sorichetti · Posted: Sun Jul 13, 2008 12:36 pm

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....