Toad is always trying to drop table when adding a column with not null.
The issue is that when an alter table is performed, Toad is creating the script with drop table. It's possible write script with ALTER TABLE ADD COLUMN ... NOT NULL DEFAULT '';
It's necessary to use option DEFAULT to add column in DB2 using ALTER TABLE command with NOT NULL option. Without this option the following error will raise:
[db2inst1@DB2 ~]$ db2 "ALTER TABLE db2inst1.test ADD COLUMN tester VARCHAR(10) NOT NULL"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0193N In an ALTER TABLE statement, the column "TESTER" has been specified
as NOT NULL and either the DEFAULT clause was not specified or was specified
as DEFAULT NULL. SQLSTATE=42601
With DEFAULT option, everything is OK:
[db2inst1@DB2 ~]$ db2 "ALTER TABLE db2inst1.test ADD COLUMN tester VARCHAR(10) NOT NULL DEFAULT ''"
DB20000I The SQL command completed successfully.
Toad for DB2 manages it this way: It's possible define table with NOT NULL option without DEFAULT value only when new table is created. It means, that Toad makes a copy of that table, drops the table, creates it again and loads data into this table again.
To specify ALTER TABLE command with DEFAULT option, Toad will manage it using ALTER TABLE command.
The DEFAULT option can be defined in table following way:
1) Click on added column. In the bottom pane you will see definition of that column.
2) Go to "Options", click drop down menu for "Data Generation" and select "User Default".
3) Option "Default" will appear under this "Data Generation" line.
4) Type default value into "Default" line.
5) Toad will generate "ALTER TABLE..." as expected.
