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

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