Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    45

    Unanswered: The REORG detail

    Hi all,

    Someone asked me about REORG in DB2 (for LUW) in details, what process happens, and stuff, and I just told him that the original table will be replaced by the temporary table.

    But, the thing is, what exactly happen in that exchange process ? Does the original table dropped ? Or does the original table's data deleted and DB2 insert the data from the temporary table to the original one ?

    After searching manuals, I couldn't find the answer until this very moment. Or had I missed any manual ?

    Should anyone care to share his/her knowledge, I'll really appreciate it

  2. #2
    Join Date
    Oct 2008
    Posts
    82
    up to my knowledge.
    reorg main purpose is it will reorganize the table....
    it will not drop the main table...
    it will not replace any tempporary table

    . if this is wrong am really sorry

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by linakichi
    had I missed any manual ?
    Probably. Try this:

    http://publib.boulder.ibm.com/infoce.../c0024781.html
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    It depends on the kind of reorg we are talking about. If you have an in-place reorg, then there will not be a shadow copy of the table in which all the reorg takes place.

    A reorg only operates at the physical level. It doesn't change any meta data, so there is no "dropping" of tables or the like. The content of pages is moved around. If a shadow copy is used, DB2 has to do a "switch" from the original data to the reorganized version. For that, it is sufficient to make sure all changes in the buffer pools are on disk, then a single "rename" of files would do the job. (This is highly simplified, of course.) The idea is that if you look at the DB2 architecture, the upper layers (like RDS, optimizer, catalog, ...) don't even notice that the data underneath has been changed. That's regular "data independence" stuff.

    p.s: Doing delete/inserts would be really stupid because that is really slow.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Aug 2008
    Posts
    45
    Thank you for the replies, I'm currently using DB2 UDB ver. 8.2 and using shadow copy approach.

    And Mr. Stolze, if I may confirm, so basically the shadow copy table will be renamed after the original table, right ?? And if the dropping isn't held, what would happen to the original one ?? Will it remain at the database ? Wouldn't it become a waste of space ?

    I've tried to check original table's memory address by using db2pd, but the reorganized table's address doesn't change, that's why I got the picture that the delete-insert process was executed. (probably my bad, though)

    And since (as you said) doing insert/delete would be stupid, there's no way DB2 could've done it, right ? Thus, how could I check if the shadow copy REALLY replaced the original one ? Could I monitor any object id ? I've tried looking a table/view that contains "object id" for tables but found nothing though

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Are you from New Jersey? You sure do ask a lot of irrelevant questions.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by linakichi
    If I may confirm, so basically the shadow copy table will be renamed after the original table, right ?? And if the dropping isn't held, what would happen to the original one ?? Will it remain at the database ? Wouldn't it become a waste of space ?
    No. Tables are not touched - only the physical storage of tables is modified. Please read my answer above again. You may also want to read up on "data independence" and the ANSI/SPARC architecture used in database systems.

    I've tried to check original table's memory address by using db2pd, but the reorganized table's address doesn't change, that's why I got the picture that the delete-insert process was executed. (probably my bad, though)
    A table doesn't have a "memory address". A table is just a logical container. At the physical level, it consists of a few rows in the catalog and a bunch of pages in a tablespace. The data that is logically in the table is stored on those pages. Reorganization is happening at the page level - and not at the table level. (Tables are used to identify the pages that need to be reorganized.)

    And since (as you said) doing insert/delete would be stupid, there's no way DB2 could've done it, right ? Thus, how could I check if the shadow copy REALLY replaced the original one ? Could I monitor any object id ? I've tried looking a table/view that contains "object id" for tables but found nothing though
    If you are asking for a "could have done it", the answer is that this would be possible. However, a much smarter way is to directly work on the internal data structures and that's what DB2 is doing.

    What I don't get is the "replace" thing. What happens is that you have the original data stored somewhere, then a shadow copy is created (which is reorganized), and then you do an 'atomic' switch-over. Nothing special about that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Aug 2008
    Posts
    45
    Thank you very much for the whole information, Mr. Stolze, you wouldn't know how much that helped me.

    Again, thank you.

  9. #9
    Join Date
    Apr 2008
    Posts
    39
    Stolze, you mentioned that the reorg happens at page level, not at table level. I'm confused with term "rename"...

    When the physical pages are getting reorganized, where does the orginal data and shadow copy come into picture?

    Also, after having the shadow copy, storing the original data somewhere...is it not a wastage of space?

    Could you please clarify?

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I am not sure that I understand your question. The "rename" referred to files/data sets. Tables have no relation to files - tablespaces have.

    Let's assume we are not doing an in-place reorg. In that case, DB2 z/OS creates a new data set where the data is copied to and then reorganized. At the end, DB2 switches-over to the new file (after applying logs if the reorg was run with SHRLEVEL CHANGE) and removes the original file.

    Also, if you don't do an in-place reorg, you cannot touch the original data. So how to do the reorg unless you create a copy of the data to operate on? That's where the "shadow copy" comes into play. Of course, without in-place reorg, you need twice the disk space for the tablespace.
    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
  •