CODEWITHSUNDEEP
db2
senior
senior

Reputation: 2276

db2 reorganize a table

When I alter a table in db2, I have to reorganize it so I execute the next query:

Call Sysproc.admin_cmd ('reorg Table myTable');

I m searching an appropriate solution to reorganize a table when it s altered, or reorganize all the schema after making various modifications

Upvotes: 11

Views: 63131

Answers (3)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11042

You can determine when tables will require a REORG by looking at SYSIBMADM.ADMINTABINFO:

select tabschema, tabname
  from sysibmadm.admintabinfo
 where reorg_pending = 'Y'

You may also want to look at the NUM_REORG_REC_ALTERS column as this may show you additional tables that don't require reorganization due to various ALTER TABLE statements.

Upvotes: 8

AngocA
AngocA

Reputation: 7693

The reorg operation is similar to a defrag in hard disk. It frees empty spaces in pages, and eventually it could reorganize data according to an index. Depending on the features, it creates the compression dictionary and compress data.

As you can see, reorg operation is an administrative task, and it is not necessary each time data is modified. A database could run without reorg.

It order to ease this, DB2 included autonomic features like automatic backup, however this doesn't answer you own question. This will only trigger reorg on tables that need that.

Upvotes: 4

senior
senior

Reputation: 2276

in db2 config we have:

Automatic reorganization (AUTO_REORG) = OFF

we can set auto_reorg to on

Upvotes: 0

Related Questions