1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
|
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 CASES SPECIFIC TO STORE IMPLEMENTATION OF READ UNCOMMITTED:
-- overview:
-- TEST 0: Test a scan positioned on a row which is deleted from it.
-- TEST 1: Test a scan positioned on a row which is purged from it.
------------------------------------------------------------------------------
--
------------------------------------------------------------------------------
run resource 'createTestProcedures.subsql';
ij> CREATE FUNCTION PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME 'com.ibm.db2j.testing.Formatters.padString' LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'com.ibm.db2j.testing.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
0 rows inserted/updated/deleted
ij> connect 'wombat' as scanner;
ij(SCANNER)> autocommit off;
ij(SCANNER)> connect 'wombat' as deleter;
ij(DELETER)> autocommit off;
ij(DELETER)> ------------------------------------------------------------------------------
-- TEST 0: Test a scan positioned on a row which is deleted from it.
------------------------------------------------------------------------------
set connection scanner;
ij(SCANNER)> set current isolation to UR;
0 rows inserted/updated/deleted
ij(SCANNER)> drop table data;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'DATA' because it does not exist.
ij(SCANNER)> -- create a table with 2 rows per index page.
create table data (keycol int, data varchar(1600)) ;
0 rows inserted/updated/deleted
ij(SCANNER)> insert into data values (0, PADSTRING('0',1600));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (10, PADSTRING('100',1600));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (20, PADSTRING('200',1600));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (30, PADSTRING('300',1600));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (40, PADSTRING('400',1600));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (50, PADSTRING('100',1600));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (60, PADSTRING('200',1600));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (70, PADSTRING('300',1600));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (80, PADSTRING('400',1600));
1 row inserted/updated/deleted
ij(SCANNER)> create index idx on data (keycol, data) ;
0 rows inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> set connection deleter;
ij(DELETER)> SET ISOLATION READ COMMITTED;
0 rows inserted/updated/deleted
ij(DELETER)> commit;
ij(DELETER)> -- position scanner with no bulk fetch on 40,400
set connection scanner;
ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
0 rows inserted/updated/deleted
ij(SCANNER)> get cursor scan_cursor as
'select keycol from data';
ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
0 rows inserted/updated/deleted
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
0
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
10
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
20
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
30
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
40
ij(SCANNER)> -- now delete all the rows except for 70 and 80
set connection deleter;
ij(DELETER)> delete from data where keycol < 70;
7 rows inserted/updated/deleted
ij(DELETER)> -- scanner should automatically jump to 70
set connection scanner;
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
70
ij(SCANNER)> -- cleanup
close scan_cursor;
ij(SCANNER)> commit;
ij(SCANNER)> set connection deleter;
ij(DELETER)> commit;
ij(DELETER)> ------------------------------------------------------------------------------
-- TEST 1: Test a scan positioned on a row which is purged.
------------------------------------------------------------------------------
set connection scanner;
ij(SCANNER)> set isolation read uncommitted;
0 rows inserted/updated/deleted
ij(SCANNER)> drop table data;
0 rows inserted/updated/deleted
ij(SCANNER)> -- create a table with 3 rows per index page.
create table data (keycol int, data varchar(1200));
0 rows inserted/updated/deleted
ij(SCANNER)> insert into data values (0, PADSTRING('0',1200));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (10, PADSTRING('100',1200));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (20, PADSTRING('200',1200));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (30, PADSTRING('300',1200));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (40, PADSTRING('400',1200));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (50, PADSTRING('100',1200));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (60, PADSTRING('200',1200));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (70, PADSTRING('300',1200));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (80, PADSTRING('400',1200));
1 row inserted/updated/deleted
ij(SCANNER)> create index idx on data (keycol, data) ;
0 rows inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> -- position scanner with no bulk fetch on 0,0 (first row in btree)
set connection scanner;
ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
0 rows inserted/updated/deleted
ij(SCANNER)> get cursor scan_cursor as
'select keycol from data';
ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
0 rows inserted/updated/deleted
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
0
ij(SCANNER)> -- delete all the rows in the table except for the last few pages, and the 1st;
set connection deleter;
ij(DELETER)> delete from data where keycol < 50 and keycol > 0;
4 rows inserted/updated/deleted
ij(DELETER)> -- insert enough rows after the first to force a split by the scanner on the 1st
-- page, it will now be positioned by key on the (0, 0) key. Then delete the
-- rows that we just inserted.
set connection scanner;
ij(SCANNER)> insert into data values (9, '3'), (9, '2'), (9, '1');
3 rows inserted/updated/deleted
ij(SCANNER)> delete from data where keycol = 9;
3 rows inserted/updated/deleted
ij(SCANNER)> -- delete the key that the scan is positioned on.
set connection deleter;
ij(DELETER)> delete from data where keycol = 0;
1 row inserted/updated/deleted
ij(DELETER)> commit;
ij(DELETER)> set connection scanner;
ij(SCANNER)> -- this should now cause another split on the first page in the btree, this
-- time it should reclaim row 0.
insert into data values (8, '3'), (8, '2'), (8, '1');
3 rows inserted/updated/deleted
ij(SCANNER)> -- scanner should automatically jump to 8, handling the fact that row (0,0)
-- no longer exists in the table.
set connection scanner;
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
50
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
60
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
70
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
80
ij(SCANNER)> -- delete all the rows that remain except the last;
set connection deleter;
ij(DELETER)> delete from data where keycol > 10 and keycol < 80;
3 rows inserted/updated/deleted
ij(DELETER)> commit;
ij(DELETER)> -- position scan on last row of scan.
set connection scanner;
ij(SCANNER)> next scan_cursor;
KEYCOL
-----------
8
ij(SCANNER)> -- now repeat process from above to make the current scan position disappear to
-- test code path executed when closing a scan where the last scan position has
-- disappeared.
set connection scanner;
ij(SCANNER)> insert into data values (82, '3'), (82, '2'), (82, '1');
3 rows inserted/updated/deleted
ij(SCANNER)> delete from data where keycol = 81;
0 rows inserted/updated/deleted
ij(SCANNER)> set connection deleter;
ij(DELETER)> delete from data where keycol = 80;
1 row inserted/updated/deleted
ij(DELETER)> commit;
ij(DELETER)> set connection scanner;
ij(SCANNER)> -- this statement will purge (80, 800) from the table.
insert into data values (81, '3'), (81, '2'), (81, '1');
3 rows inserted/updated/deleted
ij(SCANNER)> delete from data where keycol = 81;
3 rows inserted/updated/deleted
ij(SCANNER)> -- this statement will execute code which will look for last key positioned on
-- while closing the statement.
close scan_cursor;
ij(SCANNER)> -- cleanup
set connection scanner;
ij(SCANNER)> commit;
ij(SCANNER)> set connection deleter;
ij(DELETER)> commit;
ij(DELETER)>
|
