IDS Forum

Re: database reorg for large databases?

Posted By: Tilman Model-Bosch
Date: Friday, 11 March 2005, at 5:36 p.m.

In Response To: database reorg for large databases? (Darren_Jacobs@carmax.com)

Dear Darren,

Option 4 : (variant of option 3 - how sapdba works)
Instead of unloading and reloading all tables:

for all dbspaces, do
make sure you have a recent L-0 archive --just to be on the save side
turn off logging of the database ;
create new_dbspace;
for all tables in dbspace, do
save schema of table;
create new_table (same schema) in new_dbspace;
lock table in exclusive mode; --just to be sure
INSERT INTO new_table SELECT * from table;
check number of rows is the same
drop table;
rename new_table to original name of table;
recreate indices;
done
rename new_dbspace to dbspace (new option in IDS >= 9.40.FC3)
run update staistics for all tables
get a L-0 archive, turn on logging
done

This saves the need to unload all data to the file system.
Also, I suggest to adapt first extent size when creating the new
tables to ensure all current rows a loaded in one or only few extents.
Also, dbspaces can be done one by one.

With kind regards
Tilman


--
Tilman Model-Bosch
IBM Data Managment Solutions, Informix Advanced Support
c\o SAP AG
TECHDEV 05
Neurrotstr.16
69190 Walldorf




forum.subscriber@iiug.org wrote on 11/03/2005 21:42:52:

> 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)
>
>
>

Messages In This Thread

• database reorg for large databases?
  Darren_Jacobs@carmax.com -- Friday, 11 March 2005, at 3:42 p.m.
  • Re: database reorg for large databases?
    Tilman Model-Bosch -- Friday, 11 March 2005, at 5:36 p.m.
    • RE: database reorg for large databases?
      Walter Milan -- Sunday, 13 March 2005, at 1:07 a.m.
  • AW: database reorg for large databases?
    Andreas.KUTSCHE@spar.at -- Monday, 14 March 2005, at 4:01 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.