| -- Test: TEST0106 (Executor) |
| -- @@@ START COPYRIGHT @@@ |
| -- |
| -- 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. |
| -- |
| -- @@@ END COPYRIGHT @@@ |
| -- |
| -- Functionality: SQL Query Cancel |
| -- Expected files: EXPECTED106 |
| -- Table created: |
| -- Limitations: |
| -- To do: |
| -- Revision history: |
| |
| -- temporarily turning off ddl xns for this test suite. |
| -- it runs into a hang issue in dtm due to cancel processing. |
| cqd ddl_transactions 'OFF'; |
| |
| sh echo > cumulative_GET_STATS106; |
| sh echo > LOG106; |
| sh echo > T106_INTERLEAVED_LOG; |
| sh rm ${REGRRUNDIR}/test106_synch ; |
| |
| sh sqlci -i"TEST106(setup)"; |
| |
| log LOG106; |
| obey TEST106(dml); |
| |
| select msg, 'FUNKY_OPT_UNIQUE', ts |
| from T106_interleaved_log order by ts; |
| |
| obey TEST106(clnup); |
| |
| log; |
| |
| exit; |
| |
| ?section clnup |
| |
| drop table t106a; |
| drop table t106c; |
| log T106_INTERLEAVED_LOG; |
| select * from T106_interleaved_log |
| order by ts; |
| drop table T106_interleaved_log; |
| |
| ?section setup |
| |
| set schema $$TEST_SCHEMA$$; |
| |
| obey TEST106(clnup); |
| |
| log LOG106 clear; |
| |
| create table T106_interleaved_log ( |
| ts timestamp(6) not null, |
| msg char(60) ) no partition; |
| |
| |
| create table t106a |
| (uniq int not null, |
| c10K int , |
| c1K int, |
| c100 int, |
| c10 int, |
| c1 int, |
| primary key (uniq) |
| ) |
| salt using 4 partitions ; |
| |
| upsert using load into t106a |
| select |
| 0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100) |
| + (10 * x10) +( 1 * x1), |
| 0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1), |
| 0 + (100 * x100) + (10 * x10) + (1 * x1), |
| 0 + (10 * x10) + (1 * x1), |
| 0 + (1 * x1), |
| 0 |
| from (values(1)) as t1 |
| transpose 0,1 as x100000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x10000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x100 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x10 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1 |
| ; |
| |
| update statistics for table t106a on every column; |
| |
| create table t106c |
| (uniq int not null, |
| c10K int , |
| c1K int, |
| c100 int, |
| c10 int, |
| c1 int, |
| primary key (uniq) |
| ) |
| salt using 4 partitions ; |
| |
| ?section dml |
| |
| sh rm cancel_cmd cancel_log106_all LOCK_LOG106; |
| |
| obey TEST106(cursor_close_early); |
| |
| obey TEST106(uniq_query_test); |
| |
| obey TEST106(positive_test1); |
| |
| obey TEST106(positive_test2); |
| |
| obey TEST106(update_test1); |
| |
| obey TEST106(delete_test1); |
| |
| obey TEST106(insert_test1); |
| |
| obey TEST106(insert_test2); |
| |
| obey TEST106(upsert_test1); |
| |
| obey TEST106(cpu_bound_esps); |
| |
| obey TEST106(escalation1); |
| |
| #ifdef SEABASE_CANCEL_TBD |
| |
| obey TEST106(positive_test1_pname); |
| |
| obey TEST106(positive_test2_pname); |
| |
| obey TEST106(cpu_bound_esps_pname); |
| |
| obey TEST106(escalation1_pname); |
| |
| obey TEST106(positive_test1_nid_pid); |
| |
| obey TEST106(positive_test2_nid_pid); |
| |
| obey TEST106(cpu_bound_esps_nid_pid); |
| |
| obey TEST106(escalation1_nid_pid); |
| #endif |
| |
| obey TEST106(showplan1); |
| |
| obey TEST106(explain1); |
| |
| obey TEST106(invalid_qid); |
| |
| obey TEST106(invalid_pname); |
| |
| obey TEST106(invalid_nid_pid); |
| |
| obey TEST106(qc1314); |
| |
| obey TEST106(qc1351); |
| |
| -- this one removes histograms for t106a so do it at the end. |
| obey TEST106(ustats_test); |
| |
| |
| ?section cursor_close_early |
| -- From bug 2583 |
| |
| set envvar sqlci_cursor '1'; |
| |
| declare c1 cursor for select * from t106a A, t106a B; |
| values('FUNKY_OPT_UNIQUE', current_timestamp); |
| |
| open c1; |
| close c1; |
| values('FUNKY_OPT_UNIQUE', current_timestamp); |
| |
| open c1; |
| close c1; |
| values('FUNKY_OPT_UNIQUE', current_timestamp); |
| |
| open c1; |
| close c1; |
| values('FUNKY_OPT_UNIQUE', current_timestamp); |
| |
| ?section uniq_query_test |
| -- By default, cannot cancel unique queries. And since we |
| -- do not have locks in trafodion, it doesn't make sense to |
| -- try to cancel a unique query. |
| -- TBD -- add tests to show the unique query does not register |
| -- with cancel broker |
| |
| ---------- unique select query --------------------- |
| |
| prepare s1 from select 'unique query row' from t106a where uniq = 44; |
| |
| ---------- unique delete query --------------------- |
| |
| prepare s1 from delete from t106a where uniq = 44; |
| |
| ---------- unique update query --------------------- |
| |
| prepare s1 from update t106a set c10k = 12 where uniq = 46; |
| |
| ---------- unique insert query --------------------- |
| |
| prepare s1 from insert into t106a values (44, 2, 3, 4, 5, 6); |
| |
| ?section positive_test1 |
| |
| -- Test canceling a query with and without the |
| -- CANCEL_QUERY_ALLOWED. |
| |
| ------- long running, cpu-bound select query ------- |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| control query default ATTEMPT_ESP_PARALLELISM 'off'; |
| |
| prepare s1 from select count(*) from t106a A, t106a B |
| where A.uniq < 3000; |
| |
| control query default ATTEMPT_ESP_PARALLELISM 'system'; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| |
| obey TEST106(make_cancel_stmt); |
| |
| select 'subject launch canceler - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel)" & ; |
| |
| select 'subject synch cancel - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1, launch canceler, finish'); |
| |
| obey TEST106(synch_cancel); |
| |
| select 'subject execute s1 - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1, execute, start'); |
| |
| execute s1; |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1, execute, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section positive_test2 |
| |
| -- Test canceling a query with and without the |
| -- CANCEL_QUERY_ALLOWED. |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| |
| control query shape expr(sort_groupby(esp_exchange(cut))); |
| |
| prepare s1 from select count(*) from t106a A, t106a B |
| where A.uniq < 3000; |
| |
| control query shape cut; |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| |
| obey TEST106(make_cancel_stmt); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel)" & ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2, launch canceler, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2, execute s1, start'); |
| |
| ------- long running, cpu-bound select query ------- |
| execute s1; |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2, execute s1, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section update_test1 |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| |
| prepare s1 from update t106a set c100 = c100 + 1; |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| |
| obey TEST106(make_cancel_stmt); |
| |
| select 'subject launch canceler - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'update_test1, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel)" & ; |
| |
| select 'subject synch cancel - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'update_test1, launch canceler, finish'); |
| |
| obey TEST106(synch_cancel); |
| |
| select 'subject execute s1 - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'update_test1, execute, start'); |
| |
| execute s1; |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'update_test1, execute, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section delete_test1 |
| |
| prepare s1 from delete from t106a; |
| |
| obey TEST106(make_cancel_stmt); |
| |
| select 'subject launch canceler - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'delete_test1, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel)" & ; |
| |
| select 'subject synch cancel - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'delete_test1, launch canceler, finish'); |
| |
| obey TEST106(synch_cancel); |
| |
| select 'subject execute s1 - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'delete_test1, execute, start'); |
| |
| execute s1; |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'delete_test1, execute, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section insert_test1 |
| |
| prepare s1 from insert into t106c select * from t106a; |
| obey TEST106(make_cancel_stmt); |
| |
| select 'subject launch canceler - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'insert_test1, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel)" & ; |
| |
| select 'subject synch cancel - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'insert_test1, launch canceler, finish'); |
| |
| obey TEST106(synch_cancel); |
| |
| select 'subject execute s1 - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'insert_test1, execute, start'); |
| |
| execute s1; |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'insert_test1, execute, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section insert_test2 |
| |
| prepare s1 from insert into t106c |
| select |
| 0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100) |
| + (10 * x10) +( 1 * x1), |
| 0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1), |
| 0 + (100 * x100) + (10 * x10) + (1 * x1), |
| 0 + (10 * x10) + (1 * x1), |
| 0 + (1 * x1), |
| 0 |
| from (values(1)) as t1 |
| transpose 0,1 as x100000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x10000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x100 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x10 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1 |
| ; |
| |
| |
| obey TEST106(make_cancel_stmt); |
| |
| select 'subject launch canceler - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'insert_test2, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel)" & ; |
| |
| select 'subject synch cancel - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'insert_test2, launch canceler, finish'); |
| |
| obey TEST106(synch_cancel); |
| |
| select 'subject execute s1 - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'insert_test2, execute, start'); |
| |
| execute s1; |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'insert_test2, execute, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section upsert_test1 |
| cqd DYN_QUEUE_RESIZE_OVERRIDE 'ON'; |
| cqd GEN_TRSP_SIZE_DOWN '16'; |
| cqd GEN_TRSP_SIZE_UP '16'; |
| |
| prepare s1 from |
| upsert using load into t106c |
| select |
| 0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100) |
| + (10 * x10) +( 1 * x1), |
| 0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1), |
| 0 + (100 * x100) + (10 * x10) + (1 * x1), |
| 0 + (10 * x10) + (1 * x1), |
| 0 + (1 * x1), |
| 0 |
| from (values(1)) as t1 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x100000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x10000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x100 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x10 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1 |
| ; |
| |
| |
| obey TEST106(make_cancel_stmt); |
| |
| select 'subject launch canceler - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'upsert_test1, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel)" & ; |
| |
| select 'subject synch cancel - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'upsert_test1, launch canceler, finish'); |
| |
| obey TEST106(synch_cancel); |
| |
| select 'subject execute s1 - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'upsert_test1, execute, start'); |
| |
| execute s1; |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'upsert_test1, execute, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| cqd DYN_QUEUE_RESIZE_OVERRIDE reset; |
| cqd GEN_TRSP_SIZE_DOWN reset; |
| cqd GEN_TRSP_SIZE_UP reset; |
| ?section cpu_bound_esps |
| |
| -- Test cancel when esps are CPU-bound. |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C |
| group by c.c10k order by 2; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| |
| obey TEST106(make_cancel_stmt); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'cpu_bound_esps, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel)" & ; |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'cpu_bound_esps, launch canceler, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'cpu_bound_esps, execute s1, start'); |
| |
| execute s1; |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'cpu_bound_esps, execute s1, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section escalation1 |
| |
| -- Test cancel escalation. Simulate unresponsive ESPs with COMP_INT_39. |
| |
| control query default COMP_INT_39 '4'; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C |
| group by c.c10k order by 2; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| control query default COMP_INT_39 'reset'; |
| |
| obey TEST106(make_cancel_stmt); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel)" & ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1, launch canceler, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1, execute s1, start'); |
| |
| execute s1; |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1, execute s1, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section ustats_test |
| |
| upsert using load into t106a |
| select |
| 200000 + |
| 0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100) |
| + (10 * x10) +( 1 * x1), |
| 0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1), |
| 0 + (100 * x100) + (10 * x10) + (1 * x1), |
| 0 + (10 * x10) + (1 * x1), |
| 0 + (1 * x1), |
| 0 |
| from (values(1)) as t1 |
| transpose 0,1 as x100000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x10000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x100 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x10 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1 |
| ; |
| upsert using load into t106a |
| select |
| 400000 + |
| 0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100) |
| + (10 * x10) +( 1 * x1), |
| 0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1), |
| 0 + (100 * x100) + (10 * x10) + (1 * x1), |
| 0 + (10 * x10) + (1 * x1), |
| 0 + (1 * x1), |
| 0 |
| from (values(1)) as t1 |
| transpose 0,1 as x100000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x10000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1000 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x100 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x10 |
| transpose 0,1,2,3,4,5,6,7,8,9 as x1 |
| ; |
| |
| |
| update statistics for table t106a clear; |
| |
| prepare s1 from |
| update statistics for table t106a on |
| c10k, c1k, c100, c10, c1, uniq |
| , (uniq, c10k) |
| , (uniq, c1k) |
| , (uniq, c100) |
| , (uniq, c10) |
| , (uniq, c1) |
| , (c10k, c1k) |
| , (c10k, c100) |
| , (c10k, c10) |
| , (c10k, c1) |
| , (c1k, c100) |
| , (c1k, c10) |
| , (c1k, c1) |
| , (c100, c10) |
| , (c100, c1) |
| , (c10, c1) |
| , (uniq, c10k, c1k) |
| , (uniq, c10k, c100) |
| , (uniq, c10k, c10) |
| , (uniq, c10k, c1) |
| , (uniq, c1k, c100) |
| , (uniq, c1k, c10) |
| , (uniq, c1k, c1) |
| , (uniq, c100, c10) |
| , (uniq, c100, c1) |
| , (uniq, c10, c1) |
| , (c10k, c1k, c100) |
| , (c10k, c1k, c10) |
| , (c10k, c1k, c1) |
| , (c10k, c100, c10) |
| , (c10k, c100, c1) |
| , (c10k, c10, c1) |
| , (c1k, c100, c10) |
| , (c1k, c100, c1) |
| , (c1k, c10, c1) |
| , (uniq, c10k, c1k, c100) |
| , (uniq, c10k, c1k, c10) |
| , (uniq, c10k, c1k, c1) |
| , (uniq, c10k, c100, c10) |
| , (uniq, c10k, c100, c1) |
| , (uniq, c10k, c10, c1) |
| , (uniq, c1k, c100, c10) |
| , (uniq, c1k, c100, c1) |
| , (uniq, c1k, c10, c1) |
| , (c10k, c1k, c100, c10) |
| , (c10k, c1k, c100, c1) |
| , (c10k, c1k, c10, c1) |
| ; |
| |
| obey TEST106(make_cancel_stmt); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'ustats_test, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel)" & ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'ustats_test, launch canceler, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'ustats_test, execute s1, start'); |
| |
| execute s1; |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'ustats_test, execute s1, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section positive_test1_nid_pid |
| |
| -- Test canceling a query with and without the |
| -- CANCEL_QUERY_ALLOWED. |
| |
| ------- long running, cpu-bound select query ------- |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| control query default ATTEMPT_ESP_PARALLELISM 'off'; |
| |
| prepare s1 from select count(*) from t106a A, t106a B |
| where A.uniq < 3000; |
| |
| control query default ATTEMPT_ESP_PARALLELISM 'system'; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| |
| obey TEST106(make_cancel_nid_pid_stmt); |
| |
| select 'subject launch canceler - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1_nid_pid, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel_nid_pid)" & ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1_nid_pid, launch canceler, finished'); |
| |
| select 'subject synch cancel - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| obey TEST106(synch_cancel); |
| |
| select 'subject execute s1 - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1_nid_pid, execute s1, start'); |
| |
| execute s1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1_nid_pid, execute s1, finished'); |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section positive_test2_nid_pid |
| |
| -- Test canceling a query with and without the |
| -- CANCEL_QUERY_ALLOWED. |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| |
| control query shape expr(sort_groupby(esp_exchange(cut))); |
| |
| prepare s1 from select count(*) from t106a A, t106a B |
| where A.uniq < 3000; |
| |
| control query shape cut; |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| |
| obey TEST106(make_cancel_nid_pid_stmt); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2_nid_pid, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel_nid_pid)" & ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2_nid_pid, launch canceler, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2_nid_pid, execute s1, start'); |
| |
| ------- long running, cpu-bound select query ------- |
| execute s1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2_nid_pid, execute s1, finished'); |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section cpu_bound_esps_nid_pid |
| |
| -- Test cancel when esps are CPU-bound. |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C |
| group by c.c10k order by 2; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| |
| obey TEST106(make_cancel_nid_pid_stmt); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'cpu_bound_esps_nid_pid, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel_nid_pid)" & ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'cpu_bound_esps_nid_pid, launch canceler, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'cpu_bound_esps_nid_pid, execute s1, start'); |
| |
| execute s1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'cpu_bound_esps_nid_pid, execute s1, finished'); |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section escalation1_nid_pid |
| |
| -- Test cancel escalation. Simulate unresponsive ESPs with COMP_INT_39. |
| |
| control query default COMP_INT_39 '4'; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C |
| group by c.c10k order by 2; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| control query default COMP_INT_39 'reset'; |
| |
| obey TEST106(make_cancel_nid_pid_stmt); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1_nid_pid, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel_nid_pid)" & ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1_nid_pid, launch canceler, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1_nid_pid, execute s1, start'); |
| |
| execute s1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1_nid_pid, execute s1, finished'); |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section positive_test1_pname |
| |
| -- Test canceling a query with and without the |
| -- CANCEL_QUERY_ALLOWED. |
| |
| ------- long running, cpu-bound select query ------- |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| control query default ATTEMPT_ESP_PARALLELISM 'off'; |
| |
| prepare s1 from select count(*) from t106a A, t106a B |
| where A.uniq < 3000; |
| |
| control query default ATTEMPT_ESP_PARALLELISM 'system'; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| |
| obey TEST106(make_cancel_pname_stmt); |
| |
| select 'subject launch canceler - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1_pname, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel_pname)" & ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1_pname, launch canceler, finished'); |
| |
| select 'subject synch cancel - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| obey TEST106(synch_cancel); |
| |
| select 'subject execute s1 - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1_pname, execute s1, start'); |
| |
| execute s1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test1_pname, execute s1, finished'); |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section positive_test2_pname |
| |
| -- Test canceling a query with and without the |
| -- CANCEL_QUERY_ALLOWED. |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| |
| control query shape expr(sort_groupby(esp_exchange(cut))); |
| |
| prepare s1 from select count(*) from t106a A, t106a B |
| where A.uniq < 3000; |
| |
| control query shape cut; |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| |
| obey TEST106(make_cancel_pname_stmt); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2_pname, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel_pname)" & ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2_pname, launch canceler, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2_pname, execute s1, start'); |
| |
| ------- long running, cpu-bound select query ------- |
| execute s1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'positive_test2_pname, execute s1, finished'); |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section cpu_bound_esps_pname |
| |
| -- Test cancel when esps are CPU-bound. |
| |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C |
| group by c.c10k order by 2; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| |
| obey TEST106(make_cancel_pname_stmt); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIME (6), |
| 'cpu_bound_esps_pname, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel_pname)" & ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'cpu_bound_esps_pname, launch canceler, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'cpu_bound_esps_pname, execute s1, start'); |
| |
| execute s1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'cpu_bound_esps_pname, execute s1, finished'); |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section escalation1_pname |
| |
| -- Test cancel escalation. Simulate unresponsive ESPs with COMP_INT_39. |
| |
| control query default COMP_INT_39 '4'; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU '20'; |
| prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C |
| group by c.c10k order by 2; |
| control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset'; |
| control query default COMP_INT_39 'reset'; |
| |
| obey TEST106(make_cancel_pname_stmt); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1_pname, launch canceler, start'); |
| |
| sh sqlci -i"TEST106(do_cancel_pname)" & ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1_pname, launch canceler, finished'); |
| |
| obey TEST106(synch_cancel); |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1_pname, execute s1, start'); |
| |
| execute s1; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'escalation1_pname, execute s1, finished'); |
| |
| select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| obey TEST106(synch_cancel); |
| |
| obey TEST106(append_cancel_log); |
| |
| ?section showplan1 |
| |
| log; log SP106 clear; |
| |
| showplan control query cancel qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1; |
| |
| showplan control query activate qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1; |
| |
| showplan control query suspend qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1; |
| |
| showplan control query suspend qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1, force; |
| |
| #ifdef SEABASE_CANCEL_TBD |
| showplan control query cancel pid 0, 17677; |
| showplan control query cancel pid $Z00034G; |
| #endif |
| |
| log; |
| |
| sh echo "cancel-specific showplan output" >> LOG106; |
| sh grep "Contents of EX_CANCEL" SP106 >> LOG106; |
| sh grep "For ComTdbCancel" SP106 >> LOG106; |
| sh grep "^qid_ =" SP106 >> LOG106; |
| sh grep "^action_ =" SP106 >> LOG106; |
| sh grep "^cancelNid_" SP106 >> LOG106; |
| sh grep "^cancelPname_" SP106 >> LOG106; |
| sh grep "^comment_ =" SP106 >> LOG106; |
| |
| log LOG106; |
| |
| ?section explain1 |
| |
| log; log EXPLAIN106 clear; |
| |
| explain control query cancel qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1; |
| |
| explain control query suspend qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1; |
| |
| explain control query suspend qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1, force; |
| |
| explain control query activate qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1; |
| |
| #ifdef SEABASE_CANCEL_TBD |
| explain control query cancel pid 0, 17677; |
| explain control query cancel pid $Z00034G; |
| #endif |
| |
| |
| log; |
| |
| sh echo "cancel-specific explain output" >> LOG106; |
| sh grep "CONTROL_RUNNING_QUERY" EXPLAIN106 >> LOG106; |
| sh grep "control_action" EXPLAIN106 >> LOG106; |
| sh grep "nid_pid" EXPLAIN106 >> LOG106; |
| sh grep "min_blocking_interval" EXPLAIN106 >> LOG106; |
| sh grep "process_name" EXPLAIN106 >> LOG106; |
| |
| |
| log LOG106; |
| |
| ?section invalid_qid |
| control query cancel qid foo; |
| |
| set envvar HP_FAKE_ERROR_201; |
| |
| control query cancel qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1; |
| |
| reset envvar HP_FAKE_ERROR_201; |
| |
| control query cancel qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1; |
| |
| control query suspend qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1; |
| |
| control query suspend qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1, force; |
| |
| control query activate qid |
| MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1; |
| |
| ?section invalid_pname |
| control query cancel pid $ZXXXXX; |
| |
| ?section invalid_nid_pid |
| control query cancel pid 44,66666; |
| |
| ?section qc1314 |
| |
| -- Test case for QC 1314. Exercise IPC timeout in |
| -- ex_root_tcb::cancel. |
| |
| set envvar SQLMX_CANCEL_TIMEOUT '10'; |
| |
| -- Simulate unresponsive ESPs with COMP_INT_39. |
| |
| control query default COMP_INT_39 '4'; |
| |
| set envvar sqlci_cursor '1'; |
| |
| control query shape esp_exchange(cut); |
| |
| declare c1 cursor for |
| select C.c10k, count(*) from t106a A, t106a B, t106a C |
| group by c.c10k order by 2 |
| for read uncommitted access; |
| |
| control query shape cut; |
| |
| control query default COMP_INT_39 'reset'; |
| values('FUNKY_OPT_UNIQUE', current_timestamp); |
| |
| open c1; |
| |
| close c1; |
| values('FUNKY_OPT_UNIQUE', current_timestamp); |
| |
| open c1; |
| |
| close c1; |
| values('FUNKY_OPT_UNIQUE', current_timestamp); |
| |
| open c1; |
| |
| close c1; |
| values('FUNKY_OPT_UNIQUE', current_timestamp); |
| |
| open c1; |
| |
| close c1; |
| values('FUNKY_OPT_UNIQUE', current_timestamp); |
| |
| open c1; |
| |
| close c1; |
| values('FUNKY_OPT_UNIQUE', current_timestamp); |
| |
| ?section qc1351 |
| log LOG106; |
| prepare s1 from select * from t106a; |
| obey TEST106(make_cancel_stmt); |
| obey cancel_cmd; |
| |
| ?section synch_cancel |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'foreground awaits synch_cancel'); |
| |
| -- wait until other mxci is ready |
| sh sh $scriptsdir/executor/synch_106.ksh; |
| |
| select 'done with synch_cancel - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| log; |
| sh rm ${REGRRUNDIR}/test106_synch ; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'foreground finished synch_cancel'); |
| |
| log LOG106; |
| |
| ?section do_cancel |
| set schema $$TEST_SCHEMA$$; |
| log CANCEL_LOG106 clear; |
| ------------------------------------------; |
| ------ Contents of cancel session --------; |
| ------------------------------------------; |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'do_cancel has begun'); |
| |
| sh touch ${REGRRUNDIR}/test106_synch; |
| |
| select 'canceler going to sleep - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| sh sleep 13; |
| |
| select 'canceler woke up - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| set session default CANCEL_ESCALATION_INTERVAL '5'; |
| log; |
| log GET_STATS106 clear; |
| set session default RTS_TIMEOUT '10'; |
| obey get_stats_cmd; |
| log; |
| sh cat GET_STATS106 >> cumulative_GET_STATS106; |
| sh grep "Execute End Time" GET_STATS106 >> CANCEL_LOG106; |
| log CANCEL_LOG106; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'do_cancel will cancel'); |
| |
| obey cancel_cmd; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'do_cancel has canceled'); |
| |
| select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| log; |
| sh cat CANCEL_LOG106 >> cancel_log106_all; |
| |
| sh touch ${REGRRUNDIR}/test106_synch; |
| |
| ?section do_cancel_pname |
| set schema $$TEST_SCHEMA$$; |
| |
| log CANCEL_LOG106 clear; |
| ------------------------------------------; |
| ------ Contents of cancel session --------; |
| ------------------------------------------; |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'do_cancel_pname has begun'); |
| |
| sh touch ${REGRRUNDIR}/test106_synch; |
| |
| select 'canceler going to sleep - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| sh sleep 13; |
| |
| select 'canceler woke up - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| set session default CANCEL_ESCALATION_INTERVAL '5'; |
| log; |
| log GET_STATS106 clear; |
| obey get_stats_cmd; |
| log; |
| sh cat GET_STATS106 >> cumulative_GET_STATS106; |
| sh grep "Execute End Time" GET_STATS106 >> CANCEL_LOG106; |
| log CANCEL_LOG106; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'do_cancel_pname will cancel'); |
| |
| obey cancel_pname_cmd; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'do_cancel_pname has canceled'); |
| |
| select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| log; |
| sh cat CANCEL_LOG106 >> cancel_log106_all; |
| |
| sh touch ${REGRRUNDIR}/test106_synch; |
| |
| ?section do_cancel_nid_pid |
| set schema $$TEST_SCHEMA$$; |
| |
| log CANCEL_LOG106 clear; |
| ------------------------------------------; |
| ------ Contents of cancel session --------; |
| ------------------------------------------; |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'do_cancel_nid_pid has begun'); |
| |
| sh touch ${REGRRUNDIR}/test106_synch; |
| |
| select 'canceler going to sleep - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| sh sleep 13; |
| |
| select 'canceler woke up - FUNKY_OPT_UNIQUE', |
| CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| |
| set session default CANCEL_ESCALATION_INTERVAL '5'; |
| log; |
| log GET_STATS106 clear; |
| obey get_stats_cmd; |
| log; |
| sh cat GET_STATS106 >> cumulative_GET_STATS106; |
| sh grep "Execute End Time" GET_STATS106 >> CANCEL_LOG106; |
| log CANCEL_LOG106; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'do_cancel_nid_pid will cancel'); |
| |
| obey cancel_nid_pid_cmd; |
| |
| insert into T106_interleaved_log values |
| (CURRENT_TIMESTAMP (6), |
| 'do_cancel_nid_pid has canceled'); |
| |
| select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1; |
| log; |
| sh cat CANCEL_LOG106 >> cancel_log106_all; |
| |
| sh touch ${REGRRUNDIR}/test106_synch; |
| |
| ?section make_suicide_script |
| -- Make a shell script to stop this mxci. |
| sh gtacl -c 'status *,term' | grep mxci | cut -c 12-19 | sed "s/^/sleep 8; gtacl -c 'stop/g" | sed "s/$/'/g">stop_cmd ; |
| sh chmod +x stop_cmd; |
| |
| |
| ?section make_cancel_stmt |
| log; |
| log QIDLOG clear; |
| display qid for s1; |
| |
| log; |
| |
| sh grep "^QID is " QIDLOG | cut -c 8- | sed "s/^/control query cancel qid /g" | sed "s/$/;/g" > cancel_cmd; |
| sh grep "^QID is " QIDLOG | cut -c 8- | sed "s/^/get statistics for qid /g" | sed "s/$/;/g" > get_stats_cmd; |
| |
| |
| log LOG106; |
| |
| ?section make_cancel_pname_stmt |
| log; |
| log QIDLOG clear; |
| display qid for s1; |
| |
| log; |
| |
| sh echo "control query default CANCEL_MINIMUM_BLOCKING_INTERVAL '1';" > cancel_pname_cmd; |
| sh grep "^ Pin:" QIDLOG | cut -c 8- | sed "s/^/sqps | grep sqlci | grep /g" | sh | cut -c 42-50 | sed "s/^/control query cancel pid /g" >> cancel_pname_cmd; |
| sh echo ";" >> cancel_pname_cmd; |
| sh grep "^QID is " QIDLOG | cut -c 8- | sed "s/^/get statistics for qid /g" | sed "s/$/;/g" > get_stats_cmd; |
| |
| log LOG106; |
| |
| ?section make_cancel_nid_pid_stmt |
| log; |
| log QIDLOG clear; |
| display qid for s1; |
| |
| log; |
| |
| sh echo "control query default CANCEL_MINIMUM_BLOCKING_INTERVAL '1';" > cancel_nid_pid_cmd; |
| sh grep "^ Pin:" QIDLOG | cut -c 8- | sed "s/^/control query cancel pid 0,/g" >> cancel_nid_pid_cmd; |
| sh echo ";" >> cancel_nid_pid_cmd; |
| sh grep "^QID is " QIDLOG | cut -c 8- | sed "s/^/get statistics for qid /g" | sed "s/$/;/g" > get_stats_cmd; |
| |
| log LOG106; |
| |
| ?section append_cancel_log |
| log; |
| sh sleep 2; |
| sh cat CANCEL_LOG106 >> LOG106; |
| log LOG106; |
| |
| ?section append_lock_log |
| log; |
| sh sleep 2; |
| sh cat LOCK_LOG106 >> LOG106; |
| log LOG106; |
