refactions1.out

derby database source code.good for you.

ij> select * from db2test.secondemp order by dno, name, mgrname;
C0         |NAME      |MGRNAME   |DNO 
--------------------------------------
1          |ASHOK     |NULL      |K51 
ij> select * from db2test.emp2 order by dno, name, mgrname;
C0         |NAME      |MGRNAME   |DNO 
--------------------------------------
ij> -- "END OF TESTUNIT: 08";
-- *************************************************************************
-- TESTUNIT         : 09
-- DESCRIPTION      : delete on view with SQ GB Having SQ on
--                  : modified table -- 3 levels
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 09";
-- reset to original rows
delete from db2test.emp;
0 rows inserted/updated/deleted
ij> delete from db2test.emp2;
0 rows inserted/updated/deleted
ij> delete from db2test.dept;
0 rows inserted/updated/deleted
ij> delete from db2test.secondemp;
1 row inserted/updated/deleted
ij> insert into db2test.dept select * from db2test.origdept;
3 rows inserted/updated/deleted
ij> insert into db2test.emp select * from db2test.origemp;
20 rows inserted/updated/deleted
ij> insert into db2test.secondemp select * from db2test.origemp;
20 rows inserted/updated/deleted
ij> insert into db2test.emp2 select * from db2test.origemp;
20 rows inserted/updated/deleted
ij> create view db2test.vempjoin (vname1, vname2, vmgrname, vdno) as
  select e.name, e2.name, e.mgrname, e.dno
  from db2test.emp e, db2test.emp2 e2
  where e.dno = e2.dno;
0 rows inserted/updated/deleted
ij> commit;
ij> select * from db2test.dept where dno in (select vdno from
  db2test.vempjoin)
  and dno in ('K55', 'K52') order by 2, 3;
C0         |DNO|DNAME     
--------------------------
2          |K52|OFC       
1          |K55|DB        
ij> delete from db2test.dept where dno in (select vdno from
  db2test.vempjoin)
  and dno in ('K55', 'K52');
2 rows inserted/updated/deleted
ij> select * from db2test.dept order by dno, dname;
C0         |DNO|DNAME     
--------------------------
3          |K51|CS        
ij> select * from db2test.emp order by dno, name, mgrname;
C0         |NAME      |MGRNAME   |DNO 
--------------------------------------
1          |ASHOK     |NULL      |K51 
4          |JOE1      |ASHOK     |K51 
5          |JOE2      |ASHOK     |K51 
2          |JOHN      |ASHOK     |K51 
3          |ROBIN     |ASHOK     |K51 
ij> select * from db2test.secondemp order by dno, name, mgrname;
C0         |NAME      |MGRNAME   |DNO 
--------------------------------------
1          |ASHOK     |NULL      |K51 
4          |JOE1      |ASHOK     |K51 
5          |JOE2      |ASHOK     |K51 
2          |JOHN      |ASHOK     |K51 
3          |ROBIN     |ASHOK     |K51 
11         |ROGER     |ROBIN     |K52 
16         |GUY       |JOHN      |K55 
6          |HAMID     |JOHN      |K55 
ij> select * from db2test.emp2 order by dno, name, mgrname;
C0         |NAME      |MGRNAME   |DNO 
--------------------------------------
1          |ASHOK     |NULL      |K51 
4          |JOE1      |ASHOK     |K51 
5          |JOE2      |ASHOK     |K51 
2          |JOHN      |ASHOK     |K51 
3          |ROBIN     |ASHOK     |K51 
ij> -- "END OF TESTUNIT: 09";
-- *************************************************************************
-- TESTUNIT         : 10
-- DESCRIPTION      : delete on iudt where SQ on
--                  : view with join on 15 child tables
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 10";
-- reset to original rows
delete from db2test.emp;
5 rows inserted/updated/deleted
ij> delete from db2test.emp2;
5 rows inserted/updated/deleted
ij> delete from db2test.dept;
1 row inserted/updated/deleted
ij> delete from db2test.secondemp;
1 row inserted/updated/deleted
ij> insert into db2test.dept select * from db2test.origdept;
3 rows inserted/updated/deleted
ij> insert into db2test.emp select * from db2test.origemp;
20 rows inserted/updated/deleted
ij> insert into db2test.secondemp select * from db2test.origemp;
20 rows inserted/updated/deleted
ij> insert into db2test.emp2 select * from db2test.origemp;
20 rows inserted/updated/deleted
ij> -- create a third child table like emp
create table db2test.emp3 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp3 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 4th child table like emp
create table db2test.emp4 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp4 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 5th child table like emp
create table db2test.emp5 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp5 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 6th child table like emp
create table db2test.emp6 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp6 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 7th child table like emp
create table db2test.emp7 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp7 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 8th child table like emp
create table db2test.emp8 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp8 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 9th child table like emp
create table db2test.emp9 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp9 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 10th child table like emp
create table db2test.emp10 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp10 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 11th child table like emp
create table db2test.emp11 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp11 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 12th child table like emp
create table db2test.emp12 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp12 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 13th child table like emp
create table db2test.emp13 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp13 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 14th child table like emp
create table db2test.emp14 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp14 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> -- create a 15th child table like emp
create table db2test.emp15 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
0 rows inserted/updated/deleted
ij> insert into db2test.emp15 select * from db2test.emp;
20 rows inserted/updated/deleted
ij> create view db2test.vempjoin12 (vname1, vname2, vname3, vname4, vname5,
   vname6, vname7, vname8, vname9, vname10, vname11, vname12,
   vmgrname, vdno) as
  select e.name, e2.name, e3.name, e4.name, e5.name, e6.name, e7.name,
   e8.name, e9.name, e10.name, e11.name, e12.name,
   e.mgrname, e.dno
  from db2test.emp e, db2test.emp2 e2, db2test.emp3 e3, db2test.emp4 e4,
  db2test.emp5 e5, db2test.emp6 e6, db2test.emp7 e7, db2test.emp8 e8,
  db2test.emp9 e9, db2test.emp10 e10, db2test.emp11 e11,
  db2test.emp12 e12
  where e.dno = e2.dno
  and e.dno = e2.dno
  and e.dno = e2.dno
  and e.dno = e3.dno
  and e.dno = e4.dno
  and e.dno = e5.dno
  and e.dno = e6.dno
  and e.dno = e7.dno
  and e.dno = e8.dno
  and e.dno = e9.dno
  and e.dno = e10.dno
  and e.dno = e11.dno
  and e.dno = e12.dno;
