| >> |
| >>cqd hbase_serialization 'ON'; |
| |
| --- SQL operation complete. |
| >>cqd hbase_coprocessors 'ON'; |
| |
| --- SQL operation complete. |
| >>cqd hbase_filter_preds 'ON'; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST012(clnup); |
| >>drop table if exists T012T1 cascade; |
| |
| --- SQL operation complete. |
| >>drop table if exists T012T2 cascade; |
| |
| --- SQL operation complete. |
| >>drop table if exists t012tT3 cascade; |
| |
| --- SQL operation complete. |
| >>drop table if exists t012t11 cascade; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST012(ddl); |
| >>create table if not exists t012t1(a smallint not null serialized, b int serialized, |
| +> c largeint serialized, |
| +> aa smallint unsigned default 10 not null serialized, |
| +> d char(4) serialized, e varchar(5) serialized, |
| +> g numeric(5,2) serialized, |
| +> primary key(a desc)); |
| |
| --- SQL operation complete. |
| >>create index t012t1i1 on t012t1(b); |
| |
| --- SQL operation complete. |
| >>create index t012t1i2 on t012t1(g desc); |
| |
| --- SQL operation complete. |
| >>invoke t012t1; |
| |
| -- Definition of Trafodion table TRAFODION.SCH.T012T1 |
| -- Definition current Thu Mar 2 09:40:48 2017 |
| |
| ( |
| A SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B INT DEFAULT NULL |
| , C LARGEINT DEFAULT NULL |
| , AA SMALLINT UNSIGNED DEFAULT 10 NOT NULL NOT |
| DROPPABLE |
| , D CHAR(4) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , E VARCHAR(5) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , G NUMERIC(5, 2) DEFAULT NULL |
| ) |
| PRIMARY KEY (A DESC) |
| |
| --- SQL operation complete. |
| >> |
| >>create view t012v1 as select a,b,c from t012t1 where c < 0 with check option; |
| |
| --- SQL operation complete. |
| >>invoke t012v1; |
| |
| -- Definition of Trafodion view TRAFODION.SCH.T012V1 |
| -- Definition current Thu Mar 2 09:40:52 2017 |
| |
| ( |
| A SMALLINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B INT DEFAULT NULL |
| , C LARGEINT DEFAULT NULL |
| ) |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST012(tests); |
| >>insert into t012t1 values (1,2,3, 1, 'a', 'b', 5.2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t012t1 values (2, null, null, 2, null, null, null); |
| |
| --- 1 row(s) inserted. |
| >>select * from t012t1; |
| |
| A B C AA D E G |
| ------ ----------- -------------------- ----- ---- ----- ------------ |
| |
| 2 ? ? 2 ? ? ? |
| 1 2 3 1 a b 5.20 |
| |
| --- 2 row(s) selected. |
| >> |
| >>select * from t012t1 where a = 1; |
| |
| A B C AA D E G |
| ------ ----------- -------------------- ----- ---- ----- ------------ |
| |
| 1 2 3 1 a b 5.20 |
| |
| --- 1 row(s) selected. |
| >>select * from t012t1 where b = 2; |
| |
| A B C AA D E G |
| ------ ----------- -------------------- ----- ---- ----- ------------ |
| |
| 1 2 3 1 a b 5.20 |
| |
| --- 1 row(s) selected. |
| >>select * from t012t1 where b < 3; |
| |
| A B C AA D E G |
| ------ ----------- -------------------- ----- ---- ----- ------------ |
| |
| 1 2 3 1 a b 5.20 |
| |
| --- 1 row(s) selected. |
| >>select * from t012t1 where d >= 'a'; |
| |
| A B C AA D E G |
| ------ ----------- -------------------- ----- ---- ----- ------------ |
| |
| 1 2 3 1 a b 5.20 |
| |
| --- 1 row(s) selected. |
| >> |
| >>update t012t1 set g = g+10; |
| |
| --- 2 row(s) updated. |
| >>select * from t012t1; |
| |
| A B C AA D E G |
| ------ ----------- -------------------- ----- ---- ----- ------------ |
| |
| 2 ? ? 2 ? ? ? |
| 1 2 3 1 a b 15.20 |
| |
| --- 2 row(s) selected. |
| >> |
| >>delete from t012t1 where (b,g) > (2,5); |
| |
| --- 1 row(s) deleted. |
| >>select * from t012t1; |
| |
| A B C AA D E G |
| ------ ----------- -------------------- ----- ---- ----- ------------ |
| |
| 2 ? ? 2 ? ? ? |
| |
| --- 1 row(s) selected. |
| >> |
| >>insert into t012v1 values (3,3,-1); |
| |
| --- 1 row(s) inserted. |
| >>select * from t012v1; |
| |
| A B C |
| ------ ----------- -------------------- |
| |
| 3 3 -1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>obey TEST012(negtests); |
| >>create table t012t10(f real not null serialized, |
| +> h decimal(4,3) serialized, |
| +> i date not null serialized, |
| +> j interval year to month serialized); |
| |
| *** ERROR[1191] SERIALIZE option is not yet supported for REAL datatype. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>insert into t012v1 values (3,3,3); |
| |
| *** ERROR[8105] The operation is prevented by the check option on view TRAFODION.SCH.T012V1. |
| |
| --- 0 row(s) inserted. |
| >> |
| >>create table t012t11(a int not null); |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST012(schemaDrop); |
| >>create schema trafodion.zschema; |
| |
| --- SQL operation complete. |
| >>set schema trafodion.zschema; |
| |
| --- SQL operation complete. |
| >>create table t012t11 (a int not null primary key, b int not null, c int not null, d int not null); |
| |
| --- SQL operation complete. |
| >>create table t012t12 (a int not null primary key, b int not null); |
| |
| --- SQL operation complete. |
| >>create index t012t11i1 on t012t11(b); |
| |
| --- SQL operation complete. |
| >>create unique index t012t11i2 on t012t11(b); |
| |
| --- SQL operation complete. |
| >>alter table t012t11 add constraint t012t11c1 unique (c); |
| |
| --- SQL operation complete. |
| >>alter table t012t11 add constraint t012t11c2 foreign key (d) references t012t12(a); |
| |
| --- SQL operation complete. |
| >>create view t012t11v1(a1,a2) as select x.a, y.a from t012t11 x, t012t11 y where x.a = y.a; |
| |
| --- SQL operation complete. |
| >>showddl t012t11; |
| |
| CREATE TABLE TRAFODION.ZSCHEMA.T012T11 |
| ( |
| A INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , B INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , D INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY (A ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- The following index is a system created index -- |
| CREATE UNIQUE INDEX T012T11C1 ON TRAFODION.ZSCHEMA.T012T11 |
| ( |
| C ASC |
| ) |
| ; |
| |
| -- The following index is a system created index -- |
| CREATE INDEX T012T11C2 ON TRAFODION.ZSCHEMA.T012T11 |
| ( |
| D ASC |
| ) |
| ; |
| |
| CREATE INDEX T012T11I1 ON TRAFODION.ZSCHEMA.T012T11 |
| ( |
| B ASC |
| ) |
| ; |
| |
| CREATE UNIQUE INDEX T012T11I2 ON TRAFODION.ZSCHEMA.T012T11 |
| ( |
| B ASC |
| ) |
| ; |
| |
| ALTER TABLE TRAFODION.ZSCHEMA.T012T11 ADD CONSTRAINT |
| TRAFODION.ZSCHEMA.T012T11C1 UNIQUE |
| ( |
| C |
| ) |
| ; |
| |
| ALTER TABLE TRAFODION.ZSCHEMA.T012T11 ADD CONSTRAINT |
| TRAFODION.ZSCHEMA.T012T11C2 FOREIGN KEY |
| ( |
| D |
| ) |
| REFERENCES TRAFODION.ZSCHEMA.T012T12 |
| ( |
| A |
| ) |
| ; |
| |
| --- SQL operation complete. |
| >>insert into t012t12 values (1,1), (2,2), (3,3); |
| |
| --- 3 row(s) inserted. |
| >>insert into t012t11 values (1,1,1,1), (2,2,2,2), (3,3,3,3); |
| |
| --- 3 row(s) inserted. |
| >>update statistics for table t012t11 on every column; |
| |
| --- SQL operation complete. |
| >> |
| >>get tables in schema trafodion.zschema; |
| |
| Tables in Schema TRAFODION.ZSCHEMA |
| ================================== |
| |
| SB_HISTOGRAMS |
| SB_HISTOGRAM_INTERVALS |
| SB_PERSISTENT_SAMPLES |
| T012T11 |
| T012T12 |
| |
| ======================= |
| 5 row(s) returned |
| |
| --- SQL operation complete. |
| >>get indexes in schema trafodion.zschema; |
| |
| Indexes in Schema TRAFODION.ZSCHEMA |
| =================================== |
| |
| T012T11C1 |
| T012T11C2 |
| T012T11I1 |
| T012T11I2 |
| |
| ======================= |
| 4 row(s) returned |
| |
| --- SQL operation complete. |
| >>get views in schema trafodion.zschema; |
| |
| Views in Schema TRAFODION.ZSCHEMA |
| ================================= |
| |
| T012T11V1 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >> |
| >>drop schema trafodion.zschema cascade; |
| |
| --- SQL operation complete. |
| >> |
| >>get tables in schema trafodion.zschema; |
| |
| --- SQL operation complete. |
| >>get indexes in schema trafodion.zschema; |
| |
| --- SQL operation complete. |
| >>get views in schema trafodion.zschema; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST012(getStmts); |
| >>drop schema if exists zschema1 cascade; |
| |
| --- SQL operation complete. |
| >>drop schema if exists zschema2 cascade; |
| |
| --- SQL operation complete. |
| >> |
| >>create schema trafodion.zschema1; |
| |
| --- SQL operation complete. |
| >>create schema trafodion.zschema2; |
| |
| --- SQL operation complete. |
| >>create table zschema1.t (a int); |
| |
| --- SQL operation complete. |
| >>create table zschema2.t (a int); |
| |
| --- SQL operation complete. |
| >>create view zschema1.v as select zschema1.t.a a, zschema2.t.a b |
| +> from zschema1.t, zschema2.t; |
| |
| --- SQL operation complete. |
| >>create view zschema2.v as select zschema1.t.a a, zschema2.t.a b |
| +> from zschema1.t, zschema2.t; |
| |
| --- SQL operation complete. |
| >>create view zschema2.v2 as select zschema1.v.a a, zschema2.t.a b |
| +> from zschema1.v, zschema2.t; |
| |
| --- SQL operation complete. |
| >>get tables in view zschema1.v; |
| |
| Tables in View ZSCHEMA1.V |
| ========================= |
| |
| TRAFODION.ZSCHEMA1.T |
| TRAFODION.ZSCHEMA2.T |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >>get views on table zschema1.t; |
| |
| Views on Table ZSCHEMA1.T |
| ========================= |
| |
| TRAFODION.ZSCHEMA1.V |
| TRAFODION.ZSCHEMA2.V |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >> |
| >>get tables in view zschema2.v2; |
| |
| Tables in View ZSCHEMA2.V2 |
| ========================== |
| |
| TRAFODION.ZSCHEMA2.T |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get all tables in view zschema2.v2; |
| |
| Tables in View ZSCHEMA2.V2 |
| ========================== |
| |
| TRAFODION.ZSCHEMA1.T |
| TRAFODION.ZSCHEMA2.T |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >>get views in view zschema2.v2; |
| |
| Views in View ZSCHEMA2.V2 |
| ========================= |
| |
| TRAFODION.ZSCHEMA1.V |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>get all objects in view zschema2.v2; |
| |
| Objects in View ZSCHEMA2.V2 |
| =========================== |
| |
| TRAFODION.ZSCHEMA1.T |
| TRAFODION.ZSCHEMA1.V |
| TRAFODION.ZSCHEMA2.T |
| |
| ======================= |
| 3 row(s) returned |
| |
| --- SQL operation complete. |
| >> |
| >>drop schema zschema1 cascade; |
| |
| --- SQL operation complete. |
| >>drop schema zschema2 cascade; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >> |
| >> |
| >> |
| >> |
| >> |
| >> |
| >>log; |
