DB2 ADD a new COLUMN to a table with DEFAULT value from an EXPRESSION using existing data in another column

Question

ALTER TABLE my_table ADD COLUMN new_column_name data_type DEFAULT expression;

Is it possible to use an existing column data as default value for a new column in DB2?

ALTER TABLE employees ADD COLUMN work_hours INT DEFAULT (man_days);

Or an expression like this for the default value?

ALTER TABLE employees ADD COLUMN work_hours INT DEFAULT ( CASE WHEN salary < 50000 THEN (man_days + 10) * 24
   WHEN salary >= 50000 THEN (man_days) * 24 END);

Answer 1

With currently shipping versions of IBM Db2, regardless of the platform, you cannot use an ALTER TABLE statement to specify an expression-based default value.

There are minor differences between the Db2-platforms (LUW, Z/OS, i) for the syntax of the default-clause, but all limit the options to be based on null or special registers or constants.

You can achieve what you need by a combination of DDL and subsequent SQL statements.

Db2-LUW alter table

DB2-for-Z/OS alter table

Db2-for-i alter table