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

Problems with Commit


IBM Mainframe Forums -> COBOL Programming
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
simha_it

New User


Joined: 05 May 2005
Posts: 30

PostPosted: Wed Aug 10, 2011 6:44 pm
Reply with quote

Hi,
Here I am giving step by step what I am doing now. At the end I will explain about the problem.

1. I am issuing one cursor on table A with 'For Update' clause (where Status = 'N').
2. calling one third party module (say X) which internally issuing 'Commint' up on successful call (this is not in our control. we r just isuing a call).
3. Inserting one row on table A
4. Updating same table A with status = 'D'

while updating the cursor, we are getting an error saying that, cursor you are using in UPDATE or DELETE is not Opened (bcoz issuing commit in step 2).

I have modified the cursor with WITH HOLD option and kept 'For Update' clause as it is. And executed above 4 steps. But now we are getting different error (SQL CODE = -508 which means cursor used in UPDATE or Delete is not pointing to a proper row) while updating the table A. I hope this is clear...

Can anyone suggest how to achieve this.

Note: I can't avoid Commit (in step 2)

Thanks
simha
Back to top
View user's profile Send private message
Soumik Das

New User


Joined: 06 Aug 2011
Posts: 25
Location: India

PostPosted: Wed Aug 10, 2011 6:59 pm
Reply with quote

Show us some of the parts of the code. That will help us analyze it further.

Anyway, why don't you commit after the update.

Judging by the steps you discussed you can do something like this

declare cursor with hold for select (use the select to get the non-unique keys)
open cursor
fetch cursor
do while(sqlcode NE 0)
insert entry
update entry (using the non-unique keys)
issue commit
fetch cursor
end do
close cursor


This is the way I have been doing it. Works pretty well
Back to top
View user's profile Send private message
simha_it

New User


Joined: 05 May 2005
Posts: 30

PostPosted: Wed Aug 10, 2011 7:17 pm
Reply with quote

After insert, I was not able to do an update. It is throwing an error during the update itself. If I use With hold option, I am getting SQL CODE = -508. If I don't use with hold option, getting SQL CODE = -507 (cursor not open). As I mentioned earlier, after fetch I am making call to third party module which is issuing a commit.
Back to top
View user's profile Send private message
Soumik Das

New User


Joined: 06 Aug 2011
Posts: 25
Location: India

PostPosted: Wed Aug 10, 2011 7:32 pm
Reply with quote

It really looks strange you know!!
Why would you want to insert something in the same table just before an update?

It seems to me:

a) you are not checking for SQLCODE=100 as a loop-end-test condition OR

b) the insert is causing trouble OR

c) the table has no unique index defined.

Atleast paste pieces of the dump where you find the SQLCODE.

Sometime it shows information more than what we keep looking for icon_smile.gif.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Aug 10, 2011 7:55 pm
Reply with quote

you issue a commit after completion of a logical unit of work (this would be a cycle)
actually several -

a logical unit of work is:
  • fetch cursor
  • singelton insert
  • update current cursor


after a single cycle
(actually should be many 100's if not 1000's)
you then issue the call for the commit.

you do not commit after a fetch and before an insert.

what does status contain in the inserted row. hopefully not 'N'.
Back to top
View user's profile Send private message
Soumik Das

New User


Joined: 06 Aug 2011
Posts: 25
Location: India

PostPosted: Wed Aug 10, 2011 8:00 pm
Reply with quote

Oh yes thanks dbz..I missed this crucial part

declare cursor with hold for select (use the select to get the non-unique keys)
open cursor
fetch cursor
do while(sqlcode NE 0)
insert entry
update entry (using the non-unique keys)
if UOW_done then issue commit
fetch cursor
end do
close cursor


should re-check stuff after I type... icon_eek.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Aug 11, 2011 12:27 am
Reply with quote

Hello,

Does this mean the issue is solved or is there still some problem?

Quote:
should re-check stuff after I type...
Yup, so should we all icon_wink.gif
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

 


Similar Topics
Topic Forum Replies
No new posts C++ problems Java & MQSeries 3
No new posts MFT File transfer problems All Other Mainframe Topics 5
No new posts TSO initial setup / problems TSO/ISPF 9
No new posts filter COMMIT/ROLLBACK statements DFSORT/ICETOOL 13
No new posts RACF passphrase implementation problems All Other Mainframe Topics 1
Search our Forums:

Back to Top