Hi All

time1------insert into table----------uncommitted，flushed

time2------drop table---------------uncommitted，flushed

time3------backup online. -----------when finished ,the table will not be included in the image.

time4------resotre.

time5------roll forward. ----------------when it replays the step "insert into table", it cannot find the table in the resotred database , cauz the table is not included in the image. then, I wanna kown if it would populate an error?

thanks a lot

Have you actually had this happen? I would have thought that the backup image would include the table becuase the drop has not been committed and the rollforward would only process committed transactions so that steps 1 & 2 would not be applied.

Andy

not really.
if flushed,the table would be deleted ,so the image would not include it.

rollforward start from the lsn which is higher than the min(lowtranlsn,minbuflsn),in this case, it will start from Time1, because "insert into" has not been committed.

rollforward can be divided into 2 pahse, firstly , it redo(replay) from the min(lowtranlsn,minbuflsn) exactly, though some transactions hasn't been committed. secondly,it undo the transactions that hasn't been committed.

First of all, steps 1 & 2 (as you have them written) have to be in the same unit of work (transaction). Otherwise step 2 will never be accomplished because there will be a lock on the table. Secondly, flushing is not the same as committing. If the table is dropped but not commited, it is still there.

Andy

thank for your post,andy

you main that uncommitted flush just "mark" something on the table?

What DB2 version and OS are you using?

Andy

8.1, win2003enterprise

this topic is just come from imagination,not true,and I haven't done any test about it

If you test this scenarion, I doubt it will do what you think.

Andy

What does "flush" actually mean? Rollback/commit?

When you execute a DROP TABLE, DB2 writes a corresponding log record and places the table into "drop pending" state. Only if you issue a commit, the table will actually be dropped. But if a rollback comes, DB2 has to restore the table to the state it was at the beginning of the transaction. Thus, DB2 cannot simply delete the table just because you run a DROP TABLE.

Oracle took (still takes?) a different approach. They force a COMMIT operation before and after each DDL statement. Thus, the DROP TABLE would become permanent right away.

thanks a lot stolze!

the term "flush" I mean that just is page cleaning, flushing dirty pages to disks.

if it does like what you said, when replay the step "insert into",the table is already there,and is "marked" with some "tags" indicating that itself has been ready to be dropped,but hasn't happened yet. at this time ,would the "insert ino " be successful? i feel it cannot insert into a marked-dropped table .

thanks

Logic and transactional properties (ACID) demands that the table still exists after the restore/rollforward. As Andy already said, you did never commit the DROP TABLE (at the time of the backup), so DB2 can't simply drop/remove it in the restored database.

As for the "drop pending". In the original database, you will see a "drop pending" log record being written, and an appropriate lock on the table itself (to prevent others from accessing this table while it is in drop pending state). There is nothing changed at the table's data. After the restore into a new database, the lock will be gone, of course (locks are not backed up). The "drop pending" log record is not processed because the commit log record for that transaction is not in the log. Thus, after the restore you have a regular table as if the DROP TABLE never happened. (Same applies to the INSERT because that was never committed either before the backup.)

p.s: Flushing buffer pools or not is completely irrelevant for backup processing. DB2's implementation for the backup command has to make sure to write a consistent image. Thus, it may have to grab pages from the buffer pools. But that is something the user is not concerned with. (A buffer pool is just a cache of on-disk data.)

thanks.

but some official documents said that when backup finishing,the current log would be truncated and Buffer Pool will be flushed to assure the image to be taken in a consistent shape.

I suddenly remenbered that , if the DDL statements would be recorded in the logs. meybe there would be some other mechanism used to recored and solve DDL statements.

Do you have a pointer to the truncation of the log at hand? I guess that the explanation is a bit more specific regarding what will be truncated and under which conditions.

How DB2 ensures that the image is consistent is irrelevant for the user. It is an implementation detail. The DBA has to know it, however, because it implies that a backup operation involves more disk activity due to the writes happening. But that doesn't change anything at the conceptual level.

DDLs are recorded in the logs. There are log records for the changes in internal the DB2 catalog tables and also additional log records if objects are created in the tablespaces. But I don't understand what this has to do with your scenario: the table is not dropped in the backup image because that drop was never committed. From an external point of view, the drop never happened.

That are basic transactional concepts, i.e. the ACID properties (atomar, consistent, isolated, and durable). Here we talk about A (atomar = all or nothing). Since the transaction was not completed, the outcome must be "nothing" here.