Index, Foreign Keys, and Fragmented Tables?

My database currently has two large tables (and a couple hundred small ones). I'm in the process of moving the database to a new server, and fragmenting these large tables accross several dbspaces at the same time. I saw previous posts about using the High Performance Loader to load the tables (without indexes or foreign keys applied), then creating indexes on the tables (in a seperate dbspace). My first question is, after I load the tables, and create the indexes, what is the correct syntax to modify those indexes to actually be foreign keys to other tables? Another Question: I have attempted this procedure before (except I never applied foreign keys to the tables, just indexes) and thought I was finished, so I switched the new server/database to be the "live" system for a little while, and checkpoint lengths skyrocketed from 0-1 seconds every 5 minutes to 15-20 seconds every 2 minutes (which is a bad thing when records have to be updated live). I attempted to make a couple modifcations to the onconfig file (changing LRU MIN/MAX down to 0/1 from 2/5 and such), but I was unable to make sufficient improvements quickly enough to leave the system "live". What's puzzling to me is that the new system is basically identical to the old system except the processors on the new system were 240 Mhz vs. 180 MHz (both dual processor HP PA-RISC). The operating systems are basically an identical setup as well. Are there other configuration/tuning parameters that I really need to watch when I have tables fragmented accross mulitple dbspaces, as opposed to in one single dbspace? One more disgrunted question. Why do I have to update statistics on a table everytime it increases from say 33 million rows to 35 million rows. What a pain. (Yes, I already have to dostats utility). I just don't understand why the database inserts the rows ok, and updates the indexes, but then has to be told they are there to use the indexes properly. I don't expect an answer on this, just needed to vent.

Russell Bierschbach wrote: > > My database currently has two large tables (and a couple hundred small > ones). I'm in the process of moving the database to a new server, and > fragmenting these large tables accross several dbspaces at the same time. I > saw previous posts about using the High Performance Loader to load the > tables (without indexes or foreign keys applied), then creating indexes on > the tables (in a seperate dbspace). My first question is, after I load the > tables, and create the indexes, what is the correct syntax to modify those > indexes to actually be foreign keys to other tables? Just add the constraints and they will use the existing indexes if available: ALTER TABLE parent ADD CONSTRAINT PRIMARY KEY (p_key); ALTER TABLE child ADD CONSTRAINT FOREIGN KEY (c_key) REFERENCES parent (p_key); > Another Question: I have attempted this procedure before (except I never > applied foreign keys to the tables, just indexes) and thought I was > finished, so I switched the new server/database to be the "live" system for > a little while, and checkpoint lengths skyrocketed from 0-1 seconds every 5 > minutes to 15-20 seconds every 2 minutes (which is a bad thing when records > have to be updated live). I attempted to make a couple modifcations to the > onconfig file (changing LRU MIN/MAX down to 0/1 from 2/5 and such), but I > was unable to make sufficient improvements quickly enough to leave the > system "live". What's puzzling to me is that the new system is basically > identical to the old system except the processors on the new system were 240 > Mhz vs. 180 MHz (both dual processor HP PA-RISC). The operating systems are > basically an identical setup as well. Are there other configuration/tuning > parameters that I really need to watch when I have tables fragmented accross > mulitple dbspaces, as opposed to in one single dbspace? Increase the size of the physical log it is triggering the early checkpoints when it reahes 75% full. Increase the number of LRUs it tends to help spread the I/Os out and reduces buffer and LRU contention to boot. Make sure you have enough aio VPs if you are not using KAIO. And finally, you do not state you version information, error error, but there is a bug in 7.30, fixed in 7.30UC7 I believe, that causes the LRU queues to not be cleaned properly between checkpoints so you may need to upgrade. > One more disgrunted question. Why do I have to update statistics on a table > everytime it increases from say 33 million rows to 35 million rows. What a > pain. (Yes, I already have to dostats utility). I just don't understand > why the database inserts the rows ok, and updates the indexes, but then has > to be told they are there to use the indexes properly. I don't expect an > answer on this, just needed to vent. This is a common enough complaint, I have made it myself. So much so that when Informix started the PATU effort a year or two (no I do not remember what that acronym stood for but it was an attempt to build a no tuning database) STATISTICS maintenance was on the top of the list most of us participating DBAs gave them for things the engine should be able to automate. That effort died last year but someone contacted me recently so it seems it will be attempted again. The problem with keeping the statistical histograms live is the cost of doing so would tend to slow down the engine. Art S. Kagel