database reorg for large databases?

Greetings, My upper management is interested in what other companies do as far as reorg'ing large database(s). In my case I have a Peoplesoft database that is 336 gigs (used). Recent testing proved that a full database reorg (export/import) takes ~90 hrs. The last time I performed a reorg my database was ~220 gig and I was able to complete this in a weekend. I have presented several options (listed below) to management but they are very much interested in knowing how others deal with large databases and reorgs. If you have a moment and perform(ed) some type of database reorgs on a regular basis please share with me your approach. TIA Option 1 Full database reorg using dbexport/dbimport. Option 2 Break 1 large database into multiple databases (3-4) (by module has been discussed). Use synonyms from the primary database to point to tables located in other databases. Tables reside in "db specific dbspaces (ie, databaseA dbspace1 --> dbspace6, databaseB dbspace6 --> dbspace12,.....you get the idea)" Use dbexport/dbimport to reorg smaller databases over multiple weekends. (one thing to note here, psoft admins would not be fond due to dddaudit reports) Option 3 (I like to call this a "reorg by dbspace") Group tables and indexes together based on a percent of the database into a set of dbspaces. Unload all tables, drop all tables, recreate tables and load 1 after the other, recreate indexes in this set of dbspaces. This would basically represent a bastardized export and import of all tables in the specific dbspaces thus, the ultimate goal, eliminating interleaving. Little info about my env: IDS 9.40 FC2 HPUX 11 6 way nClass w/550's 6 gig memory LOCKS 5000000 # Maximum number of locks BUFFERS 1400000 # Maximum number of shared buffers NUMAIOVPS 4 # Number of IO vps PHYSBUFF 256 # Physical log buffer size (Kbytes) LOGBUFF 128 # Logical log buffer size (Kbytes)LOGSMAX 200 # Maximum number of logical log files CLEANERS 128 # Number of buffer cleaner processes SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 800000 # initial virtual shared memory segmentsize SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 128 # Number of LRU queues LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water markpercentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) sending to informix-list

I'd never use dbimport for this - far too long. I'd do a select reorg using ALTER TABLE ... FRAGMENT IN, or maybe HPL. the ALTER FRAGMENT can be made to zoom along faster by setting PDQPRIORITY, and enormous (say 7200 second) checkpoints. <Darren_Jacobs@carmax.com> wrote in message news:1110577135.4e0f70ef537703dd155c00ba5794d7d1@teranews... > > Greetings, > > My upper management is interested in what other companies do as far as > reorg'ing large database(s). In my case I have a Peoplesoft database that > is 336 gigs (used). Recent testing proved that a full database reorg > (export/import) takes ~90 hrs. The last time I performed a reorg my > database was ~220 gig and I was able to complete this in a weekend. > > I have presented several options (listed below) to management but they are > very much interested in knowing how others deal with large databases and > reorgs. If you have a moment and perform(ed) some type of database reorgs > on a regular basis please share with me your approach. > > TIA > > Option 1 > Full database reorg using dbexport/dbimport. > > Option 2 > Break 1 large database into multiple databases (3-4) (by module has been > discussed). > Use synonyms from the primary database to point to tables located in > other databases. > Tables reside in "db specific dbspaces (ie, databaseA dbspace1 --> > dbspace6, databaseB dbspace6 --> dbspace12,.....you get the idea)" > Use dbexport/dbimport to reorg smaller databases over multiple weekends. > (one thing to note here, psoft admins would not be fond due to dddaudit > reports) > > Option 3 > (I like to call this a "reorg by dbspace") Group tables and indexes > together based on a percent of the database into a set of dbspaces. > Unload all tables, drop all tables, recreate tables and load 1 after the > other, recreate indexes in this set of dbspaces. This would basically > represent a bastardized export and import of all tables in the specific > dbspaces thus, the ultimate goal, eliminating interleaving. > > > Little info about my env: > IDS 9.40 FC2 > HPUX 11 > 6 way nClass w/550's > 6 gig memory > > LOCKS 5000000 # Maximum number of locks > BUFFERS 1400000 # Maximum number of shared buffers > NUMAIOVPS 4 # Number of IO vps > PHYSBUFF 256 # Physical log buffer size (Kbytes) > LOGBUFF 128 # Logical log buffer size (Kbytes)> LOGSMAX 200 # Maximum number of logical log files > CLEANERS 128 # Number of buffer cleaner processes > SHMBASE 0x0 # Shared memory base address > SHMVIRTSIZE 800000 # initial virtual shared memory segment> size > SHMADD 32768 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited > CKPTINTVL 300 # Check point interval (in sec) > LRUS 128 # Number of LRU queues > LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit > LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit > LTXHWM 50 # Long transaction high water mark> percentage > LTXEHWM 60 # Long transaction high water mark > (exclusive) > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 64 # Stack size (Kbytes)> > > sending to informix-list