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
 

 

we should not commit every time . why?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
uma rao
Currently Banned

New User


Joined: 20 Oct 2007
Posts: 8
Location: delhi

PostPosted: Mon Oct 22, 2007 12:02 pm    Post subject: we should not commit every time . why?
Reply with quote

can anybody clarify this?
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10232
Location: italy

PostPosted: Mon Oct 22, 2007 1:09 pm    Post subject: Reply to: we should not commit every time . why?
Reply with quote

Can You clarify, please, what You/the_interviewer means by "every time"
Back to top
View user's profile Send private message
uma rao
Currently Banned

New User


Joined: 20 Oct 2007
Posts: 8
Location: delhi

PostPosted: Mon Oct 22, 2007 2:13 pm    Post subject: Reply to: we should not commit every time . why?
Reply with quote

we should not commit every time why ? and what we have to do?

for example you opened cursor and do some operations, and then you have one statement like if sqlcode=00 then commit else roll back.Then you will do further operations.

in these type of situations what you will do?
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: Mon Oct 22, 2007 8:17 pm    Post subject:
Reply with quote

Hello,

Typically a commit is issued at the end of a Logical Unit of Work (LUW).

The application process defines what a LUW is.

Commits at the end of an LUW are to provide a stable point from which a restart might be done.

Quote:
I think the question would be " why shouldn't we use COMMIT more often?".
More often than what?

Quote:
for example you opened cursor and do some operations, and then you have one statement like if sqlcode=00 then commit else roll back.Then you will do further operations.
in these type of situations what you will do?
Please elaborate on what you want to learn. In your example, the code "keeps" the update(s) when the sql was successful and "backs them out" when the sql is not successful. Usually commit/rollback this is not coded after every sql statement.
Back to top
View user's profile Send private message
uma rao
Currently Banned

New User


Joined: 20 Oct 2007
Posts: 8
Location: delhi

PostPosted: Tue Oct 23, 2007 9:43 am    Post subject: Reply to: we should not commit every time . why?
Reply with quote

Thank you d.sch. May be your way of explanation helps me to elaborate my view!
Back to top
View user's profile Send private message
muthuvel

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Tue Oct 23, 2007 11:05 am    Post subject: Reply to: we should not commit every time . why?
Reply with quote

Uma,
This is what my DBA had as the answer
Quote:
This is because.....

Whenever a commit is done the data in the bufferpool is written to the disk.........

If frequent commits are made then it will be additional overhead to the system...........

bufferpool is like RAM in our PC......
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10232
Location: italy

PostPosted: Tue Oct 23, 2007 2:46 pm    Post subject: Reply to: we should not commit every time . why?
Reply with quote

Quote:
Whenever a commit is done the data in the bufferpool is written to the disk.........


I would say that Your DBA got things wrong..
Data Buffer flushing depends only on buffer management not on commits

DB2 will write log buffers, but, that' s all

DB2 is smart enough to apply changes ,
restarting after a failure, for committed data only..
and discard in flight updates
Back to top
View user's profile Send private message
Itanium

Active User


Joined: 22 Jan 2006
Posts: 114
Location: India

PostPosted: Thu Jul 31, 2008 4:25 pm    Post subject:
Reply with quote

is there any chance for the data to be in buffer even after the commits are done ?

When we then extract that table by a 3rd party tool.. will it be able to get the date from buffer also ?
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 Jul 31, 2008 8:20 pm    Post subject:
Reply with quote

Hello,

Quote:
is there any chance for the data to be in buffer even after the commits are done ?
Other than when the database is re-cycled, there is pretty much always data in the buffers. One of the ways the database improves performance is by accessing data from the buffer rather than going to the dasd to retrieve it.

Quote:
When we then extract that table by a 3rd party tool..
Unless there are problems with the tool, you should get the correct data. Youmight want to talk with the support people from the tool vendor and ask if there is some known problem. . .

Do you have a problem or is the question for better understanding?
Back to top
View user's profile Send private message
Itanium

Active User


Joined: 22 Jan 2006
Posts: 114
Location: India

PostPosted: Wed Aug 06, 2008 7:12 pm    Post subject:
Reply with quote

Thanks Dick, for your clarification.

We had an issue in our prodbox, where the data though commited was still there in the Buffers. And when we used BMC utility to extract the table, it missed few thousand records and causing major issues.

Then contacted with the DBAs, they told this has occured previously and suggested us to have a image copy step before download. This resolved the issue.

Thanks,
Itanium
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: Wed Aug 06, 2008 9:50 pm    Post subject: Reply to: we should not commit every time . why?
Reply with quote

You're welcome icon_smile.gif

Thank you for posting what you found.

d
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 -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm
No new posts CICS transaction slow response time vasanthz All Other Mainframe Topics 3 Thu Jan 19, 2017 1:31 am
No new posts DD DUMMY & CPU TIME Nileshkul Testing & Performance analysis 16 Mon Dec 19, 2016 11:35 pm
This topic is locked: you cannot edit posts or make replies. Get a job submitted itself every time... arunsoods JCL & VSAM 3 Tue Nov 22, 2016 4:05 pm


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