Unanswered: How DB2 actually works when needs to update a table?
I'm new to DB2 and have a quastion.
How actually DB2 works when you run a transaction which contains
for example 10 updates?
which one is correct?
1- copies the rows going to be updated to a temp space -->
writes the updates directly to the main table -->
if Commit happens -- > it's OK! and forgets the temp space
if Rollback happens --> rewrites the temp space to the original table
2- inserts the updates to a temp space -->
if Commit happens --> replaces the old rows in the table with new ones
if Rollback happens --> nothing going to do with the original table and forgets about the temp space
could anyone please describe it with a refrence from IBM or ...
This is what happened in all the relational databases, with little addition
1. get an LSN no (like an internal clock cataching the begining of transaction.
2. get the data pages/index pages into the bufferpool(if they are not already in there).
3. Before image of the data is put in the log buffers.
all the changes that is happening are connected like a vector to that LSN, so that it can be rollback or rollforward during the recovery.
4. make the changes to that data pages and/or indexes.
5. if commit happens the log buffers are written to the log files.
data pages are usually written at a later time.
6. rollback just undo all the changes by either reapplying the changes or if some changes written to the files(in case of long running transactions) read the logs files and undo that changes.
Incase of update the logs contains both the before and after image.