Search code examples
sqldb2ibm-midrangealter

AS400 DDL SQL Syntax for Adding a new column with short and long field name

What is the syntax for AS400 SQL DDL to add a column to an existing table? I also need to specify the long and short field names.

(SQL - Structured Query Language) (DDL - Data Definition Language: This is SQL that changes the structure of tables or can modify the settings of the database)

Solution

  • To add a new column:

    ALTER TABLE mytable 
   ADD long_column_name FOR COLUMN shortname VARCHAR(21)
        BEFORE existing_column_name;

    You may leave out the BEFORE clause if you are adding the column at the end of the record.

    Most IBM i shops would also want column text description and column headings defined.

    LABEL ON COLUMN mytable.colname    TEXT IS '50 char description goes here';

LABEL ON COLUMN mytable.colname    IS 'Heading Line 1      Heading Line 2      Heading Line 3';

    Where the column heading string consists of up to 3 section, 20 characters each, allowing the heading to display up to 3 lines high.

    The statement defining the column text description is differentiated by using the TEXT keyword.