WITH Clause
The WITH clause begins with the keyword WITH followed by any of the following options, each separated by a comma.
Note: The only required WITH clause option is the PARTITION (or NOPARTITION) option.
STRUCTURE=
Defines the storage structure of the table. Valid structures are:
VECTORWISE
(Default) Stores data in columns. Puts as few columns as possible in one disk block.
VECTORWISE_ROW
Stores data in rows. Puts as many columns as possible in one disk block.
HEAP
Explicitly declares the structure to be a traditional Ingres table.
Note: You must be licensed to use HEAP tables in Vector.
LOCATION=location_spec
Specifies the location where the new table is created. The location_name must exist (created with the CREATE LOCATION statement) and the database must have been extended to the corresponding area.
Table location and column location cannot be specified in the same statement. All specified locations must be default or DATA locations.
If the location option is omitted, the table is created in the default database location.
For details about defining location names and extending databases, see the User Guide.
LOCATION=location_spec can be one of the following syntax options:
LOCATION=(location_name {, location_name} )
Creates all columns in the specified locations, horizontally partitioned. If one location is specified, creates all columns in the specified location.
LOCATION(column_name)=(location_name) [, LOCATION(column_name)=(location_name)]
Creates each column in the specified location (vertically partitioned). The column named "default" must be escaped.
LOCATION(column_name)=(location_name1 , location_name2)
Creates the column in horizontal partitions in the specified locations. The column named "default" must be escaped.
LOCATION(DEFAULT)=(location_name {, location_name})
Specifies the locations for columns defined in CREATE TABLE but not explicitly listed in WITH LOCATION. Creates columns in the specified locations, horizontally partitioned.
Note: The LOCATION(DEFAULT) syntax does not define "default" locations for the table.
PARTITION=(partitioning_scheme) | NOPARTITION
(Required) Specifies how a table is to be partitioned. If the table is not to be partitioned, you must specify NOPARTITION.
JOURNALING
Explicitly enables or disables journaling (incremental backup) on the table.
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.
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).
SECURITY_AUDIT = (table_audit_opt {, table_audit_opt})
Specifies the level of security, as follows:
TABLE
(Default) Implements table-level security auditing on general operations (for example create, drop, modify, insert, or delete) performed on the table.
[NO]ROW
Not supported for Vector tables. Implements row-level security auditing on operations performed on individual rows, such as insert, delete, update, or select.
[NO]MINMAX = [(column {, column})]
Creates or does not create min-max indexes on all or specified columns.
WITH [NO]MINMAX affects all columns. WITH [NO]MINMAX=(column, column) affects the listed columns and sets the other columns to the opposite.
The default behavior is to create min-max indexes on all columns.
Caution! Limiting min-max indexing with NOMINMAX can severely impact performance. Use NOMINMAX only if necessary to alleviate memory usage problems with very wide tables (many columns), and then use it only for columns not involved in restrictions or joining, if possible.