ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- This test lang/LOB.sql still includes tests for -- DB2 UDB incompatible datatype NCLOB. -- Still waiting for DB2 UDB compatible functionality for NCLOB to be implemented -- Also note that in DB2 UDB, to create BLOB and CLOB strings greater than 1 gigabyte, -- the NOT LOGGED option must be specified (SQLSTATE 42993). -- test that BLOB/CLOB are not reserved words create table blob(a int); 0 rows inserted/updated/deleted ij> insert into blob values(3); 1 row inserted/updated/deleted ij> select blob.a from blob; A ----------- 3 ij> create table clob(a int); 0 rows inserted/updated/deleted ij> insert into clob values(3); 1 row inserted/updated/deleted ij> select clob.a from clob; A ----------- 3 ij> create table nclob(a int); 0 rows inserted/updated/deleted ij> insert into nclob values(3); 1 row inserted/updated/deleted ij> select nclob.a from nclob; A ----------- 3 ij> create table a(blob int, clob int, nclob int); 0 rows inserted/updated/deleted ij> insert into a values(1,2,3); 1 row inserted/updated/deleted ij> insert into a(blob, clob, nclob) values(1,2,3); 1 row inserted/updated/deleted ij> select a.blob, a.clob, a.nclob from a; BLOB |CLOB |NCLOB ----------------------------------- 1 |2 |3 1 |2 |3 ij> select a.blob, a.clob, a.nclob from a where a.blob = 1; BLOB |CLOB |NCLOB ----------------------------------- 1 |2 |3 1 |2 |3 ij> select a.blob, a.clob, a.nclob from a where a.clob = 2; BLOB |CLOB |NCLOB ----------------------------------- 1 |2 |3 1 |2 |3 ij> select a.blob, a.clob, a.nclob from a where a.nclob = 3; BLOB |CLOB |NCLOB ----------------------------------- 1 |2 |3 1 |2 |3 ij> select a.blob, a.clob, a.nclob from a where a.blob = 1 and a.clob = 2 and a.nclob = 3; BLOB |CLOB |NCLOB ----------------------------------- 1 |2 |3 1 |2 |3 ij> create table b(blob blob(3K), clob clob(2M)); 0 rows inserted/updated/deleted ij> insert into b values(cast(X'0031' as blob(3K)),cast('2' as clob(2M))); 1 row inserted/updated/deleted ij> insert into b(blob, clob, nclob) values(cast(X'0031' as blob(3K)),cast('2' as clob(2M))); ERROR 42X14: 'NCLOB' is not a column in table or VTI 'APP.B'. ij> select b.blob, b.clob, b.nclob from b; ERROR 42X04: Column 'B.NCLOB' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'B.NCLOB' is not a column in the target table. ij> -- equal tests are not allowed select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(5)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e000' as blob(7)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where X'80' = cast(X'80' as blob(1)); ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast(X'80' as blob(1)) = X'80'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR () FOR BIT DATA' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast(X'80' as blob(1)) = cast(X'80' as blob(1)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where '1' = cast('1' as clob(1)); ERROR 42818: Comparisons between 'CHAR (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast('1' as clob(1)) = '1'; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast('1' as clob(1)) = cast('1' as clob(1)); ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where '1' = cast('1' as nclob(1)); ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('1' as nclob(1)) = '1'; ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('1' as nclob(1)) = cast('1' as nclob(1)); ERROR 0A000: Feature not implemented: NCLOB. ij> -- NCLOB is comparable with CLOB select 1 from b where cast('1' as nclob(10)) = cast('1' as clob(10)); ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('1' as clob(10)) = cast('1' as nclob(10)); ERROR 0A000: Feature not implemented: NCLOB. ij> -- comparsion using tables select * from b as b1, b as b2 where b1.blob=b2.blob; ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from b as b1, b as b2 where b1.blob!=b2.blob; ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from b as b1, b as b2 where b1.blob=X'20'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR () FOR BIT DATA' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from b as b1, b as b2 where X'20'=b1.blob; ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from b as b1, b as b2 where X'20'!=b1.blob; ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from b as b1, b as b2 where b1.blob=X'7575'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR () FOR BIT DATA' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from b as b1, b as b2 where X'7575'=b1.blob; ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select b.blob, b.clob, b.nclob from b where b.blob = '1' and b.clob = '2' and b.nclob = '3'; ERROR 42X04: Column 'B.NCLOB' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'B.NCLOB' is not a column in the target table. ij> select b.blob from b where b.blob = '1'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> -- however it works for types which cloudscape autocasts to char select b.clob from b where b.clob = '2'; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select b.nclob from b where b.nclob = '3'; ERROR 42X04: Column 'B.NCLOB' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'B.NCLOB' is not a column in the target table. ij> -- test insert of NULL insert into b values(null, null, null); ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. ij> select * from b; BLOB |CLOB ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0031 |2 ij> -- cleanup drop table blob; 0 rows inserted/updated/deleted ij> drop table clob; 0 rows inserted/updated/deleted ij> drop table nclob; 0 rows inserted/updated/deleted ij> drop table a; 0 rows inserted/updated/deleted ij> drop table b; 0 rows inserted/updated/deleted ij> -- test insert limitations create table b(b blob(5)); 0 rows inserted/updated/deleted ij> create table c(c clob(5)); 0 rows inserted/updated/deleted ij> create table n(n nclob(5)); ERROR 0A000: Feature not implemented: NCLOB. ij> insert into b values(cast(X'01020304' as blob(10))); 1 row inserted/updated/deleted ij> insert into b values(cast(X'0102030405' as blob(10))); 1 row inserted/updated/deleted ij> insert into b values(cast(X'010203040506' as blob(10))); ERROR 22001: A truncation error was encountered trying to shrink BLOB 'XXXX' to length 5. ij> -- truncate before insert, no errors insert into b values(cast(X'01020304' as blob(5))); 1 row inserted/updated/deleted ij> insert into b values(cast(X'0102030405' as blob(5))); 1 row inserted/updated/deleted ij> insert into b values(cast(X'010203040506' as blob(5))); 1 row inserted/updated/deleted WARNING 01004: Data truncation ij> -- clob/nclob -- ok in spite of not being cast insert into c values('1234'); 1 row inserted/updated/deleted ij> insert into c values('12345'); 1 row inserted/updated/deleted ij> insert into c values('123456'); ERROR 22001: A truncation error was encountered trying to shrink CLOB '123456' to length 5. ij> insert into n values('1234'); ERROR 42X05: Table/View 'N' does not exist. ij> insert into n values('12345'); ERROR 42X05: Table/View 'N' does not exist. ij> insert into n values('123456'); ERROR 42X05: Table/View 'N' does not exist. ij> -- ok insert into c values(cast('1234' as clob(5))); 1 row inserted/updated/deleted ij> insert into c values(cast('12345' as clob(5))); 1 row inserted/updated/deleted ij> insert into c values(cast('123456' as clob(5))); 1 row inserted/updated/deleted WARNING 01004: Data truncation ij> insert into n values(cast('1234' as nclob(5))); ERROR 0A000: Feature not implemented: NCLOB. ij> insert into n values(cast('12345' as nclob(5))); ERROR 0A000: Feature not implemented: NCLOB. ij> insert into n values(cast('123456' as nclob(5))); ERROR 0A000: Feature not implemented: NCLOB. ij> select * from b; B ---------- 01020304 0102030405 01020304 0102030405 0102030405 ij> select * from c; C ----- 1234 12345 1234 12345 12345 ij> select * from n; ERROR 42X05: Table/View 'N' does not exist. ij> -- concatenate values cast('12' as clob(2)) || cast('34' as clob(2)); 1 ---- 1234 ij> values cast('12' as nclob(2)) || cast('34' as nclob(2)); ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('12' as clob(2)) || cast('34' as clob(2)) = '1234'; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast('12' as nclob(2)) || cast('34' as nclob(2)) = '1234'; ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('12' as clob(2)) || cast('34' as clob(2)) = cast('1234' as clob(4)); ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast('12' as nclob(2)) || cast('34' as nclob(2)) = cast('1234' as clob(4)); ERROR 0A000: Feature not implemented: NCLOB. ij> -- like select * from b where b like '0102%'; ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found. ij> select * from c where c like '12%'; C ----- 1234 12345 1234 12345 12345 ij> select * from n where n like '12%'; ERROR 42X05: Table/View 'N' does not exist. ij> select * from b where b like cast('0102%' as blob(10)); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> select * from c where c like cast('12%' as clob(10)); C ----- 1234 12345 1234 12345 12345 ij> select * from n where n like cast('12%' as nclob(10)); ERROR 0A000: Feature not implemented: NCLOB. ij> -- cleanup drop table b; 0 rows inserted/updated/deleted ij> drop table c; 0 rows inserted/updated/deleted ij> drop table n; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'N' because it does not exist. ij> -- test syntax of using long type names create table a(a binary large object(3K)); 0 rows inserted/updated/deleted ij> create table b(a character large object(3K)); 0 rows inserted/updated/deleted ij> create table c(a national character large object(3K)); ERROR 0A000: Feature not implemented: NCLOB. ij> create table d(a char large object(204K)); 0 rows inserted/updated/deleted ij> -- create index (not allowed) create index ia on a(a); ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> create index ib on b(a); ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> create index ic on c(a); ERROR 42Y55: 'CREATE INDEX' cannot be performed on 'C' because it does not exist. ij> create index id on d(a); ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> -- cleanup drop table a; 0 rows inserted/updated/deleted ij> drop table c; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'C' because it does not exist. ij> drop table d; 0 rows inserted/updated/deleted ij> -- ORDER tests on LOB types (not allowed) select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(5)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast(X'e0' as blob(5))!=cast(X'e0' as blob(5)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast(X'e0' as blob(5)) select 1 from b where cast(X'e0' as blob(5))>cast(X'e0' as blob(7)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast(X'e0' as blob(5))<=cast(X'e0' as blob(7)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast(X'e0' as blob(5))>=cast(X'e0' as blob(7)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast('fish' as clob(5))=cast('fish' as clob(5)); ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast('fish' as clob(5))!=cast('fish' as clob(5)); ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast('fish' as clob(5)) select 1 from b where cast('fish' as clob(5))>cast('fish' as clob(7)); ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast('fish' as clob(5))<=cast('fish' as clob(7)); ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast('fish' as clob(5))>=cast('fish' as clob(7)); ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select 1 from b where cast('fish' as nclob(5))=cast('fish' as nclob(5)); ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('fish' as nclob(5))!=cast('fish' as nclob(5)); ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('fish' as nclob(5)) select 1 from b where cast('fish' as nclob(5))>cast('fish' as nclob(7)); ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('fish' as nclob(5))<=cast('fish' as nclob(7)); ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('fish' as nclob(5))>=cast('fish' as nclob(7)); ERROR 0A000: Feature not implemented: NCLOB. ij> -- test operands on autocast -- beetle 5282 -- <,> <=, >= operands are not supported in db2 but supported in cloudscape -- compare w. integer/char types are also not ok -- CLOB testing CREATE TABLE testoperatorclob (colone clob(1K)); 0 rows inserted/updated/deleted ij> INSERT INTO testoperatorclob VALUES (CAST('50' AS CLOB(1K))); 1 row inserted/updated/deleted ij> INSERT INTO testoperatorclob VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K))); 1 row inserted/updated/deleted ij> select * from testoperatorclob; COLONE -------------------------------------------------------------------------------------------------------------------------------- 50 50 ij> -- these select statements should raise an error but are successful in cloudscape select * from testoperatorclob where colone > 10; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone > 5; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone < 70; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone = 50; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone != 10; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone <= 70; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone >= 10; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone <> 10; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone > '10'; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone > '5'; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone < '70'; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone = '50'; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone != '10'; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone <= '70'; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone >= '10'; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorclob where colone <> '10'; ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> drop table testoperatorclob; 0 rows inserted/updated/deleted ij> -- BLOB testing CREATE TABLE testoperatorblob (colone blob(1K)); 0 rows inserted/updated/deleted ij> INSERT INTO testoperatorblob VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> INSERT INTO testoperatorblob VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K))); ERROR 42846: Cannot convert types 'VARCHAR' to 'BLOB'. ij> select * from testoperatorblob; COLONE -------------------------------------------------------------------------------------------------------------------------------- ij> -- these select statements should raise an error but are successful in cloudscape select * from testoperatorblob where colone > 10; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone > 5; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone < 999999; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone = 00350030; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone != 10; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone <= 999999; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone >= 10; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone <> 10; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone > '10'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone > '5'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone < '70'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone = '50'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone != '10'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone <= '70'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone >= '10'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testoperatorblob where colone <> '10'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> drop table testoperatorblob; 0 rows inserted/updated/deleted ij> -- NCLOB testing CREATE TABLE testoperatornclob (colone nclob(1K)); ERROR 0A000: Feature not implemented: NCLOB. ij> INSERT INTO testoperatornclob VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> INSERT INTO testoperatornclob VALUES (CAST(cast('50' as varchar(80)) AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> select * from testoperatornclob; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> -- these select statements should raise an error but are successful in cloudscape select * from testoperatornclob where colone > 10; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone > 5; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone < 70; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone = 50; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone != 10; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone <= 70; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone >= 10; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone <> 10; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone > '10'; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone > '5'; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone < '70'; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone = '50'; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone != '10'; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone <= '70'; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone >= '10'; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone <> '10'; ERROR 42X05: Table/View 'TESTOPERATORNCLOB' does not exist. ij> drop table testoperatornclob; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTOPERATORNCLOB' because it does not exist. ij> ----- test method invocations on LOB objects (should disallow) -- setup drop table b; 0 rows inserted/updated/deleted ij> create table b(b blob(77)); 0 rows inserted/updated/deleted ij> insert into b values(cast('33' as blob(77))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> create table c(c clob(77)); 0 rows inserted/updated/deleted ij> insert into c values(cast('33' as clob(77))); 1 row inserted/updated/deleted ij> -- LOB as main object for method invocation not allowed values (cast('1' as blob(1M)))->toString(); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> values (cast('1' as clob(1M)))->toString(); ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver. ij> values (cast('1' as nclob(1M)))->toString(); ERROR 0A000: Feature not implemented: NCLOB. ij> drop table b; 0 rows inserted/updated/deleted ij> drop table c; 0 rows inserted/updated/deleted ij> ------ TEST length functions on LOBs ---- BLOB values length(cast('foo' as blob(10))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> values {fn length(cast('foo' as blob(10)))}; ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> ---- CHAR values length(cast('foo' as char(10))); 1 ----------- 10 ij> values {fn length(cast('foo' as char(10)))}; 1 ----------- 3 ij> ---- CLOB values length(cast('foo' as clob(10))); 1 ----------- 3 ij> values {fn length(cast('foo' as clob(10)))}; 1 ----------- 3 ij> ---- NCLOB values length(cast('foo' as nclob(10))); ERROR 0A000: Feature not implemented: NCLOB. ij> values {fn length(cast('foo' as nclob(10)))}; ERROR 0A000: Feature not implemented: NCLOB. ij> -- Longvarchar negative tests create table testPredicate1 (c1 long varchar); 0 rows inserted/updated/deleted ij> create table testPredicate2 (c1 long varchar); 0 rows inserted/updated/deleted ij> insert into testPredicate1 (c1) values 'a'; 1 row inserted/updated/deleted ij> insert into testPredicate2 (c1) values 'a'; 1 row inserted/updated/deleted ij> -- UNION select * from testPredicate1 union select * from testPredicate2; ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> -- IN predicate select c1 from testPredicate1 where c1 IN (select c1 from testPredicate2); ERROR 42818: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> -- NOT IN predicate select c1 from testPredicate1 where c1 NOT IN (select c1 from testPredicate2); ERROR 42818: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> -- ORDER BY clause select * from testPredicate1 order by c1; ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> -- GROUP BY clause select substr(c1,1,2) from testPredicate1 group by c1; ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> -- JOIN select * from testPredicate1 t1, testPredicate2 t2 where t1.c1=t2.c1; ERROR 42818: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> select * from testPredicate1 LEFT OUTER JOIN testPredicate2 on testPredicate1.c1=testPredicate2.c1; ERROR 42818: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') ij> -- PRIMARY KEY create table testConst1(c1 long varchar not null primary key); ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> -- UNIQUE KEY constraints CREATE TABLE testconst2 (col1 long varchar not null, CONSTRAINT uk UNIQUE (col1)); ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> -- FOREIGN KEY constraints create table testConst3 (c1 char(10) not null, primary key (c1)); 0 rows inserted/updated/deleted ij> create table testConst4 (c1 long varchar not null, constraint fk foreign key (c1) references testConst3 (c1)); ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> drop table testConst3; 0 rows inserted/updated/deleted ij> -- MAX aggregate function select max(c1) from testPredicate1; ERROR 42Y22: Aggregate MAX cannot operate on type LONG VARCHAR. ij> -- MIN aggregate function select min(c1) from testPredicate1; ERROR 42Y22: Aggregate MIN cannot operate on type LONG VARCHAR. ij> drop table testpredicate1; 0 rows inserted/updated/deleted ij> drop table testpredicate2; 0 rows inserted/updated/deleted ij> -- CLOB/BLOB limits and sizes -- FAIL - bigger than 2G or 2Gb with no modifier create table DB2LIM.FB1(FB1C BLOB(3G)); ERROR 42X44: Invalid length '3G' in column specification. ij> create table DB2LIM.FB2(FB2C BLOB(2049M)); ERROR 42X44: Invalid length '2049M' in column specification. ij> create table DB2LIM.FB3(FB3C BLOB(2097153K)); ERROR 42X44: Invalid length '2097153K' in column specification. ij> create table DB2LIM.FB4(FB4C BLOB(2147483648)); ERROR 42X44: Invalid length '2147483648' in column specification. ij> -- OK 2G and end up as 2GB - 1 (with modifier) create table DB2LIM.GB1(GB1C BLOB(2G)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB2(GB2C BLOB(2048M)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB3(GB3C BLOB(2097152K)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB4(GB4C BLOB(2147483647)); 0 rows inserted/updated/deleted ij> -- next lower value create table DB2LIM.GB5(GB5C BLOB(1G)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB6(GB6C BLOB(2047M)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB7(GB7C BLOB(2097151K)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB8(GB8C BLOB(2147483646)); 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB5; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB6; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB7; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB8; 0 rows inserted/updated/deleted ij> -- no length (default to 1Mb) create table DB2LIM.GB9(GB9C BLOB); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB10(GB10C BINARY LARGE OBJECT); 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB9; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB10; 0 rows inserted/updated/deleted ij> -- FAIL - bigger than 2G or 2Gb with no modifier create table DB2LIM.FC1(FC1C CLOB(3G)); ERROR 42X44: Invalid length '3G' in column specification. ij> create table DB2LIM.FC2(FC2C CLOB(2049M)); ERROR 42X44: Invalid length '2049M' in column specification. ij> create table DB2LIM.FC3(FC3C CLOB(2097153K)); ERROR 42X44: Invalid length '2097153K' in column specification. ij> create table DB2LIM.FC4(FC4C CLOB(2147483648)); ERROR 42X44: Invalid length '2147483648' in column specification. ij> -- OK 2G and end up as 2GC - 1 (with modifier) create table DB2LIM.GC1(GC1C CLOB(2G)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC2(GC2C CLOB(2048M)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC3(GC3C CLOB(2097152K)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC4(GC4C CLOB(2147483647)); 0 rows inserted/updated/deleted ij> -- next lower value create table DB2LIM.GC5(GC5C CLOB(1G)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC6(GC6C CLOB(2047M)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC7(GC7C CLOB(2097151K)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC8(GC8C CLOB(2147483646)); 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC5; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC6; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC7; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC8; 0 rows inserted/updated/deleted ij> -- no length (default to 1Mb) create table DB2LIM.GC9(GC9C CLOB); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC10(GC10C CHARACTER LARGE OBJECT); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC11(GC11C CHAR LARGE OBJECT); 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC9; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC10; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC11; 0 rows inserted/updated/deleted ij> SELECT CAST (TABLENAME AS CHAR(10)) AS T, CAST (COLUMNNAME AS CHAR(10)) AS C, CAST (COLUMNDATATYPE AS CHAR(30)) AS Y FROM SYS.SYSTABLES T, SYS.SYSSCHEMAS S, SYS.SYSCOLUMNS C WHERE S.SCHEMAID = T.SCHEMAID AND CAST(S.SCHEMANAME AS VARCHAR(128))= 'DB2LIM' AND C.REFERENCEID = T.TABLEID ORDER BY 1; T |C |Y ---------------------------------------------------- GB1 |GB1C |BLOB(2147483647) GB2 |GB2C |BLOB(2147483647) GB3 |GB3C |BLOB(2147483647) GB4 |GB4C |BLOB(2147483647) GC1 |GC1C |CLOB(2147483647) GC2 |GC2C |CLOB(2147483647) GC3 |GC3C |CLOB(2147483647) GC4 |GC4C |CLOB(2147483647) ij> --- CHAR/VARCHAR and LOBs. (beetle 5741) --- test that we can insert CHAR/VARCHAR directly CREATE TABLE b (colone blob(1K)); 0 rows inserted/updated/deleted ij> VALUES '50'; 1 -- 50 ij> INSERT INTO b VALUES '50'; ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'CHAR'. ij> VALUES cast('50' as varchar(80)); 1 -------------------------------------------------------------------------------- 50 ij> INSERT INTO b VALUES cast('50' as varchar(80)); ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'VARCHAR'. ij> VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> INSERT INTO b VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K))); ERROR 42846: Cannot convert types 'VARCHAR' to 'BLOB'. ij> INSERT INTO b VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K))); ERROR 42846: Cannot convert types 'VARCHAR' to 'BLOB'. ij> VALUES cast('50' as long varchar); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO b VALUES cast('50' as long varchar); ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'LONG VARCHAR'. ij> -- test w LOBs VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> INSERT INTO b VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> VALUES (CAST('50' AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO b VALUES (CAST('50' AS CLOB(1K))); ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'CLOB'. ij> VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> INSERT INTO b VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> DROP TABLE b; 0 rows inserted/updated/deleted ij> CREATE TABLE c (colone clob(1K)); 0 rows inserted/updated/deleted ij> VALUES '50'; 1 -- 50 ij> INSERT INTO c VALUES '50'; 1 row inserted/updated/deleted ij> VALUES cast('50' as varchar(80)); 1 -------------------------------------------------------------------------------- 50 ij> INSERT INTO c VALUES cast('50' as varchar(80)); 1 row inserted/updated/deleted ij> VALUES (CAST('50' AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO c VALUES (CAST('50' AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO c VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES cast('50' as long varchar); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO c VALUES cast('50' as long varchar); 1 row inserted/updated/deleted ij> -- test w LOBs VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> INSERT INTO c VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> VALUES (CAST('50' AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO c VALUES (CAST('50' AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> INSERT INTO c VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> DROP TABLE c; 0 rows inserted/updated/deleted ij> CREATE TABLE n (colone clob(1K)); 0 rows inserted/updated/deleted ij> VALUES '50'; 1 -- 50 ij> INSERT INTO n VALUES '50'; 1 row inserted/updated/deleted ij> VALUES cast('50' as varchar(80)); 1 -------------------------------------------------------------------------------- 50 ij> INSERT INTO n VALUES cast('50' as varchar(80)); 1 row inserted/updated/deleted ij> VALUES (CAST('50' AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO n VALUES (CAST('50' AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO n VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES cast('50' as long varchar); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO n VALUES cast('50' as long varchar); 1 row inserted/updated/deleted ij> -- test w LOBs VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> INSERT INTO n VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> VALUES (CAST('50' AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO n VALUES (CAST('50' AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> INSERT INTO n VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> DROP TABLE n; 0 rows inserted/updated/deleted ij>