Re: Complete database re-org

> All, > > I am planning a complete database re-org for one of our Informix servers. > The disk layout that I inherited when I joined the company is utterly > unacceptable and must be changed. I'm drafting a step-by-step plan for this > re-org, and I thought I'd run it by the Informix community for critique. > The plan looks like this: > > 1. Perform Level 0 archive (ontape -s -L 0). > 2. Do dbexport of databases. > 3. Modify schemas of databases to place certain highly used tables in > specific dbspaces, and set first and next extent sizes for all tables. > 4. Drop databases. Step four is superfluous. When you do an "oninit -i" everything goes away. Of course, dropping databases doesn't take long. > 5. Bring Informix off-line. > 6. Modify onconfig file to set new rootdbs path, offset, and size, physical > log size, dbspacetemp names, and logical log max number and size. I assume you mean dbspacetemp will be set to blank? Since you're re-initializing, no dbspaces will exist other than rootdbs, so if you have any spaces named here, you'll get messages in your online.log. It won't crash the engine, just give nasty messages. > 7. Re-initialize disk space for informix (oninit -i). > 8. Add dbspaces/chunks of new disk layout. > 9. Modify physical log dbspace in onconfig, and bounce engine. Use onparams to move physical log. > 10. Add logical logs to new logdbs. > 11. Perform dbimport of databases. > 12. Perform Level 0 archive (ontape -s -L 0). > 13. Move current logical log to one of those in the new logdbs (onmode -l). > 14. Drop logical logs in rootdbs. I'd move steps 13 and 14 to be after step 10. Never hurts to have your logs available as early as possible. Of course, you'll have to run an ontape -s -L 0 to make the logs usable, but set TAPEDEV=/dev/null for this first ontape. > > Am I missing anything? Are there steps that are utterly extraneous? Is > there a better way to accomplish any of this? > > Other notes: > > In step 6, I plan on setting LOGFILES to just 3, and just adding the logs > with onparams. LOGSMAX would be set to 32. Makes me wonder what importance > the LOGFILES parameter has other than the point right after Informix disk > space is initialized. > > I don't think there's a way to avoid step 9, since when the Informix > instance's disk space is initialized, the only dbspace is rootdbs. Is that > correct, or is there a way to assign the physical log to a different dbspace > initially? Correct, except that I recommend using onparams rather than playing with the onconfig. I doubt that manual manipulation of onconfig will set up all of the references to the physical log location (e.g., Physical log begin address and Physical log size in the reserved pages). > I wanted to say in addition to the above, that I'm very thankful for the > advice that I've received on c.d.i. I'm only a recent contributor, since I > just changed companies. I came from a shop with a whole team of DBA's where > we had technical meetings and could confer with one another on various > issues. At my new company, I'm the only DBA. This newsgroup has become my > "team of DBA's". I guess I just want to say thanks to all who provide > insights to any of my questions. I'll try to lend any insight to anyone > asking questions that I can help with. Many might say that no thanks are > necessary; that that's why this newsgroup is here in the first place. That > may be, but the help is no less appreciated. What, none of the team from the old company want to talk to you anymore? Mark Collins mcollins@us.dhl.com Everybody at some level realizes that the calendar is a fairly arbitrary thing, invented by humans, for reasons that have more to do with how committees are structured than with anything that's really happening in the heavens. And yet people look at the fact that the calendar is about to turn 2000, and assume there's some deity who thinks that the base 10 counting system is pretty darn important, and make all sorts of predictions of doom and gloom as a result. To me, that tells you everything you need to know about human beings -- and a whole lot about the market for the NC. Scott Adams, creator of _Dilbert_

> > 11. Perform dbimport of databases. If you have big tables, it's better to correct schema file to don't load data at the time of dbimport, cause it's relatively slow. It's better to use High Perfomance Loader for big tables. Also, during the load time increase values of CKPINTVL to about 600, LRU_MAX_DIRTY to about 5 and LRU_MIN_DIRTY to about 2. That helps you to decrease checkpoint time. > > 12. Perform Level 0 archive (ontape -s -L 0). > > 13. Move current logical log to one of those in the new logdbs (onmode -l). > > 14. Drop logical logs in rootdbs. > -- With best regards, Yuri Dovgart. Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Mark Collins wrote: > > > All, > > > > I am planning a complete database re-org for one of our Informix servers. > > The disk layout that I inherited when I joined the company is utterly > > unacceptable and must be changed. I'm drafting a step-by-step plan for this > > re-org, and I thought I'd run it by the Informix community for critique. > > The plan looks like this: >[SNIP] > > I don't think there's a way to avoid step 9, since when the Informix > > instance's disk space is initialized, the only dbspace is rootdbs. Is that > > correct, or is there a way to assign the physical log to a different dbspace > > initially? > > Correct, except that I recommend using onparams rather than playing with the > onconfig. I doubt that manual manipulation of onconfig will set up all of the > references to the physical log location (e.g., Physical log begin address and > Physical log size in the reserved pages). Actually Mark it is completely safe to just change the ONCONFIG parameter PHYSDBS to relocate the physical log to another DBSPACE after the next startup. The physical log file is allocated on the fly at startup (or reallocated when onparams is used to move it afterward). I have always relocated PHYSDBS this way, no worries. John, Mark's other suggestions are right on the money, listen to the man. Art S. Kagel