With_Clause for Create Table

The CREATE TABLE statement accepts the following with_clause options:

• 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 and specified with NODUPLICATES, the heap table modified to a different table structure, the NODUPLICATES option is enforced. (By default, all structures accept duplicate rows.)

  The DUPLICATES setting can be overridden by specifying a unique key for a table in the MODIFY statement.

• PAGE_SIZE = n

  Specifies a page size, in number of bytes. Valid values are described in Page_size Option.

  Default: 2048. The tid size is 4.

  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.

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

• PARTITION =

  Defines a partitioned table. For more information, see Partitioning Schemes.

• NOPARTITION

  Indicates that the table is not to be partitioned. This is the default partitioning option.

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 row- or table-level auditing.

This option has the following format:

SECURITY_AUDIT = (audit_opt {, audit_opt})

• 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: 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.

WITH SECURITY_AUDIT_KEY Clause

The WITH SECURITY_AUDIT_KEY clause allows the user to 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.