WITH Clause

The WITH clause begins with the keyword WITH followed by any of the following options, each separated by a comma:

(Default) Stores data in columns. Puts as few columns as possible in one disk block.

Stores data in rows. Puts as many columns as possible in one disk block.

Explicitly declares the structure to be a traditional Ingres table.

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 Vector User Guide.

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.

Creates the column in horizontal partitions in the specified locations. The column named "default" must be escaped.

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.

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.

Specifies how a table is to be partitioned. For details, see Partitioning Syntax.

ENCRYPTION=encryption type, [AESKEY=hex-aes-key,] PASSPHRASE='encryption-passphrase'

Specifies encryption options to secure data in a column defined with the ENCRYPT option:

Specifies the type of Advanced Encryption Standard (AES) encryption for the column:

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.

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).

Specifies row or table level auditing, as described in Security_audit Option.

WITH [NO]MINMAX affects all columns. WITH [NO]MINMAX=(column, column) affects the listed columns and sets the other columns to the opposite.

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.