ALTER TABLE (Alter table definition)

Function

ALTER TABLE has the following functions:

Privileges

Owner of a base table
This user can specify this command only on his or her own tables.

Format

The item numbers in the following format correspond with the operand numbers:

No.Format

1

ALTER TABLE [authorization-identifier.]table-identifier

table-definition-change::={ column-addition-definition
          |RDAREA-addition-definition
          |column-attribute-change-definition
          |column-deletion-definition
          |table-name-change-definition
          |column-name-change-definition
          |partitioning-storage-condition-change-definition }

Operands

1)[authorization-identifier.]table-identifier
authorization-identifier
Specifies the authorization identifier of the owner of the base table to be redefined.
table-identifier
Specifies the name of the base table to be redefined.
2) column-addition-definition::=
ADD column-name data-type [ARRAY [maximum-number-of-elements]][NO SPLIT]
[[column-recovery-restriction-1]
{LOB-column-storage-RDAREA-specification
|matrix-partitioned-LOB-column-storage-RDAREA-specification
|abstract-date-type-definition-LOB-column-storage-RDAREA-specification
[plug-in specification]
|matrix-partitioned-LOB-attribute-storage-RDAREA-specification
[plug-in specification]}]
[DEFAULT clause]
{[NULL|NOT NULL[WITH DEFAULT]]1
|[[NOT NULL]WITH DEFAULT]2}
[updatable-column-attribute]
[WITH PROGRAM]
1 Columns in a non-FIX table
2 Columns in a FIX table
column-name
Specifies the name of the column to be added to a base table.
The following rules apply to column names:
  1. When a column is to be added to a base table, the new column must be distinct in name from any columns that are already in the table.
  2. More than 30,000 columns cannot be added to a base table.
  3. A column cannot be added to a FIX table containing data.
  4. When a column is added, the null value is assigned to the added column in an existing row. For details about how to assign the null value, see the UPDATE statement Format 1 (Update data) for data manipulation SQL statements.
data-type
Specifies the data type of the column to be added to the base table.
The following rules apply to data types:
When specifying BLOB in data-type, specify a LOB storage RDAREA.
When specifying an abstract data type in data-type, specify a LOB attribute storage RDAREA. An abstract data type for which BLOB is defined in super-type cannot be specified.
If the authorization identifier for the abstract data type is omitted and an abstract data type for the default authorization identifier does not exist, and if the abstract data type of the same name is in the MASTER schema, HiRDB assumes that that abstract data type is specified.
ARRAY [maximum-number-of-elements]
Specifies the maximum number of elements for the repetition column to be added to the base table.
The following rules apply to ARRAY maximum-number-of-elements:
  1. In maximum-number-of-elements, specify an unsigned integer in the 2 to 30,000 range.
  2. Omitting ARRAY maximum-number-of-elements indicates that the column is not a repetition column.
  3. The following data types cannot be specified for a repetition column:
    [Figure]BLOB
    [Figure] BINARY
    [Figure] Abstract data type
  4. A repetition column cannot be specified in a FIX table.
NO SPLIT
This option is specified when storing one row per page if the actual data length of a variable-length character string is 256 bytes or greater.
The NO SPLIT option can reduce the size of the database. For details about the NO SPLIT option, see the HiRDB Version 8 Installation and Design Guide.
NO SPLIT can be specified only with variable-length character types (VARCHAR, NVARCHAR, and MVARCHAR).
column-recovery-restriction-1 ::=[RECOVERY[{ALL|PARTIAL|NO}]]
When adding a BLOB data-type column or an abstract data-type column with a BLOB attribute to a table, specify an update log acquisition mode for the LOB column storage RDAREA in which the column is to be stored or for the database for the LOB storage RDAREA in the abstract data type definition.
Column recovery restriction cannot be specified for columns other than a BLOB data type column or an abstract data type column with a BLOB attribute.
ALL
This option is specified when operating the LOB RDAREA in the log acquisition mode. When the RDAREA is operated in the log acquisition mode, HiRDB acquires the database update log necessary for rollback and roll-forward operations.
PARTIAL
This option is specified when operating the LOB RDAREA in the pre-update log acquisition mode. When the RDAREA is operated in the pre-update log acquisition mode, HiRDB acquires the database update log necessary for rollback operations.
NO
This option is specified when operating the user LOB RDAREA in the no-log mode. When the RDAREA is operated in the no-log mode, HiRDB does not acquire a database update log.
Depending on the specific update log acquisition method specified for a database, HiRDB employs different UAP execution methods or user LOB RDAREA recovery methods in the event of an error. For details about no-log mode operations, see the HiRDB Version 8 System Operation Guide.
LOB-column-storage-RDAREA-specification::=
IN {LOB-column-storage-RDAREA-name
|(LOB-column-storage-RDAREA-name)
|((LOB-column-storage-RDAREA-name)
[, (LOB-column-storage-RDAREA-name)]...)}
When adding a BLOB-type column, specifies the name of the user LOB RDAREA in which BLOB column data is to be stored.
The following rules apply to LOB column storage RDAREAs:
  1. If BLOB is specified as a data type for a column, always specify the name of the LOB column storage RDAREA. A name cannot be specified for a column for which a data type other than BLOB is specified.
  2. When adding a column to a row-partitioned table, specify the same number of LOB RDAREAs as the user RDAREAs specified in the table definition, taking care that user RDAREAs and LOB RDAREAs on the same server are in the same order.
  3. LOB column storage RDAREA names cannot be specified in duplicate. When adding a column to a partitioned table with a boundary value specification or to a matrix-partitioned table, specify corresponding LOB column storage RDAREA names, in which case LOB column storage RDAREA names may be specified in duplicate.
matrix-partitioned-LOB-column-storage-RDAREA-specification::=two-dimensional-storage-RDAREA-specification
two-dimensional-storage-RDAREA-specification::=(matrix-partitioning-RDAREA-list
[, matrix-partitioning-RDAREA-list]...)
matrix-partitioning-RDAREA-list::=
(RDAREA-name[, RDAREA-name]...)
When adding a BLOB-type column to a matrix-partitioned table, specifies the name of the RDAREA in which the BLOB column is to be stored.
When adding a BLOB-type column to a matrix-partitioned table, specifies the RDAREA for storing matrix-partitioned LOB columns.
For RDAREA names, see the explanation in LOB-column-storage-RDAREA-specification.
abstract-date-type-definition LOB-column-storage-RDAREA-specification::=
ALLOCATE(attribute-name[..attribute-name]...
IN {LOB-attribute-storage-RDAREA-name
|(LOB-attribute-storage-RDAREA-name)
|((LOB-attribute-storage-RDAREA-name)
[, (LOB-attribute-storage-RDAREA-name)]...)}
[, attribute-name[..attribute-name]...
IN {LOB-attribute-storage-RDAREA-name
|(LOB-attribute-storage-RDAREA-name)
|((LOB-attribute-storage-RDAREA-name)
[, (LOB-attribute-storage-RDAREA-name)]...)}]...)
This option is specified when adding a column of the abstract data type including the LOB attribute.
attribute-name[..attribute-name]
Specifies attribute names that comprise an abstract data type. If the attribute of an abstract data type is the abstract data type and if the attribute of the nested abstract data type has a LOB-type attribute, specify the attribute name of the LOB type.
Specify an attribute name in the following cases:
  • Attribute of an abstract data type definition
    This operand is specified if the attribute of the abstract data type definition is the LOB type.
  • Nest of an abstract data type definition
    If the attribute of an abstract data type is the abstract data type and if the attribute of a nested abstract data type is a LOB-type attribute, specify the attribute name of the LOB type.
LOB-attribute-storage-RDAREA-name
Specifies the name of the user LOB RDAREA that stores BLOB attribute data, located in any hierarchy of abstract data types.
The following rules apply to LOB attribute storage RDAREAs:
  1. When an abstract data type including BLOB is specified as a data type, always specify the name of a user LOB RDAREA for each BLOB attribute. Such a name cannot be specified in attributes for which a non-BLOB data type is specified.
  2. When adding a column to a row-partitioned table, specify the same number of LOB RDAREAs as the user RDAREAs specified in the table definition, taking care that user RDAREAs and LOB RDAREAs on the same server are in the same order.
  3. LOB attribute storage RDAREA names cannot be specified in duplicate. When adding a column to a partitioned table with a boundary value specification, or to a matrix-partitioned table, specify corresponding LOB attribute storage RDAREA names, in which case LOB attribute storage RDAREA names may be specified in duplicate.
plug-in-specification ::=PLUGIN plug-in-option
Specifies as a character string literal (of up to 255 bytes) parameter information to be passed to the plug-in facility for a column that is defined as an abstract data type for which the plug-in facility is implemented. Hexadecimal character string literals cannot be specified as parameter information.
For details about parameter information, see the respective plug-in manuals.
matrix-partitioned-LOB-attribute-storage-RDAREA-specification::=two-dimensional-storage-RDAREA-specification
two-dimensional-storage-RDAREA-specification::=(matrix-partitioning-RDAREA-list
[, matrix-partitioning-RDAREA-list]...)
matrix-partitioning-RDAREA-list ::=
(RDAREA-name[, RDAREA-name]...)
When adding a column of abstract data type including the LOB attribute to a matrix-partitioned table, specifies the name of the RDAREA in which the LOB attribute is to be stored.
When adding a column of abstract data type including the LOB attribute to a matrix-partitioned table, specifies the name of the RDAREA in which the matrix-partitioned LOB attribute is to be stored.
For RDAREA names, see the explanation in abstract-date-type-definition LOB-column-storage-RDAREA-specification ::= .
DEFAULT clause ::=DEFAULT[predefined-value]
This option is specified when setting a predefined value in the column being added.
The following rules apply to DEFAULT clause:
  1. DEFAULT clause cannot be specified together with WITH DEFAULT in a single ALTER TABLE.
  2. If data is stored in the base table, DEFAULT clause cannot be specified on the column being added.
  3. The DEFAULT clause cannot be specified on a BLOB-type column, an abstract data-type column, or a BINARY-type column, with a definition length of 32,001 bytes or greater.
  4. The DEFAULT clause cannot be specified on a repetition column.
NULL
The NULL option can be specified on the column specified in column-name if the column allows the null value.
The NULL option cannot be specified on a column in a FIX table.
NOT NULL
This option is specified if the column specified in column-name must be constrained (NOT NULL-constrained) so that it does not allow the null value.
The following rules apply to the NOT NULL option:
  1. NOT NULL cannot be specified if data is already stored in the base table.
  2. If NOT NULL is omitted, the added column allows the null value, and if the column was added to a row containing data, the null value is assigned to the column.
  3. The NOT NULL option cannot be specified on repetition columns or columns of the abstract data type.
WITH DEFAULT
This option is specified when adding a NOT NULL-constrained column containing a predefined value.
The following rules apply to the WITH DEFAULT option:
  1. Specify NOT NULL when specifying WITH DEFAULT for a non-FIX table.
  2. WITH DEFAULT cannot be specified together with DEFAULT clause in a single ALTER TABLE.
  3. WITH DEFAULT cannot be specified for a repetition column.
  4. WITH DEFAULT cannot be specified for columns of the abstract data type.
