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
 

 

Problems with Commit

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
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    Post subject: Problems with Commit
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    Post subject: Reply to: Problems with Commit
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    Post subject: Re: Reply to: Problems with Commit
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    Post subject: Reply to: Problems with Commit
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: 6968
Location: porcelain throne

PostPosted: Wed Aug 10, 2011 7:55 pm    Post subject:
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    Post subject: Reply to: Problems with Commit
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

Site Director


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

PostPosted: Thu Aug 11, 2011 12:27 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problems in File-Aid / DB2 Spufi whil... amitc23 CLIST & REXX 6 Fri Feb 12, 2016 1:59 pm
No new posts Commit limit for Delete query through... mf_karthik DB2 10 Thu Dec 17, 2015 12:56 am
No new posts Commit a DB2 Table on Unix Server and... Vinodh S DB2 3 Sun Apr 12, 2015 5:05 am
No new posts Need information on DB2 COMMIT sivasaras COBOL Programming 9 Wed Jun 11, 2014 7:34 pm
No new posts DB2 Commit thru Xpediter kinatingopi DB2 4 Wed Apr 30, 2014 4:46 am


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