We're going to create new file sytems on AIX 5.2 with the mount -o cio option (Concurrent I/O) and then eventually restore a database which was on JFS.
Does one have to ALTER TABLESPACE ... NO FILE SYSTEM CACHING if the filesystem is JFS2 and mounted with Concurrent I/O option?

In an IBM doc:
Improve Database Performance on File System Containers in IBM DB2 Universal Database Stinger using Concurrent I/O on AIX p. 9, section 3.2 when discussing Enablement at File System Level:

"This method differs from the previous one in the sense that this can be done outside of DB2 UDB and if all the containers reside on the same file system, this is a much simpler method without changing any of the existing tablsepace creation scripts"

I understand this to mean that an ALTER TABLESPACE ... NO FILE SYSTEM CACHING would NOT be required?

Any experience on this subject would be greatly appreciated.

Ruby

From a recent seminar(not from practical experience)
ALTER TABLESPACE ... NO FILE SYSTEM CACHING allows you to do from within db2, what the OS "mount -o cio" option does.(assuming I remember OK)

It depends on which fixpak your using if it would take advanteage of it or not, but here is a warning from IBM Fixpak 6 and below don't use it, fp7 and above do and it can cause problems.

DB2 V8.2/DB2 V8.1 FP7 and above
|---------+------------------------------------+------------------------------------------------------------------------|
|IY62143 |SYSTEM CRASH WHEN PRE-ALLOCATING |Extending DB2 DMS file containers on JFS2 using the EXTEND or RESIZE |
| |SPACE FOR A FILE |option on the DB2 ALTER TABLESPACE command may result in an AIX system |
| | |crash . This is due to a problem with an AIX feature which allows |
| | |pre-allocation of space for a file on a JFS2 filesystem . This AIX APAR|
| | |is recommended when running DB2 Version 8.2 / Version 8.1 Fixpak 7 and |
| | |above , as prior levels of DB2 did not take advantage of this AIX |
| | |feature . |
|---------+------------------------------------+----------------------------

Here's an update from IBM:
For db2 8.2 on AIx 5.2, maintenance level 3 as well as a minimum C++
runtime level of: xlC.rte 6.0.0.0 and xlC.aix50.rte 6.0.0.5 are
prerequisites. You can fonrim the prerequisites here:
http://www-1.ibm.com/support/docview...id=swg21181544

Please let me know if performance problem persists after installing these
APARs:
IY49346, IY49129 and IY56024, IY59082, IY48339, IY49304

Also:
One can use GET SNAPSHOT in order to find out if caching is enabled for the db2 tablespaces or not.

Please use command GET SNAPSHOT to get snapshot of all the tablespaces on
your database.
db2 connect to <database>
db2 get snapshot for tablespaces global

I issued the get snapshot for tablespaces on db-alias global and even though the filesystems were mounted with cio option, the snapshot showed that file system caching is Yes.

After issuing an ALTER TABLESPACE ... NO FILE SYSTEM CACHING the snapshot showed that file system caching is No.

(I await the AIX 5.2 maintenance level to be upgraded. Without the fixes, we experienced poorer performance and other issues with cio option activated.)