📄 ieptests.out
字号:
ERROR 38000: The exception 'SQL Exception: 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: extinout/nofile.delij> select * from t1 ;A -----------1 2 1 2 3 4 1 2 5 6 ij> --END IMPORT COMMIT/ROLLBACK TESTSING---all types supported by Derby import/exportcreate table alltypes(chartype char(20) , biginttype bigint , datetype date , decimaltype decimal(10,5) , doubletype double , inttype integer , lvartype long varchar , realtype real , sminttype smallint , timetype time , tstamptype timestamp , vartype varchar(50));0 rows inserted/updated/deletedij> insert into alltypes values('chartype string' , 9223372036854775807, '1993-10-29' , 12345.54321, 10E307, 2147483647, 'long varchar testing', 10E3, 32767, '09.39.43', 'xxxxxxFILTERED-TIMESTAMPxxxxx, 'varchar testing');1 row inserted/updated/deletedij> insert into alltypes values('chartype string' , -9223372036854775808, '1993-10-29' , 0.0, -10E307, -2147483647, 'long varchar testing', -10E3, 32767, '09.39.43', 'xxxxxxFILTERED-TIMESTAMPxxxxx, 'varchar testing');1 row inserted/updated/deletedij> insert into alltypes values('"chartype" string' , 9223372036854775807, '1993-10-29' , -12345.54321, 10E307, 2147483647, 'long "varchar" testing', 10E3, 32767, '09.39.43', 'xxxxxxFILTERED-TIMESTAMPxxxxx, '"varchar" testing');1 row inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , null, null, null) ;0 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , null, null, null, 0) ;0 rows inserted/updated/deletedij> select * from alltypes ;CHARTYPE |BIGINTTYPE |DATETYPE |DECIMALTYPE |DOUBLETYPE |INTTYPE |LVARTYPE |REALTYPE |SMINT&|TIMETYPE|TSTAMPTYPE |VARTYPE --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------chartype string |9223372036854775807 |1993-10-29|12345.54321 |1.0E308 |2147483647 |long varchar testing |10000.0 |32767 |09:39:43|xxxxxxFILTERED-TIMESTAMPxxxxx|varchar testing chartype string |-9223372036854775808|1993-10-29|0.00000 |-1.0E308 |-2147483647|long varchar testing |-10000.0 |32767 |09:39:43|xxxxxxFILTERED-TIMESTAMPxxxxx|varchar testing "chartype" string |9223372036854775807 |1993-10-29|-12345.54321 |1.0E308 |2147483647 |long "varchar" testing |10000.0 |32767 |09:39:43|xxxxxxFILTERED-TIMESTAMPxxxxx|"varchar" testing chartype string |9223372036854775807 |1993-10-29|12345.54321 |1.0E308 |2147483647 |long varchar testing |10000.0 |32767 |09:39:43|xxxxxxFILTERED-TIMESTAMPxxxxx|varchar testing chartype string |-9223372036854775808|1993-10-29|0.00000 |-1.0E308 |-2147483647|long varchar testing |-10000.0 |32767 |09:39:43|xxxxxxFILTERED-TIMESTAMPxxxxx|varchar testing "chartype" string |9223372036854775807 |1993-10-29|-12345.54321 |1.0E308 |2147483647 |long "varchar" testing |10000.0 |32767 |09:39:43|xxxxxxFILTERED-TIMESTAMPxxxxx|"varchar" testing ij> delete from alltypes;6 rows inserted/updated/deletedij> --import should work with trigger enabled on append and should not work on replacecreate table test1(a char(20)) ;0 rows inserted/updated/deletedij> create trigger trig_import after INSERT on alltypesreferencing new as newrowfor each row mode db2sqlinsert into test1 values(newrow.chartype);0 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , null, null, null, 0) ;0 rows inserted/updated/deletedij> select count(*) from alltypes ;1 -----------3 ij> select * from test1;A --------------------chartype string chartype string "chartype" string ij> delete from alltypes;3 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALLTYPES' , 'extinout/alltypes.del' , null, null, null, 1) ;ERROR 38000: The exception 'SQL Exception: Bulk insert replace is not permitted on 'APP.ALLTYPES' because it has an enabled trigger (TRIG_IMPORT).' was thrown while evaluating an expression.ERROR 42Z08: Bulk insert replace is not permitted on 'APP.ALLTYPES' because it has an enabled trigger (TRIG_IMPORT).ij> select count(*) from alltypes;1 -----------0 ij> drop trigger trig_import;0 rows inserted/updated/deletedij> drop table test1;0 rows inserted/updated/deletedij> --test importing to identity columnscreate table table1(c1 char(30), c2 int generated always as identity, c3 real, c4 char(1));0 rows inserted/updated/deletedij> create table table2(c1 char(30), c2 int, c3 real, c4 char(1));0 rows inserted/updated/deletedij> insert into table2 values('Robert',100, 45.2, 'J');1 row inserted/updated/deletedij> insert into table2 values('Mike',101, 76.9, 'K');1 row inserted/updated/deletedij> insert into table2 values('Leo',102, 23.4, 'I');1 row inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select c1,c3,c4 from table2' , 'extinout/import.del' , null, null, null) ;0 rows inserted/updated/deletedij> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL,'TABLE1', 'C1,C3,C4' , null, 'extinout/import.del',null, null,null,0);0 rows inserted/updated/deletedij> select * from table1;C1 |C2 |C3 |C4 -------------------------------------------------------------Robert |1 |45.2 |J Mike |2 |76.9 |K Leo |3 |23.4 |I ij> delete from table1;3 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' , 'extinout/import.del', null, null, null) ;0 rows inserted/updated/deletedij> --following import should fail becuase of inserting into identity column.CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', 'extinout/import.del',null, null, null,1);ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'C2'.' was thrown while evaluating an expression.ERROR 42Z23: Attempt to modify an identity column 'C2'. ij> --following import should be succesfulCALL SYSCS_UTIL.SYSCS_IMPORT_DATA(NULL, 'TABLE1', 'C1,C3,C4' , '1,3,4', 'extinout/import.del',null, null, null,1);0 rows inserted/updated/deletedij> select * from table1;C1 |C2 |C3 |C4 -------------------------------------------------------------Robert |1 |45.2 |J Mike |2 |76.9 |K Leo |3 |23.4 |I ij> update table2 set c2=null;3 rows inserted/updated/deletedij> --check null values import to identity columns should also failcall SYSCS_UTIL.SYSCS_EXPORT_TABLE(null , 'TABLE2' , 'extinout/import.del' , null, null, null) ;0 rows inserted/updated/deletedij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'TABLE1', 'extinout/import.del',null, null, null,1);ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'C2'.' was thrown while evaluating an expression.ERROR 42Z23: Attempt to modify an identity column 'C2'. ij> select * from table1;C1 |C2 |C3 |C4 -------------------------------------------------------------Robert |1 |45.2 |J Mike |2 |76.9 |K Leo |3 |23.4 |I ij> --check that replace fails when there dependents and replaced data --does not violate foreign key constraints.create table parent(a int not null primary key);0 rows inserted/updated/deletedij> insert into parent values (1) , (2) , (3) , (4) ;4 rows inserted/updated/deletedij> create table child(b int references parent(a));0 rows inserted/updated/deletedij> insert into child values (1) , (2) , (3) , (4) ;4 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from parent where a < 3' , 'extinout/parent.del' , null, null, null) ;0 rows inserted/updated/deletedij> --replace should fail because of dependent tableCALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'PARENT', 'extinout/parent.del',null, null, null,1);ERROR 38000: The exception 'SQL Exception: INSERT on table 'PARENT' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (3). The statement has been rolled back.' was thrown while evaluating an expression.ERROR 23503: INSERT on table 'PARENT' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (3). The statement has been rolled back.ij> select * from parent;A -----------1 2 3 4 ij> ---test with a file which has a differen records seperators (\n, \r , \r\n)create table nt1( a int , b char(30));0 rows inserted/updated/deletedij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(NULL, 'NT1', 'extin/mixednl.del',null, null, 'UTF-8',0);0 rows inserted/updated/deletedij> select * from nt1;A |B ------------------------------------------0 |XXXXXX0 1 |XXXXXX1 2 |XXXXXX2 3 |XXXXXX3 4 |XXXXXX4 5 |YYYYY5 6 |YYYYY6 7 |YYYYY7 8 |YYYYY8 9 |YYYYY9 10 |ZZZZZZ10 11 |ZZZZZZ11 12 |ZZZZZZ12 13 |ZZZZZZ13 14 |ZZZZZZ14 ij> drop table nt1 ;0 rows inserted/updated/deleted⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -