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
theOS 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
tablespacein 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
myindextablespaceLONG IN myblobtablespace allows one to pt the table in one table space, the indexes for that table in
anothertablespace 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')
EXTENTSIZE64 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)
ONNODE 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