Hitachi

Hitachi Advanced Database SQL Reference


3.1.1 Specification format and rules for the ALTER TABLE statement

You can use the ALTER TABLE statement to perform the following operations:

Note that you cannot perform two or more operations at the same time by a single execution of the ALTER TABLE statement.

Organization of this subsection

(1) Specification format and description

(a) Adding a column to a base table

Specification format

ALTER-TABLE-statement ::= ALTER TABLE table-name
                      ADD COLUMN column-definition
 
  column-definition ::= column-name data-type [NOT NULL] [BRANCH {YES | NO | AUTO}] [compression-type-specification]
[Figure] table-name

Specify the name of the base table to which to add a column. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

Note that the following tables cannot be specified:

  • Viewed tables

  • Dictionary tables

  • System tables

ADD COLUMN column-definition
column-definition ::= column-name data-type [NOT NULL] [BRANCH {YES | NO | AUTO}] [compression-type-specification]

Specify the column definition of the column to be added.

The following conditions govern the specifications for adding a column:

  • Only one column can be added at a time. The column is added as the last column of the base table.

  • Null values are stored in the added column.

  • You cannot add a column to a base table under the following circumstances:

    • The number of columns in the target base table has already reached the maximum (1,000).

    • The target base table is a FIX table to which row storage segments have been allocated.

    • The target base table was created by running the CREATE TABLE statement with BRANCH ALL specified, and row storage segments have been allocated to the target base table.

    For details about the status in which row storage segments have been allocated, see Notes on defining B-tree indexes (unfinished status of B-tree indexes) in the HADB Setup and Operation Guide.

Note

You cannot specify a DEFAULT clause in an ALTER TABLE statement.

column-name:

Specify the name of the column to be added.

You cannot specify a column name that is already used in the table.

Do not specify a character string in the EXPnnnn_NO_NAME format as a column name. Such a column name might duplicate a derived column name that is automatically set by HADB. In this format, nnnn is an unsigned integer in the range from 0000 to 9999.

data-type:

Specify the data type of the column to be added. The data types that can be specified are shown in the following table:

Table 3‒1: Data types that can be specified (ALTER TABLE statement)

No.

Data type

Specification format

1

INTEGER

INT or INTEGER

2

SMALLINT

SMALLINT

3

DECIMAL

DEC[(m[,n])] or DECIMAL[(m[,n])]

m: Precision (total number of digits)

n: Scaling (number of decimal places)

If m is omitted, 38 is assumed, and if n is omitted, 0 is assumed.

4

DOUBLE PRECISION

DOUBLE or DOUBLE PRECISION

5

CHARACTER

CHAR(n) or CHARACTER(n)

n: Length of the character string (number of bytes)

If CHAR or CHARACTER is specified without a length, the length of the character string is assumed to be 1.

6

VARCHAR#1, #2

VARCHAR(n)

n: Maximum length of the character string (number of bytes)

7

DATE

DATE

8

TIME

TIME(p) or TIME

p: Fractional seconds precision (number of digits to the right of the decimal point)

You can specify a value of 0, 3, 6, 9, or 12 for p. If TIME is specified, p is assumed to be 0.

9

TIMESTAMP

TIMESTAMP(p) or TIMESTAMP

p: Fractional seconds precision (number of digits to the right of the decimal point)

You can specify a value of 0, 3, 6, 9, or 12 for p. If TIMESTAMP is specified, p is assumed to be 0.

10

BINARY

BINARY(n)

n: Length of the binary data (number of bytes)

If BINARY is specified without a length, the length of the binary data is assumed to be 1.

11

VARBINARY#1

VARBINARY(n)

n: Maximum length of the binary data (number of bytes)

#1: The VARCHAR and VARBINARY types cannot be specified for columns that are added to a FIX table.

#2: VARCHAR-type data whose data length exceeds 32,000 bytes cannot be specified.

For details about data types, see 6.2 Data types.

NOT NULL:

Specify this to define the NOT NULL constraint (the constraint that does not allow null values) on the column to be added.

