The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

How many tables in a table space?

At my current site the DBAs have a strict policy of only allocating one table per tablespace (using UDB on AIX), which is giving us storage guys major problems as the smallest a tablespace can be is a physical partition, resulting typically in 99%+ wasted space (average used size is somewhere around 0.2% of the tablespace!)

This is pretty wasteful as you can imagine, but storage is our problem, not the DBAs. They won't discuss the reasons for their policy. Apart from simplifying loads and housekeeping are there any good reasons for only having one table per tablespace?
Harassed Storage Guy Send private email
Tuesday, July 19, 2005
 
 
I once worked in a shop where they suffered from exactly the same reason of having one table per tablespace.

Cleaning up this approach freed some 3 Terabytes.

When I asked them why the did it this way, I was given a somewhat plausible yet not convincing answer. Unfortunately, I don't remember it.
René Nyffenegger
Tuesday, July 19, 2005
 
 
On the contrary, most UDB DBAs recommend multiple tables per tablespace; one table space for indexes, one for lookup tables, maybe one each for _LARGE_ tables (and this tablespace usually spans physical volumes). Also, too many tablespaces may degrade performance: <http://www-128.ibm.com/developerworks/db2/library/techarticle/hayes/0102_hayes.html>
<QUOTE>8. Maximum open files
DB2 tries to be a good citizen within the constraints of operating system resources. One of its "good citizen" acts is putting a ceiling, or upper limit, on the maximum number of files open at any one time. The MAXFILOP database configuration parameter stipulates the maximum number of files that DB2 can have open concurrently. After it reaches that point, DB2 will start closing and opening its tablespace files (including raw devices). Opening and closing files slows SQL response times and burns CPU cycles.</QUOTE>

See <http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0004963.htm> for help on tablespace design.

Hope this helps,
Former COBOL Programmer
Tuesday, July 19, 2005
 
 
Speaking from an Oracle background, the most sensible suggestion is probably Jonathan Lewis's, to group tables by storage characteristics: little tiny lookups here, mid-size stuff there, huge table elsewhere. And it's good to split also by function. This is particularly true in Oracle since 8i, when you can export tablespaces.
George Jansen Send private email
Tuesday, July 19, 2005
 
 
Maybe they are insane.  Put your foot down and enforce your own policies for new requests.
You can have them submit retention information, estimated growth per year/month, minimum used size, maximum used size, medium used size, and projections for the future.
Then you can start to argue on why they project using 0.05% of the tablespace in 10 years for that one table.

Oh, and another thing.  Does your department bill the dba department?  Is there a budget downside to them for leaving gobs of space empty?
Bill the dba dept.  x amount of $ per hard drive or per mb reserved for the tablespace.

If you are in the same budget, then make sure that whomever is in charge of that budget understands the financial cost.

And btw; I don't know what I'm talking about ok?  Don't take my word for any this.
Dave
Wednesday, July 20, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz