3.2.1 Specification format and rules for the ALTER TABLE statement

(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]

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

• Foreign 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 (4,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	BIGINT#6	BIGINT
3	SMALLINT	SMALLINT
4	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.
5	NUMERIC*3	NUMERIC[(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.
6	DOUBLE PRECISION	DOUBLE or DOUBLE PRECISION
7	FLOAT#4	FLOAT
8	REAL	REAL
9	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.
10	VARCHAR#1, #2	VARCHAR(n) n: Maximum length of the character string (number of bytes)
11	DATE	DATE
12	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.
13	TIMESTAMP	For TIMESTAMP WITHOUT TIME ZONE (time stamp data without time zone) Specify in either of the following formats: TIMESTAMP(p) TIMESTAMP TIMESTAMP WITH TIME ZONE (time stamp data with time zone) Specify in either of the following formats: TIMESTAMP(p) WITH TIME ZONE TIMESTAMP WITH TIME ZONE 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 (p) is omitted, p is assumed to be 0.
14	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.
15	VARBINARY#1	VARBINARY(n) n: Maximum length of the binary data (number of bytes)
16	BOOLEAN	BOOLEAN
17	UUID	UUID
18	ARRAY#5	element-data-type ARRAY[maximum-number-of-elements] element-data-type: Specifies the array element data type. For the element data type, specify numeric data, character string data, datetime data, or binary data. The specification format of the element data type follows the rules for the specification format of each data type described in No. 1 through 15. maximum-number-of-elements: Specifies the maximum number of array elements. Specify an unsigned integer literal in the range from 2 to 30,000 as the maximum number of elements. Example: If the element data type is CHAR(5) and the maximum number of elements is 20 CHAR(5) ARRAY[20] If the element data type is INTEGER and the maximum number of elements is 5 INTEGER ARRAY[5]

#1

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

#2

A VARCHAR type column whose defined length exceeds 32,000 bytes cannot be specified.

#3

If type NUMERIC is specified, HADB assumes that type DECIMAL is specified for the data type.

#4

If a FLOAT type is specified, HADB assumes that a DOUBLE PRECISION type is specified.

#5

Data type of the array type. For considerations when adding array-type columns, see Defining an array-type column [column store table] in the HADB Setup and Operation Guide.

Important

• Array-type columns can be added to the column store table. Array-type columns cannot be added to the row store table.

• Array-type columns cannot be added to a table that has a B-tree index or uniqueness constraints defined.

#6

If the integer data type format is a legacy format, when BIGINT type is specified, HADB assumes that the INTEGER type is specified.

For details about data types, see 6.2 Data types.

Important

You cannot add a STRING and STRUCT type column to a base table.

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.10.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.10.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) Deleting a base table

Specification format

ALTER-TABLE-statement::=ALTER TABLE table-name
                      DROP [COLUMN] column-name [drop-behavior]
 
   drop-behavior::= {CASCADE | RESTRICT}

● table-name

Specifies the table name of the base table from which the column is to be deleted. For the 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

• Foreign tables

• Dictionary tables

• System tables

● DROP [COLUMN] column-name [drop-behavior]

column-name:

Specifies the column name of the column to be deleted. Only one column can be deleted at a time.

drop-behavior:

drop-behavior ::= {CASCADE|RESTRICT}

Specifies whether to delete the column if any of the following conditions are met:

• There is an index defined for the column to be deleted.

• Viewed tables that depend on the base table for the column to be deleted are defined.

• A table constraint has been defined for the column to be deleted.

The following table describes the specifications for drop-behavior.

Specification of drop-behavior	Description	Handling of viewed tables that depend on the base table that deleted the column
If drop-behavior is omitted	The column is also deleted if any of the following conditions are met: There is an index defined for the column to be deleted. Viewed tables that depend on the base table for the column to be deleted are defined. A table constraint has been defined for the column to be deleted. In this case, the following items are also deleted: The index defined for the column to be deleted The primary key defined in the column to be deleted, the foreign key, and the foreign key that references the primary key defined in the column to be deleted	The viewed tables that depend on the base table that deleted the column are invalidated. Not only the viewed table in the relevant schema, but also the dependent viewed tables in other schemata, are invalidated.
If CASCADE is specified		The viewed tables that depend on the base table that deleted the column are deleted. Not only the viewed table in the relevant schema, but also the dependent viewed tables in other schemata, are deleted.
If RESTRICT is specified	If any of the following conditions are met, the ALTER TABLE statement results in an error. There is an index defined for the column to be deleted. Viewed tables that depend on the base table for the column to be deleted are defined. A table constraint has been defined for the column to be deleted.	The dependent viewed tables are not affected because the ALTER TABLE statement results in an error.

For details on deleting a column of a base table, see Deleting a column from a base table in the HADB Setup and Operation Guide.

Notes

• The column of the base table can be deleted if any of the following conditions are met:

  • Segments for storing rows are not assigned to the base table that deletes the column

  • The column to be deleted is a column added with a ALTER TABLE statement and the following SQL statement or command has not been executed after the column was added

    • DELETE, INSERT, or UPDATE statements

    • adbidxrebuild command, adbimport command, adbmergechunk command, or adbunarchivechunk command

    Note that even when the above SQL statement or command is executed, it may be possible to delete the added columns.

    Also, if the updated-row columnizing facility is enabled after adding a column, the added column may not be deleted.

• You cannot delete a column if it matches any of the following conditions:

  • If the base table from which the column is to be deleted has only one column

  • The base table from which the column is to be deleted is a FIX table to which row storage segments have been allocated

  • The base table from which the column is to be deleted was created by running the CREATE TABLE statement with BRANCH ALL specified, and row storage segments have been allocated to the target base table

  • The base table from which the column is to be deleted is an archivable multi-chunk 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.

(c) 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

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

(d) Renaming a table in a base table

Specification format

ALTER-TABLE-statement::= ALTER TABLE table-name
                      RENAME [TABLE] TO table-identifier

● table-name

Specify the name of the base table of which to rename a table. For the 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

• Foreign tables

• Dictionary tables

• System tables

● RENAME [TABLE] TO table-identifier

Specifies the table identifier after changing.

The following specification rules apply:

• You cannot specify an already existing table name.

• The same table identifier as the one before the change cannot be specified.

Important

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

(e) Changing the data type (data length of VARCHAR type) of the base table column

Specification format

ALTER-TABLE-statement::= ALTER TABLE table-name
                      {CHANGE | ALTER} [COLUMN] column-name post-change-data-type

  post-change-data-type::=data-type

● table-name

Specifies the name of the base table from which the column data type is to be changed. For the 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

• Foreign tables

• Dictionary tables

• System tables

● {CHANGE|ALTER} [COLUMN] column-name post-change-data-type

One of the following changes can be made:

• Increase the data length of VARCHAR type columns with a data length between 1 and 254 bytes to a maximum of 255 bytes.

• Increase the data length of VARCHAR type columns with a data length between 256 and 31,999 bytes to a maximum of 32,000 bytes.

Note

The column data length of a VARCHAR-type whose data length is 255 bytes cannot be changed.

column-name:

Specifies the names of the columns into which the data type is to be changed.

The following specification rules apply:

• Specify a VARCHAR type column. However, a VARCHAR-type column whose data length exceeds 255 bytes cannot be specified.

• You cannot specify a column for which a text index is defined.

• You cannot specify a foreign key column.

• You cannot specify a primary key column that is referenced by a foreign key in another table.

• When specifying a VARCHAR type column with a data length of 254 bytes or less, the following restrictions apply.

  When specifying the indexed columns of a multiple-column index, the total length of the indexed column of the multiple-column index after the data type change must be 255 bytes or less.

  For details about how to delete multiple-column indexes, see Table 3‒16: Size of columns that comprise a multiple-column index.

• When specifying a VARCHAR type column with a data length of 256 bytes or more, the following restrictions apply.

  When specifying the columns of a B-tree index, the data length of the data type after the change must satisfy the following conditional expression.

  total-length-of-B-tree-indexed-columns ≤ MIN{(a ÷ 3) - 128, 4036 } (unit: bytes)

  a: Page size of the DB area where the B-tree index is to be stored

  For details on the length of the columns that make up a single-column index or multiple-column index, see Table 3‒15: Size of a column that comprises a single-column index or Table 3‒16: Size of columns that comprise a multiple-column index.

post-change-data-type:

Specifies the data type after changing.

The following specification rules apply:

• Specify VARCHAR type for the data type after changing.

• The data length of the changed data type should be longer than the data length before the change.

• If the data length of the data type before the change is 254 bytes or less, specify a VARCHAR type with a data length of 255 bytes or less for the data type after the change.

• If the data length of the data type before the change is 256 bytes or more, specify a VARCHAR type with a data length of 32,000 bytes or less for the data type after the change.

Example:

For details on changing the column data type of a base table, see Changing the column data type of a base table (increasing the data length of the VARCHAR type column) in the HADB Setup and Operation Guide.

Important

If the data type of a column is changed, viewed tables that depend on the table that changed the data type of the column will be invalidated regardless of whether the column name of the column whose data type was changed is explicitly specified when defining the viewed table.

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 on releasing invalidated viewed tables, see When viewed tables are invalidated by using the ALTER VIEW statement to change the data type of a table column in Releasing a viewed table from invalidation in the HADB Setup and Operation Guide.

(f) 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.

(g) Increasing the maximum number of elements in an array-type column

Specification format

ALTER-TABLE-statement::= ALTER TABLE table-name
                      {CHANGE|ALTER} [COLUMN] column-name ARRAY[maximum-number-of-elements]

● table-name

Specifies the name of the table for increasing the maximum number of elements. For the 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

• Foreign tables

• Dictionary tables

• System tables

●{CHANGE|ALTER} [COLUMN] column-name ARRAY[maximum-number-of-elements]

column-name:

Specifies the name of the column for increasing the maximum number of elements.

Specify the column name of the existing array-type column.

maximum-number-of-elements:

~ <number of unsigned integers>((2 to 30,000))

Specifies the maximum number of elements before the change.

Specify a value greater than the maximum number of elements before the change.

Important

If the maximum number of elements is changed, viewed tables that depend on the table with the changed maximum number of elements will be invalidated regardless of whether the column name of the column with the changed maximum number of elements is explicitly specified when defining the viewed table.

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 on releasing invalidated viewed tables, see When viewed tables are invalidated by using the ALTER VIEW statement to change the maximum number of elements of array-type columns in Releasing a viewed table from invalidation in the HADB Setup and Operation Guide.

(h) 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.

Please note that this option cannot be specified if you are using the cloud storage facility. For details about the cloud storage facility, see Using a HADB server in a cloud environment in the HADB Setup and Operation Guide.

• 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

  • TIMESTAMP WITH TIME ZONE type

  • BINARY type

  • VARBINARY type

  • BOOLEAN type

  • UUID 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 set on 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 primary 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. This check is not conducted on the secondary nodes and worker nodes. Therefore, after the ALTER TABLE statement, check the archive directory name on each secondary node and worker 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.

(i) 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.