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. -- --testing error cases for import/export create schema iep; 0 rows inserted/updated/deleted ij> create table iep.t1(a int); 0 rows inserted/updated/deleted ij> insert into iep.t1 values(100) , (101) , (102) , (103) , (104) , (105) , (106); 7 rows inserted/updated/deleted ij> --export error cases --export can not create file call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extout/nodir/t1.dat' , null, null, null) ; ERROR XIE0I: An IOException occurred while writing data to the file. ij> --export table not found call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'NOTABLE' , 'extinout/t1.dat' , null, null, null) ; ERROR 38000: The exception 'java.sql.SQLException: Table/View 'IEP.NOTABLE' does not exist.' was thrown while evaluating an expression. ERROR 42X05: Table/View 'IEP.NOTABLE' does not exist. ij> ---export schema is not valid call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('XXXX', 'T1' , 'extinout/t1.dat' , null, null, null) ; ERROR 38000: The exception 'java.sql.SQLException: Schema 'XXXX' does not exist' was thrown while evaluating an expression. ERROR 42Y07: Schema 'XXXX' does not exist ij> --export query is invalid (syntax error) call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select from t1', 'extinout/t1.dat' , null, null, null) ; ERROR 38000: The exception 'java.sql.SQLException: Syntax error: Encountered "from" at line 1, column 8.' was thrown while evaluating an expression. ERROR 42X01: Syntax error: Encountered "from" at line 1, column 8. ij> --export codeset is invalid call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from iep.t1', 'extinout/t1.dat' , null, null, 'NOSUCHCODESET') ; ERROR XIE0I: An IOException occurred while writing data to the file. ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('XXXX', 'T1' , 'extinout/t1.dat' , null, null, null) ; ERROR 38000: The exception 'java.sql.SQLException: Schema 'XXXX' does not exist' was thrown while evaluating an expression. ERROR 42Y07: Schema 'XXXX' does not exist ij> --export delimiter errror cases --period can not be used as character ot column delimiter call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , null, '.', null) ; ERROR XIE0K: The period was specified as a character string delimiter. ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , '.', null, null) ; ERROR XIE0J: A delimiter is not valid or is used more than once. ij> --same delimter can not be used as character and column delimters call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , ';', ';', null) ; ERROR XIE0J: A delimiter is not valid or is used more than once. ij> --space character can not be a delimiter call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , ' ', ';', null) ; ERROR XIE0J: A delimiter is not valid or is used more than once. ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , null, ' ', null) ; ERROR XIE0J: A delimiter is not valid or is used more than once. ij> --if emtry strinng is passed actual value delimiter should be space --and the that should become a invalid delimiter call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , '', ';', null) ; ERROR XIE0J: A delimiter is not valid or is used more than once. ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , null, '', null) ; ERROR XIE0J: A delimiter is not valid or is used more than once. ij> --more than one character passed to the delimiters get truncated to one --following one should give error because eventually '\' delimiter --is used a both for char and col call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , '\a', '\', null) ; ERROR XIE0J: A delimiter is not valid or is used more than once. ij> --DO A VALID EXPORT AND IMPORT set schema iep; 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , null, null, 'utf-8') ; 0 rows inserted/updated/deleted ij> delete from t1 ; 7 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE('IEP', 'T1' , 'extinout/t1.dat' , null, null, 'utf-8', 0) ; 0 rows inserted/updated/deleted ij> select * from t1; A ----------- 100 101 102 103 104 105 106 ij> --import error cases --import can not find input file call SYSCS_UTIL.SYSCS_IMPORT_TABLE('IEP', 'T1' , 'extin/nodir/t1.dat' , null, null, null, 0) ; ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. ERROR 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. ERROR XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. ERROR XIE04: Data file not found: extin/nodir/t1.dat ij> --import table not found call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'NOTABLE' , 'extinout/t1.dat' , null, null, null, 0) ; ERROR XIE0M: Table 'IEP.NOTABLE' does not exist. ij> --import schema is not valid call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('XXXX', 'T1' , 'extinout/t1.dat' , null, null, null, 0) ; ERROR XIE0M: Table 'XXXX.T1' does not exist. ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , null, null, 'INCORRECTCODESET', 0) ; ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. ERROR 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. ERROR XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. ERROR XJ001: Java exception: 'java.io.UnsupportedEncodingException: INCORRECTCODESET'. ij> --check import with invalid delimiter usage --if emtry strinng is passed actual value delimiter should be space --and the that should become a invalid delimiter call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , '', ';', null, 0) ; ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. ERROR 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. ERROR XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. ERROR XIE0J: A delimiter is not valid or is used more than once. ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , null, '', null, 0) ; ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. ERROR 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. ERROR XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. ERROR XIE0J: A delimiter is not valid or is used more than once. ij> --same delimter can not be used as character and column delimters call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T1' , 'extinout/t1.dat' , ';', ';', null, 1) ; ERROR 38000: The exception 'java.sql.SQLException: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression.' was thrown while evaluating an expression. ERROR 38000: The exception 'java.lang.reflect.InvocationTargetException' was thrown while evaluating an expression. ERROR XJ001: Java exception: ': java.lang.reflect.InvocationTargetException'. ERROR XIE0J: A delimiter is not valid or is used more than once. ij> autocommit off; ij> create table v1(a int) ; 0 rows inserted/updated/deleted ij> declare global temporary table session.temp1(c1 int) on commit preserve rows not logged; 0 rows inserted/updated/deleted ij> insert into session.temp1 values(1) , (2) , (3) , (4) , (5) , (6); 6 rows inserted/updated/deleted ij> select * from session.temp1; C1 ----------- 1 2 3 4 5 6 ij> --export to from a temporary table call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('SESSION', 'TEMP1' , 'extinout/temp1.dat' , null, null, null) ; 0 rows inserted/updated/deleted ij> -- because temporary table has on commit preserve rows, commit issued by export will not delete data from the temp table. select * from session.temp1; C1 ----------- 1 2 3 4 5 6 ij> --import back to a regualr table call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'V1' , 'extinout/temp1.dat' , null, null, null, 0) ; 0 rows inserted/updated/deleted ij> select * from v1; A ----------- 1 2 3 4 5 6 ij> commit; ij> --import to a temp table should fail with a table not found errror declare global temporary table session.temp2(c1 int) not logged; 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('SESSION', 'TEMP2' , 'extinout/temp1.dat' , null, null, null, 0) ; ERROR XIE0M: Table 'SESSION.TEMP2' does not exist. ij> select * from session.temp2 ; ERROR 42X05: Table/View 'SESSION.TEMP2' does not exist. ij> commit ; ij> drop table v1; 0 rows inserted/updated/deleted ij> autocommit on; ij> create table t3(c1 int , c2 double , c3 decimal , c4 varchar(20) ); 0 rows inserted/updated/deleted ij> insert into t3 values(1 , 3.5 , 8.6 , 'test strings'); 1 row inserted/updated/deleted ij> insert into t3 values(2 , 3.5 , 8.6 , 'test strings'); 1 row inserted/updated/deleted ij> insert into t3 values(3 , 3.5 , 8.6 , 'test strings'); 1 row inserted/updated/deleted ij> insert into t3 values(4 , 3.5 , 8.6 , 'test strings'); 1 row inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T3' , 'extinout/t3.dat' , null, null, null) ; 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'T3' , 'extinout/t3.dat' , null, null, null, 0) ; 0 rows inserted/updated/deleted ij> select * from t3; C1 |C2 |C3 |C4 -------------------------------------------------------------- 1 |3.5 |8 |test strings 2 |3.5 |8 |test strings 3 |3.5 |8 |test strings 4 |3.5 |8 |test strings 1 |3.5 |8 |test strings 2 |3.5 |8 |test strings 3 |3.5 |8 |test strings 4 |3.5 |8 |test strings ij> --import data column names are incorrect call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'X1, X2, X3, X4', null, 'extinout/t3.dat' , null, null, null, 0) ; ERROR XIE08: There is no column named: X1. ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'X1, X2, X3', '1,2,3,4', 'extinout/t3.dat' , null, null, null, 0) ; ERROR XIE08: There is no column named: X1. ij> --import data insert column names count < column indexes does not match call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'C1, C2, C3', '1,2,3,4', 'extinout/t3.dat' , null, null, null, 0) ; 0 rows inserted/updated/deleted ij> --import data column indexes count > insert columns count call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'C1, C2, C3,C4', '1,2', 'extinout/t3.dat' , null, null, null, 0) ; ERROR 38000: The exception 'java.sql.SQLException: The number of values assigned is not the same as the number of specified or implied columns.' was thrown while evaluating an expression. ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , null, '11,22,12,24', 'extinout/t3.dat' , null, null, null, 0) ; ERROR 38000: The exception 'java.sql.SQLException: Column 'COLUMN11' 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 'COLUMN11' is not a column in the target table.' was thrown while evaluating an expression. ERROR 42X04: Column 'COLUMN11' 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 'COLUMN11' is not a column in the target table. ij> --repeat the above type cases with empty file and minor variation to paramters delete from t3 ; 12 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'T3' , 'extinout/t3.dat' , ';', '^', 'utf-16') ; 0 rows inserted/updated/deleted ij> --import data column names are incorrect call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'X1, X2, X3, X4', null, 'extinout/t3.dat' , ';', '^', 'utf-16', 1) ; ERROR XIE08: There is no column named: X1. ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'X1, X2, X3', '1,2,3,4', 'extinout/t3.dat' , ';', '^', 'utf-16', 1) ; ERROR XIE08: There is no column named: X1. ij> --import data insert column names count < column indexes does not match call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , 'C1, C2, C3', null, 'extinout/t3.dat' , ';', '^', 'utf-16', 1) ; 0 rows inserted/updated/deleted ij> --import data column indexes count > insert columns count call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , null, '1,2', 'extinout/t3.dat' , ';', '^', 'utf-16', 1) ; ERROR 38000: The exception 'java.sql.SQLException: The number of values assigned is not the same as the number of specified or implied columns.' was thrown while evaluating an expression. ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. ij> --specify column indexes that are not there in the file that is being imported call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'T3' , null, '11,22,12,24', 'extinout/t3.dat' , ';', '^', 'utf-16', 1) ; 0 rows inserted/updated/deleted ij> --import to a system table shoud fail call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('SYS', 'SYSTABLES' , 'extinout/t3.dat' , ';', '^', 'utf-16', 1) ; ERROR 38000: The exception 'java.sql.SQLException: 'SYS.SYSTABLES' is a system table. Users are not allowed to modify the contents of this table.' was thrown while evaluating an expression. ERROR 42Y25: 'SYS.SYSTABLES' is a system table. Users are not allowed to modify the contents of this table. ij> ---not supported by db2 cloudscape import/export create table ntype(a int , ct CLOB(1024)); 0 rows inserted/updated/deleted ij> create table ntype1(bt BLOB(1024) , a int); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('IEP', 'NTYPE' , 'extinout/ntype.dat' , null, null, null) ; ERROR XIE0B: Column 'CT' in the table is of type CLOB, it is not supported by the import/export feature. ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from iep.ntype1', 'extinout/ntype.dat' , null, null, null) ; ERROR XIE0B: Column 'BT' in the table is of type BLOB, it is not supported by the import/export feature. ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE ('IEP', 'NTYPE' , 'extinout/ntype.dat' , null, null, null, 0) ; ERROR XIE0B: Column 'CT' in the table is of type CLOB, it is not supported by the import/export feature. ij> call SYSCS_UTIL.SYSCS_IMPORT_DATA('IEP', 'NTYPE1' , null , null, 'extinout/ntype.dat' , null, null, null, 0) ; ERROR XIE0B: Column 'BT' in the table is of type BLOB, it is not supported by the import/export feature. ij> --import should aquire a lock on the table create table parent(a int not null primary key); 0 rows inserted/updated/deleted ij> insert into parent values (1) , (2) , (3) , (4) ; 4 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from parent where a < 3' , 'extinout/parent.del' , null, null, null) ; 0 rows inserted/updated/deleted ij> connect 'wombat' as c1; ij(C1)> connect 'wombat' as c2; ij(C2)> set connection c1; ij(C1)> autocommit off; ij(C1)> lock table iep.parent in share mode; 0 rows inserted/updated/deleted ij(C1)> set connection c2; ij(C2)> autocommit off; ij(C2)> --following import should fail with lock time out call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks.waitTimeout', '5'); 0 rows inserted/updated/deleted ij(C2)> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('IEP', 'PARENT', 'extinout/parent.del',null, null, null,1); ERROR 38000: The exception 'java.sql.SQLException: Table 'IEP.PARENT' cannot be locked in 'EXCLUSIVE' mode.' was thrown while evaluating an expression. ERROR X0X02: Table 'IEP.PARENT' cannot be locked in 'EXCLUSIVE' mode. ERROR 40XL1: A lock could not be obtained within the time requested ij(C2)> disconnect c1; ij(C2)> disconnect c2; ij> set connection connection0; ij>