| 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 partial row access for update and delete |
| -- |
| maximumdisplaywidth 2000; |
| ij> drop function getScanCols; |
| ERROR 42Y55: 'DROP FUNCTION' cannot be performed on 'GETSCANCOLS' because it does not exist. |
| ij> drop table basic; |
| ERROR 42Y55: 'DROP TABLE' cannot be performed on 'BASIC' because it does not exist. |
| ij> drop table p; |
| ERROR 42Y55: 'DROP TABLE' cannot be performed on 'P' because it does not exist. |
| ij> CREATE FUNCTION getScanCols(value VARCHAR(32672)) |
| RETURNS VARCHAR (32672) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.StatParser.getScanCols' |
| LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL; |
| 0 rows inserted/updated/deleted |
| ij> create table p (ccharForBitData char(1) for bit data not null, cdec dec(6,2) not null, unindexed smallint, cchar char(10) not null, |
| constraint pk1 primary key (cchar, ccharForBitData), constraint pk2 unique (cdec)); |
| 0 rows inserted/updated/deleted |
| ij> insert into p values (x'00', 0.0, 11, '00'); |
| 1 row inserted/updated/deleted |
| ij> insert into p values (x'11', 1.1, 22, '11'); |
| 1 row inserted/updated/deleted |
| ij> insert into p values (x'22', 2.2, 33, '22'); |
| 1 row inserted/updated/deleted |
| ij> insert into p values (x'33', 3.3, 44, '33'); |
| 1 row inserted/updated/deleted |
| ij> create table basic (cint int, cchar char(10), |
| ctime time, cdec dec(6,2), |
| ccharForBitData char(1) for bit data, unindexed int); |
| 0 rows inserted/updated/deleted |
| ij> create index b1 on basic (cchar, ccharForBitData, cint); |
| 0 rows inserted/updated/deleted |
| ij> create index b2 on basic (ctime); |
| 0 rows inserted/updated/deleted |
| ij> create index b3 on basic (ctime, cint); |
| 0 rows inserted/updated/deleted |
| ij> create index b4 on basic (cint); |
| 0 rows inserted/updated/deleted |
| ij> maximumdisplaywidth 200; |
| ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); |
| 0 rows inserted/updated/deleted |
| ij> -- the extra 33s are so we can ensure we'll use an index when looking for the others |
| insert into basic values (11, '11', TIME('11:11:11'), 1.1, x'11', 11); |
| 1 row inserted/updated/deleted |
| ij> insert into basic values (22, '22', TIME('22:22:22'), 2.2, x'22', 22); |
| 1 row inserted/updated/deleted |
| ij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33); |
| 1 row inserted/updated/deleted |
| ij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33); |
| 1 row inserted/updated/deleted |
| ij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33); |
| 1 row inserted/updated/deleted |
| ij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33); |
| 1 row inserted/updated/deleted |
| ij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33); |
| 1 row inserted/updated/deleted |
| ij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33); |
| 1 row inserted/updated/deleted |
| ij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33); |
| 1 row inserted/updated/deleted |
| ij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33); |
| 1 row inserted/updated/deleted |
| ij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33); |
| 1 row inserted/updated/deleted |
| ij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33); |
| 1 row inserted/updated/deleted |
| ij> -- simple update of each column |
| update basic set cint = cint; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 4} |
| ij> update basic set cchar = cchar; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1, 2, 3} |
| ij> update basic set ctime = ctime; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B3 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1, 2} |
| ij> update basic set cdec = cdec; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={3} |
| ij> update basic set ccharForBitData = ccharForBitData; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1, 2, 3} |
| ij> update basic set unindexed = unindexed; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={5} |
| ij> -- confirm the table is ok |
| select ccharForBitData, ctime, cdec, cint, cchar from basic; |
| CCH&|CTIME |CDEC |CINT |CCHAR |
| --------------------------------------------- |
| 11 |11:11:11|1.10 |11 |11 |
| 22 |22:22:22|2.20 |22 |22 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'BASIC')); |
| 1 |
| ----------- |
| 1 |
| ij> update basic set cint = cint where cint = 11; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> update basic set cchar = cchar where cint = 11; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> update basic set ctime = ctime where cint = 11; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> update basic set ctime = ctime where cint = 11; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> update basic set cdec = cdec where cint = 11; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> update basic set ccharForBitData = ccharForBitData where cint = 11; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> -- confirm the table is ok |
| select ccharForBitData, ctime, cdec, cint, cchar from basic; |
| CCH&|CTIME |CDEC |CINT |CCHAR |
| --------------------------------------------- |
| 11 |11:11:11|1.10 |11 |11 |
| 22 |22:22:22|2.20 |22 |22 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'BASIC')); |
| 1 |
| ----------- |
| 1 |
| ij> update basic set cint = cint where ccharForBitData = x'11'; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 4} |
| ij> update basic set cchar = cchar where ccharForBitData = x'11'; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1, 2, 3} |
| ij> update basic set ctime = ctime where ccharForBitData = x'11'; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 2, 4} |
| ij> update basic set cdec = cdec where ccharForBitData = x'11'; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={3, 4} |
| ij> update basic set ccharForBitData = ccharForBitData where ccharForBitData = x'11'; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1, 2, 3} |
| ij> -- confirm the table is ok |
| select ccharForBitData, ctime, cdec, cint, cchar from basic; |
| CCH&|CTIME |CDEC |CINT |CCHAR |
| --------------------------------------------- |
| 11 |11:11:11|1.10 |11 |11 |
| 22 |22:22:22|2.20 |22 |22 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'BASIC')); |
| 1 |
| ----------- |
| 1 |
| ij> autocommit off; |
| ij> update basic set cdec = cint; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 3} |
| ij> -- confirm the table is ok |
| select ccharForBitData, ctime, cdec, cint, cchar from basic; |
| CCH&|CTIME |CDEC |CINT |CCHAR |
| --------------------------------------------- |
| 11 |11:11:11|11.00 |11 |11 |
| 22 |22:22:22|22.00 |22 |22 |
| 33 |03:33:33|33.00 |33 |33 |
| 33 |03:33:33|33.00 |33 |33 |
| 33 |03:33:33|33.00 |33 |33 |
| 33 |03:33:33|33.00 |33 |33 |
| 33 |03:33:33|33.00 |33 |33 |
| 33 |03:33:33|33.00 |33 |33 |
| 33 |03:33:33|33.00 |33 |33 |
| 33 |03:33:33|33.00 |33 |33 |
| 33 |03:33:33|33.00 |33 |33 |
| 33 |03:33:33|33.00 |33 |33 |
| ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'BASIC')); |
| 1 |
| ----------- |
| 1 |
| ij> rollback; |
| ij> update basic set cchar = cchar where cdec = 3.3 and ctime = TIME('03:33:33'); |
| 10 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 3, 4} |
| ij> update basic set ctime = ctime, cchar = cchar, cint = cint, cdec = cdec, ccharForBitData = ccharForBitData; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 3, 4} |
| ij> -- confirm the table is ok |
| select ccharForBitData, ctime, cdec, cint, cchar from basic; |
| CCH&|CTIME |CDEC |CINT |CCHAR |
| --------------------------------------------- |
| 11 |11:11:11|1.10 |11 |11 |
| 22 |22:22:22|2.20 |22 |22 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| 33 |03:33:33|3.30 |33 |33 |
| ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'BASIC')); |
| 1 |
| ----------- |
| 1 |
| ij> -------------------------------------------------------------------------- |
| -- deletes |
| -------------------------------------------------------------------------- |
| -- |
| -- index scans |
| -- |
| delete from basic where cchar = '22'; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> rollback; |
| ij> delete from basic where cint = 22; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> rollback; |
| ij> delete from basic where ctime = TIME('22:22:22'); |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B2 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> rollback; |
| ij> delete from basic where ccharForBitData = x'22'; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 4} |
| ij> rollback; |
| ij> delete from basic where cdec = 2.2; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 3, 4} |
| ij> rollback; |
| ij> -- |
| -- index row to base row |
| -- |
| delete from basic where cchar = '22' and unindexed = 22; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> rollback; |
| ij> delete from basic where cint = 22 and unindexed = 22; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> rollback; |
| ij> delete from basic where ctime = TIME('22:22:22') and unindexed = 22; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B2 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> rollback; |
| ij> delete from basic where ccharForBitData = x'22' and unindexed = 22; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 4, 5} |
| ij> rollback; |
| ij> delete from basic where cdec = 2.2 and unindexed = 22; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched=All |
| |
| ij> rollback; |
| ij> -- |
| -- table scans |
| -- |
| delete from basic where cchar > '00'; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 4} |
| ij> rollback; |
| ij> delete from basic where cint > 1; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 4} |
| ij> rollback; |
| ij> delete from basic where ctime > TIME('00:00:01'); |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 4} |
| ij> rollback; |
| ij> delete from basic where ccharForBitData > x'11'; |
| 11 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 4} |
| ij> rollback; |
| ij> delete from basic where cdec > 2; |
| 11 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 3, 4} |
| ij> rollback; |
| ij> delete from basic where unindexed = 22; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 4, 5} |
| ij> rollback; |
| ij> -- |
| -- some checks on deferred deletes |
| -- |
| delete from basic where unindexed = (select min(cint) from basic); |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using instantaneous share row locking chosen by the optimizer |
| Bit set of columns fetched={2} |
| Bit set of columns fetched& |
| ij> rollback; |
| ij> delete from basic where cint = (select min(cint) from basic); |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using instantaneous share row locking chosen by the optimizer |
| Bit set of columns fetched={2} |
| Bit set of columns fetched& |
| ij> rollback; |
| ij> delete from basic where cdec = (select min(cdec) from basic); |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={3} |
| Bit set of columns fetched={0, 1, 2, 3, 4} |
| ij> rollback; |
| ij> delete from basic where cdec = 1.1 and cchar = (select min(cchar) from basic); |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using share row locking chosen by the optimizer |
| Bit set of columns fetched={0} |
| Bit set of columns fetched=All |
| |
| ij> rollback; |
| ij> -- |
| -- quickly confirm that we get all columns for updateable cursors |
| -- |
| get cursor c as 'select cint from basic for update'; |
| ij> next c; |
| CINT |
| ----------- |
| 11 |
| ij> ----------------------------------------------------------------- |
| -- now lets try some constraints |
| ----------------------------------------------------------------- |
| -- |
| -- check constraints |
| -- |
| alter table basic add constraint ck check (unindexed > cdec); |
| ERROR X0X95: Operation 'ALTER TABLE' cannot be performed on object 'BASIC' because there is an open ResultSet dependent on that object. |
| ij> commit; |
| ij> update basic set unindexed = unindexed where cint = 11; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> rollback; |
| ij> update basic set unindexed = unindexed; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={5} |
| ij> rollback; |
| ij> update basic set cdec = cdec where cint = 11; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> rollback; |
| ij> delete from basic where cint = 11; |
| 1 row inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched=All |
| |
| ij> rollback; |
| ij> -- one that isn't affected by contstraint |
| update basic set ctime = ctime; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B3 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1, 2} |
| ij> rollback; |
| ij> -- confirm it is working ok |
| update basic set unindexed = 0; |
| 12 rows inserted/updated/deleted |
| ij> rollback; |
| ij> -- |
| -- foreign keys |
| -- |
| alter table basic add constraint fk1 foreign key (cchar, ccharForBitData) references p; |
| 0 rows inserted/updated/deleted |
| ij> commit; |
| ij> update basic set cchar = cchar, ccharForBitData = ccharForBitData; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1, 2, 3} |
| ij> update basic set cchar = cchar; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1, 2, 3} |
| ij> update basic set ccharForBitData = ccharForBitData; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1, 2, 3} |
| ij> rollback; |
| ij> --pk update |
| -- only this update should fail, does not satisfy fk1 |
| update p set ccharForBitData = x'22', cchar = CAST(unindexed as CHAR(10)); |
| ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK1' for key (11 ,11). The statement has been rolled back. |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 2, 3} |
| ij> rollback; |
| ij> update p set cdec = cdec + 1.1; |
| 4 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for P using constraint PK2 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1} |
| ij> rollback; |
| ij> update p set unindexed = 666, cchar = 'fail'; |
| ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK1' for key (11 ,11). The statement has been rolled back. |
| ij> rollback; |
| ij> -- only this update should fail, does not satisfy fk1 |
| update p set ccharForBitData = x'66' where ccharForBitData = x'22'; |
| ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK1' for key (22 ,22). The statement has been rolled back. |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for P using constraint PK1 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1, 2} |
| ij> rollback; |
| ij> alter table basic add constraint fk2 foreign key (cdec) references p(cdec); |
| 0 rows inserted/updated/deleted |
| ij> commit; |
| ij> update p set cdec = cdec + 1.1; |
| 4 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Index Scan ResultSet for P using constraint PK2 at read committed isolation level using exclusive row locking chosen by the optimizer |
| Bit set of columns fetched={0, 1} |
| ij> rollback; |
| ij> update basic set cdec = cdec, cint = cint, ccharForBitData = ccharForBitData, cchar = cchar; |
| 12 rows inserted/updated/deleted |
| ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()); |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| TableScan |
| Bit set of columns fetched={0, 1, 2, 3, 4} |
| ij> rollback; |
| ij> update basic set cdec = cdec+1.1, cint = cint, ccharForBitData = ccharForBitData, cchar = cchar; |
| ERROR 23503: UPDATE on table 'BASIC' caused a violation of foreign key constraint 'FK2' for key (4.40). The statement has been rolled back. |
| ij> rollback; |
| ij> delete from p where cdec = 1.1; |
| ERROR 23503: DELETE on table 'P' caused a violation of foreign key constraint 'FK1' for key (11 ,11). The statement has been rolled back. |
| ij> rollback; |
| ij> -- clean up |
| drop function getScanCols; |
| 0 rows inserted/updated/deleted |
| ij> drop table basic; |
| 0 rows inserted/updated/deleted |
| ij> drop table p; |
| 0 rows inserted/updated/deleted |
| ij> |
