Hi

Can some one enlighten me as to the operational advantage between defining a tablespace to be manage by the System vs Database.

Peace!

This applies on DB under AIX and Linux systems

There are many factors that affect DB2 I/O performance:

1st is the Disk load balancing ( OS & paging space on physical disks, DB2 filesystems on other physical disks )

2nd Access time of the logical partion of db2 filesystems ( under AIX you can assign allocation type Center/Outer/Core/Inner )
try to use the Center part of the disk ( give minimum access time)

3rd Buffer Pool size of the Database, as much as 70% of the machine memory should be allocated to the DB buffer pools ( in case that the machine is a native DB server )

4th Type of Table Space ( SMS or DMS ) for unix systems DMS is much faster than DMS specially in large databases

5th Wait I/O should not be more than 25% of the CPU usage, otherwise there shoud be a need for I/O tunning on the system

I have heard numbers that suggest using DMS tablespaces can increase I/O performance by about 10% where the table is read in its entirety with sequential prefetch. If a small number of rows are accessed randomly via an index, there is very little, if any, difference. I am just quoting what I have heard and have not seen any comprehensive objective test results.

However, some people suggest SMS for system temporary tablespaces, because the create table overhead is lower than on DMS (DMS spends more time optimizing the placement of the data of disk). This extra DMS overhead would not be noticeable on complex decision support queries (and might pay for itself with better performance on actual data I/O), but could be noticeable on shorter queries and OLTP transactions that needed small temporary tablespaces (for cursors, sorts, etc).