Hi All,
Has anyone actually seen performance gains with raw in DB2? When would you recommend it?

HP/UX 11i 64-bit
DB2 V8.1.4
1.5 TB OLTP database with nightly batch proccesses.

Thanks,
DBAgirl

There definitely is a performance advantage of raw devices (not files ) as opposed to SMS. However, if your disk subsystem is not the best then the file system caching of SMS can sometimes perform better than raw device access. It also depends on the configuration of your RAID (I guess you do have a RAID). Write performance of some RAIDs could be noticeably worse than that of JBODs.

If you have an advance storage system, like IBM Shark, to my experience, you may not see a big difference in performance between File and Raw DMS devices ...

Another consideration is whether there are a lot of table space scans or index space scans (which cause a lot of prefetch activity). Significant prefetch activity is the one area were raw "may" have a slight advantage.

Significant amounts prefetch activity usually does not occur in an OLTP environment, so any slight theoretical benefits of raw over cooked will probably not be seen. Having decent sized buffer pools also helps keep the difference to a minimum.

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

What if DMS filesystems are mounted using the DIO option? Would that narrow the gap between raw and cooked for a DSS system where prefetching does happen? Also is anyone using this option(DIO) and if so are there any concerns?