updatable-column-attribute::= UPDATE [ONLY FROM NULL]
Specify this operand when adding an updatable column to a falsification-prevented table or adding an updatable column to a table that is to be changed to a falsification-prevented table.
The updatable column attribute is valid only when specified on a falsification-prevented table.
For details about how to change a given table to a falsification-prevented table, see the INSERT ONLY option in CHANGE.
UPDATE
Specify this operand when adding an updatable column to a falsification-prevented table.
UPDATE ONLY FROM NULL
Specify this operand when adding a column in which row values can be changed only once from the null value to a non-null value in a falsification-prevented table.
The following table summarizes the conditions under which the value of an UPDATE ONLY FROM NULL-specified column can be changed in a falsification-prevented table.
Column value before changeColumn value after changeWhether updatable
Null valueNull valueY
Null valueNon-null-valueY
Non-null-valueNull valueN
Non-null-valueNon-null-value*N
Legend:
Y: Updatable
N: Not updatable
Note
For a repetition column, only updates by column from the null value (a value in which the number of elements is 0) to an unsubscripted specification can be executed.
* Contains the same value as the pre-update value.
Specification of the UPDATE ONLY FROM NULL operand is subject to the following rules:
  1. The operand cannot be specified for a NOT NULL-specified column.
  2. The operand cannot be specified for a FIX table.
  3. The operand cannot be specified for BLOB-type columns and for BINARY type columns with a definition length of 32,001 bytes or greater.
If the attribute is specified, the column value of the updatable column attribute can be updated under the following conditions:
Table typeUPDATE specificationUPDATE ONLY FROM NULL specificationNo specification
SpecifiableColumn value updatableSpecifiableColumn value updatableSpecifiableColumn value updatable
Non-falsification-prevented tableYYYY[Figure]Y
Falsification-prevented tableYYYY*[Figure]N
Legend:
Y: Updatable
N: Not updatable
[Figure]: Not applicable
* Can be updated only once from the null value to a non-null value.
WITH PROGRAM
Specify WITH PROGRAM in any of the following operations when an SQL object with an effective function, procedure, or trigger with respect to the table is to be nullified:
  • Adding a column with a DEFAULT clause to a table
  • Adding a column with a NOT NULL specification to a table
  • Adding a column of the BLOB or abstract data type to a table
3) RDAREA-addition-definition::=
ADD RDAREA table-storage-RDAREA-name
[FOR COLUMN column-name
{LOB-column-storage-RDAREA-specification
|abstract-date-type-definition LOB-column-storage-RDAREA-specification}
[, column-name {LOB-column-storage-RDAREA-specification
|abstract-date-type-definition LOB-column-storage-RDAREA-specification}]...]
[FOR INDEX index-identifier index-storage-RDAREA-specification
[, index-identifier index-storage-RDAREA-specification]...]
[FOR [PRIMARY] CLUSTER KEY index-storage-RDAREA-specification]
[FOR PRIMARY KEY index-storage-RDAREA-specification]
[WITH PROGRAM]
table-storage-RDAREA-name
This operand is specified when a user RDAREA is to be added to a row-partitioned table by a hash function.
The following rules apply to the names of table storage RDAREAs:
  1. The addition of a user RDAREA to a table may fail if a unique-specification index is defined on the table. For details, see the explanation for CREATE INDEX Format 1, UNIQUE operand.
  2. In the table to which an RDAREA is to be added, any user RDAREA that is already in use cannot be specified in table-storage-RDAREA-name.
  3. If the rebalancing facility is not used, a user RDAREA cannot be added to a FIX hash-partitioned table in which data is stored. For details about the rebalancing facility, see the HiRDB Version 8 System Operation Guide.
  4. Other tables and indexes cannot be added to the RDAREA that stores tables that use the rebalancing facility.
  5. If an RDAREA is added to a FIX hash-partitioned table that uses the rebalancing facility, performance of the SQL statement that retrieves or updates the table may deteriorate until such time as the rebalancing utility is successfully executed.
  6. If an index with a unique specification, a unique cluster key, or the primary key is defined for a FIX hash-partitioned table using the rebalancing facility, and if an RDAREA is added to that table, no data can be added to or updated in the table until such time as the rebalancing utility is successfully executed.
  7. When adding an RDAREA to a table using the free space reusage facility, the free space reusage facility is also applied to the RDAREA that is added.
  8. A shared RDAREA cannot be specified in a table storage RDAREA.
  9. The maximum total number of split RDAREAs after a change in table definition, exclusive of duplicates, is 1024.
column-name
This operand is specified when the table to which an RDAREA is being added contains a BLOB column or a column of the abstract data type including the BLOB attribute.
In column-name, specify either a BLOB-type column or a column defined in the abstract data type including the BLOB attribute.
All BLOB-type columns and columns of the abstract data type including the BLOB attribute need to be specified.
LOB-column-storage-RDAREA-specification::=
IN {LOB-column-storage-RDAREA-name
|(LOB-column-storage-RDAREA-name)
|((LOB-column-storage-RDAREA-name)
[, (LOB-column-storage-RDAREA-name)]...)}
Specifies the name of the user LOB RDAREA in which BLOB-column data is to be stored.
The name of the RDAREA to be specified should be the user LOB RDAREA that is located on the same server as the RDAREA specified in table-storage-RDAREA-name.
abstract-data-type-definition-LOB-storage-RDAREA-specification::=
ALLOCATE(attribute-name[..attribute-name]...
IN {LOB-attribute-storage-RDAREA-name
|(LOB-attribute-storage-RDAREA-name)
|((LOB-attribute-storage-RDAREA-name)
[, (LOB-attribute-storage-RDAREA-name)]...)}
[, attribute-name[..attribute-name]...
IN {LOB-attribute-storage-RDAREA-name
|(LOB-attribute-storage-RDAREA-name)
|((LOB-attribute-storage-RDAREA-name)
[, (LOB-attribute-storage-RDAREA-name)]...)}]...)
attribute-name[..attribute-name]
Specifies the name of the attribute that comprises the abstract data type. If the attribute of an abstract data type is the abstract data type and if the attribute of a nested abstract data type is a LOB-type attribute, specify the attribute name of the LOB type.
Specify attribute-name in the following cases:
  • Attribute of an abstract data type definition
    Specify attribute-name if the data type of an attribute of an abstract data type definition is the LOB type.
  • Nest of an abstract data type definition
    If the attribute of an abstract data type is the abstract data type and if the attribute of a nested abstract data type is a LOB-type attribute, specify the attribute name of the LOB type.
LOB-attribute-storage-RDAREA-name
Specifies the name of the user LOB RDAREA for the storage of BLOB attribute data, in any hierarchy of abstract data types.
The following rules apply to LOB attribute storage RDAREAs:
  1. Specify a user LOB RDAREA for all BLOB attributes that are in the abstract data type.
  2. For the RDAREA name to be specified, specify the user LOB RDAREA that is defined on the same server as the RDAREA that was defined in table-storage-RDAREA-name.
FOR INDEX index-identifier index-storage-RDAREA-specification
[, index-identifier index-storage-RDAREA-specification]...]
If an index is defined for the table to which an RDAREA is being added, specifies the index storage RDAREA corresponding to the table storage RDAREA being added.
index-identifier
Specifies the index identifier of the index that is defined for the table to which the RDAREA is being added.
index-storage-RDAREA-specification
For index storage RDAREA specifications, see the explanation in ADD RDAREA, index storage RDAREA specification.
FOR [PRIMARY] CLUSTER KEY index-storage-RDAREA-specification
If a cluster key is already defined for the table to which an RDAREA is to be added, specifies the cluster key storage RDAREA associated with the table storage RDAREA being added.
PRIMARY
This option is specified if the cluster key is defined as the primary key.
index-storage-RDAREA-specification
For index storage RDAREA specifications, see the explanation in ADD RDAREA, index storage RDAREA specification.
FOR PRIMARY KEY index-storage-RDAREA-specification
If the primary key is already defined for the table to which an RDAREA is to be added, specifies the primary key storage RDAREA associated with the table storage RDAREA being added.
If a table comprising a cluster key is defined as the primary key, specify FOR PRIMARY CLUSTER KEY.
For index storage RDAREA specifications, see the explanation in ADD RDAREA, index storage RDAREA specification.
index-storage-RDAREA-specification::=
IN{index-storage-RDAREA-name
|(index-storage-RDAREA-name)
|((index-storage-RDAREA-name)
[, (index-storage-RDAREA-name)]...)}
Specifies the name of the RDAREA in which an index, a cluster key, or the primary key is to be stored.
For the index (the data type of the index constituent column is not the abstract data type), or cluster key or primary key defined in CREATE INDEX format 1, specify a user RDAREA. However, for HiRDB/Parallel Server, a shared RDAREA is specified for a shared table.
For the index (the data type of the index constituent column is the abstract data type) defined in CREATE INDEX format 2, specify a user LOB RDAREA.
The following rules apply to the specification of an index storage RDAREA:
  1. For HiRDB/Parallel Server, the RDAREA for storing the table being added and the RDAREA that stores the associated index must be on the same back-end server.
  2. The index that is row-partitioned on the server must have the same number of table-storage RDAREAs as index-storage RDAREAs.
  3. Indexes on a server that are not row-partitioned must have the same number of index-storing RDAREAs as the back-end server on which tables are stored.
  4. When adding a user RDAREA, you need to specify an RDAREA that satisfies the following formula:
    Key length [Figure] (page-size-of-index-storage-RDAREA[Figure] 2) - 1242
  5. Table 3-6 shows which index storage RDAREAs can be specified, depending on the type of index involved. For an index for which the specification of an index storage RDAREA is mandatory, an index storage RDAREA should be specified.

    Table 3-6 Whether an index storage RDAREA can be specified depending on the index type

    Index definition methodIndex-partitioning methodSpecifiability of index storage RDAREA
    Index and primary key defined using CREATE INDEX format 1Row-partitioning on one server1On-server partitioning key indexY
    On-server non-partitioning key index
    Row-partitioning only between servers2On-server partitioning key index
    On-server non-partitioning key indexN5, 6
    Row partitioning on and between servers3On-server partitioning key indexY
    On-server non-partitioning key index
    Non-partitioning4Non-partitioning key indexN5
    Index and cluster key defined using CREATE INDEX format 2Row-partitioning on one server1Y
    Row-partitioning only between servers2
    Row-partitioning on and between servers3
