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/