dba-db2.com - db2 performance tuning, DB2 tuning,db2 performance,sql tuning db2, DBA in DB2, DBA Scripts,DB2,DB2 consulting,DB2 consultant,DB2 architecture,DB2 training

Reorg table DB2

19 June,2011 by Tom Collins

Reorg table DB2

Step 1


run reorgchk:

 

------CODE START---------------
db2 reorgchk update statistics on table all > output.out
------CODE END------------------

Running the command this way will run across all tables rather than RUNSTATS across every table.
The reorgchk update statistics report has two sections; the first section is the table information and
the second section is the indexes. If there's an asterisk in the last column a reorganization is needed.
To reorganize the tables with an asterisk in the last column:

 

-------CODE START----------------
db2 reorg table table_name 
-------CODE END------------------

where table_name is the name of the table to be reorganized; for example, MYDB.MYTABLE.
Generally speaking, because most data in databases  is accessed by index, reorganizing tables is usually not as beneficial as reorganizing indexes.

To reorganize the indexes with an asterisk in the last column

 

----------CODE START-------------------
db2 reorg table table_name index index_name
----------CODE END---------------------

where table_name is the name of the table; for example, MYDB.MYTABLE
index_name is the name of the index

Run reorgchk again. The output from reorgchk can then be used to determine whether the reorganization worked and whether it introduced other tables and indexes that need reorganizing.

Reorg table DB2 is straightforward but, If it's a production database , run the db2 reorg procedure at a low activity time. Reorg table DB2 can be a resource intensive procedure

Related Posts

How to monitor a DB2 REORG - DBA DB2

DB2 Used Space and REORG - DBA DB2

DB2 table reorg and table truncate - DBA DB2

Author: Rambler(http://www.dba-db2.com)

Share:

Posted by Tom Collins at 6:00 AM in DBA Scripts, Indexes | Permalink | Comments (3) | TrackBack (0)

Searches: , , , ,

I am new to DB2 10.5 on Windows (I have about 20 years with MS SQL Server) and have a question about reorging indexes in DB2. I have a large table (100 million rows) with about 10 indexes. When I run reorgchk_ix on it, it tells me 4 of the indexes need to be reorganized. My question is, should I reorg each index or just reorg them all? Which would be faster and what is the best practice suggestion? Thank you!

Posted by: Jim Youmans | Aug 2, 2017 12:56:26 PM

Jim - A few things need to be considered such as:
1)ONLINE or OFFLINE for reorg. Doing an ONLINE reorg will allow availability but will take a lot longer.
2)Need to decide if you'll drop and recreate the index or use the REORG facility . Generally - dropping and recreating is quicker - but not always practical
3)Set up the script to run one after the other in low activity time
4)Make sure any REORG table activity is completed first - if a REORG table is running it may cause the REORG index to fail

Posted by: Jack Vamvas | Sep 19, 2017 12:58:48 PM

I just have a question.
I have a table with a clustered index.
That table has also a few UNIQ indexes.
After the load is finished. They're a bunch of transactions(jobs) that modify the table ( update, delete).
After all, the transactions have finished the tables becomes a read-only source for other tables.
Before the read operation starts I do reorg so the data is easily accessed.
The question is do I need to reorg only clustered index or I need to reorg the other indexes (2 UNIQ and 2 indexes)? Or re-clustering is enough?

Posted by: Alex | Apr 1, 2020 3:01:54 PM

Verify your Comment

Previewing your Comment

Posted by: |

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Reorg table DB2

Comments are moderated, and will not appear until the author has approved them.

dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright