WITH Clause for CREATE TABLE
The WITH clause begins with the keyword WITH followed by any of the following options, each separated by a comma:
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.
[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.
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})
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. Valid structures are:
X100
Creates an X100 table. Stores data in columns. Puts as few columns as possible in one disk block. An alias for X100 is VECTORWISE.
X100_ROW
Creates an X100 table. Stores data in rows. Puts as many columns as possible in one disk block. An alias for X100_ROW is VECTORWISE_ROW.
HEAP
Stores data in a “heap” with no key. Use this structure to create a traditional Ingres table.
PARTITION =
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 Actian X 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).
[NO]MINMAX = [(column {, column})]
X100 Only. 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.
Note: After a min-max index has been created for a table or column, the column_has_minmax column in the iicolumns catalog will have a value of Y (yes).
[NO]MINMAX_SAMPLES
PAGE_SIZE Option
The PAGE_SIZE option on the WITH clause in the CREATE TABLE statement creates a table with a specific page size. This option has the following format:
PAGE_SIZE = n
where n is the number of bytes.
Valid values are shown in the Number of Bytes column in the following table:
SECURITY_AUDIT Option
The SECURITY_AUDIT option on the WITH clause specifies the auditing level.
This option has the following format:
SECURITY_AUDIT = (table_audit_opt {, 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
Implements row-level security auditing on operations performed on individual rows, such as insert, delete, update, or select. If NOROW is specified, the row-level security auditing is not implemented.
For example, an SQL DELETE statement that deleted 500 rows from a table with both table and row auditing generates the following audit events:
• One table-delete audit event, indicating the user issued a delete against the table.
• 500 row-delete audit events, indicating which rows were deleted.
Note: [NO]ROW is not supported for X100 tables.
Note: Either TABLE and ROW or TABLE and NOROW auditing can be specified. If NOROW is specified, row-level auditing is not performed. If either clause is omitted, the default installation row auditing is used. The default can be either ROW or NOROW depending on how your installation is configured.
SECURITY_AUDIT_KEY Option
The SECURITY_AUDIT_KEY=column option on the WITH clause lets you specify an optional attribute to be written to the audit log to assist row or table auditing. For example, an employee number can be used as the security audit key:
CREATE TABLE employee (name CHAR(60), emp_no INTEGER)
WITH SECURITY_AUDIT = (TABLE, ROW),
SECURITY_AUDIT_KEY = (emp_no);
If no user-specified attribute is given and the table has row-level auditing, a new hidden attribute, _ii_sec_tabkey of type TABLE_KEY SYSTEM_MAINTAINED is created for the table to be used as the row audit key. Although any user attribute can be used for the security audit key (SECURITY_AUDIT_KEY clause), we recommend that a short, distinctive value be used (such as a social security ID), allowing the user to uniquely identify the row when reviewing the security audit log. If an attribute longer than 256 bytes is specified for the security audit key, only the first 256 bytes are written to the security audit log.
MINMAX_SAMPLES Option
The WITH MINMAX_SAMPLES option produces a sampled min-max index. A sampled min-max index is used in conjunction with the automatic histogram generation in the optimizer. A sampled min-max index aids the execution engine like a non-sampled one does, but also provides an automatic histogram much faster than a full scan-generated histogram, thus letting the optimizer produce better query plans faster.
The WITH [NO]MINMAX_SAMPLES option can be used on the CREATE TABLE [AS SELECT], DECLARE GLOBAL TEMPORARY TABLE [AS SELECT], and ALTER TABLE...ADD MINMAX statements.
MINMAX_SAMPLES is a table property; it sets the default sampling state for the table. The default is NOMINMAX_SAMPLES.
To retrieve the current MINMAX_SAMPLES setting (valid values are Y and N), connect to the database and issue the statement:
SELECT MINMAX_SAMPLES FROM iitables WHERE table_name = 'your_table';
To change the MINMAX_SAMPLES property of a table, use ALTER TABLE... ADD MINMAX column-list SET [NO]MINMAX_SAMPLES. The setting you specify becomes the new permanent MINMAX_SAMPLES property of the table.
Note: You cannot alter an existing min-max index to be sampled or not-sampled; you must drop and re-add it.