Legend:
Y: An index storage RDAREA needs to be specified.
N: An index storage RDAREA is not required.
1 Refers to row-partitioning on a HiRDB/Single Server or row-partitioned that is closed to a single back-end server on a HiRDB/Parallel Server.
2 Refers to row-partitioning extending across multiple back-end servers on a HiRDB/Parallel Server and not partitioning an index on the back-end server.
3 Refers to row-partitioning extending across multiple back-end servers on a HiRDB/Parallel Server and partitioning an index on the back-end server.
4 Refers to an index that is not row-partitioned.
5 If the addition of RDAREAs increases the number of back-end servers on which tables are stored, you also need to specify index storage RDAREAs.
6 If the addition of RDAREAs does not change the number of back-end servers on which tables are stored, you can also specify index storage RDAREAs. If an index storage RDAREA is specified, the index becomes a row-partitioned index on the server.
WITH PROGRAM
When adding an RDAREA to a hash-partitioned table, this option is specified to nullify the SQL object if there is an effective SQL object for a function, procedure, or trigger that uses that table.
4) column-attribute-change-definition::=
CHANGE{column-name{[{VARCHAR(data-length)
|NVARCHAR(data-length)
|MCHAR({*|data-length})
|MVARCHAR({*|data-length})}]
[ARRAY [{*|maximum-number-of-elements}]]
|[ARRAY [maximum-number-of-elements]]
|BINARY(data-length)}
[{NO SPLIT|SPLIT}]
[column-recovery-restriction-2]
[{SET DEFAULT clause|DROP DEFAULT}]
[WITH DEFAULT]
[updatable-column-attribute]
|CLUSTER KEY[UNIQUE]
|LOCK{ROW|PAGE}
|HASH hash-function-name
|SEGMENT REUSE {[number-of-segments[{K|M|G}]]|NO}}
|INSERT ONLY [WHILE {date-interval-data | labeled-interval} BY column-name]}
[WITH PROGRAM]
column-name
When modifying the definition of a column, specifies the name of the column.
The following rules apply to column names:
  1. Columns for which any of the following specifications is made during a view table definition cannot be redefined using ALTER TABLE:
    [Figure]A column specified as an argument in a scalar function in a selection expression or a search condition
    [Figure]A column specified as an argument in a function call in a selection expression or a search condition
    [Figure]A column specified as an operation term for a concatenation operation in a selection expression or a search condition
    [Figure]A column specified in a value expression in a CASE expression in a selection expression or a search condition
    [Figure]A column specified in a value expression in a CAST specification in a selection expression or a search condition
  2. If any of the following items is specified in a view definition statement during the definition of a view table, the definition of columns in the table specified in the view table cannot be altered using ALTER TABLE.
    [Figure]An item containing a row subquery
    [Figure]An item containing a scalar subquery or a table subquery using set operations
    [Figure]An item containing a table subquery with a results column count of 2 or greater in a FROM clause derived table or in a predicate other than the EXISTS predicate
    [Figure]A selection expression containing a scalar subquery
    [Figure]A row value constructor on the left side of a comparison predicate, IN predicate, or quantified predicate containing a scalar subquery
    [Figure]A row value constructor, on the right side of a comparison predicate, containing a scalar subquery, with the number of row value constructor elements being 2 or greater
    [Figure]A list of row value constructors, on the right side of an IN predicate, containing a scalar subquery
    [Figure]Scalar subquery contained in a BETWEEN predicate, LIKE predicate, XLIKE predicate, or SIMILAR predicate
  3. In column-name, specify the name of a column in the base specified in table-identifier.
  4. After column-name, specify one or more operands that provide a description of the change in column definition.
{VARCHAR (data-length))
|NVARCHAR(data-length)
|MCHAR({*|data-length})
|MVARCHAR({*|data-length})
|BINARY (data-length)}
Specifies data-length when changing the data length of variable-length data; it can also be specified when changing the data type from CHAR to MCHAR or from VARCHAR to MVARCHAR.
The following rules apply:
  1. The maximum length of a column of the variable-length data type cannot be reduced.
  2. For a variable-length data-type column for which an index is defined, its data length cannot be changed in a manner that violates the following formula:
    Key length [Figure]
    MIN ((page-size-of-index-storage-RDAREA[Figure] 2) - 1242, 4036)
  3. A data type should be omitted when you are not changing the maximum length.
  4. Of the columns that are specified in a derived query expression in a view definition statement, columns that are subject to any of the following specifications cannot be changed to BINARY greater than or equal to 32,001 bytes:
    [Figure]Columns that are specified in a subquery in a comparison predicate, quantified predicate, or IN predicate
    [Figure]Columns specified for duplicate elimination
    [Figure]Columns specified for grouping or in a set function
    [Figure]Columns specified in a selection expression with a query specification that is subject to set operations
    [Figure]Columns that are specified during the definition of a view table that is expanded as an inner derived table in a query specification that satisfies one of the conditions for the creation of an inner derived table
    For conditions for creating an inner derived table, see 2.21 Inner derived tables.
  5. CHAR can be changed to MCHAR, but the data length cannot be changed. For data length, specify either the data length before the change or an asterisk (*).
  6. VARCHAR can be changed to MVARCHAR while increasing the maximum length of the column. If the maximum length is not to be changed, for data length, specify either the maximum length before the change or an asterisk (*).
  7. The following columns cannot be changed to a BINARY type of 32,001 bytes or greater:
    [Figure]Columns for which UPDATE ONLY FROM NULL of the updatable column attribute is specified
    [Figure]Columns to which UPDATE ONLY FROM NULL of the updatable column attribute is assigned at the same time as a change in the attribute's data length
ARRAY [{*|maximum-number-of-elements}]
This operand is specified when increasing the maximum number of elements for a repetition column.
The following rules apply:
  1. In maximum-number-of-elements, specify an unsigned integer in the 2 to 30,000 range.
  2. The default is that the column is not a repetition column.
  3. When not changing the maximum number of elements, specify an asterisk (*).
  4. The maximum number of elements cannot be reduced.
ARRAY [maximum-number-of-elements]
This operand is specified when increasing the maximum number of elements for a repetition column.
In maximum-number-of-elements, specify an unsigned integer in the 2 to 30,000 range.
The maximum number of elements cannot be reduced.
{NO SPLIT|SPLIT}
This operand is specified to reduce the size of the database when storing data of a variable-length character type (VARCHAR, NVARCHAR, or MVARCHAR).
For details on NO SPLIT and SPLIT (the NO SPLIT option), see the HiRDB Version 8 Installation and Design Guide.
NO SPLIT
This option is specified when the real data length is greater than or equal to 256 bytes.
  • When defining a table or adding a column definition, specify this option for a variable-length character string type for which NO SPLIT is not specified.
  • This option cannot be specified for a base table containing data.
SPLIT
This option is specified when the real data length is less than or equal to 255 bytes.
  • When defining a table or adding a column definition, specify this option for a variable-length character type for which NO SPLIT is specified.
  • This option cannot be specified for a base table containing data.
column-recovery-restriction-2::= RECOVERY{ALL|PARTIAL|NO}
When the column data type is BLOB or when the column is an abstract data type containing the BLOB attribute, this option is specified to change the method of acquiring the database update log corresponding to the column. The details of the ALL, PARTIAL, and NO options are the same as when column-recovery-restriction-1 is specified for ALTER TABLE ADD.
{SET DEFAULT clause|DROP DEFAULT}
SET DEFAULT clause
DEFAULT clause ::=DEFAULT[predefined-value]
The column data type that can be specified is the same as the default clause of ALTER TABLE ADD.
Specifying the SET DEFAULT clause on a column for which WITH DEFAULT is defined nullifies the WITH DEFAULT specification and causes the SET DEFAULT clause specification to take effect.
This option cannot be specified with WITH DEFAULT in a single ALTER TABLE statement.
DROP DEFAULT
This option is specified when deleting a predefined value.
DROP DEFAULT should be specified for columns for which DEFAULT clause is specified.
WITH DEFAULT
This option is specified when changing a NOT NULL-constrained column without a predefined value into a NOT NULL-constrained column for which there is a predefined value.
The following rules apply to WITH DEFAULT:
  1. This option cannot be specified on not NOT NULL-constrained columns.
  2. WITH DEFAULT cannot be specified on NOT NULL-constrained columns for which there already is a predefined value.
  3. WITH DEFAULT cannot be specified on columns for which DEFAULT clause is defined.
  4. This option cannot be specified together with a SET DEFAULT or DROP DEFAULT clause in a single ALTER TABLE statement.
  5. This option cannot be used to change a column with a predefined value into a column without a predefined value.
  6. This option cannot be specified for a repetition column.
  7. This option cannot be specified for an abstract data type column.
updatable-column-attribute::= UPDATE [ONLY FROM NULL]
Specify this operand if the updatable column attribute is to be changed before a table is changed into a falsification-prevented table.
The updatable column attribute is valid only with falsification-prevented tables.
For changing a given table into a falsification-prevented table, see the INSERT ONLY option.
The following rules apply to the updatable column attribute:
  1. The attribute cannot be specified for a falsification-prevented table.
  2. The attribute cannot be specified for a column for which SYSTEM GENERATED is specified.
  3. The attribute cannot be specified for a column for which the updatable column attribute is already specified.
  4. The attribute cannot be specified for any of the following non-updatable columns:
    [Figure]Cluster key constituent column
    [Figure]Partitioning key constituent column (not including partitioning key constituent columns for a flexible hash partitioning table)
UPDATE
With this option specified, the column is updatable after the table is changed into a falsification-prevented table.
UPDATE ONLY FROM NULL
After a change into a falsification-prevented table, the column value can be updated only once from the null value to a non-null value.
The following table shows the updatability of the values of a column specified in a falsification-prevented table with the UPDATE ONLY FROM NULL option:
Column value before updateColumn value after updateUpdatable
Null valueNull valueY
Null valueNon-null valueY
Non-null valueNull valueN
Non-null valueNon-null value*N
Legend:
Y: Updatable
N: Not updatable
Note
For a repetition column, only an update by column without a subscript specification can be executed from the null value (with an element count of 0).
* Contains the same value as before the update.
The UPDATE ONLY FROM NULL specification is subject to the following rules:
  • The operand cannot be specified for columns with a NOT NULL specification.
  • The operand cannot be specified for FIX tables.
  • The operand cannot be specified for the primary key or a cluster key constituent column.
  • The operand cannot be specified for a partitioning key constituent column.
  • The operand cannot be specified for a BLOB-type column or a BINARY-type column with a definition length of 32,001 bytes or greater.
If the attribute is specified, the column value of the updatable column attribute can be updated under the following conditions:
Table typeUPDATE specificationUPDATE ONLY FROM NULL specificationNo specification
SpecifiableColumn value updatableSpecifiableColumn value updatableSpecifiableColumn value updatable
Non-falsification-prevented tableYYYY[Figure]Y
Falsification-prevented tableNYNY*[Figure]N
Legend:
Y: Updatable
N: Not updatable
[Figure]: Not applicable
* Can be updated only once from the null value to a non-null value.
CLUSTER KEY UNIQUE
This option is specified when changing a cluster key without uniqueness constraint into a cluster key with uniqueness constraint.
The following rules apply to CLUSTER KEY UNIQUE:
  1. The cluster key attribute of a base table cannot be changed if the table already contains data.
  2. This option cannot be specified for a table if a uniqueness-constrained cluster key is defined in the table definition.
  3. This option cannot be specified for flexible hash-partitioned tables.
CLUSTER KEY
This option is specified when changing a cluster key with uniqueness constraint into a cluster key without uniqueness constraint.
The cluster key attribute cannot be changed for a base table if the table already contains data.
This option cannot be specified for a table if the table definition defines a not uniqueness constrained cluster key.
LOCK{ROW|PAGE}
This option is specified when changing the minimum lock resource unit for a table.
Specify LOCK ROW when changing the minimum lock resource unit into units of rows; specify LOCK PAGE when changing it into units of pages.
HASH hash-function-name
For a table that is row-partitioned by a hash function, this option can be specified when changing hash functions. This option cannot change hash functions on FIX hash-partitioned tables containing data.
HASH1-HASH6 and HASH0 cannot be specified as a hash function for a table that uses the rebalancing facility. Similarly, HASHA-HASHF cannot be used as a hash function for a table that does not use the rebalancing facility.
SEGMENT REUSE {[number-of-segments[{K|M|G}]]|NO}
This option is specified when applying the free space reusage facility for a table for which the free space reusage facility is not being used. For details about the free space reusage facility, see the HiRDB Version 8 Installation and Design Guide.
number-of-segments[{K|M|G}]
When using the free space reusage facility for a table and assigning an upper limit on the number of segments for the table, this operand specifies an upper limit on the number of segments in an unsigned integer in the 1 to 268,435,440 range. As a unit, you can also specify K (kilo), M (mega), or G (giga).
When a table is subject to frequent insertion or deletion of rows, this operand can improve row insertion performance and the storage efficiency in a specified segment.
When number-of-segments is not specified
The operand number-of-segments can be omitted when the free space reusage facility is being used and no maximum number of segments is set for the table.
Use this operand when a table is subject to frequent row insertions or deletions and only the specified table is to be stored in the RDAREA. This operand also improves row insertion performance and the storage efficiency of free space in the RDAREA in which the specified table is to be stored.
NO
This operand is specified when not using the free space reusage facility.
Specify NO for tables that are not subject to frequent row insertions or deletions.
The following rules apply to SEGMENT REUSE:
  1. The free space reusage facility has no effect on LOB columns, abstract data type columns of the LOB attribute, or indexes.
  2. The free space reusage facility cannot be specified for rebalancing tables.
INSERT ONLY [WHILE {date-interval-data | labeled-interval} BY column-name]
Specify this operand when changing a given table into a falsification-prevented table. For details about falsification-prevented tables, see the HiRDB Version 8 Installation and Design Guide.
If a table is converted into a falsification-prevented table, its values cannot be updated; however, its updatable columns can be updated.
For the falsification-prevented table, a period during which deletion of rows is disabled (a deletion-prevented duration) can be specified. For specifying a deletion-prevented duration, use WHILE to specify a time period, and as a column name, specify an insert history maintenance column (a column of the DATE type and SYSTEM GENERATED). If a deletion-prevented duration is not specified, the column cannot be deleted at any time.
date-interval-data
Specifies a deletion-prevented duration in a decimal representation of date interval data. See 1.3.4 Decimal representation of date interval data.
Date interval data can be specified in positive numbers only.
labeled-interval
Specifies a deletion-prevented duration as a labeled duration. See 2.11 Date operations for labeled durations.
Only a positive integer literal not enclosed in parentheses can be specified in a value expression for a labeled duration.
column-name
Specify a DATE-type column that is SYSTEM GENERATED.
The deletion-prevented duration includes the day the row was inserted, and the calculation of a deletion-prevented duration is subject to the rules on adding or subtracting date data and date interval data in 2.11 Date operations. The last deletion-prevented date and the deletion-enabled date result from the following operations:
  • last-deletion-prevented-date = row-insertion-date + deletion-prevented-duration - 1-day
  • deletion-enabled-date = row-insertion-date + deletion-prevented-duration
For the relationship between a last deletion-prevented date and a deletion enabled date at specified values of a row insertion date and deletion-prevented duration, see Table 3-27 Relationship between last day of deletion prevention and the deletion-allowed data.
Changing a given table to a falsification-prevented table is subject to the following restrictions:
  1. A table that is already converted into a falsification-prevented table cannot be specified.
  2. A table for which a foreign key is defined cannot be changed into a falsification-prevented table.
  3. A table for which a check constraint is defined cannot be changed into a falsification-prevented table.
  4. A table for which the updatable column attribute is specified for all columns cannot be changed into a falsification-prevented table.
  5. If a given table is defined in an RDAREA to which the inner replica facility is applied, the table cannot be changed into a falsification-prevented table.
  6. A table containing rows cannot be changed into a falsification-prevented table.
WITH PROGRAM
This option can be specified in the following cases:
  • For disabling an enabled SQL statement for the function, procedure, or trigger for the table in question when changing column names, changing the default for the DEFAULT clause, changing the lock mode for a table, changing hash functions for a hash-partitioned table, changing SEGMENT REUSE, or changing falsification-prevented tables.
  • Nullifying an SQL object that is effective for a function, procedure, or trigger on a cluster key when the uniqueness constraint attribute of the cluster key is to be changed.
5) column-deletion-definition::=
DROP column-name[WITH PROGRAM]
column-name
This operand is specified when deleting a column.
The following rules apply to the column-name operand:
  1. In column-name, specify the name of the column that is in the base table specified in table-identifier.
  2. Columns in a base table cannot be deleted if the base table already contains data.
  3. If a column is the only one left in a table, it cannot be deleted.
  4. Deleting a column causes any index on the column, comments, and any view table (including public view tables) using the column also to be deleted.
  5. Columns comprising an index for which a cluster key or the primary key is defined cannot be deleted.
  6. Columns with a BLOB data type cannot be deleted.
  7. Columns of a table containing abstract data-type columns cannot be specified.
  8. Insert history maintenance columns for a falsification-prevented table cannot be deleted.
  9. A column in a falsification-prevented table cannot be deleted if its deletion makes all columns in the table updatable.
WITH PROGRAM
This option is specified when deleting a column in a table and when SQL objects for which functions, procedures, and triggers using the table are to be nullified.
6) table-name-change-definition::=
RENAME TABLE TO table-identifier
[WITH PROGRAM]

This option is specified when renaming a table.

table-identifier
Specifies the name of the renamed base table.
The following rules apply to the table-identifier operand:
  1. This operand can also be used to rename indexed tables.
  2. This operand cannot be used to rename view tables or foreign tables.
  3. The names of the following tables cannot be modified:
    [Figure]A table that becomes the base for a view table
    [Figure]A table specified in a CREATE PROCEDURE SQL procedure statement
    [Figure]A table specified in a CREATE TRIGGER SQL procedure statement and a table that defines a trigger
  4. The name of a table cannot be changed to a name identical to a base table, a view table, a foreign table, or a table alias, that is in the schema.
  5. Falsification-prevented tables cannot be renamed.
WITH PROGRAM
When the name of a table identifier is modified, this option is specified to invalidate the valid SQL object of a function, procedure, or trigger defined for the table. Note that even when WITH PROGRAM is specified, the table name of the table for which the trigger is defined cannot be modified.
7) column-name-change-definition::=
RENAME COLUMN FROM pre-change-column-name TO post-change-column-name
[WITH PROGRAM]

This option is specified to change the name of a column.

old-column-name, new-column-name
When renaming a column, specifies the old name and the new name.
The following rules apply to old-column-name and new-column-name operands:
  1. Index key columns can also be renamed.
  2. Columns in a view table or foreign table cannot be renamed.
  3. The names of the following columns cannot be altered:
    [Figure]Columns in a table that becomes the base for a view table
    [Figure]Columns in a table specified in a CREATE PROCEDURE SQL procedure statement
    [Figure]Columns in a table specified in a CREATE TRIGGER SQL procedure statement, trigger event columns, and columns specified in a trigger action condition
  4. The name of a column cannot be changed to a column name that is already in the table.
  5. A column cannot be renamed if its existing column name is not in the table.
  6. Columns in a falsification-prevented table cannot be renamed.
WITH PROGRAM
When the name of a column is modified, this option is specified to invalidate the valid SQL object of a function, procedure, or trigger defined for the table containing the column. However, the WITH PROGRAM option cannot rename the table for which a trigger is defined or any of the following columns:
  • Trigger event columns
  • Columns that are referenced in a trigger action condition using an old or new values correlation name
  • Columns that are referenced in a trigger SQL statement using an old or new values correlation name
8) partitioning-storage-condition-change-definition::=
CHANGE RDAREA row-partitioned-table-change-specification
[LOB-column-storage-RDAREA-change-specification]
[index-storage-RDAREA-change-specification
[index-storage-RDAREA-change-specification]...]
[cluster-key-storage-RDAREA-change-specification]
[primary-key-storage-RDAREA-change-specification]
[WITHOUT PURGE]
[WITHOUT PROGRAM]
Specify this operand when modifying the partitioning storage condition for a row-partitioned table.
The partitioning storage condition for the following tables cannot be changed:
  • Matrix-partitioned table
  • Row-partitioned table containing columns of the abstract data type
In the following text, columns for which the BLOB type is specified are referred to as LOB columns.
In an ALTER TABLE statement, table partitioning storage conditions can be partitioned and combined in the following units:
Table typeTable partitioning methodExecution type
PartitioningCombining
Row-partitioned tableKey range partitioningBoundary value specificationPartitions an arbitrarily selected boundary value into 2 to 16 values.Combines 2 to 16 arbitrarily selected boundary values into a single value.
Storage condition specificationPartitions an arbitrarily selected RDAREA into 2 to 16 segments.Combines 2 to 16 arbitrarily selected RDAREAs into a single RDAREA.
For SQL examples of partitioning or combining table partitioning storage conditions, see examples 9 (on modifying the boundary values for a row-partitioned table with a boundary value specification) and 10 (on modifying the RDAREAs of a row-partitioned table with a storage condition specification) under Examples in subsection 8-10. If a partitioning storage condition is modified, the system deletes any data stored in the RDAREA that is subject to modification when the ALTER TABLE command is executed. (If the WITHOUT PURGE option is specified, the scope of deletion of modification-object data may change. For details, see the WITHOUT PURGE option for details.) For this reason, any data that is required after the execution of ALTER TABLE must be stored again, by performing operations such as unloading the data before the partitioning storage condition is changed and loading it after the partitioning storage condition is changed. For details about how to store again the data to be deleted, see Changing the partitioning storage condition for a table in the manual HiRDB Version 8 System Operation Guide.
Modification of table partitioning storage conditions is subject to the following rules:
Items to be checked before changing table partitioning storage conditions
Before changing table partitioning storage conditions, the following items must be checked:
  1. Modifying a partitioning storage condition requires HiRDB Advanced Partitioning Option 07-01 or later.
  2. Some table types and partitioning methods involved prohibit a modification of partitioning storage conditions, as summarized in the following table:
    Table typePartitioning methodModifiability
    Row-partitioned tableKey range partitioningStorage condition specificationTable in which only = is used for the storage condition comparison operatorY
    Table in which an operator other than = is used for the storage condition comparison operatorN
    Boundary value specificationY
    Hash partitioningN
    Matrix-partitioned tableFirst dimension: Boundary value specification
    Second dimension: Boundary value specification
    N
    First dimension: Boundary value specification
    Second dimension: Hash partitioning
    N
    Non-partitioned table*[Figure]N
    Legend:
    Y: Modifiable
    N: Modifiable
    [Figure]: Not applicable
    * A table without a partitioning storage condition specification.
  3. Partitioning storage conditions cannot be modified if table storage RDAREAs, index storage RDAREAs, and LOB column storage RDAREAs are not in 1-to-1 correspondence.
  4. For a row-partitioned table with a storage condition specification, the partitioning storage condition cannot be modified if both of the following conditions are satisfied:
    [Figure]An index key, primary key, or cluster key is defined in the table that is to be modified.
    [Figure]There is only a single table storage RDAREA in the table that is to be modified or in the table resulting from the modification.
  5. Partitioning storage conditions on falsification-prevented tables cannot be modified.
  6. The following items must be checked if the inner replica facility is being used:
    [Figure]RDAREAs storing tables, indexes, primary keys, cluster keys, and LOB columns must all have the same generation number.
    [Figure]If a referential constraint is defined for a table, RDAREAs that store the referencing table and the referenced table must all have the same generation number.
Rules for modifying a table partitioning storage condition
Modifying a table partitioning storage condition is subject to the following rules:
  1. The partitioning and combining of partitioning storage conditions cannot be performed simultaneously in a single execution of ALTER TABLE. These actions should be performed in two separate executions of ALTER TABLE.
  2. The RDAREAs for the table to be modified (RDAREA identified by the pre-modification boundary value list that specifies row-partitioned table modification or RDAREA specified in the pre-modification RDAREA information list), index storage RDAREAs, and LOB column storage RDAREAs must be in 1-to-1 correspondence.
  3. Tables, indexes, primary keys, cluster keys, and LOB columns must maintain correspondence between the number of partitions and any duplication of RDAREAs based on a table. (For example, if the system consolidates boundary values into a single value before and after the modification of a table, and if the RDAREA to be specified stores data other than a boundary value to be modified, the same RDAREA must be used before and after modification, and the index, the primary key, the cluster key, and the LOB column must also specify, in an identical manner, the position in which the same RDAREA is used. For details, see Modifying the table partitioning storage condition in the manual HiRDB Version 8 System Operation Guide.)
  4. The maximum total number of partitioned RDAREAs before and after a change in table definitions is 1,024, exclusive of duplicates.
  5. For a row-partitioned table with a storage condition specification, the name of the table storage RDAREA after the table definition modification must be unique. The maximum total number of boundary values (including other) before and after table definition is 3,000.
  6. The maximum total number of storage condition specifications after the table definition modification (including RDAREAs without storage condition specification) is 15,000.
  7. The data in the table in the RDAREA specified as an object of modification is deleted by the system during the execution of ALTER TABLE (the data in the index storage RDAREA and the data in the LOB column storage RDAREA associated with the RDAREA are also deleted by the system). If the inner replica facility is used, the data in the replica RDAREAs of all generations that store the table, index, primary key, cluster key, or LOB column (including the LOB-type attribute column) that are subject to modification is also deleted by the system during the execution of ALTER TABLE (see item 4 in the notes about modification).
  8. When USE is specified for the pd_check_pending operand in the system definition and the modification-target table is a referenced table, the table information in dictionary tables and in the RDAREA is set to check pending status for the table that is referencing the referenced table. Additionally, if the inner replica facility is used, the table information in the RDAREA is set to check pending status for all generations.
  9. When the partitioning storage condition of a table that is in check pending status is modified, the following rules apply to releasing the check pending status:
    <<Check pending status specified in the table information of an RDAREA>>
    The check pending status of the modification-target table is released only from the RDAREA from which data is to be deleted.
    <<Check pending status specified in dictionary tables>>
    The table below shows the dictionary tables from which the check pending status is released when USE is specified for the pd_check_pending operand in the system definition. When NOUSE is specified, there are no dictionary tables from which the check pending status is released.

    Table 3-7 Dictionary tables from which check pending status is released when USE is specified for the pd_check_pending operand in the system definition

    Constraint defined in the modification-target tableIs there an RDAREA in which the check pending status is specified for the table information?#Dictionary tables from which the check pending status is released
    Referential constraintNoSQL_REFERENTIAL_CONSTRAINTS tableCHECK_PEND column
    SQL_TABLES tableCHECK_PEND column
    Check constraintNoSQL_CHECKS tableCHECK_PEND2 column
    SQL_TABLES tableCHECK_PEND2 column
    Referential constraint and check constraintReferential constraint: No
    Check constraint: No
    SQL_REFERENTIAL_CONSTRAINTS tableCHECK_PEND column
    SQL_CHECKS tableCHECK_PEND2 column
    SQL_TABLES tableCHECK_PEND column, CHECK_PEND2 column
    Referential constraint: No
    Check constraint: Yes
    SQL_REFERENTIAL_CONSTRAINTS tableCHECK_PEND column
    SQL_TABLES tableCHECK_PEND column
    Referential constraint: Yes
    Check constraint: No
    SQL_CHECKS tableCHECK_PEND2 column
    SQL_TABLES tableCHECK_PEND2 column
    No constraint definedNo[Figure][Figure]
    Legend:
    [Figure]: Not applicable
    # When the inner replica facility is used, includes the table RDAREAs of the modification-target table in all generations.
  10. The data to be modified is deleted according to the scope described below (when the WITHOUT PURGE option is specified, the scope in which the data subject to modification is deleted may vary; for details, see WITHOUT PURGE option):
Row-partitioned tables with a boundary value specification:
[Figure]Table, index, primary key, cluster key, and LOB column data of the RDAREA identified by the boundary value specified in the pre-modification boundary value list
[Figure]Data of the boundary value other than the modification target when the RDAREA identified by the boundary value specified in the pre-modification boundary value list is the same as the RDAREA that stores the data of the boundary value other than the modification target
[Figure]When the inner replica facility is used, table, index, primary key, cluster key, and LOB column data of all generations of replica RDAREAs of the RDAREA identified by the boundary value specified in the pre-modification boundary value list
Row-patterned tables with a storage condition specification:
[Figure]Table, index, primary key, cluster key, and LOB column data of the RDAREA specified in the pre-modification RDAREA information list
[Figure]When the inner replica facility is used, table, index, primary key, cluster key, and LOB column data of all generations of replica RDAREAs of the RDAREA identified in the pre-modification RDAREA information list
  1. For a row-partitioned table with a boundary value specification, the table below describes the conditions applicable when the RDAREAs are duplicated (RDAREAs storing non-adjacent boundary values are the same RDAREAs) or consecutive (RDAREAs storing adjacent boundary values are the same RDAREAs). For details, see the manual HiRDB Version 8 System Operation Guide.
    ItemRow-partitioned table with a boundary value specification
    Modified RDAREA specified in ALTER TABLE*DuplicateDuplication acceptable.
    ContiguousContiguous allocation should be avoided.
    RDAREA of an entire table of the results of modification of a partitioning storage conditionDuplicateDuplication acceptable.
    ContiguousDuring partitioning, the system consolidates contiguous RDAREAs into one area.
    Cannot be specified for combining.
    * The RDAREA specified in the post-change boundary value partition specification (8-3).
  2. A specification is not allowed if it results in the following condition of the table storage RDAREA as a result of the combining:
    [Figure]The number of table partitions is 1.
  3. A shared RDAREA cannot be specified for a modified RDAREA.
  4. If a function, procedure, or trigger is defined for the table in which partitioning storage conditions are to be modified, the function, procedure, or trigger must be disabled by specifying WITH PROGRAM.
When modifying a partitioning storage condition, observe the following points:
Notes on modification
  1. If a function, procedure, or trigger is defined for a table in which partitioning storage conditions are to be modified, and if the function, procedure, or trigger is disabled by specifying WITH PROGRAM, the definition-nullified function, procedure, or trigger must be recreated by using ALTER ROUTINE. (A procedure can be recreated using ALTER PROCEDURE, and a trigger, by ALTER TRIGGER).
  2. If the RDAREA identified with the boundary value to be modified is the duplicate of an RDAREA storing boundary value data that is not be to be modified, the system, which deletes all data in the table in the RDAREA to be modified, also deletes boundary value data that is not being modified.
  3. If data in the table to be modified is to be re-stored in the modified table, or if a data backup is to be obtained for error recovery, operations are required such as unloading the pre-modification data by using the database reorganization utility (pdrorg) and loading it on the modified table, or creating a backup of the RDAREA using the database copy utility (pdcopy). For details, see Changing a table's partitioning storage condition in the manual HiRDB Version 8 System Operation Guide.
  4. If the table being modified is a referenced table and the data in the table is to be deleted, compatibility between the referencing and referenced tables may be lost. In this case, modify the partitioning storage condition for the tables, re-store data in the modified table, and then verify the compatibility between the referencing and referenced tables. For details about how to store the data in the modified table, see 3. above; for the verification method, see Changing a table's partitioning storage condition in the manual HiRDB Version 8 System Operation Guide.
  5. If the inner replica facility is used, compatibility between the referencing and referenced tables may be lost unless the RDAREAs that store the referencing and referenced tables all have the same generation number. If compatibility between referencing and referenced tables is lost, operations such as restoring data from a backup RDAREA are required. For the operation method, see Changing the partitioning storage condition for a table in the manual HiRDB Version 8 System Operation Guide.
  6. When USE is specified in the pd_check_pending operand in the system definition, and the table referencing the table whose partitioning storage condition is to be changed is being used by another user, the referencing is set to check pending status once the transaction terminates.
8-1) row-partitioned-table-change-specification::=
{[PARTITIONED] pre-change-boundary-value-list INTO post-change-boundary-value-partition-specification
|PARTITIONED CONDITION pre-change-RDAREA-information-list
INTO post-change-storage-condition-partition-specification}
Specify this operand when modifying the partitioning storage condition of a row-partitioned table with a boundary value or storage condition specification.
PARTITIONED
Specify this operand when modifying the partitioning storage condition for a row-partitioned table with a boundary value specification.
The following rules apply to row-partitioned table modification specifications:
Partitioning
  1. A single SQL statement can be used to partition a boundary value and an RDAREA associated with the boundary value.
  2. Boundary values specified in a pre-modification boundary value list are partitioned according to the row-partitioned table modification specification.
Combining
  1. A single SQL statement can be used to combine consecutive, multiple boundary values and the RDAREAs associated with the boundary values.
  2. The specification combines the boundary values specified in a pre-modification boundary value list into the largest boundary value specified in the pre-modification boundary value list and into the RDAREA specified in the post-change boundary value partitioning specification.
PARTITIONED CONDITION
Specify this operand when modifying the partitioning storage condition for a row-partitioned table with a storage condition specification. The following rules apply to row-partitioned table modification specifications:
Partitioning
  1. A single SQL statement can be used to partition a single RDAREA that satisfies the storage condition.
  2. The RDAREA specified in the pre-change RDAREA information list is partitioned according to the post-change storage condition partitioning specification.
Combining
  1. A single SQL statement can be used to combine multiple RDAREAs that satisfy the storage condition.
  2. The RDAREAs specified in the pre-change RDAREA information list are combined into the RDAREA specified in the post-change storage condition partitioning specification.
8-2) pre-change-boundary-value-list::= boundary-value-list
boundary-value-list::= ((boundary-value | MAX) [, (boundary-value | MAX)]...)
Specifies a pre-modification boundary value, and identifies the RDAREA to be modified based on the specified boundary value.
The following rules apply to row-partitioned table modification specifications:
Partitioning
  1. One boundary value or one instance of MAX can be specified.
  2. Boundary values not defined in a table cannot be specified.
  3. For a partitioning key value greater than the maximum boundary value specified in the table, specify MAX instead of a boundary value.
Combining
  1. The number of boundary values and instances of MAX that can be specified is 2 to 16.
  2. Only one instance of MAX can be specified.
  3. Boundary values not defined in a table cannot be specified.
  4. For a partitioning key value greater than the maximum boundary value specified in the table, specify MAX instead of a boundary value.
  5. Consecutive boundary values should be specified in ascending order.
  6. A given boundary value cannot be specified multiple times.
boundary-value
Specifies the boundary value that is defined for the table before the table is modified. This operand is specified to identify the boundary value and the RDAREA to be modified.
MAX
Specify this option when a range greater than the maximum boundary value defined in the pre-modification table is to be modified.
8-3) post-change-boundary-value-partitioning-specification::= {table-storage-RDAREA-name
| (table-storage-RDAREA-name)
| ([(table-storage-RDAREA-name) boundary-value,]
...(table-storage-RDAREA-name))}
This operand changes the boundary value and the RDAREA to be modified that were specified in the pre-modification boundary value list (8-2) to a specified boundary value and RDAREA. Any data that has the specified boundary value as a partitioning key is stored in the RDAREA specified immediately before this specification.
Partitioning
  1. The number of table storage RDAREA names that can be specified is 2 to 16.
  2. A boundary value cannot be specified for the last RDAREA.
  3. The number of boundary values that can be specified is 1 to 15.
  4. In boundary-value, specify a literal.
  5. Boundary values should be specified in ascending order.
  6. For an RDAREA that stores the last range in which the boundary value to be modified is partitioned, as specified in the pre-modification boundary value list, specify an RDAREA name in the format in which a boundary value is not specified after the RDAREA.
  7. Boundary values greater than the range to be modified, as specified in the pre-modification boundary value list, cannot be specified.
  8. The same RDAREA name can be specified multiple times in table-storage-RDAREA-name. However, identical RDAREA names cannot be specified consecutively.
  9. For values that can be specified in boundary-value, see CREATE TABLE (Define table).
Combining
  1. Only one table storage RDAREA name can be specified.
  2. Boundary values cannot be specified.
  3. A specification cannot be made such that there is only one storage RDAREA in a modified table.
Notes
If, as a result of partitioning, the RDAREAs that store adjacent boundary values are the same RDAREA, the system consolidates the boundary values into a maximum boundary value.
table-storage-RDAREA-name
Specifies the RDAREA that stores data after modification.
boundary-value
During partitioning, specifies a modified boundary value; this operand cannot be specified during combining.
8-4) pre-change-RDAREA-information-list::=
{table-storage-RDAREA-name
|(table-storage-RDAREA-name)
|((table-storage-RDAREA-name)
[,(table-storage-RDAREA-name)]...[,OTHERS])
|OTHERS}
Specifies the name of the pre-change table storage RDAREA. The specified table storage RDAREA name is used to specify the storage condition for the change target.
The following rules apply to the pre-change RDAREA information list:
Partitioning
  1. You can specify only a single table storage RDAREA name or OTHERS.
  2. You cannot specify a table storage RDAREA name not specified in the table.
  3. You cannot specify a table storage RDAREA name for which only a single literal is specified for the storage condition. However, you can specify a table storage RDAREA name for which no storage condition is specified.
  4. If you specify a table storage RDAREA name for which a storage condition is specified, you cannot partition the storage condition in the RDAREA into other RDAREAs and add a new storage condition.
  5. If a table storage RDAREA name for which no storage condition is specified is specified, you can add a new storage condition.
  6. To add a storage condition if the table whose definition is to be changed does not include a table storage RDAREA for which no storage condition is specified, specify OTHERS in place of the table storage RDAREA name.
  7. If a table storage RDAREA for which no storage condition is specified does exist, you cannot specify OTHERS.
Combining
  1. You can specify 2 to 16 table storage RDAREA names or OTHERS.
  2. You can specify OTHERS only once.
  3. You cannot specify a table storage RDAREA name not specified in the table.
  4. The same table storage RDAREA name cannot be specified more than once.
  5. If you make a specification containing a table storage RDAREA name for which no storage condition is specified, you can delete the storage condition.
  6. To delete a storage condition if there is no table storage RDAREA name for which no storage condition is specified, specify OTHERS in place of the table storage RDAREA name.
  7. If the table does include a table storage RDAREA for which no storage condition is specified, you cannot specify OTHERS.
table-storage-RDAREA-name
Specifies the table storage RDAREA name specified in the pre-change table. You specify this name to specify the storage condition to be changed.
OTHERS
Specify this option to add or delete a storage condition when a table storage RDAREA for which no storage condition is specified does not exist in the table to be changed.
8-5) pre-change-RDAREA-information-list::= {table-storage-RDAREA-name

      |(table-storage-RDAREA-name)

|((table-storage-RDAREA-name)storage-condition
{,(table-storage-RDAREA-name)storage-condition
[[,(table-storage-RDAREA-name)storage-condition
...]
[{,(table-storage-RDAREA-name)
|,OTHERS}])
|,(table-storage-RDAREA-name))
|,OTHERS)}
|OTHERS}
storage-condition::=column-name={literal|(literal[,literal...])}
Changes the change-target RDAREAs specified in the pre-change RDAREA information list to the specified storage conditions and RDAREAs.
The following rules apply to post-change storage condition partitioning specifications:
Partitioning
  1. You can specify 2 to 16 table storage RDAREA names or OTHERS.
  2. You can specify OTHERS only once.
  3. You can specify only one table storage RDAREA name for which no storage condition is specified.
When an RDAREA for which a storage condition is specified is specified in the pre-change RDAREA list
  1. You cannot specify an RDAREA for which a storage condition is not specified or OTHERS.
When an RDAREA for which no storage condition is specified is specified in the pre-change RDAREA list
  1. Include the name of the RDAREA for which no storage condition is specified or OTHERS in the specification.
  2. When deleting an RDAREA for which no storage condition is specified, specify OTHERS.
When OTHERS is specified in the pre-change RDAREA list
  1. Include the name of the RDAREA for which no storage condition is specified or OTHERS in the specification.
  2. When adding a storage condition, specify OTHERS.
Combining
  1. You can specify only a single table storage RDAREA name, or OTHERS.
  2. You cannot specify a storage condition.
  3. When deleting a storage condition, specify OTHERS.
  4. You cannot make a specification that would eliminate all storage conditions from the combined table.
When only an RDAREA name for which a storage condition is specified is specified in the pre-change RDAREA list
  1. You cannot specify an RDAREA name for which a storage condition is not specified.
When an RDAREA name for which a storage condition is not specified is also specified in the pre-change RDAREA list
  1. When deleting an RDAREA for which no storage condition is specified, specify OTHERS.
When OTHERS is also specified in the pre-change RDAREA list
  1. You can add an RDAREA for which no storage condition is specified.
table-storage-RDAREA-name
Specifies the name of the RDAREA that stores data after the change.
storage-condition
Specifies the post-change storage condition during partitioning. This option cannot be specified during combining.
For the column name, specify the column name specified for the partitioning key.
For the storage condition, specify a literal. For details about the values that can be specified as a storage condition, see CREATE TABLE (Define table) in this chapter.
When a table storage RDAREA name for which a storage condition is specified is specified in the pre-change RDAREA information list
  1. Specify all storage conditions specified for the change-target table storage RDAREA.
  2. You cannot specify a storage condition that does not exist.
  3. You cannot specify a storage condition in duplicate.
When a table storage RDAREA name for which no storage condition is specified is specified or OTHERS in the pre-change RDAREA information list
  1. Specify a storage condition that does not exist in the table definition.
  2. You cannot specify a storage condition that exists in the table definition.
OTHERS
Specify this option when there is no need for an RDAREA for storing data that does not satisfy the storage condition defined in the table as a result of the table definition change.
8-6) index-storage-RDAREA-change-specification::=
FOR INDEX index-name
INTO post-change-index-storage-RDAREA-name-list
Specify this operand if an index is defined for the table for which partitioning storage conditions are to be modified.
The following rules apply to RDAREAs for index modification specifications:
  1. A modification cannot be specified if it only modifies an RDAREA for index.
  2. Index RDAREAs can be modified if they have the same scope or same storage condition as the boundary value specified in the row-partitioned table modification specification.
  3. All indexes that are defined in the table must be specified.
index-name
If an index is defined on the table for which partitioning storage conditions are to be modified, in this operand specify the identifier for the index that is defined.
post-change-index-storage-RDAREA-name-list::=
{RDAREA-name-for-index
| (RDAREA-name-for-index)
| ((RDAREA-name-for-index)
[, (RDAREA-name-for-index)]...[, OTHERS])
}
| OTHERS}
Specifies the name of the RDAREA that stores an index, primary key, or cluster key.
The following rules apply to modified RDAREA for index name lists:
  1. The number of RDAREAs specified in post-change-index-storage-RDAREA-name-list must be equal to the number of RDAREAs specified in the following location:
  • Post-change boundary value partitioning specification (for a row-partitioned table with a boundary value specification)
  • Post-change storage condition partitioning specification (for a row-partitioned table with a storage condition specification)
  • Matrix-partitioned table storage RDAREA change specification (for a matrix-partitioned table)
  1. If an RDAREA is specified in duplicate in the post-change boundary value partitioning specification, the RDAREAs specified in post-change-index-storage-RDAREA-name-list must also have the same duplication.
  2. Tables, indexes, primary keys, and cluster keys must maintain duplication correspondence with the number of partitions and RDAREAs based on a table. (For example, if the system consolidates boundary values into a single value before and after the modification of a table, and if a specified RDAREA stores data other than the boundary value to be modified, the same RDAREA must be used, and a position that specifies the same RDAREA must similarly be specified in the index, the primary key, and the cluster key.)
  3. If OTHERS is specified in the post-change storage condition specification, OTHERS must also be specified in post-change-index-storage-RDAREA-name-list that corresponds to the post-change storage condition specification.
RDAREA-name-for-index
Specifies the name of the RDAREA in which modified data is to be stored.
OTHERS
Specify this option when OTHERS is specified in the post-change storage condition specification.
OTHERS cannot be specified if OTHERS is not specified in the post-change storage condition specification.
8-7) primary-key-storage-RDAREA-change-specification::=
FOR PRIMARY KEY
INTO post-change-index-storage-RDAREA-name-list
Specify this operand if the primary key is defined for the table for which partitioning storage conditions are to be modified.
The following rules apply to primary key storage RDAREA modification specifications:
  1. Only one primary key storage RDAREA modification specification can be specified.
  2. A modification that only modifies the RDAREA that stores the primary key cannot be specified.
