Reorg of very large tables

[Molly]

Does anyone know of any good tools for fast, on-line reorgs of a very
large DB2 UDB table?

We need to reorg a couple of our large (+1 billion rows) tables. We
migrated from V7.2 to V8.1, but we still have type-1 indexes defined on
our tables, and cannot take advantage of the new Online Reorg utility
in V8. We could drop and recreate our indexes, but we have a lot (20+)
indexes on some of our larger tables, and that would take days, which
we don't have.

REORGCHK is starting to show that these tables are ready for a reorg,
and we'd like to get some of the free space back, to avoid expands, but
I'm not sure how.

Does anyone else have this problem? How are you reorging your
datawarehouse tables or are you not reorging them at all? Are there
any third party tools that would allow us to do inplace reorgs without
converting to type-2 indexes?

Any advice would be appreciated.

Thanks

[Jean-Marc Blaise]

"Molly" <mo*******@excite.com> a écrit dans le message de
news:11**********************@g43g2000cwa.googlegr oups.com...

Does anyone know of any good tools for fast, on-line reorgs of a very
large DB2 UDB table?

We need to reorg a couple of our large (+1 billion rows) tables. We
migrated from V7.2 to V8.1, but we still have type-1 indexes defined on
our tables, and cannot take advantage of the new Online Reorg utility
in V8. We could drop and recreate our indexes, but we have a lot (20+)
indexes on some of our larger tables, and that would take days, which
we don't have.

REORGCHK is starting to show that these tables are ready for a reorg,
and we'd like to get some of the free space back, to avoid expands, but
I'm not sure how.

Does anyone else have this problem? How are you reorging your
datawarehouse tables or are you not reorging them at all? Are there
any third party tools that would allow us to do inplace reorgs without
converting to type-2 indexes?

Any advice would be appreciated.

Thanks

Hi Molly,

A couple of suggestions from our experience on a 1.7 TB Datawarehouse:

* look for MDC for 2 reasons: reads using BID index and controlled writting
thru Rollup (FP9) - very efficient !

* big tables could be split using UNION ALL views (we do that for over 1
billion rows)

* you could even split and use MDC ... for bigger tables

* partition the database (we have 4 to 12 partitions depending on
environments)

We do occasionnally Reorgs, and MDC will help us avoid those needs (we
introduced MDC recently in our DB) ... basically, avoid deletes or updates,
and replace data will reduce the needs to Reorg.

Hope this helps,

Jean-Marc

[UDBDBA]

Hi:
I am not into big databases, but can comment on online reorg based on
OLTP types.

* online-reorg is "trickle" reorg. It is meant to run in a pace causing
least impact to system workload.

* to convert type-1 to type-2, you can use the CONVERT option in reorg
command first.
(After running REORG with CONVERT option, run a db2dart on that object,
it should tell you the index type (dart is offline mode only))

Once you have converted the type-1 to type-2 indexes, start the online
reorg. If it's a readonly database, then you should have least of
headaches with locking like I do with OLTP transactions.

Personally for a DW, just go MDC (especially with Fixpak 9 with lots of
MDC updates for SAP applications)..

Good luck

Vijay