Note the following:

  • You cannot specify NOT NULL for a base table to which row storage segments have been allocated. For details about the status in which row storage segments have been allocated, see Notes on defining B-tree indexes (unfinished status of B-tree indexes) in the HADB Setup and Operation Guide.

  • In FIX tables, the NOT NULL constraint is set for all columns. When a column is added to a FIX table, the NOT NULL constraint is considered to be specified even if it is omitted.

BRANCH {YES | NO | AUTO}:

Specifies how to store VARCHAR-type and VARBINARY-type column data.

For details about situations for which it is better to specify YES or NO for BRANCH, see Branch specification for column data of variable-length data types (BRANCH) [Row store table] in the HADB Setup and Operation Guide.

The types of storage methods that can be specified are the same as those that can be specified for a CREATE TABLE statement. In addition, if BRANCH is omitted, this statement operates in the same way as when BRANCH is omitted from the CREATE TABLE statement. For details about BRANCH in the CREATE TABLE statement, see (d) column-definition [Common] of (2) Explanation of specification format in 3.7.1 Specification format and rules for the CREATE TABLE statement.

This option cannot be specified for the following tables and columns:

  • Tables for which the BRANCH ALL table option is specified

  • Columns of data types other than VARCHAR and VARBINARY

  • Column store tables to which a column is to be added

compression-type-specification:
compression-type-specification ::= COMPRESSION TYPE {AUTO|NONE|RUNLENGTH|DICTIONARY|DELTA|DELTA_RUNLENGTH}

Specifies the compression type to be used to compress the data of the column to be added (column-data compression type).

The compression types that can be specified are the same as those that can be specified for the CREATE TABLE statement. In addition, if compression-type-specification is omitted, this statement operates in the same way as when compression-type-specification is omitted from the CREATE TABLE statement. For details about compression-type-specification in the CREATE TABLE statement, see (d) column-definition [Common] of (2) Explanation of specification format in 3.7.1 Specification format and rules for the CREATE TABLE statement.

Note that you cannot specify this option if the table to which a column is to be added is a row store table.

(b) Renaming a column in a base table

Specification format

ALTER-TABLE-statement ::= ALTER TABLE table-name
                      RENAME COLUMN [FROM] current-column-name TO new-column-name
table-name

Specify the name of the base table of which to rename a column. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

Note that the following tables cannot be specified:

  • Viewed tables

  • Dictionary tables

  • System tables

RENAME COLUMN [FROM] current-column-name TO new-column-name

Specify the current column name and new column name. The current column name is changed to the new column name.

The following rules apply:

  • An existing column name cannot be specified as the new column name.

  • The same name cannot be specified for both the current and new column names.

  • Do not specify a character string in the EXPnnnn_NO_NAME format as a new column name. Such a column name might duplicate a derived column name that is automatically set by HADB. In this format, nnnn is an unsigned integer in the range from 0000 to 9999.

Important

If a column of a table is renamed, all viewed tables that are dependent on that table are invalidated. For details about how to check the viewed tables that will be invalidated, see Checking dependent viewed tables in the HADB Setup and Operation Guide.

(c) Changing the maximum number of chunks in a multi-chunk table

Specification format

ALTER-TABLE-statement ::= ALTER TABLE table-name
                      CHANGE OPTION CHUNK=maximum-number-of-chunks
table-name

Specify the name of the multi-chunk table for which you want to change the maximum number of chunks. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

CHANGE OPTION CHUNK=maximum-number-of-chunks

~ <unsigned integer> ((2 to 30,000)) (unit: chunks)

Specify a new maximum number of chunks, replacing the maximum number of chunks that was specified using the chunk specification in the CREATE TABLE statement.

Note the following points:

  • This option can be specified only for multi-chunk tables.

  • You cannot specify a value that is less than the number of chunks that have already been created in the table.

(d) Changing a regular multi-chunk table to an archivable multi-chunk table

Specification format

ALTER-TABLE-statement::=LTER TABLE table-name
                      CHANGE OPTION CHUNK chunk-archive-specification
 
  chunk-archive-specification::=ARCHIVABLE
                                RANGECOLUMN=column-name
                               [RANGEINDEXNAME=index-identifier]
                               [IN DB-area-name]
                                ARCHIVEDIR=archive-directory-name
table-name

Specify the name of the regular multi-chunk table that you want to change to an archivable multi-chunk table. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