FOR PRIMARY KEY
Specify this option if the primary key is defined for a table.
post-change-index-storage-RDAREA-name-list
Specifies the RDAREA that stores a modified primary key. For details, see post-change-index-storage-RDAREA-name-list in 8-4.
8-8) cluster-key-storage-RDAREA-change-specification::=
FOR [PRIMARY] CLUSTER KEY
INTO post-change-index-storage-RDAREA-name-list
Specify this operand if a cluster key is defined for the table for which partitioning storage conditions are to be modified.
The following rules apply to cluster key storage RDAREA modification specifications:
  1. A modification that only modifies the RDAREA that stores a cluster key cannot be specified.
  2. Only one cluster key storage RDAREA modification specification can be specified.
FOR [PRIMARY] CLUSTER KEY
Specify this option when modifying the RDAREA for index that stores a cluster key is to be modified.
PRIMARY
Specify this option if a cluster key is defined as the primary key.
post-change-index-storage-RDAREA-name-list
Specifies the name of the RDAREA that stores a modified cluster key. For details, see post-change-index-storage-RDAREA-name-list in 8-6.
8-9) LOB-column-storage-RDAREA-change-specification::=
FOR COLUMN column-name
LOB-column-storage-RDAREA-change-list
[, column-name-LOB-column-storage-RDAREA-name-specification]...
Specify this operand if a LOB column is defined for the table for which partitioning storage conditions are to be changed.
The following rules apply to LOB column storage RDAREA modification specifications:
  1. A modification that only modifies a LOB column storage RDAREA cannot be specified.
  2. In column-name, specify a LOB column.
  3. All LOB columns that are defined for the table must be specified.
column-name
Specifies the name of the LOB column defined on the table.
8-10) LOB-column-storage-RDAREA-change-list::=
INTO {LOB-column-storage-RDAREA-name
| (LOB-column-storage-RDAREA-name)
| ((LOB-column-storage-RDAREA-name)
[, (LOB-column-storage-RDAREA-name)]...[, OTHERS])
|OTHERS}
Specifies the name of the user LOB RDAREA that stores LOB column data for a row-partitioned table.
The following rules apply to LOB column storage RDAREAs:
  1. For a column for which the BLOB data type is specified, a LOB column storage RDAREA name must always be specified. A LOB column storage RDAREA name cannot be specified for columns for which a non-BLOB data type is specified.
  2. If an RDAREA is specified in duplicate in the post-change boundary value partitioning specification, the same duplicated RDAREA must also be specified in LOB-column-storage-RDAREA-name.
  3. Tables and LOB columns must maintain duplication correspondence with the number of partitions and RDAREAs based on a table. (For example, if the system consolidates boundary values into a single value before and after the modification of a table, and if a specified RDAREA stores data other than the boundary value to be modified, the same RDAREA must be used, and a position that specifies the same RDAREA must similarly be specified in the LOB column.)
  4. If OTHERS is specified in the post-change storage condition specification, OTHERS must also be specified in LOB-column-storage-RDAREA-change-list that corresponds to the post-change storage condition specification.
LOB-column-storage-RDAREA-name
Specifies the name of the RDAREA in which the LOB column is to be stored.
WITHOUT PURGE
If the data in a post-change table does not fall within the boundary value range of the partitioning storage condition or match the storage condition specification, HiRDB deletes that data during execution of ALTER TABLE. However, if the same RDAREA is specified before and after the change (the change-target RDAREA is specified for the post-change boundary value partitioning specification or for the post-change storage condition partitioning specification), you can specify the WITHOUT PURGE option to prevent HiRDB from deleting the table data from the same RDAREA during execution of ALTER TABLE. Such a specification is useful when the pre-modification table data for the partitioning storage condition is to be used after modification so that table data unloading/loading operations can be minimized.
However, the table data in the RDAREA that is not used after modification, even with WITHOUT PURGE specification, loses association with the table to be modified, and can no longer maintain compatibility. In this case, the system deletes the data.
The WITHOUT PURGE option can be specified if the following conditions are satisfied; in other cases, a WITHOUT PURGE specification can cause an error (for details, see Changing the partitioning storage condition for a table in the manual HiRDB Version 8 System Operation Guide):
Row-partitioned table with a boundary value specification
When the RDAREA to be modified is contained in the modified RDAREA.
Row-partitioned table with a storage condition specification
When the change-target RDAREA is included in the post-change RDAREA
Notes
When specifying the WITHOUT PURGE option, observe the following points:
  1. When partitioning a partitioning storage condition, you must verify that all data in the change-target RDAREA matches the data in the boundary value range to be allocated after partitioning, or the storage condition to be allocated after partitioning. For details, see Changing the partitioning storage condition for a table in the manual HiRDB Version 8 System Operation Guide.
  2. When combining partitioning storage conditions, if the data in the RDAREA to be deleted must be reregistered, the data must be unloaded before the partitioning storage condition is modified, and it must be loaded to the table that is the result of the modification of the partitioning storage conditions. However, if the data that is not deleted due to the specification of WITHOUT PURGE is loaded after the partitioning storage condition is modified, duplicate data registration results. To guard against this possibility, RDAREAs should be unloaded and loaded with care. For details, see Changing the partitioning storage condition for a table in the manual HiRDB Version 8 System Operation Guide.
  3. For a row-partitioned table with a storage condition specification, you cannot specify WITHOUT PURGE if OTHERS is specified in the pre-change RDAREA information list during partitioning of the partitioning storage condition.
WITH PROGRAM
Specify this operand when the SQL object in which functions, procedures, and triggers that use the table being modified are in effect is to be nullified.

Common rules

  1. Changing a column in a base table using the CHANGE clause also changes the columns in the view table.
  2. ALTER TABLE cannot be specified for a column for which a storage condition is specified.
  3. If WITH PROGRAM is omitted, the table definition can be changed if there is a table to be defined or an SQL object with an effective function, procedure or trigger that uses the view table defined by referencing that table.
  4. A non-repetition column cannot be changed into a repetition column. Conversely, a repetition column cannot be changed into a non-repetition column.
  5. When adding either a BLOB column or an abstract data-type column including the BLOB attribute to a partitioned table with a boundary value specification or a matrix-partitioned table, either a LOB column storage RDAREA name or a LOB attribute storage RDAREA name must be specified, respectively, so that the resulting table corresponds to the table storage RDAREA name that was specified in the table definition. Therefore, if there is an RDAREA name duplicate for the table storage RDAREA name that is specified in the table definition, the LOB column storage RDAREA name or the LOB attribute storage RDAREA name must also be specified in duplicate.
  6. The table storage RDAREA, the LOB column storage RDAREA, the LOB attribute storage RDAREA, and the RDAREA for index that are specified in ADD COLUMN, ADD RDAREA, or CHANGE RDAREA should be created in advance using the database initialization utility, or must be added using the database structure modification utility.
  7. LOB column storage RDAREAs, LOB attribute storage RDAREAs, and index storage RDAREAs using a user LOB RDAREA cannot be specified in duplicate. For each, a different user LOB RDAREA must be specified.
  8. User LOB RDAREAs that are assigned to other BLOB columns, the BLOB attribute, or an index cannot be specified.
  9. If the SQL object is nullified during the execution of this command, ALTER TABLE cannot be executed from within a Java procedure.
  10. RDAREAs using the inner replica facility and those not using the facility cannot be specified on a mixed basis in a table storage RDAREA, LOB column storage RDAREA, LOB attribute storage RDAREA, or RDAREA for index that is specified in ADD COLUMN, ADD RDAREA, or CHANGE RDAREA. When specifying an RDAREA to which the inner replica facility is applied, specify the name of the original RDAREA.
  11. For execution conditions for ALTER TABLE using the inner replica facility, see the manual HiRDB Staticizer Option Version 7.
  12. ALTER TABLE cannot be executed for an audit trail table.
  13. A table definition cannot be modified using the DROP clause on referenced and referencing tables.
  14. Referenced and referencing tables cannot be renamed using the RENAME clause.
  15. Modifying the definition of the primary key constituent column or foreign key constituent column for a referenced table is subject to the following constraints:
    • The data type or data length cannot be modified using the CHANGE clause.
    • Columns cannot be renamed using the RENAME clause.
  16. The definition of a table for which a check constraint is defined cannot be modified using the DROP clause.
  17. Tables for which a check constraint is defined cannot be renamed using the RENAME clause.
  18. Any change of definition of columns for which a check constraint is defined is subject to the following constraints:
    • The data type or data length cannot be modified using the CHANGE clause.
    • SPLIT cannot be modified using the CHANGE clause.
    • Default values cannot be assigned or released using the CHANGE clause.
    • The WITH DEFAULT option cannot be set using the CHANGE clause.
    • Columns cannot be renamed using the RENAME clause.

