Re: informix dba routines

From: Tom Wolfe <Tom.Wolfe@fugen.com> > >First, check to ensure that UPDATE STATISTICS is run as often as is >possible. This is your first great performance improver. (Look in the "As often as possible"? "As often as you have a large change in table content", surely? >Performance Guide for guidelines on how to run). I would build a script >that builds the necessary SQL with information from the database >"syscatalogs", and run it every night (if time windows permit!, on a >regular basis if not). Or you could download several from http://www.iiug.org. >Next, simply CLUSTERING indexes will effectively rebuild them, >reclaiming space and physically removing deleted entries. Sometimes on >large tables it is actually faster to drop and recreate the index. >Again, you can build a script to do this for indexes on large/often >queried indexes. Or you could SET INDEXES DISABLED and SET INDEXES ENABLED> >I would REALLY hesitate to automate a process that drops tables on a >regular basis. I would certainly ENSURE that my backup routine was >working VERY well before attempting this type of routine on a production >system. Puck-puck-puck-UUUUCK! :-) >As for constraint names, the best thing I have found is to use an >automated tool such as ER/Win to define my database model. This not only >gives you a graphical representation of the data model, but can also >become a data dictionary if used fully. It can take substantial effort >to create the first time, but will save tons of time over the life of >the system. > >As a temporary solution, write a little 'sed' script (I assume you are >on UNIX), to simply remove the default constraint names from your schema >before trying to use. I have one somewhere, if anyone's interested. >juliehl@my-deja.com wrote: > > > Hello, > > Our dba just left us for good. and I need your > > generous help! > > I want to increase the performance of our > > informix online dynamic server 7.31. I read from > > book that recreating indexes periodically helps. > > Should I extract all create index script from the > > database and run it (how) or should I unload the > > data for each table, drop table, recreate table > > and then reload data to each table? I heard that > > recreating a table will cause the index to be > > recreated automatically. We have around 300 > > tables in the database, is there an easy way to > > construct the script to unload data, drop table > > and recreate table? > > > > One more question, when our database was created, > > we didn't add a contraint name for 'not null' > > column. After I do dbexport, I found that most of > > the not null columns have a system generated > > constraint name, but not all of them. I ran into > > problem when I import the database to another > > development server with the error message saying > > that the constraint name is already used. > > Obviously, the development machine was creating a > > constraint name for the left out "not null " > > columns and a duplicate system generated > > constraint name was created(one from development > > machine, and one from the server where export > > occured.) > > Is it better to manually add constraint name for > > all 'not null' columns? Again, what is the best > > way to do this? > > Thanks a lot!!! > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Obnoxio The Clown wrote: > > From: Tom Wolfe <Tom.Wolfe@fugen.com> > > > >First, check to ensure that UPDATE STATISTICS is run as often as is > >possible. This is your first great performance improver. (Look in the > > "As often as possible"? "As often as you have a large change in table > content", surely? Well, technically you only need to update stats when the relative relationship between the number of rows with particular attribute and key values change. If 8% of your rows have the name "FRED" and you load 10,000 new rows with 800 "FRED"s your stats are still fine. Of course it is often difficult in practice to know when the relationships change so at a practical day to day level the Clown is correct, I was just feeling more pedantic than usual this AM. Art S. Kagel [SNIP]