Preserving Rows

A global temporary table created with ON COMMIT PRESERVE ROWS cannot be altered or dropped from a secondary node if the instance of the global temporary table on the connection of the secondary node executing the drop table has data.

Truncate the table and try again or alter or drop the table from the coordinator node. If the global temporary table is created with ON COMMIT DELETE ROWS, you may alter or drop the table even if it has rows.

For example, connect to a secondary node:

CREATE GLOBAL TEMPORARY TABLE 
foo_gtt(c1 int) 
ON COMMIT PRESERVE ROWS;
INSERT INTO foo_gtt VALUES(200);
COMMIT;
DROP TABLE foo_gtt;

The drop statement fails with the following error:

Operation not allowed on global temporary
table foo_gtt as it is in use. Please reconnect and 
try again. SQLCODE=1287. ODBC 3
State="HY000"