SQL DDL ALTER

The ALTER statement is used to modify the structure of an existing database object, such as a table, index, or table space, without needing to recreate the object. This statement allows us to add, delete, or modify columns in a table, add constraints, change data types, and adjust other properties.

The ALTER statement allows database administrators and programmers to change the structure of database objects dynamically. Common uses for ALTER include:

• Adding or dropping a column from a table.
• Changing the data type of a column.
• Adding or modifying constraints (e.g., primary key or foreign key).
• Renaming objects like tables or columns.

Syntax - The basic syntax for an ALTER statement in DB2 is as follows:

ALTER TABLE table_name
ADD COLUMN new_column_name data_type
| DROP COLUMN column_name
| ALTER COLUMN column_name SET DATA TYPE new_data_type
| RENAME COLUMN old_column_name TO new_column_name;

• table_name: The name of the table to modify.
• ADD COLUMN new_column_name data_type: Adds a new column with the specified data type.
• DROP COLUMN column_name: Removes a specified column from the table.
• ALTER COLUMN column_name SET DATA TYPE new_data_type: Changes the data type of a specified column.
• RENAME COLUMN old_column_name TO new_column_name: Renames an existing column.

Examples -

ALTER TABLE EMPLOYEE
    ADD COLUMN PHONE_NUMBER CHAR(10);

This statement adds a new column PHONE_NUMBER with data type CHAR(10) to the EMPLOYEE table.

Using ALTER Statements in a COBOL Program

We can't able to use the ALTER statement in COBOL-DB2 program as it is a DDL statement. Only administrators and other users who has ADIM authority can use the ALTER statement in DB2 tools (SPUFI or QMF).

ALTER can be used on the below list of objects -

ALTER ALIAS
ALTER DATABASE
ALTER FUNCTION
ALTER INDEX
ALTER PACKAGE
ALTER PROCEDURE 
ALTER ROLE
ALTER SEQUENCE
ALTER STOGROUP
ALTER SYNONYM
ALTER TABLE
ALTER TABLESPACE
ALTER TRIGGER
ALTER TYPE
ALTER VIEW

---