RE: Database Setup

I'll venture a go at this... -----Original Message----- From: jrw3319@gis.net (John Welch) [mailto:jrw3319@gis.net] Sent: Thursday, August 17, 2000 8:57 PM Posted To: informix Conversation: Database Setup Subject: Database Setup [clipped] 1. No constraints defined (primary key, foreign key, check, etc) All constraints can be handled programmatically. They are really just a nicety at the database level, all they do is force compliance. As someone else mentioned, if you go adding them now, some applications may bomb if they were written under the assumption that constraints don't exist. There are some indexes defined. Some may be all you need... You'll have to monitor applications over time, and add them where you see appropriate. 2. Indexes not defined in their own index space. This is only for speed, so that the engine can access both the data and the indexes independently. Most of my tables have indexes mixed with the data. I have broken out some key tables which were having performance problems. You'll have to adjust this over time as you learn more about the schema, and it's relation to the applications. In your environment, it's probably not worth your time to do a table by table analysis for the amount of return you're likely to get. 2. All tables created with the default extent and next extent size of This is due to laziness, and or ignorance (as to how the data will grow). This is one of the more labor intensive duties of setting up a new database. It can be hard to predict how much space a table needs, and how it will grow. And, on a large schema, it is just plain tedious to go through each table and make your predictions. Most likely the designers just didn't want to be bothered with the work. This is not as important as it used to be, but it certainly wouldn't hurt to resize the extents. Again, this is something you can do piecemeal as tables grow. I have a script I can send you to spit out the largest offenders, and you could work on one or two a week. [clipped] a) Do we have a reason to be concerned?, Not great concern. As long as the indexes are adequate, your box will be able to give you 80-90% of capacity all things being equal. (More below) b) If so, can anyone help us provide evidence or better reasoning as to why we should be concerned? I'm in the same type of environment as you. Myself, and one other person are responsible for UNIX admin, DBA, application development, support, system analyst, etc. We only recently offloaded NT administration. There are other people in the department, but they perform in a more junior capacity. So in my opinion, you have more important tasks than restructuring the database. If I was a full time DBA, I would be able to do all of the things you mention, in fact I'd love to spend 40 hours a week doing these sorts of things for a few months. I'm certain I could squeeze an extra 10-15% more performance out of our box. But in the mean time, there would be no one to get everything else accomplished. If you had a full time DBA, then I'd say; yes, do everything you've mentioned. But as it is, you'll probably have to do what I've done, and hit the highlights as necessary, and let the rest go. HTH P.S. You sound exactly like me 5 or 6 years ago (except for the part about going to class). I'd be happy to discuss more. There are unique considerations that a small shop has, lacking the man-hours to do all of the DBA functions that everyone else considers mandatory. Thanks, John Welch Systems Analyst Brockway-Smith Co.