We're thinking of keeping temporary and catalog tablespaces as SMS and changing the rest to DMS. What things to consider when changing tablespace type from SMS to DMS? Instance level, database level, table space level, buffer pools, others?

Version 9.1 on AIX


Thank you in advance.

What is the main reason why you want to change from SMS to DMS? DMS file or raw?

You may want to look at separating data, index, LOB/LF into separate tablespaces and use different bufferpools / filesystem caching options.

I'd also suggest to take a look at DB2_OBJECT_TABLE_ENTRIES. This registry variable will preallocate more extents for the object table (internal table used to keep track of user objects). This object table will grow as you add more user objects and can use multiple extents. These additional extents can end up holding the high-water mark (HWM) of a DMS tablespace. If the HWM is being held by an extent for the object table, then it can't be lowered without dropping/recreating the tablespace (not exactly sure if this is still true in v9.7). Using this registry variable may also help to avoid locking conflict between utilities such as online backup and import.

Can look at autoresize, large RID for DMS