I see the continuing discussion here about how to specify and manage tablespaces. I'd like to point out how DB2 does it 
since their approach may be worthy of consideration. I've posted some examples and some comments before the 
examples.

Note several things about the examples below:

1) A tablespace can be managed by either the database or by the operating system. DB2's terminology is DMS vs SMS 
table spaces. 

2) You can specify a FILE or a DEVICE or a directory (absence of a FILE or DEVICE keyword means a directory is being 
specified) as the place to use as a tablespace.
   I assume that the DEVICE keyword is for specifying raw devices so that the OS file system is bypassed entirely. They 
don't support DEVICE on all operating systems for which they support DB2 btw.
   The second example below is creating a tablespace in 3 directories on 3 drives using syntax that looks like NT or OS/2 
syntax for the paths. 

3) They allow absolute or relative paths. If relative then its relative to some main database directory for that particular 
database. 

4) The 10000 and 50000 numbers refer to a number of 4K pages. 

5) The EXTENTSIZE is the number of pages to write to a particular directory or file or device before switching to the next 
dir, file or device. 
    They speak of directories, files and devices used in this way as containers. 

6) The ON NODE syntax is used in what sounds like clustered configurationsDMS. They refer to its use on MPP servers. 

7) DB2 has a good separation of tablespaces and tables. 
    CREATE TABLE mytable IN mydatatablespace INDEX IN myindextablespace LONG IN myblobtablespace
  allows one to pt the table in one table space, the indexes for that table in another tablespace and the LONG VARCHAR, 
LOB and other blobish data in yet another tablespace. 


   CREATE TABLESPACE PAYROLL
      MANAGED BY DATABASE
      USING (DEVICE'/dev/rhdisk6' 10000,
         DEVICE '/dev/rhdisk7' 10000,
         DEVICE '/dev/rhdisk8' 10000)
      OVERHEAD 24.1
      TRANSFERRATE 0.9

   CREATE TABLESPACE ACCOUNTING
      MANAGED BY SYSTEM
      USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')
      EXTENTSIZE 64
      PREFETCHSIZE 32

   CREATE TEMPORARY TABLESPACE TEMPSPACE2
      MANAGED BY DATABASE
      USING (FILE '/tmp/tempspace2.f1' 50000,
         FILE '/tmp/tempspace2.f2' 50000)
      EXTENTSIZE 256

   CREATE TABLESPACE PLANS
      MANAGED BY DATABASE
      USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn1hd01' 40000) ON NODE 1
      USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn3hd03' 40000) ON NODE 3
      USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn5hd05' 40000) ON NODE 5



Wed, 21 Jun 2000 15:36:36 -0700
"Randall Parker" <randall@nls.net>
tablespace managed by system vs managed by database