0 rows inserted/updated/deleted
ij> commit;
ij> -- FOLLOWING TWO QUERIES HANG IN DERBY NOW ..
-- UNCOMMENT once they pass.
-- select * from db2test.dept where dno in (select vdno from
--  db2test.vempjoin12)
--  and dno in ('K55', 'K52') order by 2, 3;
--delete from db2test.dept where dno in (select vdno from
--  db2test.vempjoin12)
--  and dno in ('K55', 'K52');
select * from db2test.dept order by dno, dname;
C0         |DNO|DNAME     
--------------------------
3          |K51|CS        
2          |K52|OFC       
1          |K55|DB        
ij> select * from db2test.emp order by dno, name, mgrname;
C0         |NAME      |MGRNAME   |DNO 
--------------------------------------
1          |ASHOK     |NULL      |K51 
4          |JOE1      |ASHOK     |K51 
5          |JOE2      |ASHOK     |K51 
2          |JOHN      |ASHOK     |K51 
3          |ROBIN     |ASHOK     |K51 
13         |DAN       |ROGER     |K52 
12         |JIM       |ROGER     |K52 
11         |ROGER     |ROBIN     |K52 
14         |SAM1      |ROGER     |K52 
15         |SAM2      |ROGER     |K52 
10         |BOBBIE    |HAMID     |K55 
17         |DON       |GUY       |K55 
16         |GUY       |JOHN      |K55 
6          |HAMID     |JOHN      |K55 
8          |LARRY1    |HAMID     |K55 
9          |LARRY2    |HAMID     |K55 
19         |LILY1     |GUY       |K55 
20         |LILY2     |GUY       |K55 
18         |MONICA    |GUY       |K55 
7          |TRUONG    |HAMID     |K55 
ij> select * from db2test.secondemp order by dno, name, mgrname;
C0         |NAME      |MGRNAME   |DNO 
--------------------------------------
1          |ASHOK     |NULL      |K51 
4          |JOE1      |ASHOK     |K51 
5          |JOE2      |ASHOK     |K51 
2          |JOHN      |ASHOK     |K51 
3          |ROBIN     |ASHOK     |K51 
13         |DAN       |ROGER     |K52 
12         |JIM       |ROGER     |K52 
11         |ROGER     |ROBIN     |K52 
14         |SAM1      |ROGER     |K52 
15         |SAM2      |ROGER     |K52 
10         |BOBBIE    |HAMID     |K55 
17         |DON       |GUY       |K55 
16         |GUY       |JOHN      |K55 
6          |HAMID     |JOHN      |K55 
8          |LARRY1    |HAMID     |K55 
9          |LARRY2    |HAMID     |K55 
19         |LILY1     |GUY       |K55 
20         |LILY2     |GUY       |K55 
18         |MONICA    |GUY       |K55 
7          |TRUONG    |HAMID     |K55 
ij> select * from db2test.emp2 order by dno, name, mgrname;