Table size increased drastically after online reorg in db2 (Database Growth)

[former_member432968]

Hi Experts,

Recently we have performed online reorg on table (MSEG) in production system, during online reorg activity we observed table growth at database level.

We have observed total 19 GB increase in table size within two days. So we have aborted online reorg activity immediately and observed database growth was normal.

I'm aware that online reorg of table able will not increase database growth but in my case it was happened.

Now database growth or table size was not reduced after aborting online reorg activity on table MSEG.

Can any one help me to solve this database growth issue due to online reorg on table.

Regards,

Murali

[Frank-Martin]

Hi Murali,

I still do not see why ADMIN_MOVE_TABLE caused problems. I acn only suspect that LOAD played a role here. I leave the question to the ADMIN_MOVE_TABLE/HADR experts.

Regards

Frank

[Frank-Martin]

I reconfirmed with our ADMIN_MOVE_TABLE experts. They share my view.

(1) ADMIN_MOVE_TABLE should not cause any problems if used properly.

Problems may arise if option COPY_USE_LOAD has been used and the standby database could not acccess the copy file or if the standby database has not been able to provide enough space for the table copy created by ADMIN_MOVE_TABLE.

(2) Inplace reorg should not increase the table size if the table Definition did not Change.

Please check if the table compression option has been changed or if APPEND MODE has been activated for the table.

Regards

Frank

[Frank-Martin]

Hi Murali,

I do not see how the space can be regained without finishing any kind of REORG.

Did you use LOAD in your ADMIN_MOVE_TABLE tests? This can put a table on a standby inaccessible mode when the LOAD log file is not accessible on the standby.

A standard ADMIN_MOVE_TABLE run can obviously generate a larger amount of log volume but should not put the table in inaccessible mode.

Regards

Frank

[former_member432968]

Hi Frank,

Can we perform reorg activity or ADMIN_MOVE_TABLE procedure to regain the space???

Coming to ADMIN_MOVE_TABLE procedure, we were successful in reclaiming space on database using this procedure without any performance issues in productions system (Primary), but we faced issue (table inaccessible) when we performed failover operation.

Finally we have aborted failover operation and restored standby database with primary database online backup and rebuild DR site.

This is the reason why we are not opting for ADMIN_MOVE_TABLE procedure.

Thanks and Regards,

Murali

[Frank-Martin]

Hi Murali,

yes, I also do not see why Inplace Reorg should permanently increase the table size unless you have changed the compression settings of the table.

Inplace Reorg frees up space at the end of the table. At the end of Inplace Reorg a truncate will be performed and space at the end of the table will be returned to the tablespace. Did you finish the Inplace Reorg or abort it somewhere in the middle?

I am not a big fan of Inplace Reorg and always recommend to use DB6CONV to reorg a table if this is neccessary at all. Db6CONV uses ADMIN_MOVE_TABLE to rebuild the table. This has a number of advantages. A new compression dictionary will be created, LOB data will be reorged or inlined …

Regards

Frank

[former_member432968]

Hi Frank,

Thanks for your inputs.

Did you finish the Inplace Reorg or abort it somewhere in the middle?

Yes, we have aborted/suspended online reorg activity due to performance issue.

But now my concern is database growth due to this activity.

Is it possible to regain the space? If yes, kindly guide me how to achieve it.

Earlier we have used admin_move_table procedure to rebuild the table but due to DR setup was active in our production system, we observed inconsistency (table inaccessible) in standby database.

Thanks and Regards,

Murali