Table data can be stored in the same table space as the index for the table, and any long column data associated with the table. You can also place the index in a separate table space, and place any long column data in a separate table space, apart from the table space for the rest of the table data.
Prerequisites:
All table spaces must exist before the CREATE TABLE statement is run.
Restrictions:
The separation of the parts of the table can only be done using DMS table spaces.
Procedure:
To create a table in multiple table spaces using the Control Center:
1. Expand the object tree until you see the Tables folder.
2. Right-click the Tables folder, and select Create —> Tables Using Wizard from the pop-up menu.
3. Type the table name and click Next.
4. Select columns for your table.
5. On the Table space page, click Use separate index space and Use separate long space, specify the information, and click Finish.
To create a table in multiple table spaces using the command line, enter:
CREATE TABLE <name>
(<column_name> <data_type> <null_attribute>) IN <table_space_name>
INDEX IN <index_space_name>
LONG IN <long_space_name>
The following example shows how the EMP_PHOTO table could be created to store the different parts of the table in different table spaces:
CREATE TABLE EMP_PHOTO
(EMPNO CHAR(6) NOT NULL,
PHOTO_FORMAT VARCHAR(10) NOT NULL, PICTURE BLOB(100K) )
IN RESOURCE
INDEX IN RESOURCE_INDEXES LONG IN RESOURCE_PHOTO
This example will cause the EMP_PHOTO data to be stored as follows:
v Indexes created for the EMP_PHOTO table will be stored in the RESOURCES_INDEXES table space
v Data for the PICTURE column will be stored in the RESOURCE_PHOTO table space
v Data for the EMPNO and PHOTO_FORMAT columns will be stored in the RESOURCE table space.
Related reference:
v “CREATE TABLE statement” in the SQL Reference, Volume 2
Creating a table in a partitioned database
There are performance advantages to creating a table across several partitions in a partitioned database. The work associated with the retrieval of data can be divided amoung the database partitions.
Prerequisites:
Before creating a table that will be physically divided or partitioned, you need to consider the following:
v Table spaces can span more than one database partition. The number of partitions they span depends on the number of partitions in a database partition group.
v Tables can be collocated by being placed in the same table space or by being placed in another table space that, together with the first table space, is associated with the same database partition group.
Restrictions:
You must be careful and select an appropriate partitioning key because it cannot be changed later. Furthermore, any unique indexes (and therefore unique or primary keys) must be defined as a superset of the partitioning key. That is, if a partitioning key is defined, unique keys and primary keys must include all of the same columns as the partitioning key (they may have more columns).
The size limit for one partition of a table is 64 GB, or the available disk space, whichever is smaller. (This assumes a 4 KB page size for the table space.) The size of the table can be as large as 64 GB (or the available disk space) times the number of database partitions. If the page size for the table space is 8 KB, the size of the table can be as large as 128 GB (or the available disk space) times the number of database partitions. If the page size for the table space is 16 KB, the size of the table can be as large as 256 GB (or the available disk space) times the number of database partitions. If the page size for the table
space is 32 KB, the size of the table can be as large as 512 GB (or the available disk space) times the number of database partitions.
Procedure:
Creating a table that will be apart of several database partitions is specified when you are creating the table. There is an additional option when creating a table in a partitioned database environment: the partitioning key. A partitioning key is a key that is part of the definition of a table. It determines the partition on which each row of data is stored.
If you do not specify the partitioning key explicitly, the following defaults are used. Ensure that the default partitioning key is appropriate.
v If a primary key is specified in the CREATE TABLE statement, the first column of the primary key is used as the partitioning key.
v If there is no primary key, the first column that is not a long field is used.
v If no columns satisfy the requirements for a default partitioning key, the table is created without one (this is allowed only in single-partition database partition groups).
Following is an example:
CREATE TABLE MIXREC (MIX_CNTL INTEGER NOT NULL, MIX_DESC CHAR(20) NOT NULL, MIX_CHR CHAR(9) NOT NULL, MIX_INT INTEGER NOT NULL, MIX_INTS SMALLINT NOT NULL, MIX_DEC DECIMAL NOT NULL, MIX_FLT FLOAT NOT NULL, MIX_DATE DATE NOT NULL, MIX_TIME TIME NOT NULL, MIX_TMSTMP TIMESTAMP NOT NULL) IN MIXTS12
PARTITIONING KEY (MIX_INT) USING HASHING
In the preceding example, the table space isMIXTS12 and the partitioning key isMIX_INT. If the partitioning key is not specified explicitly, it is MIX_CNTL. (If no primary key is specified and no partitioning key is defined, the
partitioning key is the first non-long column in the list.)
A row of a table, and all information about that row, always resides on the same database partition.
Related concepts:
v “Database partition groups” in the Administration Guide: Planning v “Database partition group design” in the Administration Guide: Planning v “Table collocation” in the Administration Guide: Planning
Related reference:
v “CREATE TABLE statement” in the SQL Reference, Volume 2