ALTER TABLE (Alter table definition)
Function
ALTER TABLE has the following functions:
Privileges
Format
The item numbers in the following format correspond with the operand numbers:
No. | Format |
---|---|
ALTER TABLE [authorization-identifier.]table-identifier | |
table-definition-change::={ column-addition-definition |
No. | Format |
---|---|
column-addition-definition::= | |
column-recovery-restriction-1::= RECOVERY[[ALL | PARTIAL | NO] | |
LOB-column-storage-RDAREA-specification::= | |
matrix-partitioned-LOB-column-storage-RDAREA-specification::= 2-dimension storage RDAREA specification | |
abstract data type LOB storage RDAREA specification::= | |
DEFAULT clause::= DEFAULT [default-value] | |
updatable-column-attribute::= UPDATE [ONLY FROM NULL] | |
RDAREA-addition-definition::= | |
LOB-column-storage-RDAREA-specification::= | |
abstract data type LOB storage RDAREA specification::= | |
index-storage-RDAREA-specification::= | |
column-attribute-change-definition::= | |
column-recovery-restriction-2::= RECOVERY{ALL|PARTIAL|NO} | |
column-deletion-definition::= | |
table-name-change-definition::= | |
column-name-change-definition::= | |
partitioning-storage-condition-change-definition::= | |
row-partitioned-table-change-specification::= | |
pre-change-boundary-value-list::= boundary-value-list | |
post-change-boundary-value-partitioning-specification::= {table-storage-RDAREA-name | |
pre-change-RDAREA-information-list::= | |
pre-change-RDAREA-information-list::= | |
index-storage-RDAREA-change-specification::= | |
primary-key-storage-RDAREA-change-specification::= | |
cluster-key-storage-RDAREA-change-specification::= | |
LOB-column-storage-RDAREA-change-specification::= | |
LOB-column-storage-RDAREA-change-list::= |
Legend:
: See specification locations 1 to 6.
1 Columns in a non-FIX table
2 Columns in a FIX table
Operands
Column value before change | Column value after change | Whether updatable |
---|---|---|
Null value | Null value | Y |
Null value | Non-null-value | Y |
Non-null-value | Null value | N |
Non-null-value | Non-null-value* | N |
Table type | UPDATE specification | UPDATE ONLY FROM NULL specification | No specification | |||
---|---|---|---|---|---|---|
Specifiable | Column value updatable | Specifiable | Column value updatable | Specifiable | Column value updatable | |
Non-falsification-prevented table | Y | Y | Y | Y | ![]() | Y |
Falsification-prevented table | Y | Y | Y | Y* | ![]() | N |
Table 3-6 Whether an index storage RDAREA can be specified depending on the index type
Index definition method | Index-partitioning method | Specifiability of index storage RDAREA | |
---|---|---|---|
Index and primary key defined using CREATE INDEX format 1 | Row-partitioning on one server1 | On-server partitioning key index | Y |
On-server non-partitioning key index | |||
Row-partitioning only between servers2 | On-server partitioning key index | ||
On-server non-partitioning key index | N5, 6 | ||
Row partitioning on and between servers3 | On-server partitioning key index | Y | |
On-server non-partitioning key index | |||
Non-partitioning4 | Non-partitioning key index | N5 | |
Index and cluster key defined using CREATE INDEX format 2 | Row-partitioning on one server1 | Y | |
Row-partitioning only between servers2 | |||
Row-partitioning on and between servers3 |
Column value before update | Column value after update | Updatable |
---|---|---|
Null value | Null value | Y |
Null value | Non-null value | Y |
Non-null value | Null value | N |
Non-null value | Non-null value* | N |
Table type | UPDATE specification | UPDATE ONLY FROM NULL specification | No specification | |||
---|---|---|---|---|---|---|
Specifiable | Column value updatable | Specifiable | Column value updatable | Specifiable | Column value updatable | |
Non-falsification-prevented table | Y | Y | Y | Y | ![]() | Y |
Falsification-prevented table | N | Y | N | Y* | ![]() | N |
This option is specified when renaming a table.
This option is specified to change the name of a column.
Table type | Table partitioning method | Execution type | ||
---|---|---|---|---|
Partitioning | Combining | |||
Row-partitioned table | Key range partitioning | Boundary value specification | Partitions an arbitrarily selected boundary value into 2 to 16 values. | Combines 2 to 16 arbitrarily selected boundary values into a single value. |
Storage condition specification | Partitions an arbitrarily selected RDAREA into 2 to 16 segments. | Combines 2 to 16 arbitrarily selected RDAREAs into a single RDAREA. |
Table type | Partitioning method | Modifiability | ||
---|---|---|---|---|
Row-partitioned table | Key range partitioning | Storage condition specification | Table in which only = is used for the storage condition comparison operator | Y |
Table in which an operator other than = is used for the storage condition comparison operator | N | |||
Boundary value specification | Y | |||
Hash partitioning | N | |||
Matrix-partitioned table | First dimension: Boundary value specification Second dimension: Boundary value specification | N | ||
First dimension: Boundary value specification Second dimension: Hash partitioning | N | |||
Non-partitioned table* | ![]() | N |
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 table | Is 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 constraint | No | SQL_REFERENTIAL_CONSTRAINTS table | CHECK_PEND column |
SQL_TABLES table | CHECK_PEND column | ||
Check constraint | No | SQL_CHECKS table | CHECK_PEND2 column |
SQL_TABLES table | CHECK_PEND2 column | ||
Referential constraint and check constraint | Referential constraint: No Check constraint: No | SQL_REFERENTIAL_CONSTRAINTS table | CHECK_PEND column |
SQL_CHECKS table | CHECK_PEND2 column | ||
SQL_TABLES table | CHECK_PEND column, CHECK_PEND2 column | ||
Referential constraint: No Check constraint: Yes | SQL_REFERENTIAL_CONSTRAINTS table | CHECK_PEND column | |
SQL_TABLES table | CHECK_PEND column | ||
Referential constraint: Yes Check constraint: No | SQL_CHECKS table | CHECK_PEND2 column | |
SQL_TABLES table | CHECK_PEND2 column | ||
No constraint defined | No | ![]() | ![]() |
Item | Row-partitioned table with a boundary value specification | |
---|---|---|
Modified RDAREA specified in ALTER TABLE* | Duplicate | Duplication acceptable. |
Contiguous | Contiguous allocation should be avoided. | |
RDAREA of an entire table of the results of modification of a partitioning storage condition | Duplicate | Duplication acceptable. |
Contiguous | During partitioning, the system consolidates contiguous RDAREAs into one area. | |
Cannot be specified for combining. |
|(table-storage-RDAREA-name)
Common rules
Notes
Examples
ALTER TABLE STOCK
ADD PADRS VARCHAR(40)
ALTER TABLE STOCK
ADD PADRS VARCHAR(40)
NOT NULL WITH DEFAULT
ALTER TABLE STOCK
CHANGE PADRS VARCHAR(60)
ALTER TABLE STOCK
CHANGE CLUSTER KEY UNIQUE
ALTER TABLE STOCK
DROP PADRS
ALTER TABLE STOCK
DROP PADRS WITH PROGRAM
ALTER TABLE STOCK
ADD PMAP BLOB(1M) IN (RMAPLOB1)
ALTER TABLE NSTOCK
ADD RDAREA RDA3
FOR COLUMN PMAP IN (RMAPLOB3)
FOR INDEX IPCODE IN (RDA4)
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"))
ALTER TABLE "T1" CHANGE RDAREA
((100),(200)) INTO "TA11"
FOR INDEX "I1" INTO "IA11"
ALTER TABLE "T1" CHANGE RDAREA
((400)) INTO (("TA12")300,("TA13"))
FOR INDEX "I1" INTO (("IA12"),("IA13"))
ALTER TABLE "T1" CHANGE RDAREA
((600),(MAX)) INTO "TA11"
FOR INDEX "I1" INTO "IA11"
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"))
ALTER TABLE "T1" CHANGE RDAREA
((400)) INTO (("TA3")300,("TA4"))
FOR INDEX "I1" INTO (("IA3"),("IA4"))
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"))
ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
(("TA5")) INTO (("TA6")"C1"='006',("TA5"))
FOR INDEX "I1" INTO (("IA6"),("IA5"))
ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
(("TA1"),("TA5")) INTO "TA5"
FOR INDEX "I1" INTO "IA5"
ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
(("TA4")) INTO (("TA4")"C1"='004',("TA7")"C1"='005')
FOR INDEX "I1" INTO (("IA4"),("IA7"))
ALTER TABLE "T1" CHANGE RDAREA PARTITIONED CONDITION
(("TA1"),("TA2")) INTO "TA2"
FOR INDEX "I1" INTO "IA2"
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)
ALTER TABLE ORDER
CHANGE INSERT ONLY WHILE 10 YEARS BY OINSDATE