Note that the following tables cannot be specified:

  • FIX tables

  • Single-chunk tables

  • Column store tables

CHANGE OPTION CHUNK chunk-archive specification
chunk-archive-specification::=ARCHIVABLE
                              RANGECOLUMN=column-name
                             [RANGEINDEXNAME=index-identifier]
                             [IN DB-area-name]
                              ARCHIVEDIR=archive-directory-name

Specify this option if you change a regular multi-chunk table to an archivable multi-chunk table.

RANGECOLUMN=column-name

Specify a column name. The column specified here becomes the archive range column.

The following rules apply:

  • You cannot use columns of the following data types as archive range columns:

    CHARACTER type (only if the defined length is 33 bytes or more)

    VARCHAR type

    BINARY type

    VARBINARY type

  • When you specify a column to be used as an archive range column, make sure that the NOT NULL constraint is specified for that column.

RANGEINDEXNAME=index-identifier

When the ALTER TABLE statement is run, the HADB server automatically defines a range index that uses an archive range column as the indexed column. Specify the index identifier to be assigned to this range index.

Important

If a range index has already been defined for the archive range column, no range index is automatically defined when the ALTER TABLE statement is run. In this case, the already defined range index is used. Therefore, if you specify RANGEINDEXNAME when a range index has already been defined for the archive range column, the ALTER TABLE statement will result in an error.

If RANGEINDEXNAME is not specified, the HADB server determines the index identifier in the following format:

ARCHIVE_RANGE_INDEX_nnnnnnnn

nnnnnnnn is an eight-digit character string that represents the ID of the archivable multi-chunk table in hexadecimal notation.

If the automatically determined index identifier already exists in the same schema, the ALTER TABLE statement will result in an error. Therefore, if you use the CREATE INDEX statement to define an index, we recommend that you do not use a name whose format resembles the preceding format.

Note

The range index that is automatically defined here is subject to the same rules as a range index defined by the CREATE INDEX statement.

IN DB-area-name

Specify the name of the DB area in which to store the range indexes that are automatically defined by the HADB server.

In the following case, omit specifying IN DB-area-name:

  • When a range index has already been defined for the archive range column

In this case, because the HADB server does not automatically define a range index, you do not need to specify IN DB-area-name.

Also, if all of the following conditions are met, a range index automatically defined by the HADB server is stored in the DB area specified for the adb_sql_default_dbarea_shared operand in the server definition:

  • When the IN DB-area-name specification is omitted

  • No range index is defined for the archive range column.

Note that, when both of the preceding two conditions are met, if the adb_sql_default_dbarea_shared operand is not specified in the server definition, the ALTER TABLE statement will result in an error. Also, if the DB area specified for the adb_sql_default_dbarea_shared operand in the server definition does not exist or if a DB area other than the data DB area is specified, the ALTER TABLE statement will result in an error.

ARCHIVEDIR=archive-directory-name

Specify the absolute path name of the archive directory in which to store archive files.

The following rules apply:

  • Specify the archive directory name in the character string literal format. For details about character string literals, see 6.3 Literals.

  • Specify an existent directory for the archive directory. Make sure that read, write, and execution permissions for the HADB administrator are assigned to the directory that you specify.

    Also, make sure that execution permission for the HADB administrator is assigned to all directories that are included in the path of the archive directory.

    (Example) If the archive directory is /HADB/archive:

    For the /HADB/archive directory, read, write, and execution permissions must be set.

    For the / directory and the /HADB directory, the execution permission is necessary.

  • The following directories cannot be used as the archive directory:

    • Server directory

    • Subdirectory of a server directory

    • Directory that contains a server directory

    • DB directory

    • Subdirectory of a DB directory

    • Directory that contains a DB directory

    • Root directory

    The following shows examples of directories that can be and cannot be used as the archive directory when the DB directory is /HADB/db:

    Directory

    Reason

    Example of directory that can be used as the archive directory

    /HADB/archive

    None.

    Example of directory that cannot be used as the archive directory

    /HADB/db

    This directory is the same as the DB directory.

    /HADB/db/archive

    This directory is a subdirectory of the DB directory.

    /HADB

    This directory contains the DB directory.

  • Do not specify (as the archive directory) a directory in which installation data was stored when the HADB server was installed.

  • The name of the archive directory must be 1 to 400 bytes long except the heading and trailing spaces.

    Note

    If you specify a directory name that begins and/or ends with spaces, these spaces are deleted (the resulting character string is used as the archive directory name).

  • Make sure that each element of the archive directory name is no more than NAME_MAX bytes long. The NAME_MAX value differs depending on the environment.

