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
 

 

Commit after delete statement failed

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jan 10, 2007 2:32 pm    Post subject: Commit after delete statement failed
Reply with quote

hi,

i want to delete 5 rows from a table ... after successfull execution of 3 ..4th row was not found and the delete stmt failed ....will the first 3 rows be restored ...ie will a roll back happen?

thanks
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Jan 10, 2007 2:41 pm    Post subject:
Reply with quote

HI there,

When your program abends, (if it truly abends), the system will automatically 'rollback' (backout) all of your updates to the last 'commit', or in the absence of a 'commit', to the beginning of your program.

So if u have commit stmt after every delete or befor the abends occurs then first 3 rows wont be there if it abends while deleting 4th row otherwise first 3 row will be restored
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Jan 10, 2007 2:43 pm    Post subject: Re: commit after delete
Reply with quote

hI'
ashimer wrote:
i want to delete 5 rows from a table ... after successfull execution of 3 ..4th row was not found and the delete stmt failed ....will the first 3 rows be restored ...ie will a roll back happen?
It depends....I assume this was a program? Was his a delete under a cursor or separate deletes? What did the program do when the 4th row was not found? Did it abend or continue? More information is helpful....

Bill
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jan 10, 2007 2:49 pm    Post subject:
Reply with quote

the delete was not using cursor ..it was normal delete stmt .... the program deleted the 3 rows ....then on sqlcode 100 at 4th the program abended ..but still the first 3 rows were not restored ...... does delete stmt does a commit after successfull execution ? ...
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Jan 10, 2007 3:02 pm    Post subject:
Reply with quote

Hi There,

Do u have any explicit commit befor the job abends?
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Jan 10, 2007 3:05 pm    Post subject:
Reply with quote

ashimer wrote:
the delete was not using cursor ..it was normal delete stmt .... the program deleted the 3 rows ....then on sqlcode 100 at 4th the program abended ..but still the first 3 rows were not restored ...... does delete stmt does a commit after successfull execution ? ...
Within the program, commits are not automatic, they must be specifically requested. I'd assume that the entire LUW was backed out. Can the program handle the sqlcode 100 and just continue without abending?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jan 10, 2007 3:17 pm    Post subject:
Reply with quote

there is no explicit commit ..once sqlcode 100 is found it calls another subroutine to abend ...
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts FTP - JCL failed while passing FTP co... Suneetha1612 JCL & VSAM 12 Wed Nov 16, 2016 7:33 pm


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