Re: Database restructuring

davidn@geis.geis.com wrote: : : Fellows Informixers, : : We are currently in the process of restructuring our database. The things : that we are trying to accomplish are as follows: : : 1) move "hot" tables in there own dbspaces -> better disk space management. : 2) move all Blob chunks up top with respect to chunk numbers -> minimize : the time that users cannot access the Blobs. : 3) place "hot" tables on their own disks -> minimize disk contention. : : Sounds routine right? Well, lets throw in a 30 Gig database and time : constraints and see what happens. : : In this database, there are 27 tables. The biggest table is 22 Gigs in : size, with about 2 million rows. Each row has an associated Blob located : in its own Blobspace averaging 16k. : : On a small table, what we would usually do is a dbexport/dbimport. On a : medium table, we would do a tbunload/tbload. In our case, we cannot do a : tbload on tables greater than 256,000 rows for the following reasons: : : 1) tbload does row-level locking when it creates the tables. : 2) tbload locks each Blob pages when it loads. : : Because of the above "feature", and with OnLine lock limit of 256,000 rows, : we have the following options: : : 1) perform an isql unload/load -> way too slow: ~60Mb/hr : 2) dbexport/dbimport -> basically same as an isql unload/load : 3) write an esql/c program that would select from : system1:database1.table1 and insert into system2:database2.table2 : 4) perform an isql select * from system1:database1:table1 insert into : system2:database2:table2 -> 500Mb/hr : 5) get Informix to fix tbload : : We have not yet established any benchmarks for step 3. We would like to : accomplish the restructure process under 48 hours. : : QUESTION: : : Has anyone done anything similar? If so, what was the best approach? Does : anyone have any utilities? What is the best way to do this? : Thanks in advance for any advice : Turn off logging for the database, lock the table(s) and pick any way you want to unload/move the data. -- @ David Tomlinson @ @ dtomlnsn@netcom.com @ @ "By day fantastic birds flew through the petrified forest, and @ @ jeweled crocodiles glittered like heraldic salamanders on the @ @ banks of the crystalline river. By night the illuminated man @ @ raced among the trees, his arms like golden cartwheels, his @ @ head like a spectral crown...." @