How does DB2 alter table statement for adding multiple columns work?

Question

How does DB2 alter table statement for adding multiple columns work? Does it add columns sequentially and drop already added columns if a problem is encountered while adding a column? Is the order of the columns preserved? Is it better to call the alter table statement multiple times, once for each column, performance wise ?

Answer 1

You can add columns multiple in one time like this :

alter table jgrun.tmp          
add column col1 varchar(15)    
add column col2 int default 0  
add column col3 varchar(10) 

You can remove multiple column like this :

alter table jgrun.tmp    
drop  column col1        
drop column col2 

If you must add/drop multiple columns you should use this method for perfomance (better on one by one)

Answer 2

With Db2, the DDL (including alter table) is under transaction control, so if you want to make multiple alterations (create, drop, alter etc.) you can do it in a single unit of work subject to available transaction logging capacity. So multiple DDL statements can be a single atomic action, meaning that if one of them fails the whole transaction gets rolled back.

Depending on the platform that hosts Db2 (Z/OS, i-series, Unix/windows) and Db2 version, there are limits on the number of separate alterations to a table that can happen before Db2 will need a reorg. This can influence the number of ALTER statements you use (e.g. add more than one column in a single statement).

Performance may not be the determining factor for how you group alterations. Recoverability, space considerations, object dependencies, back-out, HA/DR considerations, change-window timings, availability matters, scheduling , code-dependencies, can determine the sequence you use. One size does not fit all.