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 ================================================; -- if we don't commit, the following insert will block due to fix of beetle 4821 commit; ij> -- The following insert statement shouldn't block for 2 minutes!! The compile of the trigger -- shouldn't wait for timeout!! 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>