Notes

  1. If data is stored in the base table for which FIX was specified during the table definition, the following items can be specified in ALTER TABLE:
    • Adding a table storage RDAREA
    • Changing hash functions
    • Changing the column attribute from CHAR to MCHAR
    • Renaming tables and columns
    • Changing units of minimum locking resources
    • Changing the partitioning storage condition
    • Assigning an updatable column attribute (UPDATE specification only)
    • Changing a table to a falsification-prevented table
  2. WITH DEFAULT cannot add columns to a table already containing data.
  3. When passing or receiving date data in CHAR(10) using the row-unit interface, specify a column in CHAR(10) instead of a date data type.
  4. When passing or receiving time data in CHAR(8) using the row-unit interface, specify a column in CHAR(8) instead of a time data type.
  5. When passing or receiving time stamp data in 19, 22, 24, or 26-byte CHAR using the row-unit interface, specify a column in 19, 22, 24, or 26-byte CHAR instead of a time stamp data type.
  6. ALTER TABLE cannot be specified from an X-Open compliant UAP running under OLTP.
  7. If a table or column is renamed, when using a utility or an operating command, the new name should be specified.
  8. If a table or column is renamed, any of the following files that were created before the renaming action cannot be used:
    • Unload data files for the database reorganization utility
    • Index information files for the database load utility or the database reorganization utility
    • Export files for the dictionary import/export utility
  9. When changing a hash function or adding a table storage RDAREA, data need not be reloaded. However, because data is not stored in the added table storage RDAREA until the INSERT statement is executed, data is not stored when the table storage RDAREA is added.
  10. If an SQL object for which functions, procedures, or triggers are in effect is nullified by specifying WITH PROGRAM, any rows associated with the nullified functions, procedures, or triggers in the SQL_ROUTINE_RESOURCES dictionary table are deleted.
  11. Before executing the SQL object associated with the function, procedure, or trigger that was nullified by specifying WITH PROGRAM, you need to recreate the function, procedure, or trigger by executing ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER.
  12. Column data suppression cannot be specified for columns that are added by using the ADD option of ALTER.
  13. CHANGE LOCK PAGE cannot be specified for tables for which the WITHOUT ROLLBACK option is specified.
  14. Columns can be added to a table for which a trigger is defined. If a trigger event column is omitted from the trigger, any added columns are also subject to the execution of the trigger. If a trigger event column is specified, those columns are not subject to the trigger execution. Adding a column does not nullify the SQL object.
  15. Trigger event columns can be changed in terms of definition, or they can be deleted. Deleting all trigger event columns associated with a trigger causes the trigger to be deleted. Adding a column with the same name as a deleted column does not make the new column a target of the execution of the trigger. Changing the definition of a trigger event column or deleting the column does not nullify the SQL object for the trigger.
  16. If, after deleting a trigger event column, a different operation is used to nullify the SQL object for the trigger, one of the following operations must be performed before the nullified SQL object can be executed:
    • Reset the column definition, and execute either ALTER TRIGGER or ALTER ROUTINE to recreate the SQL object for the trigger.
    • Delete the nullified trigger by using DROP TRIGGER, and then redefine the trigger by using CREATE TRIGGER so that the deleted column is not reused. If triggers satisfying all of the following conditions exist, use DROP TRIGGER to delete them all, and redefine the triggers using CREATE TRIGGER in the order in which they were defined so that there is no change in sequence of execution of trigger actions.
      Conditions:
      [Figure]The defined trigger is later than the nullified trigger.
      [Figure]The nullified trigger is the same as the defined table.
      [Figure]The nullified trigger is the same as the trigger event (INSERT, UPDATE, or DELETE) (for UPDATE, the nullified trigger is considered to be the same as the trigger event regardless of whether a trigger event column is specified or the contents of the specification).
      [Figure]The nullified trigger has the same trigger action timing (BEFORE or AFTER) as the trigger event.
      [Figure]The nullified trigger has the same trigger action units (units of rows or statements) as the trigger event.
  17. Adding a NOT NULL column to the table being referenced from a trigger SQL statement by specifying WITH PROGRAM nullifies the SQL object associated with the trigger. Before executing the nullified SQL object, you need to recreate the trigger SQL object by executing either ALTER TRIGGER or ALTER ROUTINE.
  18. Any of the following operations performed by specifying WITH PROGRAM nullifies the SQL object for the trigger:
    • Redefining a column deleting a column, renaming a column or renaming the table with respect to the table that is referenced from the trigger SQL statement.
    • Redefining or deleting the column being referenced from a trigger SQL statement by using an old or new-values correlation name
    To execute the SQL object for the nullified trigger, you need to perform one of the following operations:
    • Reset the column definition, column name, or table name, and then execute either ALTER TRIGGER or ALTER ROUTINE to recreate the SQL object for the trigger.
    • Use CREATE TRIGGER to redefine the trigger so that, after DROP TRIGGER is used to delete the nullified trigger, the column on which column definition change or column deletion was performed is not used, or so that the old column name or old table name is not used. If there are triggers that satisfy all of the following conditions, delete them all by using DROP TRIGGER and redefine them by using CREATE TRIGGER in the order in which they were originally defined, so that the order in which trigger actions are executed does not change.
      Conditions:
      [Figure]The defined trigger is later than the nullified trigger.
      [Figure]The nullified trigger is the same as the defined table.
      [Figure]The nullified trigger is the same as the trigger event (INSERT, UPDATE, or DELETE) (for UPDATE, the nullified trigger is considered to be the same as the trigger event regardless of whether a trigger event column is specified or the contents of the specification).
      [Figure]The nullified trigger has the same trigger action timing (BEFORE or AFTER) as the trigger event.
      [Figure]The nullified trigger has the same trigger action units (units of rows or statements) as the trigger event.
  19. Changing its definition or deleting the column being referenced by using an old or new-values correlation name in a trigger action condition by specifying WITH PROGRAM causes the SQL object for the trigger to be nullified. Similarly, pre-processing a trigger-inducing SQL statement also causes an error. To execute the SQL object for a nullified trigger or perform pre-processing on a trigger-inducing SQL statement, you need to perform one of the following operations:
    • Reset the column definition, and execute either ALTER TRIGGER or ALTER ROUTINE to recreate the SQL object.
    • Use CREATE TRIGGER to redefine the trigger so that, after DROP TRIGGER is used to delete the nullified trigger, the column on which column definition change or column deletion was performed is not used. If there are triggers that satisfy all of the following conditions, delete them all by using DROP TRIGGER and redefine them by using CREATE TRIGGER in the order in which they were originally defined, so that the order in which trigger actions are executed does not change.
      Conditions:
      [Figure]The defined trigger is later than the nullified trigger.
      [Figure]The nullified trigger is the same as the defined table.
      [Figure]The nullified trigger is the same as the trigger event (INSERT, UPDATE, or DELETE) (for UPDATE, the nullified trigger is considered to be the same as the trigger event regardless of whether a trigger event column is specified or the contents of the specification).
      [Figure]The nullified trigger has the same trigger action timing (BEFORE or AFTER) as the trigger event.
      [Figure]The nullified trigger has the same trigger action units (units of rows or statements) as the trigger event.
  20. The table on which the trigger is defined or any of the following columns cannot be renamed:
    • Trigger event columns
    • Columns that are referenced in a trigger action condition, using an old or new-values correlation name
    • Columns that are referenced in a trigger SQL statement, using an old or new-values correlation name

Examples

  1. Add a warehouse address (PADRS) to an inventory table (STOCK):

    ALTER TABLE STOCK
       ADD PADRS VARCHAR(40)

  2. Add a warehouse address (PADRS) in a NOT NULL constrained column with a predefined value to an inventory table (STOCK):

    ALTER TABLE STOCK
       ADD PADRS VARCHAR(40)
       NOT NULL WITH DEFAULT

  3. In the inventory table (STOCK), change the maximum length of the column of a variable-length data type warehouse address (PADRS) to 60:

    ALTER TABLE STOCK
       CHANGE PADRS VARCHAR(60)

  4. In the inventory table (STOCK), change the attribute of the cluster key that is assigned to the product code (PCODE) column from non-uniqueness-constrained to uniqueness-constrained:

    ALTER TABLE STOCK
       CHANGE CLUSTER KEY UNIQUE

  5. From the inventory table (STOCK), delete the warehouse address (PADRS) column:

    ALTER TABLE STOCK
       DROP PADRS

  6. Nullify the effective object of a procedure on the warehouse address (PADRS) in the inventory table (STOCK), and delete the warehouse address (PADRS) column:

    ALTER TABLE STOCK
       DROP PADRS WITH PROGRAM

  7. Add a warehouse map (PMAP) to the inventory table (STOCK):

    ALTER TABLE STOCK
       ADD PMAP BLOB(1M) IN (RMAPLOB1)

  8. Add an RDAREA in which a new hash-partitioned inventory table (NSTOCK) is to be stored. Also, add an index (IPCODE) and an RDAREA in which a column of a BLOB-type warehouse map (PMAP) is to be stored:

    ALTER TABLE NSTOCK
       ADD RDAREA RDA3
         FOR COLUMN PMAP IN (RMAPLOB3)
         FOR INDEX IPCODE IN (RDA4)

  9. The following is an example of modifying a boundary value for a row- partitioned table with a boundary value specification:
    Example 1: Partitioning and combining boundary values
    Before

    CREATE FIX TABLE "T1"("C1" INT,"C2" INT) PARTITIONED BY "C1"
    IN(("TA1")100,("TA2")200,("TA3")400,("TA4")500,("TA5")600,("TA6"))
    CREATE INDEX "I1" ON "T1"("C1")
    IN(("IA1"),("IA2"),("IA3"),("IA4"),("IA5"),("IA6"))

    [Figure]

    1. Combine (Condition 1 to condition 2)

    ALTER TABLE "T1" CHANGE RDAREA
     ((100),(200)) INTO "TA11"
     FOR INDEX "I1" INTO "IA11"

    [Figure]

    2. Partition (Condition 2 to condition 3)

    ALTER TABLE "T1" CHANGE RDAREA
     ((400)) INTO (("TA12")300,("TA13"))
     FOR INDEX "I1" INTO (("IA12"),("IA13"))

    [Figure]

    3. Combine (Condition 3 to condition 4)

    ALTER TABLE "T1" CHANGE RDAREA
     ((600),(MAX)) INTO "TA11"
     FOR INDEX "I1" INTO "IA11"

    [Figure]

    Example 2: The system combines adjacent boundary values (a special case of partitioning)
    Before

    CREATE FIX TABLE "T1"("C1" INT,"C2" INT) PARTITIONED BY "C1"
     IN(("TA1")100,("TA2")200,("TA3")400,
         ("TA4")500,("TA5")600,("TA6"))
    CREATE INDEX "I1" ON "T1"("C1")
     IN(("IA1"),("IA2"),("IA3"),
        ("IA4"),("IA5"),("IA6"))

    [Figure]

    Changing boundary values

    ALTER TABLE "T1" CHANGE RDAREA
     ((400)) INTO (("TA3")300,("TA4"))
    FOR INDEX "I1" INTO (("IA3"),("IA4"))

    [Figure]

  10. The following is an example of modifying an RDAREA of a row-partitioned table with a storage condition specification:
    Before change <<State 1>>

    CREATE FIX TABLE "T1"("C1" CHAR(3),"C2" INT)
     IN(("TA1")"C1"='001',("TA2")"C1"='002',
     ("TA3")"C1"='003',("TA4")"C1"=('004','005'),("TA5"))
    CREATE INDEX "I1" ON "T1"("C1")
     IN(("IA1"),("IA2"),("IA3"),("IA4"),("IA5"))

    [Figure]

    Example 1: Adding a storage condition
    1. Partitioning <<State 1 [Figure]State 2>>

    ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
     (("TA5")) INTO (("TA6")"C1"='006',("TA5"))
      FOR INDEX "I1" INTO (("IA6"),("IA5"))

    [Figure]

    Example 2: Deleting a storage condition
    2. Combining <<State 1 [Figure]State 3>>

    ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
     (("TA1"),("TA5")) INTO "TA5"
      FOR INDEX "I1" INTO "IA5"

    [Figure]

    Example 3: Partitioning an RDAREA
    3. Partitioning <<State 1 [Figure]State 4>>

    ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
     (("TA4")) INTO (("TA4")"C1"='004',("TA7")"C1"='005')
      FOR INDEX "I1" INTO (("IA4"),("IA7"))

    [Figure]

    Example 4: Combining RDAREAs
    4. Combining <<State 1 [Figure]State 5>>

    ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
     (("TA1"),("TA2")) INTO "TA2"
      FOR INDEX "I1" INTO "IA2"

    [Figure]

  11. Change a non-falsification-prevented order table (ORDER) to a falsification-prevented table with the following conditions:
    Table definition conditions
    • In advance, define an OINSDATE column as an insert history maintenance column.
    • Define a UACOL column as an updatable column after the table is changed to a falsification-prevented table.

     CREATE TABLE ORDER
         (FNO CHAR(6),TCODE CHAR(5),PCODE CHAR(4),
          UACOL CHAR(60) UPDATE,
          OQTY INTEGER,ODATE DATE,OTIME TIME,
          OINSDATE DATE NOT NULL WITH DEFAULT SYSTEM GENERATED)

    Condition for changing the table definition
    • Set a deletion-prevented duration of 10 years.

     ALTER TABLE ORDER
         CHANGE INSERT ONLY WHILE 10 YEARS BY OINSDATE