Examples of altering a table by using the AL line command

To add a primary key to the table:

1. On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
2. On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
3. On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
4. On the Alter Table (ADB21TA) panel, type an S before ADD PRIMARY KEY, and press Enter.
5. 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.

6. Optional: In the Constraint field, specify a name for the primary key constraint.
7. Press Enter to run the ALTER TABLE statement that adds the primary key.

To add a partitioning key to the table:

1. On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
2. On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
3. On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
4. On the Alter Table (ADB21TA) panel, type an S before ADD PARTITIONING KEY, and press Enter.
5. 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.

6. Issue the NEXT primary command.
7. 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.

8. Enter the NEXT primary command to run the ALTER TABLE statement that adds the partitioning key.

To add a partition to the table:

1. On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
2. On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
3. On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
4. On the Alter Table (ADB21TA) panel, type an S before ADD/INSERT/ALTER PARTITION and press Enter.
   (This option is displayed only if applicable. The INSERT option is displayed only if you are running Db2 12 for z/OS®.)
   
5. On the Alter Partitioned Table (ADB21TAV) panel, issue the ADD primary command to add a row with the next partition number.
6. Enter the limit key value for the new partition.
7. 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.

8. On the Alter Table - Utilities (ADB21TAU) panel, specify any utilities that you want to run, and press Enter.
9. Follow the instructions on any subsequent utility panels.

   If you requested any utility operations, a utility job is generated.

10. If a utility job is generated, submit that job to run the requested utilities.

To insert a partition into a table:

1. On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
2. On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
3. On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
4. On the Alter Table (ADB21TA) panel, type an S before ADD/INSERT/ALTER PARTITION and press Enter.
   (This option is displayed only if applicable. The INSERT option is displayed only if you are running Db2 12 for z/OS.)
5. 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 *******************************

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

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

8. Specify YES in the REORG field and any other options that you want, and press Enter.
9. Follow the instructions on the subsequent panels to generate a REORG job.
10. Submit the REORG job to complete the process of inserting a new partition.

To alter a partition:

1. On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
2. On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
3. On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
4. On the Alter Table (ADB21TA) panel, type an S before ADD/INSERT/ALTER PARTITION and press Enter.
   (This option is displayed only if applicable. The INSERT option is displayed only if you are running Db2 12 for z/OS.)
   
5. On the Alter Partitioned Table (ADB21TAV) panel, change the limit key values for any of the partitions.
6. Issue the NEXT primary command to run the ALTER TABLE statement to alter the partitions with their new values.

To rotate a partition:

1. On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
2. On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
3. On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
4. On the Alter Table (ADB21TA) panel, type an S before ADD/INSERT/ALTER PARTITION and press Enter.
   (This option is displayed only if applicable. The INSERT option is displayed only if you are running Db2 12 for z/OS.)
   
5. On the Alter Partitioned Table (ADB21TAV) panel, issue the ROTATE primary command.
6. On the Alter Table (ADB21TAR) panel, enter the limit key value for the rotated partition, and press Enter.
7. On the Alter Partitioned Table (ADB21TAV) panel, issue the NEXT command.
8. 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.

9. 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;

To drop a column:

1. On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
2. On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
3. On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
4. 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              
    S DROP COLUMN                             DROP MATERIALIZED QUERY             
      ADD PRIMARY KEY                         REFRESH MATERIALIZED TABLE          
      DROP PRIMARY KEY                        ADD PARTITIONING KEY                
      ADD FOREIGN KEY                         ADD/INSERT/ALTER 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 MASK                    
      DEACTIVATE ROW ACCESS CONTROL                                               
      ACTIVATE COLUMN ACCESS CONTROL  
      DEACTIVATE COLUMN ACCESS CONTROL

   

   Restriction: 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.
5. 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.

To add or change a key label:

1. On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
2. On the System Catalog (ADB21) panel, enter any selection criteria at the bottom, specify option T, and press Enter.
3. On the Tables, Views, and Aliases (ADB21T) panel, issue the AL line command against the table that you want to alter.
4. 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              
       DROP COLUMN                             DROP MATERIALIZED QUERY             
       ADD PRIMARY KEY                         REFRESH MATERIALIZED TABLE          
       DROP PRIMARY KEY                        ADD PARTITIONING KEY                
       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 MASK                    
       DEACTIVATE ROW ACCESS CONTROL           ENABLE ARCHIVE                      
       ACTIVATE COLUMN ACCESS CONTROL          DISABLE ARCHIVE                     
       DEACTIVATE COLUMN ACCESS CONTROL                                     

   Note: 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.

5. Press Enter to run the ALTER TABLE statement with the new key label value.
6. To materialize this key label change and encrypt the data set, run the REORG TABLESPACE utility on the table space that contains the table.