Google Git
Sign in
apache / derby / 08cc0f89fe077234078430bf9b93ebc9dfd70ac5 / . / java / testing / org / apache / derbyTesting / functionTests / master / predicatesIntoViews.out
blob: 08d8c97729373f70375b247a1fad44fc100a8ae0 [file] [log] [blame]
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.
--
-- test the pushing of predicates into unflattened views
-- and derived tables
set isolation to rr;
0 rows inserted/updated/deleted
ij> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij> -- set up
create table t1(c1 int, c2 int, c3 varchar(2000));
0 rows inserted/updated/deleted
ij> -- simple views
create view sv1 (c1, c2, c3) as select c1, c2, c3 || '$' from t1;
0 rows inserted/updated/deleted
ij> create view sv2 (x1, x2, x3) as select c1, c2, c3 || '$' from t1;
0 rows inserted/updated/deleted
ij> create view sv3 (y1, y2, y3) as select x1, x2, x3 || '$' from sv2;
0 rows inserted/updated/deleted
ij> create view sv4 (z1, z2, z3, z4, z5, z6) as
select a.c1, a.c2, a.c3 || '$', b.c1, b.c2, b.c3 || '$' from t1 a, t1 b;
0 rows inserted/updated/deleted
ij> -- more complex views
create view av1 (c1, c2) as select c1, max(c2) from t1 group by c1;
0 rows inserted/updated/deleted
ij> create view av2 (x1, x2) as select c1, max(c2) from av1 group by c1;
0 rows inserted/updated/deleted
ij> create view av3 (y1, y2, y3, y4) as
select a.c1, b.c1, max(a.c2), max(b.c2) from t1 a, t1 b group by a.c1, b.c1;
0 rows inserted/updated/deleted
ij> -- non-flattenable derived table in a non-flattenable view
create view cv1 (c1, c2) as
select c1, max(c2) from (select c1, c2 + 1 from t1) t(c1, c2) group by c1;
0 rows inserted/updated/deleted
ij> -- populate the tables
insert into t1 values (1, 1, ''), (1, 1, ''), (1, 2, ''), (1, 2, ''),
(2, 2, ''), (2, 2, ''), (2, 3, ''), (2, 3, '');
8 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 20000;
ij> -- predicate should get pushed into scan
select c1, c2 from sv1 where c1 = 1 order by c1, c2;
C1 |C2
-----------------------
1 |1
1 |1
1 |2
1 |2
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- predicate should get pushed into scan
select c1, c2 from sv1 where c1 = 1 order by c1, c2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 4
Rows returned = 4
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=4
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=3
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select c1, c2 from sv1 where c1 = 1 + 1 order by c1, c2;
C1 |C2
-----------------------
2 |2
2 |2
2 |3
2 |3
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select c1, c2 from sv1 where c1 = 1 + 1 order by c1, c2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 4
Rows returned = 4
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=4
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=3
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select x1, x2 from sv2 where x1 = 1 order by x1, x2;
X1 |X2
-----------------------
1 |1
1 |1
1 |2
1 |2
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select x1, x2 from sv2 where x1 = 1 order by x1, x2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 4
Rows returned = 4
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=4
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=3
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select y1, y2 from sv3 where y1 = 1 order by y1, y2;
Y1 |Y2
-----------------------
1 |1
1 |1
1 |2
1 |2
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select y1, y2 from sv3 where y1 = 1 order by y1, y2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 4
Rows returned = 4
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=4
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=3
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select z1, z2, z4, z5 from sv4 where z1 = z4 and z2 = z5
order by z1, z2, z4, z5;
Z1 |Z2 |Z4 |Z5
-----------------------------------------------
1 |1 |1 |1
1 |1 |1 |1
1 |1 |1 |1
1 |1 |1 |1
1 |2 |1 |2
1 |2 |1 |2
1 |2 |1 |2
1 |2 |1 |2
2 |2 |2 |2
2 |2 |2 |2
2 |2 |2 |2
2 |2 |2 |2
2 |3 |2 |3
2 |3 |2 |3
2 |3 |2 |3
2 |3 |2 |3
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select z1, z2, z4, z5 from sv4 where z1 = z4 and z2 = z5
order by z1, z2, z4, z5
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 16
Rows returned = 16
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=16
Number of rows output=16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 16
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 16
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Hash Join ResultSet:
Number of opens = 1
Rows seen from the left = 8
Rows seen from the right = 16
Rows filtered = 0
Rows returned = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 8
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=3
Number of pages visited=1
Number of rows qualified=8
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
Right result set:
Hash Scan ResultSet for T1 at serializable isolation level using share table locking:
Number of opens = 8
Hash table size = 4
Hash keys are column numbers (0,1)
Rows seen = 16
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=3
Number of pages visited=1
Number of rows qualified=8
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select * from av1 where c1 = 1;
C1 |C2
-----------------------
1 |2
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select * from av1 where c1 = 1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 4
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select * from av2 where x1 = 2;
X1 |X2
-----------------------
2 |3
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select * from av2 where x1 = 2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (7):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 1
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=1
Number of rows output=1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 4
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select * from av3;
Y1 |Y2 |Y3 |Y4
-----------------------------------------------
1 |1 |2 |2
1 |2 |2 |3
2 |1 |3 |2
2 |2 |3 |3
ij> select y1, y2, y3, y4 + 0 from av3 where y1 = y2;
Y1 |Y2 |Y3 |4
-----------------------------------------------
1 |1 |2 |2
2 |2 |3 |3
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select y1, y2, y3, y4 + 0 from av3 where y1 = y2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (7):
Number of opens = 1
Rows seen = 2
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 2
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 32
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=32
Number of rows output=2
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 32
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Hash Join ResultSet:
Number of opens = 1
Rows seen from the left = 8
Rows seen from the right = 32
Rows filtered = 0
Rows returned = 32
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 8
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=8
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
Right result set:
Hash Scan ResultSet for T1 at serializable isolation level using share table locking:
Number of opens = 8
Hash table size = 2
Hash key is column number 0
Rows seen = 32
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=8
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select * from cv1;
C1 |C2
-----------------------
1 |3
2 |4
ij> select * from cv1 where c1 = 1;
C1 |C2
-----------------------
1 |3
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select * from cv1 where c1 = 1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 4
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> create index t1_c1 on t1(c1);
0 rows inserted/updated/deleted
ij> select c1, c2 from sv1 where c1 = 1 order by c1, c2;
C1 |C2
-----------------------
1 |1
1 |1
1 |2
1 |2
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select c1, c2 from sv1 where c1 = 1 order by c1, c2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 4
Rows returned = 4
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=4
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Row to Base Row ResultSet for T1:
Number of opens = 1
Rows seen = 4
Columns accessed from heap = {1, 2}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for T1 using index T1_C1 at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=5
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> select x1, x2 from sv2 where x1 = 1 order by x1, x2;
X1 |X2
-----------------------
1 |1
1 |1
1 |2
1 |2
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select x1, x2 from sv2 where x1 = 1 order by x1, x2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 4
Rows returned = 4
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=4
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Row to Base Row ResultSet for T1:
Number of opens = 1
Rows seen = 4
Columns accessed from heap = {1, 2}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for T1 using index T1_C1 at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=5
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> select y1, y2 from sv3 where y1 = 1 order by y1, y2;
Y1 |Y2
-----------------------
1 |1
1 |1
1 |2
1 |2
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select y1, y2 from sv3 where y1 = 1 order by y1, y2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 4
Rows returned = 4
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=4
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Row to Base Row ResultSet for T1:
Number of opens = 1
Rows seen = 4
Columns accessed from heap = {1, 2}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for T1 using index T1_C1 at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=5
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> select z1, z2, z4, z5 from sv4 where z1 = z4 and z2 = z5
order by z1, z2, z4, z5;
Z1 |Z2 |Z4 |Z5
-----------------------------------------------
1 |1 |1 |1
1 |1 |1 |1
1 |1 |1 |1
1 |1 |1 |1
1 |2 |1 |2
1 |2 |1 |2
1 |2 |1 |2
1 |2 |1 |2
2 |2 |2 |2
2 |2 |2 |2
2 |2 |2 |2
2 |2 |2 |2
2 |3 |2 |3
2 |3 |2 |3
2 |3 |2 |3
2 |3 |2 |3
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select z1, z2, z4, z5 from sv4 where z1 = z4 and z2 = z5
order by z1, z2, z4, z5
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 16
Rows returned = 16
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=16
Number of rows output=16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 16
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 16
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Hash Join ResultSet:
Number of opens = 1
Rows seen from the left = 8
Rows seen from the right = 16
Rows filtered = 0
Rows returned = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 8
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=3
Number of pages visited=1
Number of rows qualified=8
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
Right result set:
Hash Scan ResultSet for T1 at serializable isolation level using share table locking:
Number of opens = 8
Hash table size = 4
Hash keys are column numbers (0,1)
Rows seen = 16
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=3
Number of pages visited=1
Number of rows qualified=8
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select * from av1 where c1 = 1;
C1 |C2
-----------------------
1 |2
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select * from av1 where c1 = 1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 4
Has distinct aggregate = false
In sorted order = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Row to Base Row ResultSet for T1:
Number of opens = 1
Rows seen = 4
Columns accessed from heap = {1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for T1 using index T1_C1 at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=5
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> select * from av2 where x1 = 2;
X1 |X2
-----------------------
2 |3
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select * from av2 where x1 = 2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (8):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 1
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=1
Number of rows output=1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (7):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 4
Has distinct aggregate = false
In sorted order = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Row to Base Row ResultSet for T1:
Number of opens = 1
Rows seen = 4
Columns accessed from heap = {1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for T1 using index T1_C1 at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=4
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> select y1, y2, y3, y4 + 0 from av3 where y1 = y2;
Y1 |Y2 |Y3 |4
-----------------------------------------------
1 |1 |2 |2
2 |2 |3 |3
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select y1, y2, y3, y4 + 0 from av3 where y1 = y2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (7):
Number of opens = 1
Rows seen = 2
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 2
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 32
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=32
Number of rows output=2
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 32
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Hash Join ResultSet:
Number of opens = 1
Rows seen from the left = 8
Rows seen from the right = 32
Rows filtered = 0
Rows returned = 32
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 8
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=8
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
Right result set:
Hash Scan ResultSet for T1 at serializable isolation level using share table locking:
Number of opens = 8
Hash table size = 2
Hash key is column number 0
Rows seen = 32
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=8
Number of rows visited=8
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select * from cv1 where c1 = 1;
C1 |C2
-----------------------
1 |3
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select * from cv1 where c1 = 1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 4
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Row to Base Row ResultSet for T1:
Number of opens = 1
Rows seen = 4
Columns accessed from heap = {1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for T1 using index T1_C1 at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 4
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=4
Number of rows visited=5
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> -- cleanup
drop view cv1;
0 rows inserted/updated/deleted
ij> drop view av3;
0 rows inserted/updated/deleted
ij> drop view av2;
0 rows inserted/updated/deleted
ij> drop view av1;
0 rows inserted/updated/deleted
ij> drop view sv4;
0 rows inserted/updated/deleted
ij> drop view sv3;
0 rows inserted/updated/deleted
ij> drop view sv2;
0 rows inserted/updated/deleted
ij> drop view sv1;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> -- DERBY-649: Test for making sure predicates are pushed into unions.
create table test.table1(a integer, b integer, c integer);
0 rows inserted/updated/deleted
ij> create index test.table1idx on test.table1(b);
0 rows inserted/updated/deleted
ij> create table test.table2(a integer, b integer, c integer);
0 rows inserted/updated/deleted
ij> create index test.table2idx on test.table2(b);
0 rows inserted/updated/deleted
ij> create view test.view0 as select all a,b from test.table1 union all select a,b from test.table2;
0 rows inserted/updated/deleted
ij> create view test.view1(a,b) as select all a,b from test.table1 union all select a,b from test.table2
union all select 1,1 from test.table1;
0 rows inserted/updated/deleted
ij> create view test.view2(c,d) as select all a+1,b+1 from test.table1 union all select a,b from test.table2;
0 rows inserted/updated/deleted
ij> -- Following Selects using the tables directly would use index
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> select a from test.table1 where b=25;
A
-----------
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select a from test.table1 where b=25
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Row to Base Row ResultSet for TABLE1:
Number of opens = 1
Rows seen = 0
Columns accessed from heap = {0, 1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for TABLE1 using index TABLE1IDX at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=0
Number of rows visited=0
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> select a from test.table2 where b=25;
A
-----------
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select a from test.table2 where b=25
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Row to Base Row ResultSet for TABLE2:
Number of opens = 1
Rows seen = 0
Columns accessed from heap = {0, 1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for TABLE2 using index TABLE2IDX at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=0
Number of rows visited=0
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> -- This select from a view based on the tables would use TableScan before DERBY-649
select a from test.view0 where b=25;
A
-----------
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- This select from a view based on the tables would use TableScan before DERBY-649
select a from test.view0 where b=25
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (7):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Index Row to Base Row ResultSet for TABLE1:
Number of opens = 1
Rows seen = 0
Columns accessed from heap = {0}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for TABLE1 using index TABLE1IDX at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=0
Number of rows visited=0
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
Right result set:
Index Row to Base Row ResultSet for TABLE2:
Number of opens = 1
Rows seen = 0
Columns accessed from heap = {0}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for TABLE2 using index TABLE2IDX at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=0
Number of rows visited=0
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> -- Can't use index for the following
select a from test.view0 where b=25+a;
A
-----------
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- Can't use index for the following
select a from test.view0 where b=25+a
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Table Scan ResultSet for TABLE1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=0
Number of rows visited=0
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
Right result set:
Table Scan ResultSet for TABLE2 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=0
Number of rows visited=0
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
ij> -- Inlist tests
select * from test.view0 where b in (1, 2, 3);
A |B
-----------------------
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- Inlist tests
select * from test.view0 where b in (1, 2, 3)
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Index Row to Base Row ResultSet for TABLE1:
Number of opens = 1
Rows seen = 0
Columns accessed from heap = {0}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for TABLE1 using index TABLE1IDX at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 3
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=0
Number of rows visited=0
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
Right result set:
Index Row to Base Row ResultSet for TABLE2:
Number of opens = 1
Rows seen = 0
Columns accessed from heap = {0}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for TABLE2 using index TABLE2IDX at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 3
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=0
Number of rows visited=0
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> prepare s as 'select * from test.view0 where b in (?, ?, ?)';
ij> execute s using 'values (1,2,3)';
IJ WARNING: Autocommit may close using result set
A |B
-----------------------
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select * from test.view0 where b in (?, ?, ?)
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Index Row to Base Row ResultSet for TABLE1:
Number of opens = 1
Rows seen = 0
Columns accessed from heap = {0}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for TABLE1 using index TABLE1IDX at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 3
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=0
Number of rows visited=0
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
Right result set:
Index Row to Base Row ResultSet for TABLE2:
Number of opens = 1
Rows seen = 0
Columns accessed from heap = {0}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Index Scan ResultSet for TABLE2 using index TABLE2IDX at serializable isolation level using share row locking chosen by the optimizer
Number of opens = 3
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=0
Number of rows visited=0
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
ij> -- This select should use index for first two selects, table scan for the third
select a from test.view1 where b=25;
A
-----------
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
-- This select should use index for first two selects, table scan for the third
select a from test.view1 where b=25
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (8):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (7):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Table Scan ResultSet for TABLE1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=0
Number of rows visited=0
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
Right result set:
Table Scan ResultSet for TABLE2 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=0
Number of rows visited=0
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
Right result set:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Index Scan ResultSet for TABLE1 using index TABLE1IDX at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched={}
Number of columns fetched=0
Number of deleted rows visited=0
Number of pages visited=1
Number of rows qualified=0
Number of rows visited=0
Scan type=btree
Tree height=1
start position:
None
stop position:
None
qualifiers:
None
ij> select d from test.view2 where d=25;
D
-----------
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select d from test.view2 where d=25
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Union ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Table Scan ResultSet for TABLE1 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=0
Number of rows visited=0
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
Right result set:
Table Scan ResultSet for TABLE2 at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=0
Number of rows visited=0
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
0 rows inserted/updated/deleted
ij> -- Beetle 4983, customer case, complex query involving views, wrong column remapping
-- after view flattening, NullPointerException, and didn't get predicate pushed down
-- all the way to base table.
autocommit off;
ij> create schema xr;
0 rows inserted/updated/deleted
ij> create table xr.businessentity
( ID varchar(48) not null primary key ,
type varchar(24) not null,
name varchar(128) not null ,
uname varchar(128) ,
--GENERATED ALWAYS AS (ucase(name)),
description varchar(256),
createdate timestamp not null,
changedate timestamp not null,
deletedate timestamp,
-- for type=BUSINESS this is the delegate owner
-- for type=USER this is their userID
AuthID varchar(48) not null,
ownerUserUUID varchar(48),
-- for type=BUSINESS -- in subclass
businessTypeID varchar(48)
);
0 rows inserted/updated/deleted
ij> create trigger xr.be_uname_i after insert on xr.businessentity
referencing new as n
for each row
update xr.businessentity set uname = upper( n.name ) where name = n.name;
0 rows inserted/updated/deleted
ij> create trigger xr.be_unane_u after update of name, uname on xr.businessentity
referencing new as n
for each row
update xr.businessentity set uname = upper( n.name )
where name = n.name and uname <> upper( n.name );
0 rows inserted/updated/deleted
ij> create unique index xr.bus1 on xr.businessentity (ownerUserUUID, uname, deletedate);
0 rows inserted/updated/deleted
ij> create table xr.BEMembers(
beid varchar(48) not null,
memberid varchar(48) not null
);
0 rows inserted/updated/deleted
ij> create unique index xr.bem1 on xr.BEMembers (beid, memberid);
0 rows inserted/updated/deleted
ij> alter table xr.BEMembers add constraint bem_beid
foreign key (beid)
references xr.businessentity(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> alter table xr.BEMembers add constraint bem_memberid
foreign key (memberid)
references xr.businessentity(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.xruser
( businessentityID varchar(48) not null primary key ,
userid varchar(48) not null,
deletedate timestamp
);
0 rows inserted/updated/deleted
ij> create unique index xr.user1 on xr.xruser (userID, deletedate);
0 rows inserted/updated/deleted
ij> alter table xr.xruser add constraint u_busent foreign key (businessentityID)
references xr.businessentity(ID)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.locator
(ownerID varchar(48) not null,
type varchar(48) not null,
useTypeID varchar(48) ,
value varchar(256),
street1 varchar(48),
street2 varchar(48),
city varchar(48),
state varchar(48),
country varchar(48),
postalcode varchar(48)
);
0 rows inserted/updated/deleted
ij> create unique index xr.loc1 on xr.locator ( ownerID,type,usetypeID );
0 rows inserted/updated/deleted
ij> alter table xr.locator add constraint l_busent foreign key (ownerID)
references xr.businessentity(ID)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.BusinessEntityAssociation
(ID varchar(48) not null primary key ,
sourceID varchar(48) not null,
targetID varchar(48) not null,
ownerID varchar(48) not null,
assoctypeID varchar(48) not null,
createdate timestamp not null,
changedate timestamp not null,
deletedate timestamp,
description varchar(256)
);
0 rows inserted/updated/deleted
ij> alter table xr.BusinessEntityAssociation
add constraint bea_sourceID foreign key (sourceID)
references xr.businessentity(ID)
on delete restrict;
0 rows inserted/updated/deleted
ij> alter table xr.BusinessEntityAssociation
add constraint bea_targetID foreign key (targetID)
references xr.businessentity(ID)
on delete restrict;
0 rows inserted/updated/deleted
ij> alter table xr.BusinessEntityAssociation
add constraint bea_ownerID foreign key (ownerID)
references xr.businessentity(ID)
on delete restrict;
0 rows inserted/updated/deleted
ij> create unique index xr.BEA1
on xr.BusinessEntityAssociation( sourceid, targetid, ownerID, assoctypeID, deletedate);
0 rows inserted/updated/deleted
ij> create table xr.repositoryobjectresource (
id varchar(48) not null primary key,
type varchar(48) not null,
subtype varchar(48),
creatorid varchar(48) not null,
createdate timestamp not null,
currentVersion varchar(48),
versionControlled smallint not null with default 0,
checkedOut smallint not null with default 0,
checkForLock smallint not null with default 0
);
0 rows inserted/updated/deleted
ij> alter table xr.repositoryobjectresource add constraint ror_creatorid
foreign key (creatorid)
references xr.xruser(businessentityid)
on delete restrict;
0 rows inserted/updated/deleted
ij> create table xr.repositoryobjectversion (
id varchar(48) not null primary key,
resourceid varchar(48) not null,
name varchar(128) not null,
uname varchar(128),
-- GENERATED ALWAYS AS (ucase(name)),
folderid varchar(48),
versionName varchar(128) not null,
uri varchar(255) not null,
versionuri varchar(255) not null,
description varchar(256),
versionComment varchar(256),
ownerid varchar(48) not null,
creatorid varchar(48) not null,
versiondate timestamp not null,
changedate timestamp not null,
deletedate timestamp,
previousversion varchar(48)
);
0 rows inserted/updated/deleted
ij> create trigger xr.rov_uname_i after insert on xr.repositoryobjectversion
referencing new as n
for each row
update xr.repositoryobjectversion set uname = upper( n.name ) where name = n.name;
0 rows inserted/updated/deleted
ij> create trigger xr.rov_unane_u after update of name, uname on xr.repositoryobjectversion
referencing new as n
for each row
update xr.repositoryobjectversion set uname = upper( n.name )
where name = n.name and uname <> upper( n.name );
0 rows inserted/updated/deleted
ij> create unique index xr.versionname on xr.repositoryobjectversion (resourceid, versionName);
0 rows inserted/updated/deleted
ij> -- Don't think I want this constraint with versioning.
-- Object could have been deleted in a later version.
-- create unique index xr.versionuri on xr.repositoryobjectversion (versionuri, deletedate);
alter table xr.repositoryobjectversion add constraint rov_previousvers
foreign key (previousversion)
references xr.repositoryobjectversion(id)
on delete set null;
0 rows inserted/updated/deleted
ij> alter table xr.repositoryobjectversion add constraint rov_folderid
foreign key (folderid)
references xr.repositoryobjectresource(id)
on delete restrict;
0 rows inserted/updated/deleted
ij> alter table xr.repositoryobjectversion add constraint rov_ownerid
foreign key (ownerid)
references xr.businessentity(id)
on delete restrict;
0 rows inserted/updated/deleted
ij> alter table xr.repositoryobjectversion add constraint rov_creatorid
foreign key (creatorid)
references xr.xruser(businessentityid)
on delete restrict;
0 rows inserted/updated/deleted
ij> alter table xr.repositoryobjectresource add constraint ror_currentVersion
foreign key (currentVersion)
references xr.repositoryobjectversion(id)
on delete restrict;
0 rows inserted/updated/deleted
ij> create table xr.lock (
locktoken varchar(48) not null,
resourceid varchar(48) not null,
ownerid varchar(48) not null,
exclusive smallint not null,
timeoutSeconds bigint not null,
expirationDate timestamp not null
);
0 rows inserted/updated/deleted
ij> alter table xr.lock add primary key (locktoken, resourceid);
0 rows inserted/updated/deleted
ij> alter table xr.lock add constraint l_resourceid
foreign key (resourceid)
references xr.repositoryobjectresource(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> alter table xr.lock add constraint l_ownerid
foreign key (ownerid)
references xr.xruser(businessentityid)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.keyword (
versionid varchar(48) not null,
keyword varchar(128) not null
);
0 rows inserted/updated/deleted
ij> alter table xr.keyword add constraint k_versionid
foreign key (versionid)
references xr.repositoryobjectversion(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.slot (
versionid varchar(48) not null,
name varchar(128) not null,
value varchar(256)
);
0 rows inserted/updated/deleted
ij> alter table xr.slot add constraint s_versionid
foreign key (versionid)
references xr.repositoryobjectversion(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.versionlabel (
versionid varchar(48) not null,
label varchar(128) not null
);
0 rows inserted/updated/deleted
ij> alter table xr.versionlabel add constraint vl_versionid
foreign key (versionid)
references xr.repositoryobjectversion(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.repositoryentry (
versionid varchar(48) not null primary key,
versioncontentid varchar(48),
mimetype varchar(48),
stability varchar(48),
status varchar(48),
startdate timestamp,
expirationdate timestamp,
isopaque smallint not null with default 0
);
0 rows inserted/updated/deleted
ij> alter table xr.repositoryentry add constraint re_versionid
foreign key (versionid)
references xr.repositoryobjectversion(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.repositoryentrycontent (
versionid varchar(48) not null primary key,
contentchangedate timestamp,
content long varchar
--blob(1M)
);
0 rows inserted/updated/deleted
ij> alter table xr.repositoryentry add constraint re_versioncontent
foreign key (versioncontentid)
references xr.repositoryentrycontent(versionid)
on delete set null;
0 rows inserted/updated/deleted
ij> create table xr.objectgroup_content (
versionid varchar(48) not null,
memberid varchar(48) not null
);
0 rows inserted/updated/deleted
ij> alter table xr.objectgroup_content add constraint ogc_versionid
foreign key (versionid)
references xr.repositoryobjectversion(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> alter table xr.objectgroup_content add constraint ogc_memberid
foreign key (memberid)
references xr.repositoryobjectresource(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.externaldependency_content (
versionid varchar(48) not null,
objectid varchar(48) not null
);
0 rows inserted/updated/deleted
ij> alter table xr.externaldependency_content add constraint edc_objectid
foreign key (objectid)
references xr.repositoryobjectresource(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.objectassociation (
id varchar(48) not null primary key,
sourceid varchar(48) not null,
targetid varchar(48) not null
);
0 rows inserted/updated/deleted
ij> alter table xr.objectassociation add constraint oa_id
foreign key (id)
references xr.repositoryobjectresource(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> alter table xr.objectassociation add constraint oa_sourceid
foreign key (sourceid)
references xr.repositoryobjectresource(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> alter table xr.objectassociation add constraint oa_targetid
foreign key (targetid)
references xr.repositoryobjectresource(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.classificationscheme (
id varchar(48) not null primary key,
structuretype varchar(48) not null
);
0 rows inserted/updated/deleted
ij> alter table xr.classificationscheme add constraint cs_id
foreign key (id)
references xr.repositoryobjectresource(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.classification_values (
versionid varchar(48) not null,
valueid varchar(48) not null,
value varchar(128) not null,
description varchar(256),
parentvalueid varchar(48)
);
0 rows inserted/updated/deleted
ij> alter table xr.classification_values add primary key (versionid, valueid);
0 rows inserted/updated/deleted
ij> alter table xr.classification_values add constraint cv_versionid
foreign key (versionid)
references xr.repositoryobjectversion(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> alter table xr.classification_values add constraint cv_parentvalueid
foreign key (versionid, parentvalueid)
references xr.classification_values(versionid, valueid)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.classification_value_ancestors (
versionid varchar(48) not null,
valueid varchar(48) not null,
ancestorid varchar(48) not null
);
0 rows inserted/updated/deleted
ij> alter table xr.classification_value_ancestors add constraint cva_versionid
foreign key (versionid)
references xr.repositoryobjectversion(id)
on delete cascade;
0 rows inserted/updated/deleted
ij> alter table xr.classification_value_ancestors add constraint cva_valueid
foreign key (versionid, valueid)
references xr.classification_values(versionid, valueid)
on delete cascade;
0 rows inserted/updated/deleted
ij> alter table xr.classification_value_ancestors add constraint cva_ancestorid
foreign key (versionid, ancestorid)
references xr.classification_values(versionid, valueid)
on delete cascade;
0 rows inserted/updated/deleted
ij> create table xr.classifications (
objectversionid varchar(48) not null,
valueid varchar(48) not null
);
0 rows inserted/updated/deleted
ij> create view xr.classificationcurrentvalueview (
valueid,
value
) as select
v.valueid,
v.value
from xr.classification_values v, xr.repositoryobjectresource ror
where v.versionid = ror.currentversion;
0 rows inserted/updated/deleted
ij> create view xr.classificationschemecurrentversionview (
id,
versionid,
name,
uname,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownername,
uownername,
ownerauthid,
creatorname,
description,
versionComment,
objecttype,
subtypeid,
subtype,
structuretype,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
currentversion,
previousversion,
checkedout
) as select
ror.id,
rov.id,
rov.name,
rov.uname,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.name,
beo.uname,
beo.authid,
bec.name,
rov.description,
rov.versionComment,
ror.type,
ror.subtype,
cvtype.value,
cls.structuretype,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.currentversion,
rov.previousversion,
ror.checkedout
from xr.repositoryobjectresource ror
inner join xr.classificationscheme cls on (ror.id = cls.id)
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
where rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.classificationschemeallversionview (
id,
versionid,
name,
uname,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownername,
uownername,
ownerauthid,
creatorname,
description,
versionComment,
objecttype,
subtypeid,
subtype,
structuretype,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
currentversion,
previousversion,
checkedout
) as select
ror.id,
rov.id,
rov.name,
rov.uname,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.name,
beo.uname,
beo.authid,
bec.name,
rov.description,
rov.versionComment,
ror.type,
ror.subtype,
cvtype.value,
cls.structuretype,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.currentversion,
rov.previousversion,
ror.checkedout
from xr.repositoryobjectresource ror
inner join xr.classificationscheme cls on (ror.id = cls.id)
inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
where rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.classificationschemelifecycleview (
id,
versionid,
name,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownerauthid,
ownername,
description,
objecttype,
subtypeid,
subtype,
structuretype,
checkforlock,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
currentversion,
previousversion,
checkedout
) as select
ror.id,
rov.id,
rov.name,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.authid,
beo.name,
rov.description,
ror.type,
ror.subtype,
cvtype.value,
cls.structuretype,
ror.checkforlock,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.currentversion,
rov.previousversion,
ror.checkedout
from xr.repositoryobjectresource ror
inner join xr.classificationscheme cls on (ror.id = cls.id)
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid);
0 rows inserted/updated/deleted
ij> create view xr.classificationvalueview (
id,
value,
description,
parentid,
parentvalue,
schemeid,
schemeversionid,
schemename
) as select
v.valueid,
v.value,
v.description,
p.valueid,
p.value,
rov.resourceid,
rov.id,
rov.name
from xr.classification_values v
inner join xr.repositoryobjectversion rov on (v.versionid = rov.id)
left outer join xr.classification_values p on (v.parentvalueid = p.valueid)
where rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.classification_direct_view (
objectversionid,
valueid,
schemeversionid,
value
) as select
c.objectversionid,
cv.valueid,
cv.versionid,
cv.value
from xr.classifications c, xr.classification_values cv,
xr.repositoryobjectresource ror
where c.valueid = cv.valueid and cv.versionid = ror.currentversion;
0 rows inserted/updated/deleted
ij> create view xr.classification_indirect_view (
objectversionid,
valueid,
schemeversionid,
value
) as select
c.objectversionid,
cv.valueid,
cv.versionid,
cv.value
from xr.classifications c, xr.classification_values cv,
xr.classification_value_ancestors cva, xr.repositoryobjectresource ror
where c.valueid = cva.valueid and
cva.ancestorid = cv.valueid and cva.versionid = cv.versionid and
cv.versionid = ror.currentversion;
0 rows inserted/updated/deleted
ij> create view xr.businessentityqueryview (
id,
name,
uname,
type,
createdate,
changedate,
description,
authID,
ownerid,
ownername,
uownername,
businessTypeID,
businessType
) as select
be.id,
be.name,
be.uname,
be.type,
be.createdate,
be.changedate,
be.description,
be.authID ,
o.id,
o.name,
o.uname,
be.businessTypeID,
cv.value
from xr.businessentity be left outer join xr.businessentity o on be.owneruserUUID = o.id
left outer join xr.classificationcurrentvalueview cv on cv.valueid = be.businessTypeID
where be.deletedate is null and o.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.businessassociationqueryview (
id,
sourceid,
sourcename,
usourcename,
sourcetype,
targetid,
targetname,
utargetname,
targettype,
createdate,
changedate,
description,
ownerid,
ownername,
uownername,
associationTypeID,
associationType
) as select
bea.id,
bea.sourceid,
s.name,
s.uname,
s.type,
bea.targetid,
t.name,
t.uname,
t.type,
bea.createdate,
bea.changedate,
bea.description,
o.id,
o.name,
o.uname,
bea.assoctypeID,
cv.value
from xr.businessentityassociation bea
left outer join xr.businessentity s on bea.sourceID = s.ID
left outer join xr.businessentity t on bea.targetID = t.ID
left outer join xr.businessentity o on bea.ownerID = o.ID
left outer join xr.classificationcurrentvalueview cv on cv.valueid = bea.assoctypeID
where bea.deletedate is null and s.deletedate is null and t.deletedate is null and o.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.repositoryobjectcurrentversionview (
id, versionid, name, uname, versionName,
uri, versionuri, folderid,
ownerid, ownerdeletedate, ownername, uownername, ownerauthid, creatorname,
description, versionComment, objecttype, subtypeid, subtype,
checkforlock, createdate, versiondate, changedate, deletedate,
versioncontrolled, currentversion, previousversion, checkedout,
-- from RepositoryEntry
stability, statusid, status, isopaque,
startdate, expirationdate, contentchangedate, versioncontentid,
-- from ObjectAssociation
sourceid, targetid,
-- from ClassificationScheme
structuretype
) as select
ror.id, rov.id, rov.name, rov.uname, rov.versionName,
rov.uri, rov.versionuri, rov.folderid,
rov.ownerid, beo.deletedate, beo.name, beo.uname, beo.authid, bec.name,
rov.description, rov.versionComment, ror.type, ror.subtype, cvsubt.value,
ror.checkforlock, ror.createdate, rov.versiondate, rov.changedate, rov.deletedate,
ror.versioncontrolled, ror.currentversion, rov.previousversion, ror.checkedout,
-- from RepositoryEntry
re.stability, re.status, cvstatus.value, re.isopaque,
re.startdate, re.expirationdate, rec.contentchangedate, re.versioncontentid,
-- from ObjectAssociation
oa.sourceid, oa.targetid,
-- from ClassificationScheme
cs.structuretype
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
left outer join xr.classificationcurrentvalueview cvsubt on (ror.subtype = cvsubt.valueid)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.repositoryentry re on (ror.currentversion = re.versionid)
left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)
left outer join xr.classificationcurrentvalueview cvstatus on (re.status = cvstatus.valueid)
left outer join xr.objectassociation oa on (ror.id = oa.id)
left outer join xr.classificationscheme cs on (ror.id = cs.id)
where rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.repositoryobjectallversionview (
id, versionid, name, uname, versionName,
uri, versionuri, folderid,
ownerid, ownerdeletedate, ownername, uownername, ownerauthid, creatorname,
description, versionComment, objecttype, subtypeid, subtype,
checkforlock, createdate, versiondate, changedate, deletedate,
versioncontrolled, currentversion, previousversion, checkedout,
-- from RepositoryEntry
stability, statusid, status, isopaque,
startdate, expirationdate, contentchangedate, versioncontentid,
-- from ObjectAssociation
sourceid, targetid,
-- from ClassificationScheme
structuretype
) as select
ror.id, rov.id, rov.name, rov.uname, rov.versionName,
rov.uri, rov.versionuri, rov.folderid,
rov.ownerid, beo.deletedate, beo.name, beo.uname, beo.authid, bec.name,
rov.description, rov.versionComment, ror.type, ror.subtype, cvsubt.value,
ror.checkforlock, ror.createdate, rov.versiondate, rov.changedate, rov.deletedate,
ror.versioncontrolled, ror.currentversion, rov.previousversion, ror.checkedout,
-- from RepositoryEntry
re.stability, re.status, cvstatus.value, re.isopaque,
re.startdate, re.expirationdate, rec.contentchangedate, re.versioncontentid,
-- from ObjectAssociation
oa.sourceid, oa.targetid,
-- from ClassificationScheme
cs.structuretype
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
left outer join xr.classificationcurrentvalueview cvsubt on (ror.subtype = cvsubt.valueid)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.repositoryentry re on (ror.currentversion = re.versionid)
left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)
left outer join xr.classificationcurrentvalueview cvstatus on (re.status = cvstatus.valueid)
left outer join xr.objectassociation oa on (ror.id = oa.id)
left outer join xr.classificationscheme cs on (ror.id = cs.id)
where rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.repositoryobjectlifecycleview (
id,
versionid,
name,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerauthid,
ownername,
ownerdeletedate,
description,
objecttype,
subtypeid,
checkforlock,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
checkedout,
currentversion,
previousversion
) as select
ror.id,
rov.id,
rov.name,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.authid,
beo.name,
beo.deletedate,
rov.description,
ror.type,
ror.subtype,
ror.checkforlock,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.checkedout,
ror.currentversion,
rov.previousversion
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
left outer join xr.businessentity beo on (rov.ownerid = beo.id);
0 rows inserted/updated/deleted
ij> create view xr.repositoryobjectlabelview (
id,
versionid,
name,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerauthid,
ownername,
ownerdeletedate,
description,
objecttype,
subtypeid,
checkforlock,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
checkedout,
currentversion,
previousversion
) as select
ror.id,
rov.id,
rov.name,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.authid,
beo.name,
beo.deletedate,
rov.description,
ror.type,
ror.subtype,
ror.checkforlock,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.checkedout,
ror.currentversion,
rov.previousversion
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
left outer join xr.businessentity beo on (rov.ownerid = beo.id);
0 rows inserted/updated/deleted
ij> create view xr.repositoryentrycurrentversionview (
id,
versionid,
name,
uname,
versionName,
uri,
versionuri,
versioncontentid,
folderid,
ownerid,
ownerdeletedate,
ownername,
uownername,
ownerauthid,
creatorname,
description,
versionComment,
objecttype,
createdate,
versiondate,
changedate,
contentchangedate,
deletedate,
versioncontrolled,
currentversion,
previousversion,
checkedout,
subtypeid,
subtype,
stability,
statusid,
status,
startdate,
expirationdate,
isopaque
) as select
ror.id,
rov.id,
rov.name,
rov.uname,
rov.versionName,
rov.uri,
rov.versionuri,
re.versioncontentid,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.name,
beo.uname,
beo.authid,
bec.name,
rov.description,
rov.versionComment,
ror.type,
ror.createdate,
rov.versiondate,
rov.changedate,
rec.contentchangedate,
rov.deletedate,
ror.versioncontrolled,
ror.currentversion,
rov.previousversion,
ror.checkedout,
re.mimetype,
cvmime.value,
re.stability,
re.status,
cvstatus.value,
re.startdate,
re.expirationdate,
re.isopaque
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
inner join xr.repositoryentry re on (rov.id = re.versionid)
left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.classificationcurrentvalueview cvmime on (re.mimetype = cvmime.valueid)
left outer join xr.classificationcurrentvalueview cvstatus on (re.status = cvstatus.valueid)
where rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.repositoryentryallversionview (
id,
versionid,
name,
uname,
versionName,
uri,
versionuri,
versioncontentid,
folderid,
ownerid,
ownerdeletedate,
ownername,
uownername,
ownerauthid,
creatorname,
description,
versionComment,
objecttype,
createdate,
versiondate,
changedate,
contentchangedate,
deletedate,
versioncontrolled,
currentversion,
previousversion,
checkedout,
subtypeid,
subtype,
stability,
statusid,
status,
startdate,
expirationdate,
isopaque
) as select
ror.id,
rov.id,
rov.name,
rov.uname,
rov.versionName,
rov.uri,
rov.versionuri,
re.versioncontentid,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.name,
beo.uname,
beo.authid,
bec.name,
rov.description,
rov.versionComment,
ror.type,
ror.createdate,
rov.versiondate,
rov.changedate,
rec.contentchangedate,
rov.deletedate,
ror.versioncontrolled,
ror.currentversion,
rov.previousversion,
ror.checkedout,
re.mimetype,
cvmime.value,
re.stability,
re.status,
cvstatus.value,
re.startdate,
re.expirationdate,
re.isopaque
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
inner join xr.repositoryentry re on (rov.id = re.versionid)
left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.classificationcurrentvalueview cvmime on (re.mimetype = cvmime.valueid)
left outer join xr.classificationcurrentvalueview cvstatus on (re.status = cvstatus.valueid)
where rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.repositoryentrylifecycleview (
id,
versionid,
name,
versionName,
uri,
versionuri,
versioncontentid,
folderid,
ownerid,
ownerdeletedate,
ownerauthid,
ownername,
description,
objecttype,
subtypeid,
subtype,
checkforlock,
createdate,
versiondate,
changedate,
contentchangedate,
deletedate,
versioncontrolled,
checkedout,
currentversion,
previousversion
) as select
ror.id,
rov.id,
rov.name,
rov.versionName,
rov.uri,
rov.versionuri,
re.versioncontentid,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.authid,
beo.name,
rov.description,
ror.type,
re.mimetype,
cvmime.value,
ror.checkforlock,
ror.createdate,
rov.versiondate,
rov.changedate,
rec.contentchangedate,
rov.deletedate,
ror.versioncontrolled,
ror.checkedout,
ror.currentversion,
rov.previousversion
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
inner join xr.repositoryentry re on (rov.id = re.versionid)
left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.classificationcurrentvalueview cvmime on (re.mimetype = cvmime.valueid);
0 rows inserted/updated/deleted
ij> create view xr.repositoryentrycontentview (
id,
versionid,
versioncontentid,
uri,
versionuri,
content,
contentchangedate,
currentversion,
mimetypeid,
mimetype
) as select
ror.id,
rov.id,
re.versioncontentid,
rov.uri,
rov.versionuri,
rec.content,
rec.contentchangedate,
ror.currentversion,
re.mimetype,
cvmime.value
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
inner join xr.repositoryentry re on (rov.id = re.versionid)
left outer join xr.repositoryentrycontent rec on (re.versioncontentid = rec.versionid)
left outer join xr.classificationcurrentvalueview cvmime on (re.mimetype = cvmime.valueid)
left outer join xr.classificationcurrentvalueview cvstatus on (re.status = cvstatus.valueid)
where rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.objectgroupcurrentversionview (
id,
versionid,
name,
uname,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownername,
uownername,
ownerauthid,
creatorname,
description,
versionComment,
objecttype,
subtypeid,
subtype,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
currentversion,
previousversion,
checkedout
) as select
ror.id,
rov.id,
rov.name,
rov.uname,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.name,
beo.uname,
beo.authid,
bec.name,
rov.description,
rov.versionComment,
ror.type,
ror.subtype,
cvtype.value,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.currentversion,
rov.previousversion,
ror.checkedout
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
where ror.type = 'COLLECTION' and rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.objectgroupallversionview (
id,
versionid,
name,
uname,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownername,
uownername,
ownerauthid,
creatorname,
description,
versionComment,
objecttype,
subtypeid,
subtype,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
currentversion,
previousversion,
checkedout
) as select
ror.id,
rov.id,
rov.name,
rov.uname,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.name,
beo.uname,
beo.authid,
bec.name,
rov.description,
rov.versionComment,
ror.type,
ror.subtype,
cvtype.value,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.currentversion,
rov.previousversion,
ror.checkedout
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
where ror.type = 'COLLECTION' and rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.objectgrouplifecycleview (
id,
versionid,
name,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownerauthid,
ownername,
description,
objecttype,
subtypeid,
subtype,
checkforlock,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
checkedout,
currentversion,
previousversion
) as select
ror.id,
rov.id,
rov.name,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.authid,
beo.name,
rov.description,
ror.type,
ror.subtype,
cvtype.value,
ror.checkforlock,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.checkedout,
ror.currentversion,
rov.previousversion
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
where ror.type = 'COLLECTION';
0 rows inserted/updated/deleted
ij> create view xr.externaldependencycurrentversionview (
id,
versionid,
name,
uname,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownername,
uownername,
ownerauthid,
creatorname,
description,
versionComment,
objecttype,
subtypeid,
subtype,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
checkedout,
currentversion,
previousversion
) as select
ror.id,
rov.id,
rov.name,
rov.uname,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.name,
beo.uname,
beo.authid,
bec.name,
rov.description,
rov.versionComment,
ror.type,
ror.subtype,
cvtype.value,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.checkedout,
ror.currentversion,
rov.previousversion
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
where ror.type = 'EXTERNAL_DEPENDENCY' and rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.externaldependencyallversionview (
id,
versionid,
name,
uname,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownername,
uownername,
ownerauthid,
creatorname,
description,
versionComment,
objecttype,
dependencytypeid,
dependencytype,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
checkedout,
currentversion,
previousversion
) as select
ror.id,
rov.id,
rov.name,
rov.uname,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.name,
beo.uname,
beo.authid,
bec.name,
rov.description,
rov.versionComment,
ror.type,
ror.subtype,
cvtype.value,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.checkedout,
ror.currentversion,
rov.previousversion
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
where ror.type = 'EXTERNAL_DEPENDENCY' and rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.objectassociationcurrentversionview (
id,
versionid,
name,
uname,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownername,
uownername,
ownerauthid,
creatorname,
description,
versionComment,
objecttype,
subtypeid,
subtype,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
currentversion,
previousversion,
checkedout,
sourceid,
targetid
) as select
ror.id,
rov.id,
rov.name,
rov.uname,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.name,
beo.uname,
beo.authid,
bec.name,
rov.description,
rov.versionComment,
ror.type,
ror.subtype,
cvtype.value,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.currentversion,
rov.previousversion,
ror.checkedout,
oa.sourceid,
oa.targetid
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
inner join xr.objectassociation oa on (ror.id = oa.id)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
where rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.objectassociationallversionview (
id,
versionid,
name,
uname,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownername,
uownername,
ownerauthid,
creatorname,
description,
versionComment,
objecttype,
subtypeid,
subtype,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
currentversion,
previousversion,
checkedout,
sourceid,
targetid
) as select
ror.id,
rov.id,
rov.name,
rov.uname,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.name,
beo.uname,
beo.authid,
bec.name,
rov.description,
rov.versionComment,
ror.type,
ror.subtype,
cvtype.valueid,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.currentversion,
rov.previousversion,
ror.checkedout,
oa.sourceid,
oa.targetid
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.id = rov.resourceid)
inner join xr.objectassociation oa on (ror.id = oa.id)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
where rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.objectassociationlifecycleview (
id,
versionid,
name,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownerauthid,
ownername,
description,
objecttype,
subtypeid,
subtype,
checkforlock,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
checkedout,
currentversion,
previousversion,
sourceid,
targetid
) as select
ror.id,
rov.id,
rov.name,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.authid,
beo.name,
rov.description,
ror.type,
ror.subtype,
cvtype.value,
ror.checkforlock,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.checkedout,
ror.currentversion,
rov.previousversion,
oa.sourceid,
oa.targetid
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
inner join xr.objectassociation oa on (ror.id = oa.id)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid);
0 rows inserted/updated/deleted
ij> create view xr.objectassociationcurrentversionqueryview (
id,
versionid,
name,
uname,
versionName,
uri,
versionuri,
folderid,
ownerid,
ownerdeletedate,
ownername,
uownername,
ownerauthid,
creatorname,
description,
versionComment,
objecttype,
subtypeid,
subtype,
createdate,
versiondate,
changedate,
deletedate,
versioncontrolled,
currentversion,
previousversion,
checkedout,
sourceid,
sourcename,
usourcename,
sourcetype,
targetid,
targetname,
utargetname,
targettype
) as select
ror.id,
rov.id,
rov.name,
rov.uname,
rov.versionName,
rov.uri,
rov.versionuri,
rov.folderid,
rov.ownerid,
beo.deletedate,
beo.name,
beo.uname,
beo.authid,
bec.name,
rov.description,
rov.versionComment,
ror.type,
ror.subtype,
cvtype.value,
ror.createdate,
rov.versiondate,
rov.changedate,
rov.deletedate,
ror.versioncontrolled,
ror.currentversion,
rov.previousversion,
ror.checkedout,
oa.sourceid,
s.name,
s.uname,
s.objecttype,
oa.targetid,
t.name,
t.uname,
t.objecttype
from xr.repositoryobjectresource ror
inner join xr.repositoryobjectversion rov on (ror.currentversion = rov.id)
inner join xr.objectassociation oa on (ror.id = oa.id)
left outer join xr.repositoryobjectcurrentversionview s on (oa.sourceid = s.id)
left outer join xr.repositoryobjectcurrentversionview t on (oa.targetid = t.id)
left outer join xr.businessentity beo on (rov.ownerid = beo.id)
left outer join xr.businessentity bec on (rov.creatorid = bec.id)
left outer join xr.classificationcurrentvalueview cvtype on (ror.subtype = cvtype.valueid)
where rov.deletedate is null;
0 rows inserted/updated/deleted
ij> create view xr.lockview (
locktoken,
resourceid,
resourcename,
userid,
username,
exclusive,
timeoutseconds,
expirationdate
) as select
l.locktoken,
l.resourceid,
rov.name,
l.ownerid,
be.name,
l.exclusive,
l.timeoutseconds,
l.expirationdate
from xr.lock l, xr.repositoryobjectresource ror, xr.repositoryobjectversion rov,
xr.businessentity be
where l.resourceid = ror.id and ror.currentversion = rov.id and l.ownerid = be.id;
0 rows inserted/updated/deleted
ij> --echo === Create Users ================================================;
insert into xr.businessentity ( ID, type, name, authID, createdate, changedate )
values ('xxxxFILTERED-UUIDxxxx',
'USER',
'Administrator',
'xradmin',
CURRENT TIMESTAMP, CURRENT TIMESTAMP);
1 row inserted/updated/deleted
ij> insert into xr.xruser
(businessentityid,
userid)
values ('xxxxFILTERED-UUIDxxxx',
'xradmin');
1 row inserted/updated/deleted
ij> insert into xr.businessentity ( ID, type, name, authID, createdate, changedate )
values ('xxxxFILTERED-UUIDxxxx',
'USER',
'Sample XR User',
'xrguest', CURRENT TIMESTAMP, CURRENT TIMESTAMP);
1 row inserted/updated/deleted
ij> insert into xr.xruser (businessentityid, userid)
values ('xxxxFILTERED-UUIDxxxx',
'xrguest');
1 row inserted/updated/deleted
ij> --echo == Create locators ==============================================;
insert into xr.locator (
ownerID,
type,
usetypeID,
value,
street1,
street2,
city,
state,
country,
postalcode
) values (
'xxxxFILTERED-UUIDxxxx',
'EMAIL',
'xxxxFILTERED-UUIDxxxx',
'xradmin@xr.com',
'',
'',
'',
'',
'',
'');
1 row inserted/updated/deleted
ij> insert into xr.locator (
ownerID,
type,
usetypeID,
value,
street1,
street2,
city,
state,
country,
postalcode
) values (
'xxxxFILTERED-UUIDxxxx',
'EMAIL',
'xxxxFILTERED-UUIDxxxx',
'xrguest@yourmail.com',
'',
'',
'',
'',
'',
'');
1 row inserted/updated/deleted
ij> --echo =============================================================================;
--echo xr database insert script
--echo =============================================================================;
--XRADMIN ID 'xxxxFILTERED-UUIDxxxx'
-- repositoryobjectversion.folderid references xr.repositoryobjectresource(id)
--ROOT FolderID foreign 'xxxxFILTERED-UUIDxxxx'
--echo == ROOT Folder ====================================;
--echo == ROOT Folder ====================================;
insert into xr.repositoryobjectversion (
id, resourceid, name, versionName, uri, versionuri, description,
ownerid, creatorid, versiondate, changedate, previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'', '1.0', '/',
'1.0/',
'Root XR folder',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP, CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> -- Root folder
insert into xr.repositoryobjectresource (
id, type, subtype, creatorid, createdate, currentVersion, versionControlled
) values (
'xxxxFILTERED-UUIDxxxx',
'COLLECTION',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx',
0);
1 row inserted/updated/deleted
ij> -- This ObjectGroup classified as INTERNAL_USE
insert into xr.classifications ( objectversionid, valueid )
values ('xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==================================================;
--echo =============================================================================;
--echo xr database insert script
--echo =============================================================================;
--XRADMIN ID 'xxxxFILTERED-UUIDxxxx'
-- repositoryobjectversion.folderid references xr.repositoryobjectresource(id)
--ROOT FolderID foreign 'xxxxFILTERED-UUIDxxxx'
--echo == Business types =======================================;
--echo =========================================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'Business Types', '1.0',
'xxxxFILTERED-UUIDxxxx',
'Business+Types',
'1.0/Business+Types/1.0',
'Valid values for the Business TYpe property of a Business',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id,
type,
subtype,
creatorid,
createdate,
currentVersion,
versionControlled
) values (
'xxxxFILTERED-UUIDxxxx',
'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx',
0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (
id,
structuretype
) values (
'xxxxFILTERED-UUIDxxxx',
'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as INTERNAL_USE
insert into xr.classifications (
objectversionid,
valueid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo =======================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'CORPORATION',
'CORPORATION');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'ORGANIZATION',
'ORGANIZATION');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'DIVISION',
'DIVISION');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'GROUP',
'GROUP');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'PARTNERSHIP',
'PARTNERSHIP');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
--echo == Locator Use types ====================================;
--echo =========================================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'Locator Use Types', '1.0',
'xxxxFILTERED-UUIDxxxx',
'Locator+Use+Types',
'1.0/Locator+Use+Types/1.0',
'Valid values for the Use Type property of a Locator',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id,
type,
subtype,
creatorid,
createdate,
currentVersion,
versionControlled
) values (
'xxxxFILTERED-UUIDxxxx',
'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx',
0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (
id,
structuretype
) values (
'xxxxFILTERED-UUIDxxxx',
'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as INTERNAL_USE
insert into xr.classifications (
objectversionid,
valueid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'HOME',
'HOME');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'OFFICE',
'OFFICE');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'MOBILE',
'MOBILE');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'PAGER',
'PAGER');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
--echo == Create Association types ClassificationScheme ====================================;
--echo =====================================================================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'Business Relationship Types', '1.0',
'xxxxFILTERED-UUIDxxxx',
'Business+Relationship+Types',
'1.0/Business+Relationship+Types/1.0',
'Valid values for the Type property of a Business Relationship',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id,
type,
subtype,
creatorid,
createdate,
currentVersion,
versionControlled
) values (
'xxxxFILTERED-UUIDxxxx',
'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx',
0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (
id,
structuretype
) values (
'xxxxFILTERED-UUIDxxxx',
'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as INTERNAL_USE
insert into xr.classifications (
objectversionid,
valueid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo =====================================================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'CUSTOMER',
'CUSTOMER');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo =====================================================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'SUPPLIER',
'SUPPLIER');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo =====================================================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'PARTNER',
'PARTNER');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo =====================================================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'MANAGER',
'MANAGER');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo == Create Scheme Types ClassificationScheme ====================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'Classification Scheme Types', '1.0',
'xxxxFILTERED-UUIDxxxx',
'Classification+Scheme+Types',
'1.0/Classification+Scheme+Types/1.0',
'Valid values for the ClassificationSchemeType property of a Classification Scheme',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP, CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id, type, subtype, creatorid, createdate, currentVersion, versionControlled
) values (
'xxxxFILTERED-UUIDxxxx', 'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx', 0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (id, structuretype)
values ('xxxxFILTERED-UUIDxxxx', 'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as INTERNAL_USE
insert into xr.classifications ( objectversionid, valueid )
values ('xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'VALUE_LIST');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo == Create MimeTypes ClassificationScheme ====================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'Mime Types', '1.0',
'xxxxFILTERED-UUIDxxxx',
'Mime+Types',
'1.0/Mime+Types/1,0',
'Valid values for the MimeType property of a Registry Entry',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP, CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id, type, subtype, creatorid, createdate, currentVersion, versionControlled
) values (
'xxxxFILTERED-UUIDxxxx', 'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx', 0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (id, structuretype)
values ('xxxxFILTERED-UUIDxxxx', 'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as INTERNAL_USE
insert into xr.classifications ( objectversionid, valueid )
values ('xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, description )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'TEXT/S-SSI-HTML',
'For: htmls and shtml file types');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, description )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'TEXT/PLAIN',
'For: htmls and shtml file types');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, description )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'APPLICATION/OCTET-STREAM',
'For: htmls and shtml file types');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, description )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'APPLICATION/XML',
'For: xsl file type');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, description )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'TEXT/RICHTEXT',
'For: rtx file type');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, description )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'TEXT/CSS',
'For: css and s file type');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, description )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'APPLICATION/X-TEXTINFO',
'For: texi and texinfo file types');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, description )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'TEXT/HTML',
'For: htm and html file types');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, description )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'TEXT/XML',
'For: xml and dtd file types');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo == Create Group Types ClassificationScheme ====================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'Collection Types', '1.0',
'xxxxFILTERED-UUIDxxxx',
'Collection+Types',
'1.0/Collection+Types/1.0',
'Valid values for the CollectionType property of a Collection',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP, CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id, type, subtype, creatorid, createdate, currentVersion, versionControlled
) values (
'xxxxFILTERED-UUIDxxxx', 'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx', 0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (id, structuretype)
values ('xxxxFILTERED-UUIDxxxx', 'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as INTERNAL_USE
insert into xr.classifications ( objectversionid, valueid )
values ('xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==================================================================================;
insert into xr.classification_values ( versionid, valueid, value )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'FOLDER');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'PROJECT');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo == Create Object Relationship Types ClassificationScheme ====================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'Object Relationship Types','1.0',
'xxxxFILTERED-UUIDxxxx',
'Object+Relationship+Types',
'1.0/Object+Relationship+Types/1.0',
'Valid values for the RelationshipType property of an Object Relationship',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP, CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id, type, subtype, creatorid, createdate, currentVersion, versionControlled
) values (
'xxxxFILTERED-UUIDxxxx', 'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx', 0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (id, structuretype)
values ('xxxxFILTERED-UUIDxxxx', 'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as INTERNAL_USE
insert into xr.classifications ( objectversionid, valueid )
values ('xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo =====================================================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'INCLUDES',
'INCLUDES');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo =====================================================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'IMPORTS',
'IMPORTS');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo =====================================================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'REDEFINES',
'REDEFINES');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo == Create Scheme Usage ClassificationScheme ====================================;
--echo ================================================================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'Classification Scheme Uses', '1.0',
'xxxxFILTERED-UUIDxxxx',
'Classification+Scheme+Uses',
'1.0/Classification+Scheme+Uses/1.0',
'Usage categories for Classification Schemes',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP, CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id, type, subtype, creatorid, createdate, currentVersion, versionControlled
) values (
'xxxxFILTERED-UUIDxxxx',
'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx', 0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (id, structuretype)
values ('xxxxFILTERED-UUIDxxxx', 'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as INTERNAL_USE
insert into xr.classifications ( objectversionid, valueid )
values ('xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> -- This scheme classified as CLASSIFIES_CLASSIFICATION_SCHEME
insert into xr.classifications ( objectversionid, valueid )
values ('xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ================================================================================;
-- Values
insert into xr.classification_values ( versionid, valueid, value, description, parentvalueid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'CLASSIFIES_ALL',
'Values in this ClassificationScheme can classify objects of any type.',
null);
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ================================================================================;
insert into xr.classification_values ( versionid, valueid, value, description, parentvalueid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'INTERNAL_USE',
'This ClassificationScheme has a special use in XR.',
null);
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ================================================================================;
insert into xr.classification_values ( versionid, valueid, value, description, parentvalueid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'CLASSIFIES_ORGANIZATION',
'Values in this ClassificationScheme can classify objects of type ORGANIZATION.',
null);
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ================================================================================;
insert into xr.classification_values ( versionid, valueid, value, description, parentvalueid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'CLASSIFIES_REGISTRY_OBJECTS',
'Values in this ClassificationScheme can classify objects of type REGISTRY_OBJECT.',
null);
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ================================================================================;
insert into xr.classification_values ( versionid, valueid, value, description, parentvalueid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'CLASSIFIES_REGISTRY_ENTRIES',
'Values in this ClassificationScheme can classify objects of type REGISTRY_ENTRY.',
null);
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ================================================================================;
insert into xr.classification_values ( versionid, valueid, value, description, parentvalueid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'CLASSIFIES_COLLECTIONS',
'Values in this ClassificationScheme can classify objects of type COLLECTIONS.',
null);
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ================================================================================;
insert into xr.classification_values ( versionid, valueid, value, description, parentvalueid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'CLASSIFIES_CLASSIFICATION_SCHEMES',
'values in this ClassificationScheme can classify objects of type CLASSIFICATION_SCHEMES',
null);
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ================================================================================;
--echo ================================================================================;
--echo == RepositoryEntry Status values ClassificationScheme ====================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'Status Values', '1.0',
'xxxxFILTERED-UUIDxxxx',
'Status+Values',
'1.0/Status+Values/1.0',
'Valid values for the Status property of a Registry Entry',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP, CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id, type, subtype, creatorid, createdate, currentVersion, versionControlled
) values (
'xxxxFILTERED-UUIDxxxx', 'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx', 0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (id, structuretype)
values ('xxxxFILTERED-UUIDxxxx', 'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as INTERNAL_USE
insert into xr.classifications ( objectversionid, valueid )
values ('xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Submitted');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Approved');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Deprecated');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Withdrawn');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo == Create Industries ClassificationScheme ====================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'Industries', '1.0',
'xxxxFILTERED-UUIDxxxx',
'Industries', '1.0/industries/1.0',
'North American Industry Classification Scheme',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP, CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id, type, subtype, creatorid, createdate, currentVersion, versionControlled
) values (
'xxxxFILTERED-UUIDxxxx',
'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx', 0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (id, structuretype)
values ('xxxxFILTERED-UUIDxxxx', 'TREE');
1 row inserted/updated/deleted
ij> -- This scheme classified as CLASSIFIES_ALL
insert into xr.classifications ( objectversionid, valueid )
values ('xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Wholesale Trade');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, parentvalueid)
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Wholesale Trade, Durable Goods',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> -- Wholesale Trade is ancestor of Wholesale Trade, Durable Goods
insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value)
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Manufacturing');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, parentvalueid)
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Food Manufacturing',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> -- Manufacturing is ancestor of Food Manufacturing
insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, parentvalueid)
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Computer and Electronic Product Manufacturing',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> -- Manufacturing is ancestor of Computer and Electronic Product Manufacturing
insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value)
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Utilities');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value)
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Construction');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value)
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Retail Trade');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value)
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Finance and Insurance');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, parentvalueid)
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Insurance Carriers and Related Activities',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> -- Finance and Insurance is ancestor of Insurance Carriers and Related Activities
insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_values ( versionid, valueid, value, parentvalueid)
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'Funds, Trusts, and Other Financial Vehicles',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> -- Finance and Insurance is ancestor of Funds, Trusts, and Other Financial Vehicles
insert into xr.classification_value_ancestors ( versionid, valueid, ancestorid )
values ( 'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx', 'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
--echo == Web Service Schemes ===================================;
--echo ==========================================================;
--XRADMIN ID 'xxxxFILTERED-UUIDxxxx'
-- repositoryobjectversion.folderid references xr.repositoryobjectresource(id)
--ROOT FolderID foreign 'xxxxFILTERED-UUIDxxxx'
--echo ==========================================================;
--echo == WSDL Function =========================================;
--echo ==========================================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'WSDL Function', '1.0',
'xxxxFILTERED-UUIDxxxx',
'WSDL+Function',
'1.0/WSDL+Function/1.0',
'The function that this wsdl provides, (interface/implementation/both/etc) ',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id,
type,
subtype,
creatorid,
createdate,
currentVersion,
versionControlled
) values (
'xxxxFILTERED-UUIDxxxx',
'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx',
0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (
id,
structuretype
) values (
'xxxxFILTERED-UUIDxxxx',
'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as
insert into xr.classifications (
objectversionid,
valueid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'INTERFACE',
'INTERFACE');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'IMPLEMENTAION',
'IMPLEMENTAION');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'ALL',
'This WSDL doc stands alone and has all of the information in one file');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'BINDING',
'BINDING');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'SERVICE',
'SERVICE');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
--echo == Web Service Creation Toolkit ==========================;
--echo ==========================================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'Web Service Toolkit', '1.0',
'xxxxFILTERED-UUIDxxxx',
'Web+Service+Toolkit',
'1.0/Web+Service+Toolkit/1.0',
'Web Service toolkit used to create this Document',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id,
type,
subtype,
creatorid,
createdate,
currentVersion,
versionControlled
) values (
'xxxxFILTERED-UUIDxxxx',
'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx',
0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (
id,
structuretype
) values (
'xxxxFILTERED-UUIDxxxx',
'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as
insert into xr.classifications (
objectversionid,
valueid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'MICROSOFT',
'MICROSOFT');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'WSAD',
'WSAD');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'WSTK',
'WSTK');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'WSIF',
'WSIF');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
--echo == WSDL Binding Schema ===================================;
--echo ==========================================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'WSDL Binding Schema', '1.0',
'xxxxFILTERED-UUIDxxxx',
'WSDL+Binding+Schema',
'1.0/WSDL+Binding+Schema/1.0',
'Valid types of soap binding style, see www.w3c.org wsdl/soap spec',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id,
type,
subtype,
creatorid,
createdate,
currentVersion,
versionControlled
) values (
'xxxxFILTERED-UUIDxxxx',
'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx',
0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (
id,
structuretype
) values (
'xxxxFILTERED-UUIDxxxx',
'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as
insert into xr.classifications (
objectversionid,
valueid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'SOAP',
'SOAP');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'HTTP',
'HTTP');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'MIME',
'MIME');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
--echo == SOAP Binding Style ====================================;
--echo ==========================================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'SOAP Binding Style', '1.0',
'xxxxFILTERED-UUIDxxxx',
'SOAP+Binding+Style',
'1.0/SOAP+Binding+Style/1.0',
'Valid types of soap binding style, see www.w3c.org wsdl/soap spec',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id,
type,
subtype,
creatorid,
createdate,
currentVersion,
versionControlled
) values (
'xxxxFILTERED-UUIDxxxx',
'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx',
0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (
id,
structuretype
) values (
'xxxxFILTERED-UUIDxxxx',
'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as
insert into xr.classifications (
objectversionid,
valueid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'RPC',
'RPC');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'DOCUMENT',
'DOCUMENT');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
--echo == WSDL Port types ====================================;
--echo =========================================================;
insert into xr.repositoryobjectversion (
id,
resourceid,
name,
versionName,
folderid,
uri,
versionuri,
description,
ownerid,
creatorid,
versiondate,
changedate,
previousversion
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'WSDL Port Type Operations', '1.0',
'xxxxFILTERED-UUIDxxxx',
'WSDL+Port+Type+Operations',
'1.0/WSDL+Port+Type+Operations/1.0',
'Valid types for port type operation, see www.w3c.org wsdl spec',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
null
);
1 row inserted/updated/deleted
ij> insert into xr.repositoryobjectresource (
id,
type,
subtype,
creatorid,
createdate,
currentVersion,
versionControlled
) values (
'xxxxFILTERED-UUIDxxxx',
'CLASSIFICATION_SCHEME',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
CURRENT TIMESTAMP,
'xxxxFILTERED-UUIDxxxx',
0);
1 row inserted/updated/deleted
ij> -- This scheme goes into root folder
insert into xr.objectgroup_content (
versionid,
memberid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx'
);
1 row inserted/updated/deleted
ij> insert into xr.classificationscheme (
id,
structuretype
) values (
'xxxxFILTERED-UUIDxxxx',
'LIST');
1 row inserted/updated/deleted
ij> -- This scheme classified as
insert into xr.classifications (
objectversionid,
valueid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'ONE-WAY',
'ONE-WAY');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'REQUEST-RESPONSE',
'REQUEST-RESPONSE');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
)values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'SOLICIT-RESPONSE',
'SOLICIT-RESPONSE');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> --echo ==========================================================;
insert into xr.classification_values (
versionid,
valueid,
value,
description
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'NOTIFICATION',
'NOTIFICATION');
1 row inserted/updated/deleted
ij> insert into xr.classification_value_ancestors (
versionid,
valueid,
ancestorid
) values (
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx',
'xxxxFILTERED-UUIDxxxx');
1 row inserted/updated/deleted
ij> -- Now do really what I wanted (this gets NullPointerException before the fix):
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 40000;
ij> SELECT id, versionid, name, versionName, folderid, uri, versionuri, ownerid, ownername, ownerauthid,
description, versionComment, objecttype, subtypeid, subtype, createdate,versiondate,
changedate, deletedate, versioncontrolled, currentversion, previousversion, checkedout,
statusid, status, startdate, expirationdate, contentchangedate, versioncontentid, sourceid,
targetid, structuretype
FROM xr.repositoryobjectallversionview rov
where (uname = UPPER('two') or uname = UPPER('my project'))
and (versionid in
(select versionid
from xr.versionlabel
where UPPER(label) = UPPER('Snapshot')))
and deletedate is null
order by versionname asc;
ID |VERSIONID |NAME |VERSIONNAME |FOLDERID |URI |VERSIONURI |OWNERID |OWNERNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij> -- NOTE: EXCEPT THE FIRST TWO TABLES IN OUTPUT (REPOSITORYOBJECTRESOURCE AND REPOSITORYOBJECTVERSION),
-- ALL OTHER TABLES SHOULD HAVE: Number of opens = 0 AND Rows seen = 0. THIS IS BECAUSE PREDICATE
-- ON UNAME OF REPOSITORYOBJECTVERSION SHOULD BE PUSHED DOWN ALL THE WAY TO BASE TABLE ! bug 4983
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
SELECT id, versionid, name, versionName, folderid, uri, versionuri, ownerid, ownername, ownerauthid,
description, versionComment, objecttype, subtypeid, subtype, createdate,versiondate,
changedate, deletedate, versioncontrolled, currentversion, previousversion, checkedout,
statusid, status, startdate, expirationdate, contentchangedate, versioncontentid, sourceid,
targetid, structuretype
FROM xr.repositoryobjectallversionview rov
where (uname = UPPER('two') or uname = UPPER('my project'))
and (versionid in
(select versionid
from xr.versionlabel
where UPPER(label) = UPPER('Snapshot')))
and deletedate is null
order by versionname asc
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 0
Rows returned = 0
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=0
Number of rows output=0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (32):
Number of opens = 1
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Hash Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 0
Rows seen from the right = 0
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Hash Join ResultSet:
Number of opens = 1
Rows seen from the left = 16
Rows seen from the right = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Table Scan ResultSet for REPOSITORYOBJECTRESOURCE at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 16
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1, 2, 4, 5, 6, 7, 8}
Number of columns fetched=8
Number of pages visited=2
Number of rows qualified=16
Number of rows visited=16
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
Right result set:
Project-Restrict ResultSet (13):
Number of opens = 16
Rows seen = 16
Rows filtered = 16
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Hash Scan ResultSet for REPOSITORYOBJECTVERSION at serializable isolation level using share table locking:
Number of opens = 16
Hash table size = 16
Hash key is column number 1
Rows seen = 16
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=16
Number of pages visited=2
Number of rows qualified=16
Number of rows visited=16
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
Column[0][0] Id: 14
Operator: =
Ordered nulls: true
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 14
Operator: =
Ordered nulls: true
Unknown return value: false
Negate comparison result: false
next qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Right result set:
Hash Table ResultSet (18):
Number of opens = 0
Hash table size = 0
Hash key is column number 0
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Source result set:
Project-Restrict ResultSet (17):
Number of opens = 0
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Hash Join ResultSet:
Number of opens = 0
Rows seen from the left = 0
Rows seen from the right = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Table Scan ResultSet for CLASSIFICATION_VALUES at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 0
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
null
stop position:
null
qualifiers:
None
Right result set:
Hash Scan ResultSet for REPOSITORYOBJECTRESOURCE using constraint ROR_CURRENTVERSION at serializable isolation level using share table locking:
Number of opens = 0
Hash table size = 0
Hash key is column number 0
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
None
stop position:
None
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Right result set:
Hash Scan ResultSet for BUSINESSENTITY at serializable isolation level using share table locking:
Number of opens = 0
Hash table size = 0
Hash key is column number 0
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Right result set:
Hash Scan ResultSet for BUSINESSENTITY at serializable isolation level using share table locking:
Number of opens = 0
Hash table size = 0
Hash key is column number 0
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Right result set:
Hash Scan ResultSet for REPOSITORYENTRY at serializable isolation level using share table locking:
Number of opens = 0
Hash table size = 0
Hash key is column number 0
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Right result set:
Hash Scan ResultSet for REPOSITORYENTRYCONTENT at serializable isolation level using share table locking:
Number of opens = 0
Hash table size = 0
Hash key is column number 0
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Right result set:
Hash Table ResultSet (27):
Number of opens = 0
Hash table size = 0
Hash key is column number 0
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Source result set:
Project-Restrict ResultSet (26):
Number of opens = 0
Rows seen = 0
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Hash Join ResultSet:
Number of opens = 0
Rows seen from the left = 0
Rows seen from the right = 0
Rows filtered = 0
Rows returned = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
Index Scan ResultSet for REPOSITORYOBJECTRESOURCE using constraint ROR_CURRENTVERSION at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 0
Rows seen = 0
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
None
stop position:
None
qualifiers:
None
Right result set:
Hash Scan ResultSet for CLASSIFICATION_VALUES at serializable isolation level using share table locking:
Number of opens = 0
Hash table size = 0
Hash key is column number 0
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Right result set:
Hash Scan ResultSet for OBJECTASSOCIATION at serializable isolation level using share table locking:
Number of opens = 0
Hash table size = 0
Hash key is column number 0
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Right result set:
Hash Scan ResultSet for CLASSIFICATIONSCHEME at serializable isolation level using share table locking:
Number of opens = 0
Hash table size = 0
Hash key is column number 0
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Right result set:
Project-Restrict ResultSet (31):
Number of opens = 0
Rows seen = 0
Rows filtered = 0
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Hash Scan ResultSet for VERSIONLABEL at serializable isolation level using share table locking:
Number of opens = 0
Hash table size = 0
Hash key is column number 0
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> rollback;
ij> autocommit on;
ij> CREATE TABLE D1 (A INT, B VARCHAR(4) FOR BIT DATA);
0 rows inserted/updated/deleted
ij> INSERT INTO D1 VALUES (1, x'600Eaaef') ;
1 row inserted/updated/deleted
ij> INSERT INTO D1 VALUES (2, x'83452213') ;
1 row inserted/updated/deleted
ij> select * from D1 where B IN (x'600Eaaef',x'83452213') ;
A |B
--------------------
1 |600eaaef
2 |83452213
ij> select * from D1 where B IN (x'83452213') ;
A |B
--------------------
2 |83452213
ij> select * from D1 where B IN (x'600Eaaef') ;
A |B
--------------------
1 |600eaaef
ij> CREATE VIEW V1 AS SELECT A,B FROM D1 UNION SELECT A,B FROM D1;
0 rows inserted/updated/deleted
ij> SELECT * FROM V1;
A |B
--------------------
1 |600eaaef
2 |83452213
ij> select * from V1 where B IN (x'83452213') ;
A |B
--------------------
2 |83452213
ij> select * from V1 where B IN (x'600Eaaef') ;
A |B
--------------------
1 |600eaaef
ij> select * from V1 where B = x'600Eaaef' ;
A |B
--------------------
1 |600eaaef
ij> -- these all failed with the initial patch to DERBY-649.
select * from V1 where B IN (x'600Eaaef',x'83452213') ;
A |B
--------------------
1 |600eaaef
2 |83452213
ij> select * from V1 where B >= x'600Eaaef' ;
A |B
--------------------
1 |600eaaef
2 |83452213
ij> select * from V1 where B <= x'83452213' ;
A |B
--------------------
1 |600eaaef
2 |83452213
ij> select * from V1 where B <> x'83452213' ;
A |B
--------------------
1 |600eaaef
ij> DROP VIEW V1;
0 rows inserted/updated/deleted
ij> DROP TABLE D1;
0 rows inserted/updated/deleted
ij>