📄 schemas.out
字号:
0 rows inserted/updated/deletedij> create table foo1 (a int);ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSPROC' schema.ij> create view foo1 as select * from app.test;ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSPROC' schema.ij> set schema SYSSTAT;0 rows inserted/updated/deletedij> create table foo1 (a int);ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema.ij> create view foo1 as select * from app.test;ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSSTAT' schema.ij> set schema SYSSTAT;0 rows inserted/updated/deletedij> create table foo1 (a int);ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema.ij> create view foo1 as select * from app.test;ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSSTAT' schema.ij> set schema SYSCS_DIAG;0 rows inserted/updated/deletedij> create table foo1 (a int);ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_DIAG' schema.ij> create view foo1 as select * from app.test;ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCS_DIAG' schema.ij> set schema SYSCS_UTIL;0 rows inserted/updated/deletedij> create table foo1 (a int);ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_UTIL' schema.ij> create view foo1 as select * from app.test;ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCS_UTIL' schema.ij> set schema NULLID;0 rows inserted/updated/deletedij> create table foo1 (a int);ERROR 42X62: 'CREATE TABLE' is not allowed in the 'NULLID' schema.ij> create view foo1 as select * from app.test;ERROR 42X62: 'CREATE VIEW' is not allowed in the 'NULLID' schema.ij> set schema SYSIBM;0 rows inserted/updated/deletedij> create table foo1 (a int);ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSIBM' schema.ij> create view foo1 as select * from app.test;ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSIBM' schema.ij> set schema SQLJ;0 rows inserted/updated/deletedij> create table foo1 (a int);ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SQLJ' schema.ij> create view foo1 as select * from app.test;ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SQLJ' schema.ij> -- Negative tests. Disable use of schemas starting with SYSset schema app;0 rows inserted/updated/deletedij> create table t1 (c1 int);0 rows inserted/updated/deletedij> create trigger sysblah.trig1 after update of c1 on t1 for each row mode db2sql insert into t1 values 1;ERROR 42X62: 'CREATE TRIGGER' is not allowed in the 'SYSBLAH' schema.ij> create procedure sysblah.dummy() language java external name 'NotReallyThere.NoMethod' parameter style java;ERROR 42X62: 'CREATE PROCEDURE' is not allowed in the 'SYSBLAH' schema.ij> drop table t1;0 rows inserted/updated/deletedij> set schema app;0 rows inserted/updated/deletedij> -- create a schemacreate schema test;0 rows inserted/updated/deletedij> -- create it again, should failcreate schema test;ERROR X0Y68: Schema 'TEST' already exists.ij> -- verify itselect schemaname, authorizationid from sys.sysschemas where schemaname = 'TEST';SCHEMANAME |AUTHORIZATIONID -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TEST |APP ij> -- create a table in testset schema test;0 rows inserted/updated/deletedij> create table sampletab (c1 int check (c1 > 1), c2 char(20));0 rows inserted/updated/deletedij> insert into sampletab values (1,'in schema: TEST');ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'TEST.SAMPLETAB'.ij> insert into sampletab values (2,'in schema: TEST');1 row inserted/updated/deletedij> -- verify itselect schemaname, tablename, descriptor from sys.sysschemas s, sys.sysconglomerates c , sys.systables t where t.tablename = 'SAMPLETAB' and s.schemaid = c.schemaid and c.tableid = t.tableid;SCHEMANAME |TABLENAME |DESCRIPTOR ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TEST |SAMPLETAB |NULL ij> -- do some ddl on said tablecreate index ixsampletab on sampletab(c1);0 rows inserted/updated/deletedij> create index ix2sampletab on test.sampletab(c1);0 rows inserted/updated/deletedWARNING 01504: The new index is a duplicate of an existing index: IXSAMPLETAB.ij> create view vsampletab as select * from sampletab;0 rows inserted/updated/deletedij> create view v2sampletab as select * from test.sampletab;0 rows inserted/updated/deletedij> alter table sampletab add column c3 int;0 rows inserted/updated/deletedij> -- switch schemasset schema APP;0 rows inserted/updated/deletedij> -- create table with same name in APPcreate table sampletab (c1 int check(c1 > 1), c2 char(20));0 rows inserted/updated/deletedij> insert into sampletab values (2,'in schema: APP');1 row inserted/updated/deletedij> -- verify it--select schemaname, tablename, descriptor as descrfrom sys.sysschemas s, sys.sysconglomerates c , sys.systables twhere t.tablename = 'SAMPLETAB' and s.schemaid = c.schemaid and c.tableid = t.tableidorder by schemaname, tablename;SCHEMANAME |TABLENAME |DESCR ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------APP |SAMPLETAB |NULL TEST |SAMPLETAB |BTREE (1) TEST |SAMPLETAB |NULL ij> -- select from both the tablesselect * from sampletab;C1 |C2 --------------------------------2 |in schema: APP ij> select * from test.sampletab;C1 |C2 |C3 --------------------------------------------2 |in schema: TEST |NULL ij> -- switch to the test schemaset schema test;0 rows inserted/updated/deletedij> select * from sampletab;C1 |C2 |C3 --------------------------------------------2 |in schema: TEST |NULL ij> select * from app.sampletab;C1 |C2 --------------------------------2 |in schema: APP ij> -- try a drop, should fail since we haven't-- cleaned out everything in the schemadrop schema test RESTRICT;ERROR X0Y54: Schema 'TEST' cannot be dropped because it is not empty.ij> -- make sure use the correct schema for various ddldrop view vsampletab;0 rows inserted/updated/deletedij> drop view v2sampletab;0 rows inserted/updated/deletedij> drop index ixsampletab;0 rows inserted/updated/deletedij> drop index ix2sampletab;ERROR 42X65: Index 'IX2SAMPLETAB' does not exist.ij> alter table sampletab add column c4 int;0 rows inserted/updated/deletedij> select * from sampletab;C1 |C2 |C3 |C4 --------------------------------------------------------2 |in schema: TEST |NULL |NULL ij> -- get rid of last object in testdrop table sampletab;0 rows inserted/updated/deletedij> -- try a drop now, should be okdrop schema test RESTRICT;0 rows inserted/updated/deletedij> -- use quoted idcreate schema "heLLo";0 rows inserted/updated/deletedij> create schema "sys";0 rows inserted/updated/deletedij> -- should faildrop schema "hello" RESTRICT;ERROR 42Y07: Schema 'hello' does not existij> -- okdrop schema "heLLo" RESTRICT;0 rows inserted/updated/deletedij> drop schema "sys" RESTRICT;0 rows inserted/updated/deletedij> -- try prepared statements, should failprepare createSchema as 'create schema ?';ERROR 42X01: Syntax error: Encountered "?" at line 1, column 15.ij> prepare dropSchema as 'drop schema ? RESTRICT';ERROR 42X01: Syntax error: Encountered "?" at line 1, column 13.ij> ---- specific drop schema tests, all should fail--create schema x;0 rows inserted/updated/deletedij> set schema x;0 rows inserted/updated/deletedij> create view vx as select * from sys.sysschemas;0 rows inserted/updated/deletedij> drop schema x RESTRICT;ERROR X0Y54: Schema 'X' cannot be dropped because it is not empty.ij> drop view x.vx;0 rows inserted/updated/deletedij> create table x (x int);0 rows inserted/updated/deletedij> drop schema x restrict;ERROR X0Y54: Schema 'X' cannot be dropped because it is not empty.ij> drop table x.x;0 rows inserted/updated/deletedij> -- syntax not supported yet (but is in the parser)drop schema x cascade;ERROR 42X01: Syntax error: Encountered "cascade" at line 2, column 15.ij> set schema app;0 rows inserted/updated/deletedij> drop schema x restrict;0 rows inserted/updated/deletedij> ---- test using schema names and correlation names-- first test simple use of schema namescreate schema test;0 rows inserted/updated/deletedij> set schema test;0 rows inserted/updated/deletedij> autocommit off;ij> -- create the all type tablescreate table s (i int, s smallint, c char(30), vc char(30));0 rows inserted/updated/deletedij> create table t (i int, s smallint, c char(30), vc char(30));0 rows inserted/updated/deletedij> create table tt (ii int, ss smallint, cc char(30), vcvc char(30));0 rows inserted/updated/deletedij> create table ttt (iii int, sss smallint, ccc char(30), vcvcvc char(30));0 rows inserted/updated/deletedij> -- populate the tablesinsert into s values (null, null, null, null);1 row inserted/updated/deletedij> insert into s values (0, 0, '0', '0');1 row inserted/updated/deletedij> insert into s values (1, 1, '1', '1');1 row inserted/updated/deletedij> insert into t values (null, null, null, null);1 row inserted/updated/deletedij> insert into t values (0, 0, '0', '0');1 row inserted/updated/deletedij> insert into t values (1, 1, '1', '1');1 row inserted/updated/deletedij> insert into t values (1, 1, '1', '1');1 row inserted/updated/deletedij> insert into tt values (null, null, null, null);1 row inserted/updated/deletedij> insert into tt values (0, 0, '0', '0');1 row inserted/updated/deletedij> insert into tt values (1, 1, '1', '1');1 row inserted/updated/deletedij> insert into tt values (1, 1, '1', '1');1 row inserted/updated/deletedij> insert into tt values (2, 2, '2', '2');1 row inserted/updated/deletedij> insert into ttt values (null, null, null, null);1 row inserted/updated/deletedij> insert into ttt values (11, 11, '11', '11');1 row inserted/updated/deletedij> insert into ttt values (11, 11, '11', '11');1 row inserted/updated/deletedij> insert into ttt values (22, 22, '22', '22');1 row inserted/updated/deletedij> commit;ij> set schema app;0 rows inserted/updated/deletedij> -- test simple statements which use schema namesinsert into test.t values (2, 2, '2', '2');1 row inserted/updated/deletedij> update test.t set s = 2 where i = 2;1 row inserted/updated/deletedij> update test.t set s = 2 where test.t.i = 2;1 row inserted/updated/deletedij> delete from test.t where i = 1;2 rows inserted/updated/deletedij> select * from test.t;I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 2 |2 |2 |2 ij> insert into test.t values (1, 1, '1', '1');1 row inserted/updated/deletedij> insert into test.t values (1, 1, '1', '1');1 row inserted/updated/deletedij> -- test correlated names with tables and schema namesselect * from test.t t1;I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 2 |2 |2 |2 1 |1 |1 |1 1 |1 |1 |1 ij> -- test subqueriesselect * from test.s where exists (select test.s.* from test.t);I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> select * from test.s t where exists (select t.* from test.t);I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> select * from test.s u where exists (select u.* from test.t);I |S |C |VC --------------------------------------------------------------------------------NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> -- column reference in select listselect * from test.s where exists (select i from test.t);⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -