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

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

Probably. Try this:

http://publib.boulder.ibm.com/infoce.../c0024781.html

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.

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

Are you from New Jersey? You sure do ask a lot of irrelevant questions.

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.

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.)

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.

Thank you very much for the whole information, Mr. Stolze, you wouldn't know how much that helped me.

Again, thank you.

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?

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.