Toad World® Forums

Consolide alter column operations into one alter table operation

I posted this a few years ago in the Idea Pond but got no response... is Idea Pond defunct? At any rate please consider the following:

If a configuration parm currently exists that will allow this please let me know (I looked but couldn't find anything). When altering columns of a table using either the alter table function from the browser or as a result of compare, there are times when multiple columns are altered or dropped. As in the example below alter 11 columns from SMALLINT to INTEGER. Currently Toad will build an individual alter table/alter column statement for each column... and in the example after every 3rd alter a REORG TABLE is done to get out of the REORG PENDING status. I had a production table that required this that had 200 million rows.... so multiple reorgs would have been done if I had ran what was produced by the alter as-is.... the reorg took approximately 1 hour... so instead of a 1 hour change it would have turned into a 4 hour change. So instead of multiple alter "table/alter column reorg" my request is to build just 1 alter statement followed by a reorg.

i.e.

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL1 SET DATA TYPE INTEGER

ALTER COLUMN COL2 SET DATA TYPE INTEGER

ALTER COLUMN COL3 SET DATA TYPE INTEGER

ALTER COLUMN COL4 SET DATA TYPE INTEGER

ALTER COLUMN COL5 SET DATA TYPE INTEGER

ALTER COLUMN COL6 SET DATA TYPE INTEGER

ALTER COLUMN COL7 SET DATA TYPE INTEGER

ALTER COLUMN COL8 SET DATA TYPE INTEGER

ALTER COLUMN COL9 SET DATA TYPE INTEGER

ALTER COLUMN COL10 SET DATA TYPE INTEGER

ALTER COLUMN COL11 SET DATA TYPE INTEGER

/

REORG TABLE SCHEMA1.TB_IS_TEST_TABLE
/

This is what is currently built:

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL1 SET DATA TYPE INTEGER/

COMMIT/

REORG TABLE SCHEMA1.TB_IS_TEST_TABLE/

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL2 SET DATA TYPE INTEGER/

COMMIT/

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL3 SET DATA TYPE INTEGER/

COMMIT/

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL4 SET DATA TYPE INTEGER/

COMMIT/

REORG TABLE SCHEMA1.TB_IS_TEST_TABLE/

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL5 SET DATA TYPE INTEGER/

COMMIT/

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL6 SET DATA TYPE INTEGER/

COMMIT/

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL7 SET DATA TYPE INTEGER/

COMMIT/

REORG TABLE SCHEMA1.TB_IS_TEST_TABLE/

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL8 SET DATA TYPE INTEGER/

COMMIT/

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL9 SET DATA TYPE INTEGER/

COMMIT/

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL10 SET DATA TYPE INTEGER/

COMMIT/

REORG TABLE SCHEMA1.TB_IS_TEST_TABLE/

ALTER TABLE SCHEMA1.TB_IS_TEST_TABLE

ALTER COLUMN COL11 SET DATA TYPE INTEGER/

COMMIT/

REORG TABLE SCHEMA1.TB_IS_TEST_TABLE/

COMMIT/

FYI Running on Toad 7.0.7.106 - hitting DB2 LUW 10.5

Kevin,
The Idea Pond exists - I see your original idea is still on it at:


...however our pond does not get as much traffic as other ponds.
I have opened task TMB-1560 to track your request.
Thanks,
Jeff