-- tests for drop table--autocommit off;---- test simple table - all should work--create table t1 ( a int);drop table t1;-- t1 shouldn't be foundselect * from t1;create table t1 (a int);drop table t1;-- t1 shouldn't be foundselect * from t1;create table t1 (a int);drop table t1;-- t1 shouldn't be foundselect * from t1;---- test table with unique constraint - all should work--create table t1 (a int not null unique);drop table t1;-- t1 shouldn't be foundselect * from t1;create table t1 (a int not null unique);drop table t1;-- t1 shouldn't be foundselect * from t1;create table t1 (a int not null unique);drop table t1;-- t1 shouldn't be foundselect * from t1;---- test table with primary constraint - all should work--create table t1 ( a int not null primary key);drop table t1;-- t1 shouldn't be foundselect * from t1;create table t1 ( a int not null primary key);drop table t1;-- t1 shouldn't be foundselect * from t1;create table t1 ( a int not null primary key);drop table t1;-- t1 shouldn't be foundselect * from t1;---- test table with check constraint - all should work--create table t1 ( a int check(a > 0));drop table t1;-- t1 shouldn't be foundselect * from t1;create table t1 ( a int check(a > 0));drop table t1;-- t1 shouldn't be foundselect * from t1;create table t1 ( a int check(a > 0));drop table t1;-- t1 shouldn't be foundselect * from t1;---- test table with index - all should work--create table t1 ( a int);create index t1index on t1(a);drop table t1;-- t1 shouldn't be foundselect * from t1;create table t1 (a int);create index t1index on t1(a);drop table t1;-- t1 shouldn't be foundselect * from t1;create table t1 (a int);create index t1index on t1(a);drop table t1;-- t1 shouldn't be foundselect * from t1;---- test table with foreign key references;--create table t1(a int not null primary key);create table t2(a int constraint reft1a references t1(a));-- this should fail with a dependent constraint errordrop table t1;-- this should fail with a dependent constraint errordrop table t1;-- dropping dependent constraintalter table t2 drop constraint reft1a;-- this should work since dependent constraint was droppeddrop table t1;-- t1 shouldn't be foundselect * from t1;-- the following should work since no referential constraint is leftinsert into t2 values(1);drop table t2;---- test table with view--create table t1(a int, b int);create table t2(c int, d int);create view vt1a as select a from t1;create view vt1b as select b from t1;create view vt1t2 as select * from t1, t2;create view vvt1a as select * from vt1a;create view vvvt1a as select * from vvt1a;-- this should fail with view being a dependent objectdrop table t1;-- this should fail with view being a dependent objectdrop table t1;-- dropping dependent viewsdrop view vvvt1a;drop view vvt1a;drop view vt1t2;drop view vt1b;drop view vt1a;-- this should work after dependent views were droppeddrop table t1;-- this shouldn't find the viewselect * from vt1a;select * from vt1b;select * from vt1t2;select * from vvt1a;select * from vvvt1a;drop table t2;---- test table with prepared statement--create table t1(a int);prepare t1stmt as 'select * from t1';-- this should work, statement will be invalidated and will fail when recompileddrop table t1;execute t1stmt;remove t1stmt;create table t1(a int);prepare t1stmt as 'select * from t1';-- this should work, statement will be invalidated and will fail when recompileddrop table t1;execute t1stmt;remove t1stmt;create table t1(a int);prepare t1stmt as 'select * from t1';-- this should work, statement will be invalidated and will fail when recompileddrop table t1;execute t1stmt;remove t1stmt;---- test table with triggers--create table t1(a int);create table t2(a int);create trigger t1trig after insert on t1 for each row mode db2sql insert into t2 values(1);-- this should work - trigger should be deleteddrop table t1;-- t1 shouldn't be foundselect * from t1;create table t1(a int);create trigger t1trig after insert on t1 for each row mode db2sql insert into t2 values(1);-- this should work - trigger should be deleteddrop table t1;-- t1 shouldn't be foundselect * from t1;create table t1(a int);create trigger t1trig after insert on t1 for each row mode db2sql insert into t2 values(1);-- this should work - trigger should be deleteddrop table t1;-- t1 shouldn't be foundselect * from t1;drop table t2;---- test table within the body of a trigger on another table--create table t1(a int);create table t2(a int);create trigger t2trig after insert on t2 for each row mode db2sql insert into t1 values(1);-- this should workdrop table t1;-- the following should get an error when trying to recompile the trigger actioninsert into t2 values(1);drop table t2;create table t1(a int);create table t2(a int);create trigger t2trig after insert on t2 for each row mode db2sql insert into t1 values(1);-- this should workdrop table t1;-- the following should get an error when trying to recompile the trigger actioninsert into t2 values(1);drop table t2;create table t1(a int);create table t2(a int);create trigger t2trig after insert on t2 for each row mode db2sql insert into t1 values(1);-- this should workdrop table t1;-- the following should get an error when trying to recompile the trigger actioninsert into t2 values(1);drop table t2;---- test drop view--create table t1(a int);create view vt1 as select * from t1;create view vvt1 as select * from vt1;-- these should faildrop view vt1;drop view vt1 restrict;drop view vt1 cascade;-- -- make sure that indexes are dropped for drop table--create table t2(a int not null primary key);create table reft2(a int constraint ref1 references t2);-- count should be 2select count(*) from sys.sysconglomerates c, sys.systables twhere t.tableid = c.tableid andt.tablename = 'REFT2';-- drop dependent referential constraintalter table reft2 drop constraint ref1;-- should work since dependent constraint was previously droppeddrop table t2;-- count should be 1select count(*) from sys.sysconglomerates c, sys.systables twhere t.tableid = c.tableid andt.tablename = 'REFT2';-- unsuccessful drop table should not affect open cursor-- beetle 4393rollback;create table T1 (i int, c varchar(255), d varchar(255));insert into T1(i) values(1);insert into T1(i) values(2);get cursor X1 as 'select i from t1 for update of c';prepare U as 'update t1 set c = CHAR(i) where current of X1';next X1;drop table T1;execute U;select * from T1;-- pretend all of the above didn't happenautocommit on;