How to reorg (DB2/DB6)?

[Former Member]

Hi All,

Recently I had a requirement to REORG a big table occupying around 700 GB size in a tablespace. During this I came across about REORG and wanted to share with you all if that helps you in this regards.

Below are the few options that we can opt to REORG table at DB level with few additional information...

Q) How to REORG a table by avoiding logging

$ DB2 ALTER TABLE <schema_name>.<table_name> ACTIVATE NOT LOGGED INITIALLY
$ DB2 REORG TABLE <schema_name>.<table_name>
$ DB2 COMMIT

Q) How to REORG a table using temporary tablespace

$ db2 REORG TABLE <schema_name>.<table_name> USE temp_tablespace

Make sure you have enough space where the temporary table space resides to avoid unwanted failure and File system full issue.

Q) Script to REORG a set of tables
It is possible to create a script listing sequentially all the tables to be reorganized. The owner of this script should be the DB2 administrator

Create a script:

Script Name - vi reorgtables.sh
reorg table <schema_name>.<table_name> USE PSAPTEMP
reorg table <schema_name>.<table_name> USE PSAPTEMP
reorg table <schema_name>.<table_name> USE PSAPTEMP
reorg table <schema_name>.<table_name> USE PSAPTEMP
reorg table <schema_name>.<table_name> USE PSAPTEMP
.

.

.

.
:wq!

Run the script:
$ db2 -tvf file_name or
$ nohup db2 -tvf Script.sh &  --> this run in background

Q) Reorg index
$ db2 REORG INDEXES ALL FOR TABLE table_owner.table_name

Q) How to check the status of the REORG:
$ db2pd –d <db_name> -reorg
$ db2 "SELECT TABSCHEMA, TABNAME, REORG_PHASE, REORG_STATUS, REORG_START, REORG_END, REORG_PHASE_START FROM SYSIBMADM.SNAPTAB_REORG"

After reorganization of a table, it is required:
• Reorganize all the associated indexes
• Re-generate statistics for both the table and the indexes with below command

$ DB2 RUNSTATS ON TABLE table_owner.table_name FOR INDEXES ALL

Process for REORG: RUNSTAT before REORG --> REORG --> RUNSTAT after REORG

Phases of OFFLINE REORG:

• SORT: is a first phase of reorg where data is sorted according to index if you have specified in the reorg statement, or clustering index is defined on the table. Reorg will take place as per that sorted order.

• BUILD: is the second phase where your entire data is rebuilt and any free space is claimed. This is the most time consuming phase as its operating on the entire data of table and re-organizing it. As of best practice, we use the temporary tablespace where the data is built to reorganize as we do not want to blow up the table's own tablespace.

Note: if you have enough space in your table's tablespace, DO NOT specify the option of temporary tablespace. This improves the performance of next phase which is REPLACE

• REPLACE: is the phase where reorganized table object is copied over to the tablespace where the table is created when temporary tablespace is specified during reorg. This takes a while to complete.

Note: if you do not specify the temporary tablespace, the data reorganization takes place within the same tablespace of the table. In this case, new set of organized table updates the pointer and old table object is dropped. This saves the complete time for copying over the data from temporary tablespace to the table's tablespace. Just ensure that you are using DMS tablespace for the table reorganization to take this benefit, SMS tablespace does not have any effect of it and it always uses a process of copying the object from one location to another location.

• INDEX RECREATE: is the last phase where all the indexes are recreated after the reorg. Nothing is there to tune it as such. Just make sure after the table reorg, you do not require to reorg the indexes explicitly again.

Hope the above information is useful.

Regards,

Prithviraj.