With_Clause for Create Table
CREATE TABLE accepts the following options on the WITH clause, specified as a comma-separated list:
LOCATION = (location_name {, location_name})
Specifies the locations where the new table is created. To create locations, use the CREATE LOCATION statement. The location_names must exist and the database must have been extended to the corresponding areas. If the location option is omitted, the table is created in the default database location. If multiple location_names are specified, the table is physically partitioned across the areas. For details about defining location names and extending databases, see the Database Administrator Guide.
[NO]JOURNALING
Explicitly enables or disables journaling on the table. For details about journaling, see the Database Administrator Guide.
To set the session default for journaling, use the SET [NO]JOURNALING statement. The session default specifies the setting for tables created during the current session. To override the session default, specify the WITH [NO]JOURNALIING clause in the CREATE TABLE statement.
If journaling is enabled for the database and a table is created with journaling enabled, journaling begins immediately. If journaling is not enabled for the database and a table is created with journaling enabled, journaling begins when journaling is enabled for the entire database.
Note: To enable or disable journaling for the database and for system catalogs, use the ckpdb command. For information about ckpdb, see the Command Reference Guide.
[NO]DUPLICATES
Allows or disallows duplicate rows in the table. This option does not affect a table created as heap. Heap tables always accept duplicate rows regardless of the setting of this option.
If a heap table is created with NODUPLICATES and is subsequently modified to a different table structure, the NODUPLICATES option will be enforced with the result that rows which are totally identical will have the duplicates dropped without warning.
The DUPLICATES setting can be overridden by specifying a unique key for a table in the MODIFY statement.
Default: DUPLICATES
PAGE_SIZE = n
Specifies a page size, in number of bytes. Valid values are described in
Page_size Option (see page
Page_size Option).
Default: 8192. The tid size is 8.
The buffer cache for the installation must also be configured with the page size specified in CREATE TABLE or an error occurs.
SECURITY_AUDIT = (audit_opt {, audit_opt})
Specifies row or table level auditing, as described in
Security_audit Option (see page
Security_audit Option).
SECURITY_AUDIT_KEY = (column)
Writes an attribute to the audit log to uniquely identify the row in the security audit log. For example, an employee number can be used as the security audit key.
STRUCTURE = structure
Specifies the storage structure of the table. The only valid value is HEAP.
PARTITION =
Defines a partitioned table. For more information, see
Partitioning Schemes (see page
Partitioning Schemes).
NOPARTITION
Indicates that the table is not to be partitioned. This is the default partitioning option.
[NO]AUTOSTRUCT
Specifies whether the storage structure of the table should automatically default to B-tree (WITH AUTOSTRUCT) or to heap (WITH NOAUTOSTRUCT), in combination with constraint definitions in CREATE TABLE.
ALLOCATION = n
Specifies the number of pages initially allocated for the table.
Limits: Integer between 4 and 8,388,607.
Default: 4
EXTEND = n
Specifies the number of pages by which the table is extended when more space is required.
Limits: Integer between 1 and 8,388,607
Default: 16
COMPRESSION [= ([[NO]KEY] [,[NO]DATA])] | NOCOMPRESSION
Specifies whether the key or data is to be compressed. If compression is specified, the structure clause must be specified.
PRIORITY = n
Specifies cache priority. If an explicit priority is not set for an index belonging to a base table to which an explicit priority has been assigned, the index inherits the priority of the base table.
Limits: Integer between 0 and 8, with 0 being the lowest priority and 8 being the highest.
Default: 0. (Causes the table to revert to a normal cache management algorithm.)
ENCRYPTION=encryption type, [AESKEY=hex-aes-key,] PASSPHRASE='encryption-passphrase'
Specifies encryption options to secure data in a column defined with the ENCRYPT option:
encryption type
Specifies the type of Advanced Encryption Standard (AES) encryption for the column:
AES128
128-bit encryption
AES192
192-bit encryption
AES256
256-bit encryption
AESKEY=
Specifies the internal key used to encrypt the user data. The key is protected by a key derived from the PASSPHRASE. The AESKEY must be the exact hex value of the key, which depends on whether AES128, AES192, or AES156 is specified (thus 16, 24, or 32 bytes) and is in the usual Ingres hex constant format of x'03010401050902060503050901040104' or 0x03010401050902060503050901040104.
If no AESKEY is specified, a random key is generated.
PASSPHRASE='encryption passphrase'
Specifies the encryption passphrase used to encrypt and decrypt the column data. The passphrase must be at least eight characters, can contain spaces, and must be specified within single quotation marks (' '). The string is converted to the appropriate AES key size (16, 24, or 32 bytes).