05-01-09, 11:40 #1Registered User
- Join Date
- May 2007
Unanswered: Table per Tablespace ratio - HELP URGENT
I have couple (20) DB2 servers running DB2 v9.5.3 ESE on Linux RedHat. Some severs (like development) we have 4 instances with many databaes each.
One of our databases, has around 505 tables all of them created in one single DMS tablespace including indexes. Even if I spread my only one tablespace into more than one containers I still think that I should create at least more than one tablespace.
Considering that I just have one data mounted point to put my data on it, what should I do?
My questions are:
1) Having one tablespace to host all tables could cause some kind of contention?
2) Having Indexes in a separated tablespace could improve performance?
3) Having tables that are mostly joined into the same tablespace separated from others would help?
4) Historic o(appending or volatile) tables should have also a separate tablespace?
5) How operations such as load, reorg could be affected in case of having more than one tablespace?
I really appreciate any help.
05-01-09, 14:01 #2Registered User
- Join Date
- May 2003
1). It could, especially if it is a data warehouse. For OLTP it is less likely to cause a problem, but it just depends on your physical I/O rate.
2). It might, but mainly because you could assign them to different bufferpools.
3). Make no difference. Tables are joined in the bufferpool, not on disk. If a join required physical disk I/O to place the data in the bufferpool, then it "might" benefit more by having them in separate tablespaces, but you might not notice the difference.
4) No, unless you are doing that to control which data goes into which bufferpool.
5) It might help if you are doing multiple of these at one time.M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390