Offline reorg :
1. Database will be unavailable during offline reorg. If the db is quiesced then only instance owner will be able perform any operation .. other application will not be able to connect.
2. In offline reorg when we use "use tempspace_name" phrase then actually db2 creates a copy of that table in temp_tablespace by building structure of the table ,,, in second phase db2 starts to copy the data as well in proper way and removes dirty pages.. In last phase once this activity gets completed db2 removed the old table and places the newly organized table in its previous tablespace from temp_tablespace.
If we do not use temp_tablespace then at least double space of the table data size + index size is required to continue the offline reorg.
Online reorg :
In online reorg all application can connect to database .but applications perpormance gets slow.. hence creates lock-waits as well. Here db2 do not need temp_tablespace as db2 part by part reorganizes the table data.. Tables data are moved from one place to another place and leaves a row identifier to mention where the data pages are moved.Clean up only option is useful to clear the dirty pages.
But I personally feel online reorg is not useful for big tables. Most of the time it take s much more time compare to offline reorg timings.
Online reorg is compatible with online database backup...
Please correct me if my understanding is not correct ...
offline reorg will only render the table being reorg unavailable. The db will NOT be offline and does not need to be offline.
ReplyDeleteand to be more accurate, reorg only holds exclusive lock on the table being reorged at replace and index rebuild phases if "allow read access" option is specified.
Delete