If a symbolic link is specified as the archive directory name, the system checks whether the absolute path name that the symbolic link substitutes for obeys the rules that are described here.

About the multi-node function:

If the multi-node function is enabled, note the following points:

  • Use the NFS or other means to share the archive directory by all nodes. Note that the archive directory must be shared by all nodes when the ALTER TABLE statement is run.

  • On the master node, when the ALTER TABLE statement is run, a check to see whether the archive directory name obeys the specification rules that are described here is conducted. On the slave nodes, this check is not conducted. Therefore, after the ALTER TABLE statement, check the archive directory name on each slave node.

About the location table that is defined when a regular multi-chunk table is changed to an archivable multi-chunk table

If a regular multi-chunk table is changed to an archivable multi-chunk table by running the ALTER TABLE statement, the HADB server automatically defines the location table and the index of the location table. The HADB server uses the location table and index. Therefore, no user can directly manipulate, redefine, or delete the location table or index. For details about the location table, see Searching an archivable multi-chunk table in the HADB Setup and Operation Guide.

The location table and its index are stored in the same DB area as the archivable multi-chunk table.

The names of the location table and its index are determined according to the rules that are described in the following table.

Table 3‒2: Naming rules for the location table and location table index

Item

Naming rule

Information managed by the index

Columns in the index

Location table

"HADB"."LOCATION_TABLE_nnnnnnnn"

--

--

Location table index

"HADB"."LOCATION_INDEX_nnnnnnnn_CHUNK_ID"

Manages the chunk ID of the chunk that corresponds to the archive file.

CHUNK_ID

"HADB"."LOCATION_INDEX_nnnnnnnn_RANGE_01"

Manages the range (upper and lower limits) of values in the archive range column of data stored in the archive file.

  • RANGE_MAX

  • RANGE_MIN

"HADB"."LOCATION_INDEX_nnnnnnnn_RANGE_02"

Manages the lower limit of values in the archive range column of data stored in the archive file.

RANGE_MIN

Legend:

--: Not applicable.

Note:

nnnnnnnn is an eight-digit character string that represents the ID of the archivable multi-chunk table in hexadecimal notation.

The schema name of the location table and location table index is HADB.

Note the following points when changing a regular multi-chunk table to an archivable multi-chunk table:

  • If a regular multi-chunk table is changed to an archivable multi-chunk table, the HADB server automatically defines the location table, the location table index, and the range index of the archive range column. However, if a range index has been defined for the column that is specified as the archive range column, a new range index is not defined. In this case, the already defined range index is used. For details about how to check whether a range index has already been defined for a column, see Investigating whether range indexes are defined in the column specified as the archive range column in Searching a dictionary table in the HADB Setup and Operation Guide.

  • If a regular multi-chunk table is changed to an archivable multi-chunk table, all viewed tables that are dependent on the table to be changed are invalidated. For details about how to check the viewed tables that will be invalidated, see Checking dependent viewed tables in the HADB Setup and Operation Guide.

For details about how to change a regular multi-chunk table to an archivable multi-chunk table, see Changing a regular multi-chunk table to an archivable multi-chunk table in the HADB Setup and Operation Guide.

(e) Changing an archivable multi-chunk table to a regular multi-chunk table

Specification format

ALTER-TABLE-statement ::= ALTER TABLE table-name
                      CHANGE OPTION CHUNK UNARCHIVABLE
table-name

Specify the name of the archivable multi-chunk table that you want to change into a regular multi-chunk table. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

CHANGE OPTION CHUNK UNARCHIVABLE

Specify this option if you change an archivable multi-chunk table to a regular multi-chunk table.

