CREATE TABLESPACE

CREATE [GLOBAL | LOCAL] TABLESPACE tablespacename location [TYPE {TREE | FLAT | TEMPORARY}] [SOURCE_IMAGE image OBJECT_IMAGE image] [FOR USER user] [SIZE n] [AUTOEXTEND n]

Creates a tablespace in the database schema. A tablespace is a shorthand naming mechanism that allows tables to be created in specific directories without having to explicitly name the directory in the application. The tablespacename is case sensitive and the location must be a directory if it already exists, if it does not exist it will be created as a directory. For KDB the location may contain environment variables.

The default type is FLAT meaning no interpretation of the directory name. The TREE tablespace type allows tables to be placed into a hierarchical directory structure with the minimum of effort, eg.

CREATE TABLE NL_01_example (pid INTEGER(4), longname VARCHAR(20)) TYPE 7, TABLESPACE Data1

would create the file 'C:/Comm8/Data1/NL/01/NL_01_example.kdb' if the 'Data1' tablespace was created as in the example below. Alternatively, creating the table in the 'disk2' tablespace would yield the file '/disk2/NL_01_example.kdb'.

The TEMPORARY tablespace type allows temporary tables to be created. For more information see temporary files.

KDB tablespaces may be created globally, using the GLOBAL clause, in which case all databases in the kconf.xml configuration file will be able to use the tablespace. Or, the tablespace may be created locally, using the LOCAL clause, in which case the tablespace will only be useable by connections to the same database that the CREATE TABLESPACE statement was issued against. If no GLOBAL or LOCAL clause is used the default is to create a GLOBAL tablespace.

The optional SOURCE_IMAGE and OBJECT_IMAGE clauses add the images to the tablespace in kconf.xml. These are used by KI_DB_NAME, CREATE TABLE and KI_OPEN.

This function can be executed whilst connected to a 'virtual' database, to create GLOBAL tablespaces, to allow boot-strapping of new systems.

KDB ignores SIZE, AUTOEXTEND and USER clauses.

Oracle considerations

Oracle supports only the FLAT and TEMPORARY types and interprets the location as a filename for the tablespace.

If you don't specify a SIZE clause for an initial size, KCML will allocate 20MB. The initial size is in units on megabytes. If you set the size to 0 then it will use the Oracle default, i.e. 100MB with 10g.

Tablespaces in Oracle autoextend. If you don't specify the increment with AUTOEXTEND (in MB) then KCML will use half the original size.

PostgreSQL considerations

Tablespaces in PostgreSQL are filesystem directories that are owned by the Unix user postgres and are not created in the context of any particular database though they do have a database user as an owner. Access to the tablespace is controlled by PostgreSQL permissions granted to users. Consequently you must be connected to the built in postgres database as the postgres DBA user to be able to create a tablespace on behalf of a normal user which you specify with the required FOR USER clause.

PostgreSQL only supports the default FLAT type and it interprets the location as the name of a directory which must already exist, be owned by the Unix postgres account with 700 permissions and be empty. PostgreSQL will ignore SIZE and AUTOEXTEND clauses.

KDB Examples

CREATE TABLESPACE disk2 '/disk2' TYPE FLAT
CREATE GLOBAL TABLESPACE tmp '/tmp' TYPE TEMPORARY
CREATE LOCAL TABLESPACE Data1 'C:/Comm8/Data1' TYPE TREE
CREATE LOCAL TABLESPACE MyTables '$HOME/tables' TYPE FLAT