db2compatibility.out

derby database source code.good for you.

0 rows inserted/updated/deleted
ij> DROP TABLE testconst2;
0 rows inserted/updated/deleted
ij> -- CREATE TRIGGERS
-- beetle 5253
CREATE TABLE tb1 (col1 int, col2 int, col3 int, constraint chk1 check (col1 > 0));
0 rows inserted/updated/deleted
ij> CREATE TABLE tb2 (col1 char(30), c2 int, c3 int);
0 rows inserted/updated/deleted
ij> -- 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;
0 rows inserted/updated/deleted
ij> 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);
0 rows inserted/updated/deleted
ij> 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);
0 rows inserted/updated/deleted
ij> -- clean up
DROP TRIGGER testtrig1;
0 rows inserted/updated/deleted
ij> DROP TRIGGER testtrig2;
0 rows inserted/updated/deleted
ij> DROP TRIGGER testtrig3;
0 rows inserted/updated/deleted
ij> DROP TABLE tb1;
0 rows inserted/updated/deleted
ij> DROP TABLE tb2;
0 rows inserted/updated/deleted
ij> -- 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);
0 rows inserted/updated/deleted
ij> insert into t1 values 1;
1 row inserted/updated/deleted
ij> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR 42X01: Syntax error: Encountered "TRANSACTION" at line 1, column 5.
ij> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
ERROR 42X01: Syntax error: Encountered "TRANSACTION" at line 1, column 5.
ij> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
ERROR 42X01: Syntax error: Encountered "TRANSACTION" at line 1, column 5.
ij> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
ERROR 42X01: Syntax error: Encountered "TRANSACTION" at line 1, column 5.
ij> -- clean up
drop table t1;
0 rows inserted/updated/deleted
ij> -- statements should pass in db2 compat mode
-- beetle 5260
autocommit off;
ij> create table t1(c1 int not null constraint asdf primary key);
0 rows inserted/updated/deleted
ij> commit;
ij> insert into t1 values 1;
1 row inserted/updated/deleted
ij> -- verify SET TRANSACTION ISOLATION commits and changes isolation level
set isolation serializable;
0 rows inserted/updated/deleted
ij> -- rollback should find nothing to undo
rollback;
ij> select * from t1;
C1         
-----------
1          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
NULL                                                                                                                            
ij> -- verify SET TRANSACTION ISOLATION commits and changes isolation level
set isolation read committed;
0 rows inserted/updated/deleted
ij> -- rollback should find nothing to undo
rollback;
ij> select * from t1;
C1         
-----------
1          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
NULL                                                                                                                            
ij> -- verify SET TRANSACTION ISOLATION commits and changes isolation level
set isolation repeatable read;
0 rows inserted/updated/deleted
ij> -- rollback should find nothing to undo
rollback;
ij> select * from t1;
C1         
-----------
1          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
NULL                                                                                                                            
ij> -- verify SET TRANSACTION ISOLATION commits and changes isolation level
set isolation read uncommitted;
0 rows inserted/updated/deleted
ij> -- rollback should find nothing to undo
rollback;
ij> select * from t1;
C1         
-----------
1          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
NULL                                                                                                                            
ij> drop table t1;
0 rows inserted/updated/deleted
ij> -- SET ISOLATION statement
-- beetle 5260
-- set isolation statement that are supported in db2
create table t1(c1 int not null constraint asdf primary key);
0 rows inserted/updated/deleted
ij> insert into t1 values 1;
1 row inserted/updated/deleted
ij> set isolation serializable;
0 rows inserted/updated/deleted
ij> set isolation read committed;
0 rows inserted/updated/deleted
ij> set isolation repeatable read;
0 rows inserted/updated/deleted
ij> set isolation read uncommitted;
0 rows inserted/updated/deleted
ij> -- clean up
drop table t1;
0 rows inserted/updated/deleted
ij> -- SELECT statement testing
-- beetle 5255
CREATE TABLE t1(col1 int, col2 int);
0 rows inserted/updated/deleted
ij> CREATE TABLE t2(col1 int, col2 int);
0 rows inserted/updated/deleted
ij> INSERT INTO t1 VALUES(3,4);
1 row inserted/updated/deleted
ij> INSERT INTO t2 VALUES(3,4);
1 row inserted/updated/deleted
ij> -- 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;
ERROR 42X01: Syntax error: Encountered "<EOF>" at line 3, column 30.
ij> -- (2) USING should be disabled in INNER JOIN of SELECT statement
SELECT * FROM t1 INNER JOIN t2 USING (col1);
ERROR 42X01: Syntax error: Encountered "USING" at line 2, column 32.
ij> -- (3) USING should be disabled in INNER JOIN of SELECT statement
SELECT * FROM t1 LEFT OUTER JOIN t2 USING (col1);
ERROR 42X01: Syntax error: Encountered "USING" at line 2, column 37.
ij> -- (4) USING should be disabled in INNER JOIN of SELECT statement
SELECT * FROM t1 RIGHT OUTER JOIN t2 USING (col1);
ERROR 42X01: Syntax error: Encountered "USING" at line 2, column 38.
ij> -- (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;
ERROR 42X01: Syntax error: true.
ij> SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1 WHERE false;
ERROR 42X01: Syntax error: false.
ij> -- (5) TRUE and FALSE constants should be disabled in WHERE clause of DELETE statement
DELETE FROM t1 where true;
ERROR 42X01: Syntax error: true.
ij> DELETE FROM t1 where false;
ERROR 42X01: Syntax error: false.
ij> -- (5) TRUE and FALSE constants should be disabled in WHERE clause of DELETE statement
UPDATE t2 SET col1 = NULL WHERE true;
ERROR 42X01: Syntax error: true.
ij> UPDATE t2 SET col1 = NULL WHERE false;
ERROR 42X01: Syntax error: false.
ij> -- (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;
ERROR 42X01: Syntax error: Encountered "AT" at line 3, column 18.
ij> SELECT * FROM t1 AT ISOLATION READ COMMITTED;
ERROR 42X01: Syntax error: Encountered "AT" at line 1, column 18.
ij> SELECT * FROM t1 AT ISOLATION SERIALIZABLE;
ERROR 42X01: Syntax error: Encountered "AT" at line 1, column 18.
ij> SELECT * FROM t1 AT ISOLATION REPEATABLE READ;
ERROR 42X01: Syntax error: Encountered "AT" at line 1, column 18.
ij> -- clean up
DROP TABLE t1;
0 rows inserted/updated/deleted
ij> DROP TABLE t2;
0 rows inserted/updated/deleted
ij> -- 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)));
ERROR 42X01: Syntax error: Encountered "cast" at line 2, column 45.
ij> create table testsessionuser(col1 BLOB(3K) default cast(session_user as blob(3k)));
ERROR 42X01: Syntax error: Encountered "cast" at line 1, column 52.
ij> create table testcurrentuser(col1 BLOB(3K) default cast(current_user as blob(3k)));
ERROR 42X01: Syntax error: Encountered "cast" at line 1, column 52.
ij> create table testschema(col1 BLOB(3K) default cast(current schema as blob(3k)));
ERROR 42X01: Syntax error: Encountered "cast" at line 1, column 47.
ij> -- 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);
0 rows inserted/updated/deleted
ij> -- increasing the length of the varchar column
alter table testmodify alter col1 set data type varchar(60);
0 rows inserted/updated/deleted
ij> -- specifying the interval between consecutive values of col2, the identity column 
alter table testmodify alter col2 set increment by 2;
0 rows inserted/updated/deleted
ij> -- clean up
drop table testmodify;
0 rows inserted/updated/deleted
ij> -- (1) adding more than one column 
-- beetle 5268
-- db2 compat mode should support the following statements
create table testaddcol (col1 int);
0 rows inserted/updated/deleted
ij> alter table testaddcol add column col2 int add col3 int;
ERROR 42X01: Syntax error: Encountered "add" at line 1, column 44.
ij> drop table testaddcol;
0 rows inserted/updated/deleted
ij> -- (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);
0 rows inserted/updated/deleted
ij> create table testaddconst2 (col1 int not null primary key, col2 int not null unique);
0 rows inserted/updated/deleted
ij> create table testaddconst3 (col1 int not null, col2 int not null, col3 int not null, col4 int not null, col5 int, col6 int);
0 rows inserted/updated/deleted
ij> create table testaddconst4 (col1 int not null, col2 int not null, col3 int not null, col4 int not null, col5 int, col6 int);
0 rows inserted/updated/deleted
ij> -- adding more than one unique-constraint 
alter table testaddconst3 add primary key (col1) add unique (col2);
ERROR 42X01: Syntax error: Encountered "add" at line 2, column 50.
ij> alter table testaddconst3 add unique (col3) add unique (col4);
ERROR 42X01: Syntax error: Encountered "add" at line 1, column 45.
ij> -- adding more than one referential-constraint 
alter table testaddconst3 add foreign key (col1) references testaddconst1(col1) add foreign key (col2) references testaddconst2(col2);
ERROR 42X01: Syntax error: Encountered "add" at line 2, column 81.
ij> -- adding more than one check-constraint 
alter table testaddconst3 add check (col5 is null) add check (col6 is null);
ERROR 42X01: Syntax error: Encountered "add" at line 2, column 52.
ij> -- 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);
ERROR 42X01: Syntax error: Encountered "add" at line 2, column 49.
ij> -- clean up
drop table testaddconst1;
0 rows inserted/updated/deleted
ij> drop table testaddconst2;
0 rows inserted/updated/deleted
ij> drop table testaddconst3;
0 rows inserted/updated/deleted
ij> drop table testaddconst4;
0 rows inserted/updated/deleted
ij> -- (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));
0 rows inserted/updated/deleted
ij> 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));
0 rows inserted/updated/deleted
ij> CREATE TABLE testdropconst3 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk4 UNIQUE , PRIMARY KEY(col1));
0 rows inserted/updated/deleted
ij> CREATE TABLE testdropconst4 (col1 CHAR(7) NOT NULL, col2 int not null CONSTRAINT uk5 UNIQUE , PRIMARY KEY(col1));
0 rows inserted/updated/deleted
ij> 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));