db2compatibility.sql

derby database source code.good for you.

-- beetle 5251
CREATE TABLE testsetconst1 (col1 CHAR(7) NOT NULL, PRIMARY KEY(col1));
CREATE TABLE testsetconst2 (col1 char(7) NOT NULL, CONSTRAINT fk FOREIGN KEY(col1) REFERENCES testsetconst1(col1));
SET CONSTRAINTS fk DISABLED;
SELECT STATE FROM SYS.SYSCONSTRAINTS;
SET CONSTRAINTS fk ENABLED;
SELECT STATE FROM SYS.SYSCONSTRAINTS;
SET CONSTRAINTS ALL DISABLED;
SELECT STATE FROM SYS.SYSCONSTRAINTS;
SET CONSTRAINTS FOR testsetconst1 ENABLED;
SELECT STATE FROM SYS.SYSCONSTRAINTS;

-- clean up
DROP TABLE testsetconst1;
DROP TABLE testsetconst2;

-- CALL statement
-- beetle 5252
call org.apache.derby.iapi.db.Factory::getDatabaseOfConnection().dropAllJDBCMetaDataSPSes();

-- Beetle 5203: DB2 restricts what can be used for default clauses, and enforces
-- constraints on the default clause that Cloudscape does not.
-- Following should be okay:
create table deftest1 (i int default 1);
create table deftest2 (vc varchar(30) default 'howdy');
create table deftest21 (vc clob(10) default 'okie');
create table deftest3 (d date default current date);
create table deftest31 (d date default '2004-02-08');
create table deftest5 (vc char(130) default current schema);
create table deftest4 (c char(130) default user);
create table deftest6 (d decimal(5,2) default null);
create table deftest7 (d decimal(5,2) default 123.450);
create table deftest8 (f float default 1.234);
-- make sure they actually work @ insertion.
insert into deftest1 values (default);
insert into deftest2 values (default);
insert into deftest21 values (default);
insert into deftest3 values (default);
insert into deftest31 values (default);
insert into deftest4 values (default);
insert into deftest5 values (default);
insert into deftest6 values (default);
insert into deftest7 values (default);
insert into deftest8 values (default);
-- cleanup.
drop table deftest1;
drop table deftest2;
drop table deftest21;
drop table deftest3;
drop table deftest31;
drop table deftest4;
drop table deftest5;
drop table deftest6;
drop table deftest7;
drop table deftest8;
-- Beetle 5203, con't: following should all fail (though they'd pass in Cloudscape mode).
-- expressions:
create table deftest1 (vc varchar(30) default java.lang.Integer::toBinaryString(3));
create table deftest2 (i int default 3+4);
-- floating point assignment to non-float column.
create table deftest3 (i int default 1.234);
-- decimal value with too much precision.
create table deftest4 (d decimal(5,2) default 1.2234);
-- char constant longer than 254.
create table deftest5 (vc varchar(300) default 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
-- function calls (built-in and other) should fail with error 42894 (NOT with 42X01), to match DB2.
create table t1 (i int default abs(0));
create table t1 (i int default someFunc('hi'));
-- Type mismatches should fail with 42894 (NOT with 42821), to match DB2.
create table t1 (i int default 'hi');

-- Beetle 5281: <cast-function> for a default.
-- Date-time functions (DATE, TIME, and TIMESTAMP)
create table t1a (d date default date(current date));
create table t1b (d date default date('1978-03-22'));
create table t2a (t time default time(current time));
create table t2b (t time default time('08:28:08'));
create table t3a (ch timestamp default timestamp(current timestamp));
create table t3b (ts timestamp default timestamp('2004-04-27 08:59:02.91'));
-- BLOB function (not yet supported).
create table t4 (b blob default blob('nope'));
-- cleanup.
drop table t1a;
drop table t1b;
drop table t2a;
drop table t2b;
drop table t3a;
drop table t3b;

-- DROP constraint syntax that should be supported in db2 compat mode:
-- beetle 5204
CREATE TABLE testconst1 (col1 CHAR(7) NOT NULL, col2 int CONSTRAINT cc CHECK(col2 > 1), PRIMARY KEY(col1));
CREATE TABLE testconst2 (col1 char(7) NOT NULL, col2 char(7) NOT NULL, col3 int, CONSTRAINT fk FOREIGN KEY(col1) REFERENCES testconst1(col1), CONSTRAINT uk UNIQUE (col2));
-- DROP FOREIGN KEY syntax should be supported in DB2 compat mode
insert into testconst1( col1, col2) values( 'a', 2);
insert into testconst1( col1, col2) values( 'a', 2);
insert into testconst1( col1, col2) values( 'b', 0);

insert into testconst2( col1, col2, col3) values( 'a', 'a', 1);
insert into testconst2( col1, col2, col3) values( 'z', 'b', 1);
insert into testconst2( col1, col2, col3) values( 'a', 'a', 1);

-- beetle 5204
ALTER TABLE testconst1 DROP FOREIGN KEY cc;
ALTER TABLE testconst2 DROP UNIQUE fk;
ALTER TABLE testconst2 DROP CHECK fk;

ALTER TABLE testconst2 DROP FOREIGN KEY fk;
-- DROP PRIMARY KEY syntax should be supported in DB2 compat mode
-- beetle 5204
ALTER TABLE testconst1 DROP PRIMARY KEY;
-- DROP UNIQUE KEY syntax should be supported in DB2 compat mode
-- beetle 5204
ALTER TABLE testconst2 DROP UNIQUE uk;
-- DROP CHECK condition syntax should be supported in DB2 compat mode
-- beetle 5204
ALTER TABLE testconst1 DROP CHECK cc;

insert into testconst1( col1, col2) values( 'a', 2);
insert into testconst1( col1, col2) values( 'b', 0);

insert into testconst2( col1, col2, col3) values( 'z', 'b', 1);
insert into testconst2( col1, col2, col3) values( 'a', 'a', 1);

ALTER TABLE testconst2 DROP FOREIGN KEY noSuchConstraint;
ALTER TABLE testconst2 DROP CHECK noSuchConstraint;
ALTER TABLE testconst2 DROP UNIQUE noSuchConstraint;
ALTER TABLE testconst1 DROP PRIMARY KEY;

-- clean up
DROP TABLE testconst1;
DROP TABLE testconst2;

-- CREATE TRIGGERS
-- beetle 5253
CREATE TABLE tb1 (col1 int, col2 int, col3 int, constraint chk1 check (col1 > 0));
CREATE TABLE tb2 (col1 char(30), c2 int, c3 int);
-- change syntax of before to "NO CASCADE BEFORE"
CREATE TRIGGER testtrig1 NO CASCADE BEFORE UPDATE OF col1,col2 on tb1 FOR EACH ROW MODE DB2SQL VALUES 1;
CREATE TRIGGER testtrig2 AFTER UPDATE on tb1
REFERENCING OLD as oldtable FOR EACH ROW MODE DB2SQL INSERT INTO tb2 VALUES ('tb', oldtable.col1, oldtable.col2);
CREATE TRIGGER testtrig3 AFTER UPDATE on tb1
REFERENCING OLD as oldtable FOR EACH ROW MODE DB2SQL INSERT INTO tb2 VALUES ('tb', oldtable.col1, oldtable.col2);
-- clean up
DROP TRIGGER testtrig1;
DROP TRIGGER testtrig2;
DROP TRIGGER testtrig3;
DROP TABLE tb1;
DROP TABLE tb2;

-- SET TRANSACTION ISOLATION LEVEL
-- beetle 5254
-- these SET TRANSACTION ISOLATION statements fail in db2 compat mode because it has cloudscape specific syntax
create table t1(c1 int not null constraint asdf primary key);
insert into t1 values 1;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- clean up
drop table t1;

-- statements should pass in db2 compat mode
-- beetle 5260
autocommit off;
create table t1(c1 int not null constraint asdf primary key);
commit;
insert into t1 values 1;
-- verify SET TRANSACTION ISOLATION commits and changes isolation level
set isolation serializable;
-- rollback should find nothing to undo
rollback;
select * from t1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- verify SET TRANSACTION ISOLATION commits and changes isolation level
set isolation read committed;
-- rollback should find nothing to undo
rollback;
select * from t1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- verify SET TRANSACTION ISOLATION commits and changes isolation level
set isolation repeatable read;
-- rollback should find nothing to undo
rollback;
select * from t1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- verify SET TRANSACTION ISOLATION commits and changes isolation level
set isolation read uncommitted;
-- rollback should find nothing to undo
rollback;
select * from t1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
drop table t1;

-- SET ISOLATION statement
-- beetle 5260
-- set isolation statement that are supported in db2
create table t1(c1 int not null constraint asdf primary key);
insert into t1 values 1;
set isolation serializable;
set isolation read committed;
set isolation repeatable read;
set isolation read uncommitted;
-- clean up
drop table t1;

-- SELECT statement testing
-- beetle 5255

CREATE TABLE t1(col1 int, col2 int);
CREATE TABLE t2(col1 int, col2 int);
INSERT INTO t1 VALUES(3,4);
INSERT INTO t2 VALUES(3,4);

-- CROSS JOIN not supported in both Cloudscape and DB2 mode and that is why rather than getting feature not implemented, we will get syntax error
-- (1) CROSS JOIN should be disabled in FROM clause of SELECT statement
SELECT * FROM t1 CROSS JOIN t2;

-- (2) USING should be disabled in INNER JOIN of SELECT statement
SELECT * FROM t1 INNER JOIN t2 USING (col1);

-- (3) USING should be disabled in INNER JOIN of SELECT statement
SELECT * FROM t1 LEFT OUTER JOIN t2 USING (col1);

-- (4) USING should be disabled in INNER JOIN of SELECT statement
SELECT * FROM t1 RIGHT OUTER JOIN t2 USING (col1);

-- (5) TRUE and FALSE constants should be disabled in WHERE clause of SELECT statement
SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1 WHERE true;
SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1 WHERE false;

-- (5) TRUE and FALSE constants should be disabled in WHERE clause of DELETE statement
DELETE FROM t1 where true;
DELETE FROM t1 where false;

-- (5) TRUE and FALSE constants should be disabled in WHERE clause of DELETE statement
UPDATE t2 SET col1 = NULL WHERE true;
UPDATE t2 SET col1 = NULL WHERE false;

-- (6) AT ISOLATION clause should be disabled in SELECT statement
-- AT ISOLATION not supported in both Cloudscape and DB2 mode and that is why rather than getting feature not implemented, we will get syntax error
SELECT * FROM t1 AT ISOLATION READ UNCOMMITTED;
SELECT * FROM t1 AT ISOLATION READ COMMITTED;
SELECT * FROM t1 AT ISOLATION SERIALIZABLE;
SELECT * FROM t1 AT ISOLATION REPEATABLE READ;

-- clean up
DROP TABLE t1;
DROP TABLE t2;

-- DEFAULT CAST not supported in both Cloudscape and DB2 mode and that is why rather than getting feature not implemented, we will get syntax error
create table testuser(col1 BLOB(3K) default cast(user as blob(3k)));
create table testsessionuser(col1 BLOB(3K) default cast(session_user as blob(3k)));
create table testcurrentuser(col1 BLOB(3K) default cast(current_user as blob(3k)));
create table testschema(col1 BLOB(3K) default cast(current schema as blob(3k)));

-- alter table syntax that should be supported in db2 compat mode
-- beetle 5267
create table testmodify (col1 varchar(30), col2 int generated always as identity);
-- increasing the length of the varchar column
alter table testmodify alter col1 set data type varchar(60);
-- specifying the interval between consecutive values of col2, the identity column 
alter table testmodify alter col2 set increment by 2;
-- clean up
drop table testmodify;

-- (1) adding more than one column 
-- beetle 5268
-- db2 compat mode should support the following statements
create table testaddcol (col1 int);
alter table testaddcol add column col2 int add col3 int;
drop table testaddcol;

-- (2) adding more than one unique, referential, or check constraint 
-- beetle 5268
-- db2 compat mode should support the following statements
create table testaddconst1 (col1 int not null primary key, col2 int not null unique);
create table testaddconst2 (col1 int not null primary key, col2 int not null unique);
create table testaddconst3 (col1 int not null, col2 int not null, col3 int not null, col4 int not null, col5 int, col6 int);
create table testaddconst4 (col1 int not null, col2 int not null, col3 int not null, col4 int not null, col5 int, col6 int);
-- adding more than one unique-constraint 
alter table testaddconst3 add primary key (col1) add unique (col2);
alter table testaddconst3 add unique (col3) add unique (col4);
-- adding more than one referential-constraint 
alter table testaddconst3 add foreign key (col1) references testaddconst1(col1) add foreign key (col2) references testaddconst2(col2);
-- adding more than one check-constraint 
alter table testaddconst3 add check (col5 is null) add check (col6 is null);
-- adding a primary, unique, foreign key, and  check-constraint 
alter table testaddconst4 add primary key(col1) add unique(col2) add foreign key (col1) references testaddconst1(col1) add check (col2 is null);
-- clean up
drop table testaddconst1;
drop table testaddconst2;
drop table testaddconst3;
drop table testaddconst4;

-- (3) adding more than one unique, referential, or check constraints 
-- beetle 5268
-- syntax that will be supported in db2 compat mode (beetle 5204)
CREATE TABLE testdropconst1 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk1 UNIQUE , PRIMARY KEY(col1));
CREATE TABLE testdropconst2 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk2 UNIQUE, col3 CHAR(5) not null CONSTRAINT uk3 UNIQUE, PRIMARY KEY(col1));
CREATE TABLE testdropconst3 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk4 UNIQUE , PRIMARY KEY(col1));
CREATE TABLE testdropconst4 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk5 UNIQUE , PRIMARY KEY(col1));
CREATE TABLE testdropconst5 (col1 CHAR(7) NOT NULL, col2 int, col3 CHAR(5) not null, CONSTRAINT fk1 FOREIGN KEY (col1) REFERENCES testdropconst3(col1), CONSTRAINT fk2 FOREIGN KEY (col1) REFERENCES testdropconst4(col1));
CREATE TABLE testdropconst6 (col1 CHAR(7) CONSTRAINT ck1 CHECK (col1 is null), col2 int CONSTRAINT ck2 CHECK (col2 is null));
-- dropping more than one unique-constraint 
alter table testdropconst1 drop primary key drop constraint uk1;
alter table testdropconst2 drop primary key drop constraint uk2 drop constraint uk3;
-- dropping more than one foreign key constraint
alter table testdropconst5 drop constraint fk1 drop constraint fk2;
-- dropping more than one check constraint
alter table testdropconst6 drop constraint ck1 drop constraint ck2;
--clean up
drop table testdropconst1;
drop table testdropconst2;
drop table testdropconst3;
drop table testdropconst4;
drop table testdropconst5;
drop table testdropconst6;

-- (4) altering more than one column
-- beetle 5268
-- syntax that will be supported in db2 compat mode (beetle 5267)
-- db2 compat mode should support 
create table testmodify (col1 varchar(30), col2 varchar(30));
alter table testmodify alter col1 set data type varchar(60) alter col2 set data type varchar(60);
-- clean up
drop table testmodify;

-- number of values assigned in an INSERT statement should be the same as the number of specified or implied columns 
-- beetle 5269
create table t1(a int, b int, c char(10));
-- this statement should throw an error in db2 compat mode, but it does not
insert into t1 values(1);

-- clean up
drop table t1;

-- beetle 5281
-- These statements are successful in DB2 UDB v8, but not in Cloudscape
-- Cloudscape does not support cast-functions such as blob, timestamp, time, and date
-- DB2 does support cast functions such as these below:
create table t1 (ch blob(10));
insert into t1 values (blob('hmm'));
create table t2 (ch timestamp);
insert into t2 values (timestamp(current timestamp));
create table t3 (ch time);
insert into t3 values (time(current time));
create table t4 (ch date);
insert into t4 values (date(current date));
drop table t1;
drop table t2;
drop table t3;
drop table t4;

-- test operands
-- beetle 5282
-- <,> =, !=, <=, >= operands are not supported in db2 but supported in cloudscape
CREATE TABLE testoperatorclob (colone clob(1K));
INSERT INTO testoperatorclob VALUES (CAST('50' AS CLOB(1K)));
select * from testoperatorclob;
-- these select statements should raise an error but are successful in cloudscape
select * from testoperatorclob where colone > 10;
select * from testoperatorclob where colone < 70;
select * from testoperatorclob where colone = 50;
select * from testoperatorclob where colone != 10;
select * from testoperatorclob where colone <= 70;
select * from testoperatorclob where colone >= 10;
select * from testoperatorclob where colone <> 10;
drop table testoperatorclob;

-- beetle 5282
CREATE TABLE testoperatorblob (colone clob(1K));
INSERT INTO testoperatorblob VALUES (CAST('50' AS BLOB(1K)));
select * from testoperatorblob;
-- these select statements should raise an error but are successful in cloudscape
select * from testoperatorblob where colone > 10;
select * from testoperatorblob where colone < 999999;
select * from testoperatorblob where colone = 00350030;
select * from testoperatorblob where colone != 10;
select * from testoperatorblob where colone <= 999999;
select * from testoperatorblob where colone >= 10;
select * from testoperatorblob where colone <> 10;
drop table testoperatorblob;

-- beetle 5283
-- casting using "X" for hex constant, "B" literal is not allowed in DB2
-- db2 raises ERROR 56098, cloudscape should raise error msg?a
values cast(B'1' as char(100));
values cast(B'1' as clob(1M));
values cast(B'1' as blob(1M));
values cast(X'11' as char(100));
values cast(X'11' as clob(1M));
values cast(X'11' as blob(1M));

-- beetle 5284
-- minor difference in outputs when casting to blob in Cloudscape and DB2. 
values cast('   ' as blob(1M));