db2compatibility.out

derby database source code.good for you.

ij> -- 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));
0 rows inserted/updated/deleted
ij> INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA);
1 row inserted/updated/deleted
ij> SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA;
1          
-----------
1          
ij> SET SCHEMA DILBERT;
ERROR 42Y07: Schema 'DILBERT' does not exist
ij> connect 'jdbc:derby:wombat;user=dilbert';
ij(CONNECTION1)> INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA);
1 row inserted/updated/deleted
ij(CONNECTION1)> SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA;
1          
-----------
2          
ij(CONNECTION1)> VALUES CURRENT SCHEMA;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
DILBERT                                                                                                                         
ij(CONNECTION1)> disconnect;
ij> SET CONNECTION CONNECTION0;
ij> -- still should not be created
SET SCHEMA DILBERT;
ERROR 42Y07: Schema 'DILBERT' does not exist
ij> connect 'jdbc:derby:wombat;user=dilbert';
ij(CONNECTION1)> INSERT INTO DST.DEF_SCHEMA_TEST VALUES(USER, CURRENT SCHEMA);
1 row inserted/updated/deleted
ij(CONNECTION1)> SELECT COUNT(*) FROM DST.DEF_SCHEMA_TEST WHERE NAME_USER = NAME_SCHEMA;
1          
-----------
3          
ij(CONNECTION1)> VALUES CURRENT SCHEMA;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
DILBERT                                                                                                                         
ij(CONNECTION1)> CREATE TABLE SCOTT(i int);
0 rows inserted/updated/deleted
ij(CONNECTION1)> insert into SCOTT VALUES(4);
1 row inserted/updated/deleted
ij(CONNECTION1)> disconnect;
ij> SET CONNECTION CONNECTION0;
ij> SELECT * FROM DILBERT.SCOTT;
I          
-----------
4          
ij> DROP TABLE DILBERT.SCOTT;
0 rows inserted/updated/deleted
ij> DROP TABLE DST.DEF_SCHEMA_TEST;
0 rows inserted/updated/deleted
ij> DROP SCHEMA DST RESTRICT;
0 rows inserted/updated/deleted
ij> DROP SCHEMA DILBERT RESTRICT;
0 rows inserted/updated/deleted
ij> -- Simple Cloudscape specific features.
-- CLASS ALIAS;
create class alias MyMath for java.lang.Math;
ERROR 42X01: Syntax error: Encountered "class" at line 5, column 8.
ij> drop class alias MyMath;
ERROR 42X01: Syntax error: Encountered "class" at line 1, column 6.
ij> create class alias for java.lang.Math;
ERROR 42X01: Syntax error: Encountered "class" at line 1, column 8.
ij> drop class alias Math;
ERROR 42X01: Syntax error: Encountered "class" at line 1, column 6.
ij> -- METHOD ALIAS;
create method alias myabs for java.lang.Math.abs;
ERROR 42X01: Syntax error: Encountered "method" at line 3, column 8.
ij> drop method alias myabs;
ERROR 42X01: Syntax error: Encountered "method" at line 1, column 6.
ij> -- 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;
ERROR 42X01: Syntax error: Encountered "statement" at line 3, column 8.
ij> -- alter, drop and execute statements are still supported for existing stored prepared statements for customers
alter statement recompile all;
ERROR 42X01: Syntax error: Encountered "statement" at line 2, column 7.
ij> -- following will give error because there is no stored prepared statement s1 in the database
drop statement s1;
ERROR 42X01: Syntax error: Encountered "statement" at line 2, column 6.
ij> -- clean up
DROP TABLE t1;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T1' because it does not exist.
ij> DROP TABLE t2;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T2' because it does not exist.
ij> DROP CLASS ALIAS ExternalInsert;
ERROR 42X01: Syntax error: Encountered "CLASS" at line 1, column 6.
ij> DROP STATEMENT insert1;
ERROR 42X01: Syntax error: Encountered "STATEMENT" at line 1, column 6.
ij> -- Primary key constraint, DB2 requires NOT null on the columns.
create table customer (id int primary key, name char(100));
0 rows inserted/updated/deleted
ij> drop table customer;
0 rows inserted/updated/deleted
ij> create table customer (id  int NOT NULL, id2 int, name char(100), primary key (id, id2));
0 rows inserted/updated/deleted
ij> drop table customer;
0 rows inserted/updated/deleted
ij> -- Unique key constraint, DB2 requires NOT null on the columns.
create table customer (id int unique, name char(100));
ERROR 42831: 'ID' cannot be a column of a primary key or unique key because it can contain null values.
ij> create table customer (id  int NOT NULL, id2 int, constraint custCon unique(id, id2));
ERROR 42831: 'ID2' cannot be a column of a primary key or unique key because it can contain null values.
ij> -- check they actually work!
create table customer (id int NOT NULL primary key, name char(100));
0 rows inserted/updated/deleted
ij> drop table customer;
0 rows inserted/updated/deleted
ij> create table customer (id  int NOT NULL, id2 int NOT NULL, name char(100), primary key (id, id2));
0 rows inserted/updated/deleted
ij> drop table customer;
0 rows inserted/updated/deleted
ij> -- drop schema requires restrict
create schema fred;
0 rows inserted/updated/deleted
ij> drop schema fred;
ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 16.
ij> drop schema fred restrict;
0 rows inserted/updated/deleted
ij> -- create schema not supported for schemas that start with SYS
create schema SYS;
ERROR 42939: An object cannot be created with the schema name 'SYS'.
ij> create schema SYSDJD;
ERROR 42939: An object cannot be created with the schema name 'SYSDJD'.
ij> create schema "SYSNO";
ERROR 42939: An object cannot be created with the schema name 'SYSNO'.
ij> create schema "sys";
0 rows inserted/updated/deleted
ij> create schema "sysok";
0 rows inserted/updated/deleted
ij> drop schema "sys" restrict;
0 rows inserted/updated/deleted
ij> drop schema "sysok" restrict;
0 rows inserted/updated/deleted
ij> -- data types not supported
create table NOTYPE(i int, b BOOLEAN);
ERROR 42X01: Syntax error: BOOLEAN.
ij> create table NOTYPE(i int, b TINYINT);
ERROR 42X01: Syntax error: Encountered "" at line 1, column 30.
ij> create table NOTYPE(i int, b java.lang.String);
ERROR 42X01: Syntax error: Encountered "" at line 1, column 30.
ij> create table NOTYPE(i int, b com.acme.Address);
ERROR 42X01: Syntax error: Encountered "" at line 1, column 30.
ij> create table NOTYPE(i int, b org.apache.derby.vti.VTIEnvironment);
ERROR 42X01: Syntax error: Encountered "" at line 1, column 30.
ij> -- VTI in the DELETE statement
-- beetle 5234
CREATE TABLE testCS (col1 int, col2 char(30), col3 int);
0 rows inserted/updated/deleted
ij> INSERT INTO testCS VALUES (100, 'asdf', 732);
1 row inserted/updated/deleted
ij> DELETE FROM NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') WHERE col1 = 100 and col3 = 732;
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable.
ij> -- 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);
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.serializabletypes.ExternalTable.
ij> -- VTI in the SELECT statement
-- beetle 5234
select * from testCS, new org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI(col1, 1) a;
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI.
ij> select * from new com.acme.myVTI() as T;
ERROR 42X01: Syntax error: com.acme.myVTI.
ij> select * from new org.apache.derbyTesting.not.myVTI() as T;
ERROR 42X01: Syntax error: org.apache.derbyTesting.not.myVTI.
ij> select * from new org.apache.derby.diag.LockTable() as T;
XID            |TYPE |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE|TABLETYPE|LOCK&|INDEXNAME                                                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij> -- VTI in CREATE TRIGGER statement
-- beetle 5234
CREATE TABLE tb1(a int);
0 rows inserted/updated/deleted
ij> 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);
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable.
ij> -- 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;
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable.
ij> -- 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;
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI.
ij> -- clean up
DROP TABLE tb1;
0 rows inserted/updated/deleted
ij> DROP TABLE testCS;
0 rows inserted/updated/deleted
ij> -- PROPERTIES in DB2 mode
create table maps (country_ISO_code char(2)) PROPERTIES derby.storage.pageSize=262144;
ERROR 42X01: Syntax error: PROPERTIES.
ij> -- PROPERTIES in DB2 mode
-- beetle 5177
create table maps2 (country_ISO_code char(2));
0 rows inserted/updated/deleted
ij> create index map_idx1 on maps2(country_ISO_code) properties derby.storage.pageSize = 2048;
ERROR 42X01: Syntax error: PROPERTIES.
ij> -- 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);
ERROR 42X01: Syntax error: Encountered "btree" at line 2, column 8.
ij> create unique btree index map_idx2 on maps2(country_ISO_code);
ERROR 42X01: Syntax error: Encountered "btree" at line 1, column 15.
ij> drop table maps2;
0 rows inserted/updated/deleted
ij> -- SET LOCKING clause in DB2 mode
-- beetle 5208
create table maps1 (country_ISO_code char(2)) set locking = table;
ERROR 42X01: Syntax error: Encountered "set" at line 3, column 47.
ij> create table maps2 (country_ISO_code char(2)) set locking = row;
ERROR 42X01: Syntax error: Encountered "set" at line 1, column 47.
ij> drop table maps1;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'MAPS1' because it does not exist.
ij> drop table maps2;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'MAPS2' because it does not exist.
ij> -- ALTER TABLE statement
-- beetle 5201
-- Locking syntax
-- negative tests
create table tb1 (country_ISO_code char(2));
0 rows inserted/updated/deleted
ij> alter table tb1 set locking = table;
ERROR 42X01: Syntax error: Encountered "set" at line 1, column 17.
ij> alter table tb1 set locking = row;
ERROR 42X01: Syntax error: Encountered "set" at line 1, column 17.
ij> -- Locking syntax 
-- positive tests
-- beetle 5201
create table tb2 (country_ISO_code char(2));
0 rows inserted/updated/deleted
ij> alter table tb2 locksize table;
0 rows inserted/updated/deleted
ij> alter table tb2 locksize row;
0 rows inserted/updated/deleted
ij> -- clean up
drop table tb1;
0 rows inserted/updated/deleted
ij> drop table tb2;
0 rows inserted/updated/deleted
ij> -- VTI in the DELETE statement
-- beetle 5234
CREATE TABLE testCS (col1 int, col2 char(30), col3 int);
0 rows inserted/updated/deleted
ij> INSERT INTO testCS VALUES (100, 'asdf', 732);
1 row inserted/updated/deleted
ij> DELETE FROM NEW org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable('jdbc:derby:wombat', 'testCS') WHERE col1 = 100 and col3 = 732;
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable.
ij> -- 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);
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.ExternalTable.
ij> -- VTI in the SELECT statement
-- beetle 5234
select * from testCS, new org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI(col1, 1) a;
ERROR 42X01: Syntax error: org.apache.derbyTesting.functionTests.util.VTIClasses.PositiveInteger_VTICosting_SI.
ij> -- VTI in CREATE TRIGGER statement
-- beetle 5234
CREATE TABLE tb1(a int);