Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2007
    Posts
    23

    Unanswered: got a intresting problem

    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
    Last edited by myprotein; 08-15-07 at 08:25.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Aug 2007
    Posts
    23
    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.
    Last edited by myprotein; 08-15-07 at 08:52.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Aug 2007
    Posts
    23
    thank for your post,andy

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

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using?

    Andy

  7. #7
    Join Date
    Aug 2007
    Posts
    23
    8.1, win2003enterprise

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

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you test this scenarion, I doubt it will do what you think.

    Andy

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Aug 2007
    Posts
    23
    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

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Aug 2007
    Posts
    23
    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.
    Last edited by myprotein; 08-16-07 at 06:07.

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by myprotein
    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.
    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.

    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.
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •