📄 synonym.out
字号:
ij> -- tests for synonym supportset schema APP;0 rows inserted/updated/deletedij> -- negative tests-- Create a synonym to itself. Error.create synonym syn for syn;ERROR 42916: Synonym 'SYN' cannot be created for 'APP.SYN' as it would result in a circular synonym chain.ij> create synonym syn for APP.syn;ERROR 42916: Synonym 'SYN' cannot be created for 'APP.SYN' as it would result in a circular synonym chain.ij> create synonym APP.syn for syn;ERROR 42916: Synonym 'APP.SYN' cannot be created for 'APP.SYN' as it would result in a circular synonym chain.ij> create synonym APP.syn for APP.syn;ERROR 42916: Synonym 'APP.SYN' cannot be created for 'APP.SYN' as it would result in a circular synonym chain.ij> -- Create a simple synonym loop. Error.create synonym synonym1 for synonym;0 rows inserted/updated/deletedWARNING 01522: The newly defined synonym 'SYNONYM1' resolved to the object 'APP.SYNONYM' which is currently undefined.ij> create synonym synonym for synonym1;ERROR 42916: Synonym 'SYNONYM' cannot be created for 'SYNONYM1' as it would result in a circular synonym chain.ij> drop synonym synonym1;0 rows inserted/updated/deletedij> -- Create a larger synonym loop.create synonym ts1 for ts;0 rows inserted/updated/deletedWARNING 01522: The newly defined synonym 'TS1' resolved to the object 'APP.TS' which is currently undefined.ij> create synonym ts2 for ts1;0 rows inserted/updated/deletedWARNING 01522: The newly defined synonym 'TS2' resolved to the object 'APP.TS' which is currently undefined.ij> create synonym ts3 for ts2;0 rows inserted/updated/deletedWARNING 01522: The newly defined synonym 'TS3' resolved to the object 'APP.TS' which is currently undefined.ij> create synonym ts4 for ts3;0 rows inserted/updated/deletedWARNING 01522: The newly defined synonym 'TS4' resolved to the object 'APP.TS' which is currently undefined.ij> create synonym ts5 for ts4;0 rows inserted/updated/deletedWARNING 01522: The newly defined synonym 'TS5' resolved to the object 'APP.TS' which is currently undefined.ij> create synonym ts6 for ts5;0 rows inserted/updated/deletedWARNING 01522: The newly defined synonym 'TS6' resolved to the object 'APP.TS' which is currently undefined.ij> create synonym ts for ts6;ERROR 42916: Synonym 'TS' cannot be created for 'TS6' as it would result in a circular synonym chain.ij> drop synonym App.ts1;0 rows inserted/updated/deletedij> drop synonym "APP".ts2;0 rows inserted/updated/deletedij> drop synonym TS3;0 rows inserted/updated/deletedij> drop synonym ts4;0 rows inserted/updated/deletedij> drop synonym ts5;0 rows inserted/updated/deletedij> drop synonym app.ts6;0 rows inserted/updated/deletedij> -- Synonyms and table/view share same namespace. Negative tests for this.create table table1 (i int, j int);0 rows inserted/updated/deletedij> insert into table1 values (1,1), (2,2);2 rows inserted/updated/deletedij> create view view1 as select i, j from table1;0 rows inserted/updated/deletedij> create synonym table1 for t1;ERROR X0Y68: Table/View 'TABLE1' already exists.ij> create synonym APP.Table1 for t1;ERROR X0Y68: Table/View 'TABLE1' already exists.ij> create synonym app.TABLE1 for "APP"."T";ERROR X0Y68: Table/View 'TABLE1' already exists.ij> create synonym APP.VIEW1 for v1;ERROR X0Y68: Table/View 'VIEW1' already exists.ij> create synonym "APP"."VIEW1" for app.v;ERROR X0Y68: Table/View 'VIEW1' already exists.ij> -- Synonyms can't be created on temporary tablesdeclare global temporary table session.t1 (c1 int) not logged;0 rows inserted/updated/deletedij> create synonym synForTemp for session.t1;ERROR XCL51: The requested function can not reference tables in SESSION schema.ij> create synonym synForTemp for session."T1";ERROR XCL51: The requested function can not reference tables in SESSION schema.ij> -- Synonyms can't be created in session schemascreate synonym session.table1 for APP.table1;ERROR XCL51: The requested function can not reference tables in SESSION schema.ij> -- Creating a table or a view when a synonym of that name is present. Error.create synonym myTable for table1;0 rows inserted/updated/deletedij> create table myTable(i int, j int);ERROR X0Y32: Table/View 'MYTABLE' already exists in Schema 'APP'.ij> create view myTable as select * from table1;ERROR X0Y32: Table/View 'MYTABLE' already exists in Schema 'APP'.ij> -- Positive test cases-- Using synonym in DMLselect * from myTable;I |J -----------------------1 |1 2 |2 ij> select * from table1;I |J -----------------------1 |1 2 |2 ij> insert into myTable values (3,3), (4,4);2 rows inserted/updated/deletedij> select * from mytable;I |J -----------------------1 |1 2 |2 3 |3 4 |4 ij> update myTable set i=3 where j=4;1 row inserted/updated/deletedij> select * from mytable;I |J -----------------------1 |1 2 |2 3 |3 3 |4 ij> select * from table1;I |J -----------------------1 |1 2 |2 3 |3 3 |4 ij> delete from myTable where i> 2;2 rows inserted/updated/deletedij> select * from "APP"."MYTABLE";I |J -----------------------1 |1 2 |2 ij> select * from APP.table1;I |J -----------------------1 |1 2 |2 ij> -- Try some cursorsget cursor c1 as 'select * from myTable';ij> next c1;I |J -----------------------1 |1 ij> next c1;I |J -----------------------2 |2 ij> close c1;ij> -- Try updatable cursorsautocommit off;ij> get cursor c2 as 'select * from myTable for update';ij> next c2;I |J -----------------------1 |1 ij> update myTable set i=5 where current of c2;1 row inserted/updated/deletedij> close c2;ij> autocommit on;ij> select * from table1;I |J -----------------------5 |1 2 |2 ij> -- Try updatable cursors, with synonym at the top, base table inside.autocommit off;ij> get cursor c2 as 'select * from app.table1 for update';ij> next c2;I |J -----------------------5 |1 ij> update myTable set i=6 where current of c2;1 row inserted/updated/deletedij> close c2;ij> autocommit on;ij> select * from table1;I |J -----------------------6 |1 2 |2 ij> -- trigger testscreate table table2 (i int, j int);0 rows inserted/updated/deletedij> -- Should failcreate trigger tins after insert on myTable referencing new_table as new for each statement mode db2sql insert into table2 select i,j from table1;ERROR 42Y55: 'CREATE TRIGGER' cannot be performed on 'MYTABLE' because it does not exist.ij> -- Should passcreate trigger tins after insert on table1 referencing new_table as new for each statement mode db2sql insert into table2 select i,j from table1;0 rows inserted/updated/deletedij> drop trigger tins;0 rows inserted/updated/deletedij> create trigger triggerins after insert on table2 referencing new_table as new for each statement mode db2sql insert into myTable select i,j from new;0 rows inserted/updated/deletedij> select * from myTable;I |J -----------------------6 |1 2 |2 ij> insert into table2 values (5, 5);1 row inserted/updated/deletedij> select * from myTable;I |J -----------------------6 |1 2 |2 5 |5 ij> drop table table2;0 rows inserted/updated/deletedij> -- Try referential constraints. Synonyms should not be allowed there.create table primaryTab (i int not null primary key, j int, c char(10));0 rows inserted/updated/deletedij> create synonym synPrimary for primaryTab;0 rows inserted/updated/deletedij> -- Should failcreate table foreignTab(i int, j int references synPrimary(i));ERROR X0Y46: Constraint 'xxxxGENERATED-IDxxxx' is invalid: referenced table SYNPRIMARY does not exist.ij> create table foreignTab(i int, j int references primaryTab(i));0 rows inserted/updated/deletedij> drop table foreignTab;0 rows inserted/updated/deletedij> drop table primaryTab;0 rows inserted/updated/deletedij> drop synonym synPrimary;0 rows inserted/updated/deletedij> -- Tests with non existant schemas-- Implicitly creates junkSchemacreate synonym junkSchema.syn1 for table2;0 rows inserted/updated/deletedWARNING 01522: The newly defined synonym 'SYN1' resolved to the object 'JUNKSCHEMA.TABLE2' which is currently undefined.ij> select * from junkSchema.syn1;ERROR 42X05: Table 'JUNKSCHEMA.TABLE2' does not exist.ij> set schema junkSchema;0 rows inserted/updated/deletedij> create table table2(c char(10));0 rows inserted/updated/deletedij> select * from syn1;C ----------ij> set schema APP;0 rows inserted/updated/deletedij> -- Should resolve to junkSchema.table2select * from junkSchema.syn1;C ----------ij> drop table junkSchema.table2;0 rows inserted/updated/deletedij> -- Should fail. Need to drop synonym firstdrop schema junkSchema restrict;ERROR X0Y54: Schema 'JUNKSCHEMA' cannot be dropped because it is not empty.ij> drop synonym junkSchema.syn1;0 rows inserted/updated/deletedij> drop schema junkSchema restrict;0 rows inserted/updated/deletedij> -- Test with target schema missingcreate synonym mySynonym for notPresent.t1;0 rows inserted/updated/deletedWARNING 01522: The newly defined synonym 'MYSYNONYM' resolved to the object 'NOTPRESENT.T1' which is currently undefined.ij> select * from mySynonym;ERROR 42Y07: Schema 'NOTPRESENT' does not existij> create table notPresent.t1(j int, c char(10));0 rows inserted/updated/deletedij> insert into notPresent.t1 values (100, 'satheesh');1 row inserted/updated/deletedij> -- Should resolve nowselect * from mySynonym;J |C ----------------------100 |satheesh ij> drop table notPresent.t1;0 rows inserted/updated/deletedij> drop synonym mySynonym;0 rows inserted/updated/deletedij> -- Positive test case with three levels of synonym chainingcreate schema synonymSchema;0 rows inserted/updated/deletedij> create synonym synonymSchema.mySynonym1 for APP.table1;0 rows inserted/updated/deletedij> create synonym APP.mySynonym2 for "SYNONYMSCHEMA"."MYSYNONYM1";0 rows inserted/updated/deletedij> create synonym mySynonym for mySynonym2;0 rows inserted/updated/deletedij> select * from table1;I |J -----------------------6 |1 2 |2 5 |5 ij> select * from mySynonym;I |J -----------------------6 |1 2 |2 5 |5 ij> insert into mySynonym values (6,6);1 row inserted/updated/deletedij> insert into mySynonym select * from mySynonym where i<2;0 rows inserted/updated/deletedij> select * from mySynonym;I |J -----------------------6 |1 2 |2 5 |5 6 |6 ij> update mySynonym set j=5;4 rows inserted/updated/deletedij> update mySynonym set j=4 where i=5;1 row inserted/updated/deletedij> delete from mySynonym where j=6;0 rows inserted/updated/deletedij> select * from mySynonym;I |J -----------------------6 |5 2 |5 5 |4 6 |5 ij> select * from table1;I |J -----------------------6 |5 2 |5 5 |4 6 |5 ij> -- cursor on mySynonymget cursor c1 as 'select * from mySynonym';ij> next c1;I |J -----------------------6 |5 ij> next c1;I |J -----------------------2 |5 ij> next c1;I |J -----------------------5 |4 ij> close c1;ij> -- More negative tests to check dependenciesselect * from mySynonym;I |J -----------------------6 |5 2 |5 5 |4 6 |5 ij> drop synonym mySynonym;0 rows inserted/updated/deletedij> -- Previously compiled cached statement should get invalidatedselect * from mySynonym;ERROR 42X05: Table 'MYSYNONYM' does not exist.ij> create synonym mySyn for table1;0 rows inserted/updated/deletedij> create view v1 as select * from mySyn;0 rows inserted/updated/deletedij> create view v2 as select * from v1;0 rows inserted/updated/deletedij> -- Drop synonym should fail since it is used in two views.drop synonym mySyn;ERROR X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V1' is dependent on that object.ERROR X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V2' is dependent on that object.ij> drop view v2;0 rows inserted/updated/deletedij> -- fail stilldrop synonym mySyn;ERROR X0Y23: Operation 'DROP SYNONYM' cannot be performed on object 'MYSYN' because VIEW 'V1' is dependent on that object.ij> drop view v1;0 rows inserted/updated/deletedij> -- should passdrop synonym mySyn;0 rows inserted/updated/deletedij> -- drop and recreate schema testcreate schema testSchema;0 rows inserted/updated/deletedij> create synonym multiSchema for testSchema.testtab;0 rows inserted/updated/deletedWARNING 01522: The newly defined synonym 'MULTISCHEMA' resolved to the object 'TESTSCHEMA.TESTTAB' which is currently undefined.ij> select * from multiSchema;ERROR 42X05: Table 'TESTSCHEMA.TESTTAB' does not exist.ij> create table testSchema.testtab(i int, c char(10));0 rows inserted/updated/deletedij> insert into testSchema.testtab values (1, 'synonym');1 row inserted/updated/deletedij> select * from multiSchema;I |C ----------------------1 |synonym ij> drop table testSchema.testtab;0 rows inserted/updated/deletedij> drop schema testSchema restrict;0 rows inserted/updated/deletedij> create schema testSchema;0 rows inserted/updated/deletedij> create table testSchema.testtab(j int, c1 char(10), c2 char(20));0 rows inserted/updated/deletedij> insert into testSchema.testtab values (1, 'synonym', 'test');1 row inserted/updated/deletedij> select * from multiSchema;J |C1 |C2 -------------------------------------------1 |synonym |test ij> drop synonym multiSchema;0 rows inserted/updated/deletedij> drop table testSchema.testtab;0 rows inserted/updated/deletedij> drop view view1;0 rows inserted/updated/deletedij> drop table table1;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -