db2compatibility.sql

derby database source code.good for you.

-- With DB2 current schema is equal to the user name on login.
CREATE TABLE DST.DEF_SCHEMA_TEST(NAME_USER VARCHAR(128), NAME_SCHEMA VARCHAR(128));
INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA);
SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA;
SET SCHEMA DILBERT;

connect 'jdbc:derby:wombat;user=dilbert';
INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA);
SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA;
VALUES CURRENT SCHEMA;
disconnect;

SET CONNECTION CONNECTION0;

-- still should not be created
SET SCHEMA DILBERT;

connect 'jdbc:derby:wombat;user=dilbert';
INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA);
SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA;
VALUES CURRENT SCHEMA;
CREATE TABLE SCOTT(i int);
insert into SCOTT VALUES(4);
disconnect;

SET CONNECTION CONNECTION0;

SELECT * FROM DILBERT.SCOTT;
DROP TABLE DILBERT.SCOTT;
DROP TABLE DST.DEF_SCHEMA_TEST;
DROP SCHEMA DST RESTRICT;
DROP SCHEMA DILBERT RESTRICT;


-- Simple Cloudscape specific features.

-- CLASS ALIAS;

create class alias MyMath for java.lang.Math;
drop class alias MyMath;
create class alias for java.lang.Math;
drop class alias Math;

-- METHOD ALIAS;

create method alias myabs for java.lang.Math.abs;
drop method alias myabs;

-- STORED PREPARED STATEMENTS 
-- create statement no more supported both in db2 and cloudscpae mode. -ve test for that
create statement s1 as values 1,2;
-- alter, drop and execute statements are still supported for existing stored prepared statements for customers
alter statement recompile all;
-- following will give error because there is no stored prepared statement s1 in the database
drop statement s1;

-- clean up
DROP TABLE t1;
DROP TABLE t2;
DROP CLASS ALIAS ExternalInsert;
DROP STATEMENT insert1;

-- Primary key constraint, DB2 requires NOT null on the columns.

create table customer (id int primary key, name char(100));
drop table customer;

create table customer (id  int NOT NULL, id2 int, name char(100), primary key (id, id2));
drop table customer;

-- Unique key constraint, DB2 requires NOT null on the columns.

create table customer (id int unique, name char(100));

create table customer (id  int NOT NULL, id2 int, constraint custCon unique(id, id2));

-- check they actually work!
create table customer (id int NOT NULL primary key, name char(100));
drop table customer;

create table customer (id  int NOT NULL, id2 int NOT NULL, name char(100), primary key (id, id2));
drop table customer;


-- drop schema requires restrict
create schema fred;
drop schema fred;
drop schema fred restrict;

-- create schema not supported for schemas that start with SYS

create schema SYS;
create schema SYSDJD;
create schema "SYSNO";
create schema "sys";
create schema "sysok";
drop schema "sys" restrict;
drop schema "sysok" restrict;

-- data types not supported
create table NOTYPE(i int, b BOOLEAN);
create table NOTYPE(i int, b TINYINT);
create table NOTYPE(i int, b java.lang.String);
create table NOTYPE(i int, b com.acme.Address);
create table NOTYPE(i int, b org.apache.derby.vti.VTIEnvironment);

-- VTI in the DELETE statement
-- beetle 5234
CREATE TABLE testCS (col1 int, col2 char(30), col3 int);
INSERT INTO testCS VALUES (100, 'asdf', 732);
DELETE FROM NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') WHERE col1 = 100 and col3 = 732;

-- VTI in the INSERT statement
-- beetle 5234
INSERT INTO NEW org.apache.derbyTesting.functionTests.util.serializabletypes.ExternalTable('jdbc:derby:wombat', 'testCS') VALUES (100, 'asdf', 732);

-- VTI in the SELECT statement
-- beetle 5234
select * from testCS, new org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI(col1, 1) a;
select * from new com.acme.myVTI() as T;
select * from new org.apache.derbyTesting.not.myVTI() as T;
select * from new org.apache.derby.diag.LockTable() as T;

-- VTI in CREATE TRIGGER statement
-- beetle 5234
CREATE TABLE tb1(a int);
CREATE TRIGGER testtrig1 AFTER DELETE ON tb1 FOR EACH ROW MODE DB2SQL INSERT INTO NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') VALUES (1000);

-- VTI in CREATE TRIGGER statement
-- beetle 5234
CREATE TRIGGER testtrig2 AFTER DELETE ON tb1 FOR EACH ROW MODE DB2SQL DELETE FROM NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') WHERE col1 = 100 and col3 = 732;

-- VTI in CREATE TRIGGER statement
-- beetle 5234
CREATE TRIGGER testtrig3 AFTER DELETE ON tb1 FOR EACH ROW MODE DB2SQL SELECT * FROM testCS, NEW org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI(col1, 1) a;

-- clean up
DROP TABLE tb1;
DROP TABLE testCS;

-- PROPERTIES in DB2 mode
create table maps (country_ISO_code char(2)) PROPERTIES derby.storage.pageSize=262144;

-- PROPERTIES in DB2 mode
-- beetle 5177
create table maps2 (country_ISO_code char(2));
create index map_idx1 on maps2(country_ISO_code) properties derby.storage.pageSize = 2048;
-- BTREE not supported in both Cloudscape and DB2 mode and that is why rather than getting feature not implemented, we will get syntax error in DB2 mode
create btree index map_idx2 on maps2(country_ISO_code);
create unique btree index map_idx2 on maps2(country_ISO_code);
drop table maps2;

-- SET LOCKING clause in DB2 mode
-- beetle 5208
create table maps1 (country_ISO_code char(2)) set locking = table;
create table maps2 (country_ISO_code char(2)) set locking = row;
drop table maps1;
drop table maps2;

-- ALTER TABLE statement
-- beetle 5201

-- Locking syntax
-- negative tests
create table tb1 (country_ISO_code char(2));
alter table tb1 set locking = table;
alter table tb1 set locking = row;

-- Locking syntax 
-- positive tests
-- beetle 5201
create table tb2 (country_ISO_code char(2));
alter table tb2 locksize table;
alter table tb2 locksize row;

-- clean up
drop table tb1;
drop table tb2;

-- VTI in the DELETE statement
-- beetle 5234
CREATE TABLE testCS (col1 int, col2 char(30), col3 int);
INSERT INTO testCS VALUES (100, 'asdf', 732);
DELETE FROM NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') WHERE col1 = 100 and col3 = 732;

-- VTI in the INSERT statement
-- beetle 5234
INSERT INTO NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') VALUES (100, 'asdf', 732);

-- VTI in the SELECT statement
-- beetle 5234
select * from testCS, new org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI(col1, 1) a;

-- VTI in CREATE TRIGGER statement
-- beetle 5234
CREATE TABLE tb1(a int);
CREATE TRIGGER testtrig1 AFTER DELETE ON tb1 FOR EACH ROW MODE DB2SQL INSERT INTO NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') VALUES (1000);

-- VTI in CREATE TRIGGER statement
-- beetle 5234
CREATE TRIGGER testtrig2 AFTER DELETE ON tb1 FOR EACH ROW MODE DB2SQL DELETE FROM NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') WHERE col1 = 100 and col3 = 732;

-- VTI in CREATE TRIGGER statement
-- beetle 5234
CREATE TRIGGER testtrig3 AFTER DELETE ON tb1 FOR EACH ROW MODE DB2SQL SELECT * FROM testCS, NEW org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI(col1, 1) a;

-- clean up
DROP TABLE tb1;
DROP TABLE testCS;

-- RENAME/DROP COLUMN
-- ALTER RENAME TABLE/COLUMN
-- beetle 5205
create table table tt (a int, b int, c int);
alter table tt drop column b;
alter table tt rename to ttnew;
alter table tt rename c to d;
rename column tt.c to tt.d;
drop table tt;

-- CASCADE/RESTRICT on DROP CONSTRAINT
-- beetle 5204
ALTER TABLE TT DROP CONSTRAINT ABC CASCADE;
ALTER TABLE TT DROP CONSTRAINT ABC2 RESTRICT;

-- CASCADE/RESTRICT on DROP TABLE
-- beetle 5206
DROP TABLE TT CASCADE;
DROP TABLE TT RESTRICT;

-- beetle 5216
-- there should only be one autoincrement column per table 
CREATE TABLE T1 (C1 INT GENERATED ALWAYS AS IDENTITY 
(START WITH 1, INCREMENT BY 1));
-- this statement should raise an error because it has more than one auto increment column in a table
CREATE TABLE T2 (C1 INT GENERATED ALWAYS AS IDENTITY 
(START WITH 1, INCREMENT BY 1), C2 INT GENERATED ALWAYS AS 
IDENTITY (START WITH 1, INCREMENT BY 1));
-- clean up
DROP TABLE t1;
DROP TABLE t2;

-- limit to 16 columns in an index key
-- beetle 5181
-- this create index statement should be successful in db2 compat mode because ix2 specifies 16 columns
create table testindex1 (a int,b int,c int,d int ,e int ,f int,g int,h int,i int,j int,k int,l int,m int,n int,o int,p int);
create unique index ix1 on testindex1(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p);

-- this create index statement should fail in db2 compat mode because ix2 specifies more than 16 columns
create table testindex2 (a int,b int,c int,d int ,e int ,f int,g int,h int,i int,j int,k int,l int,m int,n int,o int,p int,q int);
create unique index ix2 on testindex2(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q);
--clean up
drop table testindex1;
drop table testindex2;

-- insert into a lob column using explicit cast
-- positive test
-- beetle 5221
CREATE TABLE testblob(col1 BLOB(1M));
INSERT INTO testblob (col1) VALUES cast(X'11' as blob(1M));
CREATE TABLE testclob(col1 CLOB(1M));
INSERT INTO testclob (col1) VALUES cast('asdf' as clob(1M));

-- ALTER INDEX
-- beetle 5222
CREATE TABLE TT (A INT);
CREATE INDEX TTIDX ON TT(A);
ALTER INDEX TTIDX RENAME TTIDXNEW;
-- clean up
drop table tt;

-- CREATE and DROP AGGREGATE
-- beetle 5222
CREATE AGGREGATE STDEV FOR org.apache.derbyTesting.functionTests.util.aggregates.StandardDeviation;
DROP AGGREGATE STDEV;
CREATE AGGREGATE MAXBUTONE FOR org.apache.derbyTesting.functionTests.util.aggregates.MaxButOneDef;
DROP AGGREGATE MAXBUTONE;

-- CREATE and DROP CLASS ALIAS
-- beetle 5222
create class alias for java.util.Hashtable;
drop class alias Hashtable;

-- CREATE and DROP METHOD ALIAS
-- beetle 5222
create method alias hashtable for java.lang.Math.sin;
drop method alias hashtable;

-- RENAME COLUMN
-- beetle 5222
create table TT(col1 int, col2 int);
rename column TT.col2 to newcolumn2;
drop table TT;

-- SET TRIGGERS
-- beetle 5222
CREATE TABLE tb1 (col1 int, col2 int, col3 int, constraint chk1 check (col1 > 0));
CREATE TABLE tb2 (col1 char(30), c2 int, c3 int);
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);
SET TRIGGERS FOR tb1 ENABLED;
SET TRIGGERS FOR tb1 DISABLED;
SET TRIGGERS testtrig2 ENABLED;
SET TRIGGERS testtrig2 DISABLED;
-- clean up
DROP TRIGGER testtrig1;
DROP TRIGGER testtrig2;
DROP TRIGGER testtrig3;
DROP TABLE tb1;
DROP TABLE tb2;

-- INSTANCEOF in where clause of select, delete, update,
-- beetle 5224
create table t1 (i int, s smallint, c10 char(10), vc30 varchar(30), b boolean);
create table mm (x org.apache.derbyTesting.functionTests.util.ManyMethods);
create table sc (x org.apache.derbyTesting.functionTests.util.SubClass);
select i from t1 where i instanceof java.lang.Integer;
select i from t1 where i instanceof java.lang.Number;
select i from t1 where i instanceof java.lang.Object;
select s from t1 where s instanceof java.lang.Integer;
select b from t1 where b instanceof java.lang.Boolean;
select c10 from t1 where c10 instanceof java.lang.String;
select vc30 from t1 where vc30 instanceof java.lang.String;
-- following are negative test cases because boolean values disallowed in select clause
select x instanceof org.apache.derbyTesting.functionTests.util.ManyMethods from mm; 
select x instanceof org.apache.derbyTesting.functionTests.util.SubClass from mm; 
select x instanceof org.apache.derbyTesting.functionTests.util.SubSubClass from mm; 
select (i + i) instanceof java.lang.Integer from t1;
select (i instanceof java.lang.Integer) = true from t1;
DELETE FROM t1 where i INSTANCEOF 
org.apache.derbyTesting.functionTests.util.serializabletypes.City;
UPDATE t1 SET s = NULL WHERE i INSTANCEOF 
org.apache.derbyTesting.functionTests.util.serializabletypes.City;
-- clean up
drop table t1;
drop table mm;
drop table sc;

-- datatypes
-- beetle 5233
create table testtype1(col1 bit);
create table testtype2(col1 bit varying(10));
-- boolean datatype already disabled
create table testtype3(col1 boolean);
create table testtype4(col1 LONG NVARCHAR);
create table testtype5(col1 LONG VARBINARY);
create table testtype6(col1 LONG BIT VARYING);
create table testtype7(col1 LONG BINARY);
create table testtype8(col1 NCHAR);
create table testtype9(col1 NVARCHAR(10));
-- tinyint datatype already disabled
create table testtype10(col1 TINYINT);
create table testtype11 (a national character large object (1000));
-- beetle5426
-- disable nclob
create table beetle5426 (a nclob (1M));
create table testtype12 (a national char(100));
CREATE CLASS ALIAS FOR org.apache.derbyTesting.functionTests.util.serializabletypes.Tour;
create table testtype13 (a Tour);
-- clean up
drop table testtype1;
drop table testtype2;
drop table testtype3;
drop table testtype4;
drop table testtype5;
drop table testtype6;
drop table testtype7;
drop table testtype8;
drop table testtype9;
drop table testtype10;
drop table testtype11;
drop table beetle5426;
drop table testtype12;
drop class alias Tours;
drop table testtype13;

-- limit char to 254 and varchar to 32672 columns in db2 mode
-- beetle 5552
-- following will fail because char length > 254
create table test1(col1 char(255));
-- following will pass because char length <= 254
create table test1(col1 char(254), col2 char(23));
-- try truncation error with the 2 chars
-- the trailing blanks will not give error
insert into test1 values('a','abcdefghijklmnopqrstuvw   ');
-- the trailing non-blank characters will give error
insert into test1 values('a','abcdefghijklmnopqrstuvwxyz');
insert into test1 values('12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890','a');
drop table test1;
-- following will fail because varchar length > 32672
create table test1(col1 varchar(32673));
-- following will pass because varchar length <= 32672
create table test1(col1 varchar(32672), col2 varchar(1234));
drop table test1;

-- SET CONSTRAINTS statement