Note the following points when changing an archivable multi-chunk table to a regular multi-chunk table:

  • You cannot change an archivable multi-chunk table to a regular multi-chunk table if there are archived chunks. In this case, unarchive the chunks, and then change an archivable multi-chunk table to a regular multi-chunk table if there are archived chunks.

  • When an archivable multi-chunk table is changed to a regular multi-chunk table, the location table and the index that has been defined for the location table are deleted. However, the range index that has automatically been defined for the archive range column is not deleted. If this range index is unnecessary, delete it by using the DROP INDEX statement after changing an archivable multi-chunk table to a regular multi-chunk table.

  • If an archivable multi-chunk table is changed to a regular multi-chunk table, all viewed tables that are dependent on the table to be changed are invalidated. For details about how to check the viewed tables that will be invalidated, see Checking dependent viewed tables in the HADB Setup and Operation Guide.

For details about how to change an archivable multi-chunk table to a regular multi-chunk table, see Changing an archivable multi-chunk table to a regular multi-chunk table in the HADB Setup and Operation Guide.

(2) Privileges required at execution

To execute the ALTER TABLE statement, the CONNECT privilege and schema definition privilege are required.

(3) Rules

  1. You can only alter the definition of a table in the schema of the current user (the HADB user whose authorization identifier is currently connected to the HADB server). You cannot alter the definition of a table in a schema owned by another HADB user.

  2. You cannot add a column unless the sum of the sizes of all columns in the base table (the row length) satisfies the following formula:

    ● Formula (if the target base table is a row store table)
    ROWSZ-(row-length)page-size - 56 (unit: bytes)
    ● Formula (if the target base table is a column store table)
    ROWSZ-(row-length)page-size - 80 (unit: bytes)

    For details about the formula for calculating ROWSZ (row length), see Determining the number of pages for storing each type of row in the HADB Setup and Operation Guide.

  3. You cannot change the definition of a table that has been rendered non-updatable due to an interrupted command.

(4) Examples

Example 1: Adding a column to a row store table

Add a column for the email address of each shop (EMAIL_ADDRESS) to the shops table (SHOPSLIST), which is a row store table.

  • Column name: EMAIL_ADDRESS

  • Data type: VARCHAR(100)

  • Branch the data in the column

ALTER TABLE "SHOPSLIST"
    ADD COLUMN "EMAIL_ADDRESS" VARCHAR(100) BRANCH YES

[Figure]

Example 2: Adding a column to a column store table

Add a column for the times that receipts were issued (ISSUE_TIME) to the receipt table (RECEIPT), which is a column store table.

  • Column name: ISSUE_TIME

  • Data type: TIME

  • Compress the data in the column by using the delta run-length encoding algorithm (DELTA_RUNLENGTH).

ALTER TABLE "RECEIPT"
    ADD COLUMN  "ISSUE_TIME" TIME COMPRESSION TYPE DELTA_RUNLENGTH

[Figure]

Example 3: Renaming a column

In the shops table (SHOPSLIST), rename the EMAIL_ADDRESS column to EMAIL.

ALTER TABLE "SHOPSLIST"
    RENAME COLUMN FROM EMAIL_ADDRESS TO EMAIL
Example 4: Changing the maximum number of chunks

Change the maximum number of chunks in the shops table (SHOPSLIST) to 300.

ALTER TABLE "SHOPSLIST"
    CHANGE OPTION CHUNK=300
Example 5: Changing a regular multi-chunk table to an archivable multi-chunk table

Change the format of the shops table (SHOPSLIST), which is a row store table, from that of a regular multi-chunk table to that of an archivable multi-chunk table. The specifications related to the archive range column and other items are as follows:

  • The RECORD_DAY column is used as the archive range column.

  • The /mnt/nfs/archivedir directory is used as the archive directory.

  • The DB area that stores the range indexes that are automatically defined by the HADB server is DBAREA02.

ALTER TABLE "SHOPSLIST"
    CHANGE OPTION CHUNK ARCHIVABLE
        RANGECOLUMN="RECORD_DAY"
        IN "DBAREA02"
        ARCHIVEDIR='/mnt/nfs/archivedir'
Example 6: Changing an archivable multi-chunk table to a regular multi-chunk table

Change the format of the shops table (SHOPSLIST) from an archivable multi-chunk table to a regular multi-chunk table.

ALTER TABLE "SHOPSLIST"
    CHANGE OPTION CHUNK UNARCHIVABLE