|
View previous topic :: View next topic
|
| Author |
Message |
uma rao
Currently Banned
New User
Joined: 20 Oct 2007
Posts: 8
Location: delhi
|
|
|
|
| can anybody clarify this? |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Can You clarify, please, what You/the_interviewer means by "every time" |
|
| Back to top |
|
|
uma rao
Currently Banned
New User
Joined: 20 Oct 2007
Posts: 8
Location: delhi
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
uma rao
Currently Banned
New User
Joined: 20 Oct 2007
Posts: 8
Location: delhi
|
|
|
|
| Thank you d.sch. May be your way of explanation helps me to elaborate my view! |
|
| Back to top |
|
|
muthuvel
Active User
Joined: 29 Nov 2005
Posts: 217
Location: Canada
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| 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 |
|
|
Itanium
Active User
Joined: 22 Jan 2006
Posts: 114
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Itanium
Active User
Joined: 22 Jan 2006
Posts: 114
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
You're welcome 
Thank you for posting what you found.
d |
|
| Back to top |
|
|
|
|
