Examples of redefining a table by using the ALT line command
You can use the ALT line command to make changes to your tables. With ALT, you can make both changes that are done by ALTER TABLE statements and changes that cannot be done by ALTER TABLE statements. Because ALT supports more than just ALTER statement changes, the ALT process is referred to as redefining a table.
- Inserting a column
- Updating a column
- Adding a unique key to a table
- Changing a unique key
- Adding a column to a primary key
- Renaming a table
- Adding a partition to a table
- Adding a partition to a table in a partition-by-growth (PBG) table space
- Inserting a partition into a table
Adding a foreign key
Identity columns: If you are redefining a table that contains an
identity column and the table is dropped and re-created, the column definition becomes GENERATED BY
DEFAULT to preserve current data values. The first value that is generated for the identity column
(specified in the START WITH clause) is also changed. The new START WITH value, which is the value
that is to assigned next to the identity column, is the last unassigned value (MAXASSIGNEDVAL in
SYSIBM.SYSSEQUENCES) plus the increment value (INCREMENT in SYSIBM.SYSSEQUENCES). If values were
cached, any existing unassigned values in the cache that have not been used are lost. Loss of
unassigned cached values causes a gap between the last assigned value of the identity column and the
new starting value.
Inserting a column
Procedure
To insert a column:
-
On the ALTER Table (ADB27C)
panel, issue
the I line command against the column after which you
want to insert a new column:
Figure 1. ALTER Table (ADB27C) panel- Inserting a column ADB27C in ------------------- DD1A ALTER Table ------------------ Row 1 from 5 Command ===> Scroll ===> PAGE New schema . . DSN8D10 > Old schema: DSN8D10 New name . . . DEPT > Old name : DEPT New owner . . DSN8D10 > Type: (U/R) Old owner : DSN8D10 Partitions ==> 1 Action : NONE New DB : DSN8D81A Rows/Page : 48.188 New TS : DSN8S81D Commands : NEXT CONSTRAINTS TBLOPTS LONGNAMES HASH Line commands : I - Insert U - Update D - Delete R - Repeat LAB - Label COM - Comment M - Move A - After B - Before X - Index RES - Reset update ? - Show all line commands Old Operation Select Column Name Col No Col Type Length Scale Null D Col No Type * * * * * * * * * ------ ------------------ ------ -------- ------ ------ ---- - ------ --------- DEPTNO 1 CHAR 3 0 N N 1 I DEPTNAME 2 VARCHAR 36 0 N N 2 MGRNO 3 CHAR 6 0 Y Y 3 ADMRDEPT 4 CHAR 3 0 N N 4 LOCATION 5 CHAR 16 0 Y Y 5 ******************************* END OF DB2 DATA *******************************
-
For the line that contains
INSERT in the Operation
Type column, complete the fields that contain
? as shown to define the new column, and press
Enter:
Figure 2. ALTER Table (ADB27C) panel - Specifying attributes for the inserted column ADB27C in ------------------- DD1A ALTER Table --------------- Row 1 to 6 of 6 Command ===> Scroll ===> PAGE New schema . . DSN8A10 > Old schema: DSN8A10 New name . . . DEPT > Old name : DEPT Partitions . : 0 New DB . . DSN8DA1A Rows per page: 53 New TS . . DSN8SA1D Commands: CONTINUE CONSTRAINTS TBLOPTS LONGNAMES HASH Line commands: I - Insert U - Update D - Delete R - Repeat LAB - Label COM - Comment M - Move A - After B - Before X - Index RES - Reset update ? - Show all line commands Old Operation Sel Column Name Col No Col Type Length Scale N D Col No Type * * * * * * * * * --- -----------------> ------ -------- ----------- ------ - - ------ --------- DEPTNO 1 CHAR 3 0 N N 1 * DEPTNAME 2 VARCHAR 36 0 N N 2 BUILDING 3 CHAR 0 0 ? ? 0 INSERT MGRNO 4 CHAR 6 0 Y Y 3 ADMRDEPT 5 CHAR 3 0 N N 4 LOCATION 6 CHAR 16 0 Y Y 5 ******************************* END OF DB2 DATA *******************************
-
If you want to specify additional attributes for the new column, use the
U line command, and on the ALTER Table (ADB26CTU)
panel, specify the
updated values, and press Enter:
Figure 3. ALTER Table (ADB26CTU) panel ADB26CTU ----------------------- DD1A ALTER Table ---------------------- 10:08 Command ===> DB2 Admin ALTER (column number 2) Schema . : DSN81010 > Name . . : DEPT > Commands: NEXTCOL Press ENTER to continue, END to cancel, or NEXTCOL to move to the next column. Column name . . NEWCOL Column type . . CHAR (CHAR,DECIMAL,INTEGER,SMALLINT,etc.) Data length . . 1 Inline length . (0-32680 BLOB or CLOB, 0-16340 DBCLOB) Precision . . . (FLOAT and DECIMAL only) Scale . . . . . (DECIMAL and TIMESTAMP only) Type schema . . (User-defined type schema) Type name . . . (User-defined type name) CCSID . . . . . 1208 (1208 VARCHAR, 1200 VARGRAPHIC) WITH TIME ZONE . (Yes/No - for TIMESTAMP only) Allow Nulls . . NO (Yes-Nullable, No-NOT NULL) FOR ? DATA . . . (B - Bit, S - SBCS, M - Mixed, or blank) WITH DEFAULT . . NO (Yes, No, L (SECLABEL) or enter value below) Default value . HIDDEN . . . . . NO (Yes/No) GENERATED . . . CP (A-ALWAYS, D-DEFAULT, I-ALWAYS AS IDENTITY, J-DEFAULT AS IDENTITY E-ALWAYS AS UPD TIMESTAMP, F-DEFAULT AS UPD TIMESTAMP, Q-ALWAYS AS ROW BEGIN, R-ALWAYS AS ROW END, O-ALWAYS AS DATA CHANGE OPERATION, X-ALWAYS AS TRANSACTION START ID, CA,CP,CT,CI,CW,CV,CS,CU - Special registers, SN,SS,SV - Session variables) FIELDPROC Program name . . Program parm . . >
Updating a column
Procedure
To update a column:
-
If you want to update column attributes that are not displayed on the ALTER Table (ADB27C)
panel, issue the
U line command against the column:
Figure 4. ALTER Table (ADB27C) panel - Updating a column ADB27C in ------------------- DD1A ALTER Table --------------- Row 1 to 5 of 5 Command ===> Scroll ===> CSR New schema . . DSN8D10 > Old schema: DSN8D10 New name . . . DEPT > Old name : DEPT New owner . . DSN8D10 > Type: (U/R) Old owner : DSN8D10 Partitions . : 1 New DB . . DSN8D10A Rows per page: 47 New TS . . DSN8S10D Commands : NEXT CONSTRAINTS TBLOPTS LONGNAMES HASH Line commands : I - Insert U - Update D - Delete R - Repeat LAB - Label COM - Comment M - Move A - After B - Before X - Index RES - Reset update ? - Show all line commands Old Operation Sel Column Name Col No Col Type Length Scale N D Col No Type * * * * * * * * * --- -----------------> ------ -------- ----------- ------ - - ------ --------- DEPTNO 1 CHAR 3 0 N N 1 DEPTNAME 2 VARCHAR 36 0 N N 2 MGRNO 3 CHAR 16 0 Y Y 3 ADMRDEPT 4 CHAR 3 0 N N 4 U LOCATION 5 CHAR 16 0 Y Y 5 ******************************* END OF DB2 DATA *******************************
-
On the ALTER Table (ADB26CTU)
panel, specify
updated values for any attributes, and press Enter:
Figure 5. ALTER Table (ADB26CTU) panel ADB26CTU ----------------------- DD1A ALTER Table ---------------------- 10:27 Command ===> DB2 Admin ALTER (column number 6) Schema . : DSN81010 > Name . . : DEPT > Commands: NEXTCOL Press ENTER to continue, END to cancel, or NEXTCOL to move to the next column Column name . . LOCATION Column type . . CHAR (CHAR,DECIMAL,INTEGER,SMALLINT,etc.) Data length . . 16 Inline length . (0-32680 BLOB or CLOB, 0-16340 DBCLOB) Precision . . . (FLOAT and DECIMAL only) Scale . . . . . (DECIMAL and TIMESTAMP only) Type schema . . (User-defined type schema) Type name . . . (User-defined type name) WITH TIME ZONE . (Yes/No - for TIMESTAMP only) Allow Nulls . . YES (Yes-Nullable, No-NOT NULL) FOR ? DATA . . . (B - Bit, S - SBCS, M - Mixed, or blank) WITH DEFAULT . . YES (Yes, No, L (SECLABEL) or enter value below) Default value . NULL HIDDEN . . . . . NO (Yes/No) GENERATED . . . CP (A-ALWAYS, D-DEFAULT, I-ALWAYS AS IDENTITY, J-DEFAULT AS IDENTITY E-ALWAYS AS UPD TIMESTAMP, F-DEFAULT AS UPD TIMESTAMP, Q-ALWAYS AS ROW BEGIN, R-ALWAYS AS ROW END, O-ALWAYS AS DATA CHANGE OPERATION, X-ALWAYS AS TRANSACTION START ID, CA,CP,CT,CI,CW,CV,CS,CU - Special registers, SN,SS,SV - Session variables) FIELDPROC Program name . . Program parm . . >
If you altered a primary key column of a table, an additional primary command, ADDFK, is displayed on the ALTER Table (ADB27C) panel. ADDFK propagates the primary key column updates for the target table to all tables that are affected by the update. If you specify ADDFK, all affected tables are displayed on the Alter Objects (ADB27CA) panel and included in the subsequently generated JCL for the ALTER. Submit that JCL and skip the remaining steps.
Adding a unique key to a table
Procedure
To add a unique key to a table:
Changing a unique key
Procedure
To change a unique key:
Adding a column to a primary key
About this task
When you add a column to a primary key, the underlying index that enforces that constraint needs to be changed at the same time. (This index is called the primary index.) The complete change can be made using a single ALT command.
Procedure
To add a column to a primary key:
Renaming a table
Procedure
To rename a table:
- On the DB2 Administration Menu (ADB2) panel, select option 1.
- On the System Catalog (ADB21) panel, select option T.
- On the Tables, Views, and Aliases (ADB21T) panel, issue the REN line command against the table that you want to rename.
- On the Rename Table (ADB21TR) panel, in the New name field, specify the new name, and press Enter.
- If the Statement Execution Prompt (ADB2PSTM) panel is displayed, enter the appropriate action to execute the RENAME statement.
Adding a partition to a table
Procedure
To add a partition to a table:
Adding a partition to a table in a partition-by-growth (PBG) table space
Procedure
To add a partition to a table in a PBG table space:
Inserting a partition into a table
Procedure
To insert a partition into a table:
Adding a foreign key
Procedure
To add a foreign key:
Results