CREATE PARTITION

The CREATE PARTITION statement defines a partition to be used for audit logging purposes.

Scope

ISQL or Application Programs

SQL Syntax

  CREATE PARTITION PartitionName WITH ID = PartitionNumber

Parameters

PartitionName: specifies the logical name to be given to the new partition. Two partitions in the same DBEnvironment cannot have the same name. PartitionName may not be DEFAULT or NONE.
PartitionNumber: is an integer specifying the partition number. It must be a positive integer in the range 1 to 32767. The partition number identifies the partition in the audit log record.

Description

The CREATE PARTITION statement creates a new audit partition, which is a unit of data logging for an audit DBEnvironment.
The partition number may already be assigned to another partition, including the default partition. For example, several partitions with different partition names may have the same partition number in the audit log file. This allows the Audit Tool to gather statistics for all of these partitions as one unit while preserving the ability to manipulate each partition separately.
Creation of a partition does not cause a check against the maximum number of partitions. Only creation of audit log records in a partition checks if the maximum number of partitions is exceeded. The process of determining the number of partitions in a DBEnvironment is described under the START DBE NEW statement.
One data partition can be defined with the START DBE NEW or START DBE NEWLOG statements -- the DEFAULT partition. Before tables are assigned to a particular partition, they are placed in the DEFAULT partition.
To put a table in a partition, use the CREATE TABLE or ALTER TABLE SET PARTITION statement.
To remove a table from a partition, or change the partition it is in, use the ALTER TABLE SET PARTITION statement.
To delete the definition of a partition, use the DROP PARTITION statement.
Partitions can be created and tables placed in them without audit logging being enabled for a DBEnvironment. However, the partition information is only used in audit log records. Thus, partition information will not be utilized in logging until the DBEnvironment has audit logging enabled.
Data partition information (including the default partition) appears in the system view SYSTEM.PARTITION. If the default partition is set to NONE, or is never defined, no row appears in SYSTEM.PARTITION for it.
The DROP PARTITION and CREATE PARTITION statements are used to change the partition number assigned to a partition other than the default partition. The START DBE NEWLOG statement is used to change the partition number of the default partition.
The partition number, not the partition name, is used in audit logging. A partition name is used in the CREATE TABLE and ALTER TABLE statements to associate a table with a partition.

Authorization

You must have DBA authority to use this statement.

Example

To create a partition containing tables, first create the partition.

CREATE PARTITION PartsPart WITH ID = 10;

Then assign tables(s) to the partition.

ALTER TABLE PurchDB.Parts SET PARTITION PartsPart;

To drop a partition, first assign all tables in the partition to the NONE partition.

ALTER TABLE PurchDB.Parts SET PARTITION NONE;

Then drop the partition.

DROP PARTITION PartsPart;