Examples of altering a table by using the AL line command
You can use the AL line command to make changes to your tables that are supported by the ALTER TABLE statement.
Adding a primary key to the table
Procedure
To add a primary key to the table:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
- On the Alter Table (ADB21TA) panel, type an S before ADD PRIMARY KEY, and press Enter.
-
On the Add Primary Key Constraint (ADB21TAN) panel, in
the PRIMARY KEY field, specify the column
names.
For help selecting the columns for the primary key, use the COLUMNS primary command to display a list of the columns. Then, use the nn (Sequence) line command to specify a number for the relative position of each column that you want to include in the primary key. When you are done, press PF3 to return to the previous panel.
- Optional: In the Constraint field, specify a name for the primary key constraint.
- Press Enter to run the ALTER TABLE statement that adds the primary key.
Adding a partitioning key to the table
Procedure
To add a partitioning key to the table:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
- On the Alter Table (ADB21TA) panel, type an S before ADD PARTITIONING KEY, and press Enter.
-
On the Alter Table (ADB21TAP) panel, select
the columns to be part of the partitioning key by using the Sequence &
order line command.
For example, specifying 1D next to a column name assigns that column to be first in the partitioning key with the values in descending order.
To clear all of your changes, use the ORIGINAL primary command.
- Issue the NEXT primary command.
-
On the Alter Partitioned Table (ADB21TAV) panel, enter the
limit key values.
For help determining the limit key values, you can use the COLUMNS primary command to list the details of the columns that are part of the key.
- Enter the NEXT primary command to run the ALTER TABLE statement that adds the partitioning key.
Adding a partition to the table
Procedure
To add a partition to the table:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
-
On
the Alter Table (ADB21TA) panel, type an
S before ADD/INSERT/ALTER
PARTITION and press Enter.
(This option is displayed only if applicable.
- On the Alter Partitioned Table (ADB21TAV) panel, issue the ADD primary command to add a row with the next partition number.
- Enter the limit key value for the new partition.
-
Issue the NEXT primary command to run the ALTER TABLE statement to add the
partition.
If the Statement Execution Prompt (ADB2PSTM) panel is displayed, enter the appropriate action to execute the statement.
- On the Alter Table - Utilities (ADB21TAU) panel, specify any utilities that you want to run, and press Enter.
-
Follow the instructions on any subsequent utility panels.
If you requested any utility operations, a utility job is generated.
- If a utility job is generated, submit that job to run the requested utilities.
Inserting a partition into a table
Procedure
To insert a partition into a table:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
-
On the Alter Table (ADB21TA) panel, type an
S before ADD/INSERT/ALTER
PARTITION and press Enter.
(This option is displayed only if applicable.
-
On the Alter Partitioned Table (ADB21TAV) panel, issue the
INS line command to insert a new partition, as shown in the following
example:
Note: You do not have to issue the INS line command next to the row where you want to insert the partition. The logical partition number will be determined by the limit key, not by where you specify INS. For example, if you want to insert a partition before partition 1, you can issue the INS line command next to any row and, as part of the next step, specify the lowest limit key.
ADB21TAV --------------- DC1A Alter Partitioned Table ----- Row 1 to 10 of 10 Command ===> Scroll ===> CSR Commands: NEXT COLUMNS ORIGINAL ADD ROTATE Line commands: INS - Insert Partition ALTER TABLE : TS5771.TB1100 Sel Part Limit Key Value --- ------ -------------------------------------------------------------------> INS 1 '09999999999999999999999999999999' 2 '19999999999999999999999999999999' 3 '29999999999999999999999999999999' 4 '39999999999999999999999999999999' 5 '49999999999999999999999999999999' 6 '59999999999999999999999999999999' 7 '69999999999999999999999999999999' 8 '79999999999999999999999999999999' 9 '89999999999999999999999999999999' 10 MAXVALUE ******************************* END OF DB2 DATA ******************************* -
Enter the limit key value for the new partition, as shown in the following
example:
ADB21TAV --------------- DC1A Alter Partitioned Table ----- Row 1 to 10 of 10 Command ===> Scroll ===> CSR Commands: NEXT COLUMNS ORIGINAL ADD ROTATE Line commands: INS - Insert Partition ALTER TABLE : TS5771.TB1100 Sel Part Limit Key Value --- ------ -------------------------------------------------------------------> * 1 '09999999999999999999999999999999' ? '10000000000000000000000000000000' 2 '19999999999999999999999999999999' 3 '29999999999999999999999999999999' 4 '39999999999999999999999999999999' 5 '49999999999999999999999999999999' 6 '59999999999999999999999999999999' 7 '69999999999999999999999999999999' 8 '79999999999999999999999999999999' 9 '89999999999999999999999999999999' 10 MAXVALUE ******************************* END OF DB2 DATA *******************************The logical partition number of the inserted partition is determined by Db2® after the ALTER statement is executed. Db2 determines the logical partition number based on the values of the specified limit key.
-
Issue the NEXT primary command to run the ALTER TABLE statement to add the
partition.
If the Statement Execution Prompt (ADB2PSTM) panel is displayed, enter the appropriate action to execute the statement.
The Restrictive State (ADBPM610) panel is displayed to indicate that the table is in advisory REORG-pending status. You must run the REORG TABLESPACE utility to materialize the change that inserts the partition.
- Specify YES in the REORG field and any other options that you want, and press Enter.
- Follow the instructions on the subsequent panels to generate a REORG job.
- Submit the REORG job to complete the process of inserting a new partition.
Altering a partition
Procedure
To alter a partition:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
-
On
the Alter Table (ADB21TA) panel, type an
S before ADD/INSERT/ALTER
PARTITION and press Enter.
(This option is displayed only if applicable.)
- On the Alter Partitioned Table (ADB21TAV) panel, change the limit key values for any of the partitions.
- Issue the NEXT primary command to run the ALTER TABLE statement to alter the partitions with their new values.
Rotating a partition
Procedure
To rotate a partition:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
-
On
the Alter Table (ADB21TA) panel, type an
S before ADD/INSERT/ALTER
PARTITION and press Enter.
(This option is displayed only if applicable.)
- On the Alter Partitioned Table (ADB21TAV) panel, issue the ROTATE primary command.
- On the Alter Table (ADB21TAR) panel, enter the limit key value for the rotated partition, and press Enter.
- On the Alter Partitioned Table (ADB21TAV) panel, issue the NEXT command.
-
On the Alter Table - Utilities (ADB21TAU) panel, specify any
utilities that you want to run, and press Enter.
The ROTATE statement is held until all other ALTER statements are executed. If the first logical partition of the table space is in REORG, run the REORG utility before running ROTATE.
-
Submit the generated job to rotate the partition.
The generated job contains SQL that is similar to the following example SQL:
ALTER TABLE "SMITHJR"."TBADAJ01" ROTATE PARTITION FIRST TO LAST ENDING AT ('10500') RESET;
Dropping a column
Procedure
To drop a column:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
-
On the Alter Table (ADB21TA) panel, type an
S before DROP COLUMN and
press Enter:
ADB21TA n ----------------------- DD1A Alter Table ---------------------- Command ===> More: + Table schema . . : DSN81010 > Table name . . . : T1 > AUDIT . . . . . . . NONE (None, Changes, or All) DATA CAPTURE . . . . NONE (None/Changes) VALIDPROC . . . . . NULL (NULL/Program name) RESTRICT ON DROP . . NO (Yes/No) VOLATILE . . . . . . NO (Yes/No) APPEND . . . . . . . NO (Yes/No) KEY LABEL . . . . . > (Key label name, NO or blank to remove) ALTER TABLE with any of the above changes OR select one of the options below ADD COLUMN ADD MATERIALIZED QUERY ALTER/RENAME COLUMN DROP MATERIALIZED QUERY S DROP COLUMN REFRESH MATERIALIZED TABLE ADD PRIMARY KEY DROP PRIMARY KEY ADD/ALTER PARTITIONING BY RANGE ADD FOREIGN KEY ADD PARTITION DROP FOREIGN KEY ADD CLONE ADD CHECK constraint DROP CLONE DROP CHECK constraint ADD VERSIONING ADD UNIQUE constraint DROP VERSIONING DROP UNIQUE constraint ADD PERIOD ADD ORGANIZE BY HASH ADD ROW PERMISSION ALTER ORGANIZATION DROP ROW PERMISSION DROP ORGANIZATION ADD COLUMN MASK ACTIVATE ROW ACCESS CONTROL DROP COLUMN MASKE DEACTIVATE ROW ACCESS CONTROL NABLE ARCHIVE ACTIVATE COLUMN ACCESS CONTROL DISABLE ARCHIVE DEACTIVATE COLUMN ACCESS CONTROLRestriction: DROP COLUMN is not valid if any of the following conditions are true:- The table in not contained in a universal table space (UTS).
- The table is a materialized query table (MQT).
- The table is referenced in a MQT definition.
- The table contains an edit procedure or a validation-exit procedure.
- The table is in an incomplete state.
- The table is a system-period temporal table.
- The table contains extended indexes that are dependent on the table.
- The table contains triggers that are dependent on the table.
- The table contains row permissions that are dependent on the table.
- The table contains column masks that are dependent on the table.
- The table contains check constraints that are dependent on the table.
-
On the Columns in Table (ADB21TC) panel, issue the
DROP line command against the column that you want to drop:
ADB21TC n -- DD1A Columns in Table DSN81010.T1 ------- Row 1 to 2 of 2 Command ===> Scroll ===> PAGE Select by typing 'DROP' Line commands: T - Tables X - Indexes A - Auth GR - Grant H - Homonyms I - Interpret UR - Update runstats LAB - Label COM - Comment DI - Distribution stats ? - Show all line commands Select Column Name Col No Col Type Length Scale Null Def FP Col Card * * * * * * * * * ------ ------------------ ------ -------- ------ ------ ---- --- -- ----------- DROP C1 1 INTEGER 4 0 N N N -1 C2 2 CHAR 1 0 Y Y N -1 ******************************* END OF DB2 DATA *******************************Restriction: The DROP line command can be issued against only one column at a time.
Adding or changing a key label
Before you begin
Procedure
To add or change a key label:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
-
On the Alter Table (ADB21TA) panel, specify
the key label name in the KEY LABEL field:
ADB21TA n ----------------------- DD1A Alter Table ---------------------- 11:20 Command ===> Table schema . . : TS5816 > Table name . . . : TESTKEY > AUDIT . . . . . . . NONE (None, Changes, or All) DATA CAPTURE . . . . NONE (None/Changes) VALIDPROC . . . . . NULL (NULL/Program name) RESTRICT ON DROP . . NO (Yes/No) VOLATILE . . . . . . NO (Yes/No) APPEND . . . . . . . NO (Yes/No) KEY LABEL . . . . . > (Key label name, NO or blank to remove) ALTER TABLE with any of the above changes OR select one of the options below ADD COLUMN ADD MATERIALIZED QUERY ALTER/RENAME COLUMN DROP MATERIALIZED QUERY DROP COLUMN REFRESH MATERIALIZED TABLE ADD PRIMARY KEY DROP PRIMARY KEY ADD/ALTER PARTITIONING BY RANGE ADD FOREIGN KEY ADD PARTITION DROP FOREIGN KEY ADD CLONE ADD CHECK constraint DROP CLONE DROP CHECK constraint ADD VERSIONING ADD UNIQUE constraint DROP VERSIONING DROP UNIQUE constraint ADD PERIOD ADD ORGANIZE BY HASH ADD ROW PERMISSION ALTER ORGANIZATION DROP ROW PERMISSION DROP ORGANIZATION ADD COLUMN MASK ACTIVATE ROW ACCESS CONTROL DROP COLUMN MASKE DEACTIVATE ROW ACCESS CONTROL NABLE ARCHIVE ACTIVATE COLUMN ACCESS CONTROL DISABLE ARCHIVE DEACTIVATE COLUMN ACCESS CONTROLNote: If the KEY LABEL field is not displayed, you are not running Db2 12 for z/OS function level 502 or higher.If this field is initially blank, a key label is not defined on this table. You can add a key label by specifying a valid key label name.
If this field is initially populated, a key label is already defined on the table. You can change the key label by specifying a new value. Alternatively, you can remove it by specifying NO or blank, in which case the NO KEY LABEL clause is added to the ALTER TABLE statement.
- Press Enter to run the ALTER TABLE statement with the new key label value.
- To materialize this key label change and encrypt the data set, run the REORG TABLESPACE utility on the table space that contains the table.
Altering a column
Procedure
To alter a column:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
-
On the Alter Table (ADB21TA) panel, type an
S before ALTER/RENAME
COLUMN, and press Enter:
ADB21TA n ----------------------- DD1A Alter Table ---------------------- Command ===> Table schema . . : ADM001 > Table name . . . : TABLE01 > AUDIT . . . . . . . NONE (None, Changes, or All) DATA CAPTURE . . . . NONE (None/Changes) VALIDPROC . . . . . NULL (NULL/Program name) RESTRICT ON DROP . . NO (Yes/No) VOLATILE . . . . . . NO (Yes/No) APPEND . . . . . . . NO (Yes/No) KEY LABEL . . . . . > (Key label name, NO or blank to remove) ALTER TABLE with any of the above changes OR select one of the options below ADD COLUMN ADD MATERIALIZED QUERY S ALTER/RENAME COLUMN DROP MATERIALIZED QUERY DROP COLUMN REFRESH MATERIALIZED TABLE ADD PRIMARY KEY DROP PRIMARY KEY ADD/ALTER PARTITIONING BY RANGE ADD FOREIGN KEY ADD PARTITION DROP FOREIGN KEY ADD CLONE ADD CHECK constraint DROP CLONE DROP CHECK constraint ADD VERSIONING ADD UNIQUE constraint DROP VERSIONING DROP UNIQUE constraint ADD PERIOD ADD ORGANIZE BY HASH ADD ROW PERMISSION ALTER ORGANIZATION DROP ROW PERMISSION DROP ORGANIZATION ADD COLUMN MASK ACTIVATE ROW ACCESS CONTROL DROP COLUMN MASKE DEACTIVATE ROW ACCESS CONTROL NABLE ARCHIVE ACTIVATE COLUMN ACCESS CONTROL DISABLE ARCHIVE DEACTIVATE COLUMN ACCESS CONTROL -
On the Columns in Table (ADB21TC) panel, issue the
AL line command next to the column that you want to alter:
ADB21TC n -- DD1A Columns in Table ADM001.TABLE01 ------- Row 1 to 5 of 5 Command ===> Scroll ===> PAGE Select by typing 'AL' Line commands: T - Tables X - Indexes A - Auth GR - Grant H - Homonyms I - Interpret UR - Update runstats LAB - Label COM - Comment DI - Distribution stats ? - Show all line commands Select Column Name Col No Col Type Length Scale Null Def FP Col Card * * * * * * * * * ------ ------------------ ------ -------- ------ ------ ---- --- -- ----------- HLEVELQ 1 CHAR 8 0 N N N -1 PNSUFX 2 CHAR 2 0 N N N -1 TYPE 3 CHAR 1 0 N N N -1 AL LOCATION 4 CHAR 16 0 N Y N -1 TIMESTMP 5 TIMESTMP 10 6 Y Y N -1 ******************************* END OF DB2 DATA ******************************* -
On the Alter Table (ADB21TAB) panel, make your
changes, and press Enter.
ADB21TAB ----------------------- DD1A Alter Table ---------------------- Command ===> More: + ALTER TABLE Table schema . . ADM001 > Table name . . . TABLE01 > ALTER Column name . . LOCATION > (? to look up) Column type . . CHAR (Built-in only) Data length . . 16 (Built-in only) Inline length . (0-32680 BLOB or CLOB, 0-16340 DBCLOB) Precision . . . (used only w/FLOAT and DECIMAL) Scale . . . . . (used only w/DECIMAL and TIMESTAMP) Type schema . . > (User-defined only) Type name . . . > (User-defined only) CCSID . . . . . (1200, 1208, or blank for EBCDIC) WITH TIME ZONE . (Yes/No - for TIMESTAMP only) Allow nulls . . YES (Yes or blank-nullable, No-NOT NULL) FOR ? DATA . . . S (B-Bit, S-SBCS, M-Mixed, blank-N/A) WITH DEFAULT . . YES (Yes, No, L (SECLABEL) or enter value below) Default value . >The ALTER TABLE ALTER COLUMN statement is generated. - If the Statement Execution Prompt (ADB2PSTM) panel is displayed, confirm that you want to execute the statement.
Results
Renaming a column
Procedure
To rename a column:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
-
On the Alter Table (ADB21TA) panel, type an
S before ALTER/RENAME
COLUMN, and press Enter:
ADB21TA n ----------------------- DD1A Alter Table ---------------------- Command ===> Table schema . . : ADM001 > Table name . . . : TABLE01 > AUDIT . . . . . . . NONE (None, Changes, or All) DATA CAPTURE . . . . NONE (None/Changes) VALIDPROC . . . . . NULL (NULL/Program name) RESTRICT ON DROP . . NO (Yes/No) VOLATILE . . . . . . NO (Yes/No) APPEND . . . . . . . NO (Yes/No) KEY LABEL . . . . . > (Key label name, NO or blank to remove) ALTER TABLE with any of the above changes OR select one of the options below ADD COLUMN ADD MATERIALIZED QUERY S ALTER/RENAME COLUMN DROP MATERIALIZED QUERY DROP COLUMN REFRESH MATERIALIZED TABLE ADD PRIMARY KEY DROP PRIMARY KEY ADD/ALTER PARTITIONING BY RANGE ADD FOREIGN KEY ADD PARTITION DROP FOREIGN KEY ADD CLONE ADD CHECK constraint DROP CLONE DROP CHECK constraint ADD VERSIONING ADD UNIQUE constraint DROP VERSIONING DROP UNIQUE constraint ADD PERIOD ADD ORGANIZE BY HASH ADD ROW PERMISSION ALTER ORGANIZATION DROP ROW PERMISSION DROP ORGANIZATION ADD COLUMN MASK ACTIVATE ROW ACCESS CONTROL DROP COLUMN MASKE DEACTIVATE ROW ACCESS CONTROL NABLE ARCHIVE ACTIVATE COLUMN ACCESS CONTROL DISABLE ARCHIVE DEACTIVATE COLUMN ACCESS CONTROL -
On the Columns in Table (ADB21TC) panel, issue the
AL line command next to the column that you want to rename:
ADB21TC n -- DD1A Columns in Table ADM001.TABLE01 ------- Row 1 to 5 of 5 Command ===> Scroll ===> PAGE Select by typing 'AL' Line commands: T - Tables X - Indexes A - Auth GR - Grant H - Homonyms I - Interpret UR - Update runstats LAB - Label COM - Comment DI - Distribution stats ? - Show all line commands Select Column Name Col No Col Type Length Scale Null Def FP Col Card * * * * * * * * * ------ ------------------ ------ -------- ------ ------ ---- --- -- ----------- HLEVELQ 1 CHAR 8 0 N N N -1 PNSUFX 2 CHAR 2 0 N N N -1 TYPE 3 CHAR 1 0 N N N -1 AL LOCATION 4 CHAR 16 0 N Y N -1 TIMESTMP 5 TIMESTMP 10 6 Y Y N -1 ******************************* END OF DB2 DATA ******************************* -
On the Alter Table (ADB21TAB) panel, type the
new column name:
ADB21TAB ----------------------- DD1A Alter Table ---------------------- Command ===> More: + ALTER TABLE Table schema . . ADM001 > Table name . . . TABLE01 > ALTER Column name . . OLD_LOC > (? to look up) Column type . . CHAR (Built-in only) Data length . . 16 (Built-in only) Inline length . (0-32680 BLOB or CLOB, 0-16340 DBCLOB) Precision . . . (used only w/FLOAT and DECIMAL) Scale . . . . . (used only w/DECIMAL and TIMESTAMP) Type schema . . > (User-defined only) Type name . . . > (User-defined only) CCSID . . . . . (1200, 1208, or blank for EBCDIC) WITH TIME ZONE . (Yes/No - for TIMESTAMP only) Allow nulls . . YES (Yes or blank-nullable, No-NOT NULL) FOR ? DATA . . . S (B-Bit, S-SBCS, M-Mixed, blank-N/A) WITH DEFAULT . . YES (Yes, No, L (SECLABEL) or enter value below) Default value . > - Optional: Specify values for any other attributes on this panel that you want to change.
-
Press Enter.
The ALTER TABLE RENAME COLUMN statement is generated. If you changed other attributes, additional ALTER TABLE ALTER COLUMN statements are generated first.
- If the Statement Execution Prompt (ADB2PSTM) panel is displayed, confirm that you want to execute the statement.