If you specify automatic storage (good idea to do that about 99% of the time) DB2 will automatically use SMS for system temporary tablespaces, and DMS for others.
When a new table is created in a DMS tablespace, DB2 spends a lot of time looking for the optimal place inside the tablespace container(s) to put a new table. This is fine for most tables that stay around for awhile, but since a system temporary table gets created and dropped each time a query is run (if such a system temporary table is needed by DB2 for that SQL), then DMS is not suitable for such system temporary tablespaces.
Indexes should be in their own tablespaces in order to specify (if desired) their own bufferpools (usually a good idea). Again, DB2 will choose DMS with automatic storage (and you should also even if you don't use automatic storage). But you will probably be making a mistake if not using automatic storage. When defining automatic storage, specify multiple file paths for automatic storage on different disks or arrays if possible if there is a lot of data in the database (up to about 4 is sufficient in most cases).
Last edited by Marcus_A; 04-25-13 at 01:26.
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390