>>
>>create table T106_interleaved_log (
+>                   ts timestamp(6) not null,
+>                   msg char(60) ) no partition;

--- SQL operation complete.
>>
>>
>>create table t106a
+>  (uniq int not null,
+>   c10K int ,
+>   c1K   int,
+>   c100  int,
+>   c10   int,
+>   c1    int,
+>   primary key (uniq)
+>  )
+>salt using 4 partitions ;

--- SQL operation complete.
>>
>>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
+>  ;

--- 200000 row(s) inserted.
>>
>>update statistics for table t106a on every column;

--- SQL operation complete.
>>
>>create table t106c
+>  (uniq int not null,
+>   c10K int ,
+>   c1K   int,
+>   c100  int,
+>   c10   int,
+>   c1    int,
+>   primary key (uniq)
+>  )
+>salt using 4 partitions ;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

>>obey TEST106(dml);
>>
>>sh rm cancel_cmd cancel_log106_all LOCK_LOG106;
>>
>>obey TEST106(cursor_close_early);
>>-- From bug 2583
>>
>>set envvar sqlci_cursor '1';

--- SQL operation complete.
>>
>>declare c1 cursor for select * from t106a A, t106a B;

--- SQL operation complete.
>>values('FUNKY_OPT_UNIQUE', current_timestamp);

(EXPR)            (EXPR)                    
----------------  --------------------------

FUNKY_OPT_UNIQUE  2018-09-11 06:33:58.000451

--- 1 row(s) selected.
>>
>>open c1;

--- SQL operation complete.
>>close c1;

--- SQL operation complete.
>>values('FUNKY_OPT_UNIQUE', current_timestamp);

(EXPR)            (EXPR)                    
----------------  --------------------------

FUNKY_OPT_UNIQUE  2018-09-11 06:34:02.000806

--- 1 row(s) selected.
>>
>>open c1;

--- SQL operation complete.
>>close c1;

--- SQL operation complete.
>>values('FUNKY_OPT_UNIQUE', current_timestamp);

(EXPR)            (EXPR)                    
----------------  --------------------------

FUNKY_OPT_UNIQUE  2018-09-11 06:34:06.000900

--- 1 row(s) selected.
>>
>>open c1;

--- SQL operation complete.
>>close c1;

--- SQL operation complete.
>>values('FUNKY_OPT_UNIQUE', current_timestamp);

(EXPR)            (EXPR)                    
----------------  --------------------------

FUNKY_OPT_UNIQUE  2018-09-11 06:34:07.000032

--- 1 row(s) selected.
>>
>>
>>obey TEST106(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;

--- SQL command prepared.
>>
>>---------- unique delete query ---------------------
>>
>>prepare s1 from delete from t106a where uniq = 44;

--- SQL command prepared.
>>
>>---------- unique update query ---------------------
>>
>>prepare s1 from update t106a set c10k = 12 where uniq = 46;

--- SQL command prepared.
>>
>>---------- unique insert query ---------------------
>>
>>prepare s1 from insert into t106a values (44, 2, 3, 4, 5, 6);

--- SQL command prepared.
>>
>>
>>obey TEST106(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';

--- SQL operation complete.
>>control query default ATTEMPT_ESP_PARALLELISM 'off';

--- SQL operation complete.
>>
>>prepare s1 from select count(*) from t106a A, t106a B 
+>where A.uniq < 3000;

--- SQL command prepared.
>>
>>control query default ATTEMPT_ESP_PARALLELISM 'system';

--- SQL operation complete.
>>control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

--- SQL operation complete.
>>
>>obey TEST106(make_cancel_stmt);
>>log;
>>
>>
>>select 'subject launch canceler - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject launch canceler - FUNKY_OPT_UNIQUE  2018-09-11 06:34:07.000305

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'positive_test1, launch canceler, start');

--- 1 row(s) inserted.
>>
>>sh sqlci -i"TEST106(do_cancel)" & ;
>>
>>select 'subject synch cancel - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject synch cancel - FUNKY_OPT_UNIQUE     2018-09-11 06:34:08.000400

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'positive_test1, launch canceler, finish');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:34:15.000481

--- 1 row(s) selected.
>>log;
>>
>>
>>select 'subject execute s1 - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject execute s1 - FUNKY_OPT_UNIQUE       2018-09-11 06:34:15.000508

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'positive_test1, execute, start');

--- 1 row(s) inserted.
>>
>>execute s1;

*** ERROR[8007] The operation has been canceled.

*** ERROR[8811] Trying to close a statement that is either not in the open state or has not reached EOF.

--- 0 row(s) selected.
>>
>>select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject - FUNKY_OPT_UNIQUE                  2018-09-11 06:34:28.000500

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'positive_test1, execute, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:34:28.000539

--- 1 row(s) selected.
>>log;
>>
>>
>>obey TEST106(append_cancel_log);
>>log;
>>------------------------------------------;
>>------ Contents of cancel session --------;
>>------------------------------------------;
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has begun');

--- 1 row(s) inserted.
>>
>>sh touch ${REGRRUNDIR}/test106_synch;
>>
>>select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler going to sleep - FUNKY_OPT_UNIQUE  2018-09-11 06:34:15.000384

--- 1 row(s) selected.
>>
>>sh sleep 13;
>>
>>select 'canceler woke up - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler woke up - FUNKY_OPT_UNIQUE         2018-09-11 06:34:28.000405

--- 1 row(s) selected.
>>
>>set session default CANCEL_ESCALATION_INTERVAL '5';

--- SQL operation complete.
>>log;
Execute End Time         -1
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel will cancel');

--- 1 row(s) inserted.
>>
>>obey cancel_cmd;
>>control query cancel qid MXID11000020942212403407567430015000000000206U3333300_415_S1;

--- SQL operation complete.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has canceled');

--- 1 row(s) inserted.
>>
>>select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler - FUNKY_OPT_UNIQUE                 2018-09-11 06:34:28.000496

--- 1 row(s) selected.
>>log;
>>
>>
>>
>>obey TEST106(positive_test2);
>>
>>-- Test canceling a query with and without the 
>>-- CANCEL_QUERY_ALLOWED.
>>
>>control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';

--- SQL operation complete.
>>
>>control query shape expr(sort_groupby(esp_exchange(cut)));

--- SQL operation complete.
>>
>>prepare s1 from select count(*) from t106a A, t106a B 
+>where A.uniq < 3000;

--- SQL command prepared.
>>
>>control query shape cut;

--- SQL operation complete.
>>
>>control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

--- SQL operation complete.
>>
>>obey TEST106(make_cancel_stmt);
>>log;
>>
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'positive_test2, launch canceler, start');

--- 1 row(s) inserted.
>>
>>sh sqlci -i"TEST106(do_cancel)" & ;
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'positive_test2, launch canceler, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:34:37.000537

--- 1 row(s) selected.
>>log;
>>
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'positive_test2, execute s1, start');

--- 1 row(s) inserted.
>>
>>------- long running, cpu-bound select query -------
>>execute s1;

*** ERROR[8007] The operation has been canceled.

*** ERROR[8811] Trying to close a statement that is either not in the open state or has not reached EOF.

--- 0 row(s) selected.
>>
>>select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject - FUNKY_OPT_UNIQUE                  2018-09-11 06:34:53.000589

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'positive_test2, execute s1, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:34:53.000623

--- 1 row(s) selected.
>>log;
>>
>>
>>obey TEST106(append_cancel_log);
>>log;
>>------------------------------------------;
>>------ Contents of cancel session --------;
>>------------------------------------------;
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has begun');

--- 1 row(s) inserted.
>>
>>sh touch ${REGRRUNDIR}/test106_synch;
>>
>>select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler going to sleep - FUNKY_OPT_UNIQUE  2018-09-11 06:34:37.000479

--- 1 row(s) selected.
>>
>>sh sleep 13;
>>
>>select 'canceler woke up - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler woke up - FUNKY_OPT_UNIQUE         2018-09-11 06:34:50.000499

--- 1 row(s) selected.
>>
>>set session default CANCEL_ESCALATION_INTERVAL '5';

--- SQL operation complete.
>>log;
Execute End Time         -1
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel will cancel');

--- 1 row(s) inserted.
>>
>>obey cancel_cmd;
>>control query cancel qid MXID11000020942212403407567430015000000000206U3333300_473_S1;

--- SQL operation complete.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has canceled');

--- 1 row(s) inserted.
>>
>>select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler - FUNKY_OPT_UNIQUE                 2018-09-11 06:34:53.000582

--- 1 row(s) selected.
>>log;
>>
>>
>>
>>obey TEST106(update_test1);
>>
>>control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';

--- SQL operation complete.
>>
>>prepare s1 from update t106a set c100 = c100 + 1;

--- SQL command prepared.
>>
>>control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

--- SQL operation complete.
>>
>>obey TEST106(make_cancel_stmt);
>>log;
>>
>>
>>select 'subject launch canceler - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject launch canceler - FUNKY_OPT_UNIQUE  2018-09-11 06:34:55.000817

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'update_test1, launch canceler, start');

--- 1 row(s) inserted.
>>
>>sh sqlci -i"TEST106(do_cancel)" & ;
>>
>>select 'subject synch cancel - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject synch cancel - FUNKY_OPT_UNIQUE     2018-09-11 06:34:55.000839

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'update_test1, launch canceler, finish');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:35:02.000687

--- 1 row(s) selected.
>>log;
>>
>>
>>select 'subject execute s1 - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject execute s1 - FUNKY_OPT_UNIQUE       2018-09-11 06:35:02.000710

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values 
+>(CURRENT_TIMESTAMP (6),
+>'update_test1, execute, start');

--- 1 row(s) inserted.
>>
>>execute s1;

*** ERROR[8007] The operation has been canceled.

--- 0 row(s) updated.
>>
>>select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject - FUNKY_OPT_UNIQUE                  2018-09-11 06:35:26.000940

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'update_test1, execute, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:35:26.000980

--- 1 row(s) selected.
>>log;
>>
>>
>>obey TEST106(append_cancel_log);
>>log;
>>------------------------------------------;
>>------ Contents of cancel session --------;
>>------------------------------------------;
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has begun');

--- 1 row(s) inserted.
>>
>>sh touch ${REGRRUNDIR}/test106_synch;
>>
>>select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler going to sleep - FUNKY_OPT_UNIQUE  2018-09-11 06:35:02.000666

--- 1 row(s) selected.
>>
>>sh sleep 13;
>>
>>select 'canceler woke up - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler woke up - FUNKY_OPT_UNIQUE         2018-09-11 06:35:15.000685

--- 1 row(s) selected.
>>
>>set session default CANCEL_ESCALATION_INTERVAL '5';

--- SQL operation complete.
>>log;
Execute End Time         -1
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel will cancel');

--- 1 row(s) inserted.
>>
>>obey cancel_cmd;
>>control query cancel qid MXID11000020942212403407567430015000000000206U3333300_488_S1;

--- SQL operation complete.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has canceled');

--- 1 row(s) inserted.
>>
>>select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler - FUNKY_OPT_UNIQUE                 2018-09-11 06:35:15.000794

--- 1 row(s) selected.
>>log;
>>
>>
>>
>>obey TEST106(delete_test1);
>>
>>prepare s1 from delete from t106a;

--- SQL command prepared.
>>
>>obey TEST106(make_cancel_stmt);
>>log;
>>
>>
>>select 'subject launch canceler - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject launch canceler - FUNKY_OPT_UNIQUE  2018-09-11 06:35:29.000124

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'delete_test1, launch canceler, start');

--- 1 row(s) inserted.
>>
>>sh sqlci -i"TEST106(do_cancel)" & ;
>>
>>select 'subject synch cancel - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject synch cancel - FUNKY_OPT_UNIQUE     2018-09-11 06:35:29.000143

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'delete_test1, launch canceler, finish');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:35:35.000996

--- 1 row(s) selected.
>>log;
>>
>>
>>select 'subject execute s1 - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject execute s1 - FUNKY_OPT_UNIQUE       2018-09-11 06:35:36.000017

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values 
+>(CURRENT_TIMESTAMP (6),
+>'delete_test1, execute, start');

--- 1 row(s) inserted.
>>
>>execute s1;

*** ERROR[8007] The operation has been canceled.

--- 0 row(s) deleted.
>>
>>select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject - FUNKY_OPT_UNIQUE                  2018-09-11 06:35:58.000197

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'delete_test1, execute, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:35:58.000238

--- 1 row(s) selected.
>>log;
>>
>>
>>obey TEST106(append_cancel_log);
>>log;
>>------------------------------------------;
>>------ Contents of cancel session --------;
>>------------------------------------------;
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has begun');

--- 1 row(s) inserted.
>>
>>sh touch ${REGRRUNDIR}/test106_synch;
>>
>>select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler going to sleep - FUNKY_OPT_UNIQUE  2018-09-11 06:35:35.000921

--- 1 row(s) selected.
>>
>>sh sleep 13;
>>
>>select 'canceler woke up - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler woke up - FUNKY_OPT_UNIQUE         2018-09-11 06:35:48.000940

--- 1 row(s) selected.
>>
>>set session default CANCEL_ESCALATION_INTERVAL '5';

--- SQL operation complete.
>>log;
Execute End Time         -1
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel will cancel');

--- 1 row(s) inserted.
>>
>>obey cancel_cmd;
>>control query cancel qid MXID11000020942212403407567430015000000000206U3333300_504_S1;

--- SQL operation complete.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has canceled');

--- 1 row(s) inserted.
>>
>>select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler - FUNKY_OPT_UNIQUE                 2018-09-11 06:35:49.000030

--- 1 row(s) selected.
>>log;
>>
>>
>>
>>obey TEST106(insert_test1);
>>
>>prepare s1 from insert into t106c select * from t106a;

--- SQL command prepared.
>>obey TEST106(make_cancel_stmt);
>>log;
>>
>>
>>select 'subject launch canceler - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject launch canceler - FUNKY_OPT_UNIQUE  2018-09-11 06:36:01.000614

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'insert_test1, launch canceler, start');

--- 1 row(s) inserted.
>>
>>sh sqlci -i"TEST106(do_cancel)" & ;
>>
>>select 'subject synch cancel - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject synch cancel - FUNKY_OPT_UNIQUE     2018-09-11 06:36:01.000635

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'insert_test1, launch canceler, finish');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:36:08.000598

--- 1 row(s) selected.
>>log;
>>
>>
>>select 'subject execute s1 - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject execute s1 - FUNKY_OPT_UNIQUE       2018-09-11 06:36:08.000621

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values 
+>(CURRENT_TIMESTAMP (6),
+>'insert_test1, execute, start');

--- 1 row(s) inserted.
>>
>>execute s1;

*** ERROR[8007] The operation has been canceled.

--- 0 row(s) inserted.
>>
>>select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject - FUNKY_OPT_UNIQUE                  2018-09-11 06:36:26.000066

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'insert_test1, execute, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:36:26.000099

--- 1 row(s) selected.
>>log;
>>
>>
>>obey TEST106(append_cancel_log);
>>log;
>>------------------------------------------;
>>------ Contents of cancel session --------;
>>------------------------------------------;
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has begun');

--- 1 row(s) inserted.
>>
>>sh touch ${REGRRUNDIR}/test106_synch;
>>
>>select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler going to sleep - FUNKY_OPT_UNIQUE  2018-09-11 06:36:08.000507

--- 1 row(s) selected.
>>
>>sh sleep 13;
>>
>>select 'canceler woke up - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler woke up - FUNKY_OPT_UNIQUE         2018-09-11 06:36:21.000532

--- 1 row(s) selected.
>>
>>set session default CANCEL_ESCALATION_INTERVAL '5';

--- SQL operation complete.
>>log;
Execute End Time         -1
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel will cancel');

--- 1 row(s) inserted.
>>
>>obey cancel_cmd;
>>control query cancel qid MXID11000020942212403407567430015000000000206U3333300_519_S1;

--- SQL operation complete.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has canceled');

--- 1 row(s) inserted.
>>
>>select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler - FUNKY_OPT_UNIQUE                 2018-09-11 06:36:24.000621

--- 1 row(s) selected.
>>log;
>>
>>
>>
>>obey TEST106(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
+>  ;

--- SQL command prepared.
>>
>>
>>obey TEST106(make_cancel_stmt);
>>log;
>>
>>
>>select 'subject launch canceler - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject launch canceler - FUNKY_OPT_UNIQUE  2018-09-11 06:36:28.000327

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'insert_test2, launch canceler, start');

--- 1 row(s) inserted.
>>
>>sh sqlci -i"TEST106(do_cancel)" & ;
>>
>>select 'subject synch cancel - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject synch cancel - FUNKY_OPT_UNIQUE     2018-09-11 06:36:28.000355

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'insert_test2, launch canceler, finish');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:36:36.000161

--- 1 row(s) selected.
>>log;
>>
>>
>>select 'subject execute s1 - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject execute s1 - FUNKY_OPT_UNIQUE       2018-09-11 06:36:36.000183

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values 
+>(CURRENT_TIMESTAMP (6),
+>'insert_test2, execute, start');

--- 1 row(s) inserted.
>>
>>execute s1;

*** ERROR[8007] The operation has been canceled.

--- 0 row(s) inserted.
>>
>>select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject - FUNKY_OPT_UNIQUE                  2018-09-11 06:36:53.000319

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'insert_test2, execute, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:36:53.000352

--- 1 row(s) selected.
>>log;
>>
>>
>>obey TEST106(append_cancel_log);
>>log;
>>------------------------------------------;
>>------ Contents of cancel session --------;
>>------------------------------------------;
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has begun');

--- 1 row(s) inserted.
>>
>>sh touch ${REGRRUNDIR}/test106_synch;
>>
>>select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler going to sleep - FUNKY_OPT_UNIQUE  2018-09-11 06:36:36.000131

--- 1 row(s) selected.
>>
>>sh sleep 13;
>>
>>select 'canceler woke up - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler woke up - FUNKY_OPT_UNIQUE         2018-09-11 06:36:49.000152

--- 1 row(s) selected.
>>
>>set session default CANCEL_ESCALATION_INTERVAL '5';

--- SQL operation complete.
>>log;
Execute End Time         -1
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel will cancel');

--- 1 row(s) inserted.
>>
>>obey cancel_cmd;
>>control query cancel qid MXID11000020942212403407567430015000000000206U3333300_578_S1;

--- SQL operation complete.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has canceled');

--- 1 row(s) inserted.
>>
>>select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler - FUNKY_OPT_UNIQUE                 2018-09-11 06:36:52.000247

--- 1 row(s) selected.
>>log;
>>
>>
>>
>>obey TEST106(upsert_test1);
>>cqd DYN_QUEUE_RESIZE_OVERRIDE 'ON';

--- SQL operation complete.
>>cqd GEN_TRSP_SIZE_DOWN '16';

--- SQL operation complete.
>>cqd GEN_TRSP_SIZE_UP '16';

--- SQL operation complete.
>>
>>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
+>  ;

--- SQL command prepared.
>>
>>
>>obey TEST106(make_cancel_stmt);
>>log;
>>
>>
>>select 'subject launch canceler - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject launch canceler - FUNKY_OPT_UNIQUE  2018-09-11 06:36:55.000602

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'upsert_test1, launch canceler, start');

--- 1 row(s) inserted.
>>
>>sh sqlci -i"TEST106(do_cancel)" & ;
>>
>>select 'subject synch cancel - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject synch cancel - FUNKY_OPT_UNIQUE     2018-09-11 06:36:55.000634

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'upsert_test1, launch canceler, finish');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:37:02.000489

--- 1 row(s) selected.
>>log;
>>
>>
>>select 'subject execute s1 - FUNKY_OPT_UNIQUE',
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject execute s1 - FUNKY_OPT_UNIQUE       2018-09-11 06:37:02.000512

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values 
+>(CURRENT_TIMESTAMP (6),
+>'upsert_test1, execute, start');

--- 1 row(s) inserted.
>>
>>execute s1;

*** ERROR[8007] The operation has been canceled.

--- 0 row(s) inserted.
>>
>>select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

subject - FUNKY_OPT_UNIQUE                  2018-09-11 06:37:15.000544

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'upsert_test1, execute, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE   2018-09-11 06:37:15.000581

--- 1 row(s) selected.
>>log;
>>
>>
>>obey TEST106(append_cancel_log);
>>log;
>>------------------------------------------;
>>------ Contents of cancel session --------;
>>------------------------------------------;
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has begun');

--- 1 row(s) inserted.
>>
>>sh touch ${REGRRUNDIR}/test106_synch;
>>
>>select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler going to sleep - FUNKY_OPT_UNIQUE  2018-09-11 06:37:02.000440

--- 1 row(s) selected.
>>
>>sh sleep 13;
>>
>>select 'canceler woke up - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler woke up - FUNKY_OPT_UNIQUE         2018-09-11 06:37:15.000459

--- 1 row(s) selected.
>>
>>set session default CANCEL_ESCALATION_INTERVAL '5';

--- SQL operation complete.
>>log;
Execute End Time         -1
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel will cancel');

--- 1 row(s) inserted.
>>
>>obey cancel_cmd;
>>control query cancel qid MXID11000020942212403407567430015000000000206U3333300_596_S1;

--- SQL operation complete.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has canceled');

--- 1 row(s) inserted.
>>
>>select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler - FUNKY_OPT_UNIQUE                 2018-09-11 06:37:15.000546

--- 1 row(s) selected.
>>log;
>>
>>cqd DYN_QUEUE_RESIZE_OVERRIDE reset;

--- SQL operation complete.
>>cqd GEN_TRSP_SIZE_DOWN reset;

--- SQL operation complete.
>>cqd GEN_TRSP_SIZE_UP reset;

--- SQL operation complete.
>>
>>obey TEST106(cpu_bound_esps);
>>
>>-- Test cancel when esps are CPU-bound.
>>
>>control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';

--- SQL operation complete.
>>prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C
+>group by c.c10k order by 2;

--- SQL command prepared.
>>control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

--- SQL operation complete.
>>
>>obey TEST106(make_cancel_stmt);
>>log;
>>
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'cpu_bound_esps, launch canceler, start');

--- 1 row(s) inserted.
>>
>>sh sqlci -i"TEST106(do_cancel)" & ;
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'cpu_bound_esps, launch canceler, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                     (EXPR)                    
-----------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE  2018-09-11 06:37:24.000952

--- 1 row(s) selected.
>>log;
>>
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'cpu_bound_esps, execute s1, start');

--- 1 row(s) inserted.
>>
>>execute s1;

*** ERROR[8007] The operation has been canceled.

*** ERROR[8811] Trying to close a statement that is either not in the open state or has not reached EOF.

--- 0 row(s) selected.
>>
>>select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                     (EXPR)                    
-----------------------------------------  --------------------------

subject - FUNKY_OPT_UNIQUE                 2018-09-11 06:37:52.000240

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'cpu_bound_esps, execute s1, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                     (EXPR)                    
-----------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE  2018-09-11 06:37:52.000293

--- 1 row(s) selected.
>>log;
>>
>>
>>obey TEST106(append_cancel_log);
>>log;
>>------------------------------------------;
>>------ Contents of cancel session --------;
>>------------------------------------------;
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has begun');

--- 1 row(s) inserted.
>>
>>sh touch ${REGRRUNDIR}/test106_synch;
>>
>>select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler going to sleep - FUNKY_OPT_UNIQUE  2018-09-11 06:37:24.000927

--- 1 row(s) selected.
>>
>>sh sleep 13;
>>
>>select 'canceler woke up - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler woke up - FUNKY_OPT_UNIQUE         2018-09-11 06:37:37.000947

--- 1 row(s) selected.
>>
>>set session default CANCEL_ESCALATION_INTERVAL '5';

--- SQL operation complete.
>>log;
Execute End Time         -1
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel will cancel');

--- 1 row(s) inserted.
>>
>>obey cancel_cmd;
>>control query cancel qid MXID11000020942212403407567430015000000000206U3333300_615_S1;

--- SQL operation complete.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has canceled');

--- 1 row(s) inserted.
>>
>>select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler - FUNKY_OPT_UNIQUE                 2018-09-11 06:37:41.000047

--- 1 row(s) selected.
>>log;
>>
>>
>>
>>obey TEST106(escalation1);
>>
>>-- Test cancel escalation.  Simulate unresponsive ESPs with COMP_INT_39.
>>
>>control query default COMP_INT_39 '4';

--- SQL operation complete.
>>control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';

--- SQL operation complete.
>>prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C
+>group by c.c10k order by 2;

--- SQL command prepared.
>>control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

--- SQL operation complete.
>>control query default COMP_INT_39 'reset';

--- SQL operation complete.
>>
>>obey TEST106(make_cancel_stmt);
>>log;
>>
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'escalation1, launch canceler, start');

--- 1 row(s) inserted.
>>
>>sh sqlci -i"TEST106(do_cancel)" & ;
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'escalation1, launch canceler, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                     (EXPR)                    
-----------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE  2018-09-11 06:38:01.000517

--- 1 row(s) selected.
>>log;
>>
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'escalation1, execute s1, start');

--- 1 row(s) inserted.
>>
>>execute s1;

*** ERROR[8007] The operation has been canceled.

*** ERROR[8811] Trying to close a statement that is either not in the open state or has not reached EOF.

--- 0 row(s) selected.
>>
>>select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                     (EXPR)                    
-----------------------------------------  --------------------------

subject - FUNKY_OPT_UNIQUE                 2018-09-11 06:38:28.000772

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'escalation1, execute s1, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                     (EXPR)                    
-----------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE  2018-09-11 06:38:28.000814

--- 1 row(s) selected.
>>log;
>>
>>
>>obey TEST106(append_cancel_log);
>>log;
>>------------------------------------------;
>>------ Contents of cancel session --------;
>>------------------------------------------;
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has begun');

--- 1 row(s) inserted.
>>
>>sh touch ${REGRRUNDIR}/test106_synch;
>>
>>select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler going to sleep - FUNKY_OPT_UNIQUE  2018-09-11 06:38:01.000478

--- 1 row(s) selected.
>>
>>sh sleep 13;
>>
>>select 'canceler woke up - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler woke up - FUNKY_OPT_UNIQUE         2018-09-11 06:38:14.000502

--- 1 row(s) selected.
>>
>>set session default CANCEL_ESCALATION_INTERVAL '5';

--- SQL operation complete.
>>log;
Execute End Time         -1
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel will cancel');

--- 1 row(s) inserted.
>>
>>obey cancel_cmd;
>>control query cancel qid MXID11000020942212403407567430015000000000206U3333300_630_S1;

--- SQL operation complete.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has canceled');

--- 1 row(s) inserted.
>>
>>select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler - FUNKY_OPT_UNIQUE                 2018-09-11 06:38:17.000590

--- 1 row(s) selected.
>>log;
>>
>>
>>
>>#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);
>>
>>log;
cancel-specific showplan output
Contents of EX_CANCEL [1]:
For ComTdbCancel :
qid_ = MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1
action_ = cancel by queryId
comment_ =
>>
>>
>>obey TEST106(explain1);
>>
>>log;
cancel-specific explain output
CONTROL_RUNNING_QUERY =====================  SEQ_NO 1        NO CHILDREN
  control_action ......... cancel by qid
>>
>>
>>obey TEST106(invalid_qid);
>>control query cancel qid foo;

*** ERROR[8919] Unable to retrieve Runtime Statistics for invalid Query Id .

--- SQL operation failed with errors.
>>
>>set envvar HP_FAKE_ERROR_201;

--- SQL operation complete.
>>
>>control query cancel qid
+>MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

*** ERROR[2034] I say: Operating system error 201 while communicating with server process control broker.

--- SQL operation failed with errors.
>>
>>reset envvar HP_FAKE_ERROR_201;

--- SQL operation complete.
>>
>>control query cancel qid
+>MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

*** ERROR[8026] Server declined cancel request. The query ID  of the targeted query was not found.

--- SQL operation failed with errors.
>>
>>control query suspend qid
+>MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

*** ERROR[1010] The statement just entered is currently not supported.

*** ERROR[8822] The statement was not prepared.

>>
>>control query suspend qid
+>MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1, force;

*** ERROR[1010] The statement just entered is currently not supported.

*** ERROR[8822] The statement was not prepared.

>>
>>control query activate qid
+>MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

*** ERROR[1010] The statement just entered is currently not supported.

*** ERROR[8822] The statement was not prepared.

>>
>>
>>obey TEST106(invalid_pname);
>>control query cancel pid $ZXXXXX;

*** ERROR[1010] The statement just entered is currently not supported.

*** ERROR[8822] The statement was not prepared.

>>
>>
>>obey TEST106(invalid_nid_pid);
>>control query cancel pid 44,66666;

*** ERROR[1010] The statement just entered is currently not supported.

*** ERROR[8822] The statement was not prepared.

>>
>>
>>obey TEST106(qc1314);
>>
>>-- Test case for QC 1314.  Exercise IPC timeout in 
>>-- ex_root_tcb::cancel.
>>
>>set envvar SQLMX_CANCEL_TIMEOUT '10';

--- SQL operation complete.
>>
>>-- Simulate unresponsive ESPs with COMP_INT_39.
>>
>>control query default COMP_INT_39 '4';

--- SQL operation complete.
>>
>>set envvar sqlci_cursor '1';

--- SQL operation complete.
>>
>>control query shape esp_exchange(cut);

--- SQL operation complete.
>>
>>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;

--- SQL operation complete.
>>
>>control query shape cut;

--- SQL operation complete.
>>
>>control query default COMP_INT_39 'reset';

--- SQL operation complete.
>>values('FUNKY_OPT_UNIQUE', current_timestamp);

(EXPR)            (EXPR)                    
----------------  --------------------------

FUNKY_OPT_UNIQUE  2018-09-11 06:38:39.000193

--- 1 row(s) selected.
>>
>>open c1;

--- SQL operation complete.
>>
>>close c1;

--- SQL operation complete.
>>values('FUNKY_OPT_UNIQUE', current_timestamp);

(EXPR)            (EXPR)                    
----------------  --------------------------

FUNKY_OPT_UNIQUE  2018-09-11 06:39:09.000721

--- 1 row(s) selected.
>>
>>open c1;

--- SQL operation complete.
>>
>>close c1;

--- SQL operation complete.
>>values('FUNKY_OPT_UNIQUE', current_timestamp);

(EXPR)            (EXPR)                    
----------------  --------------------------

FUNKY_OPT_UNIQUE  2018-09-11 06:39:40.000544

--- 1 row(s) selected.
>>
>>open c1;

--- SQL operation complete.
>>
>>close c1;

--- SQL operation complete.
>>values('FUNKY_OPT_UNIQUE', current_timestamp);

(EXPR)            (EXPR)                    
----------------  --------------------------

FUNKY_OPT_UNIQUE  2018-09-11 06:39:43.000151

--- 1 row(s) selected.
>>
>>open c1;

--- SQL operation complete.
>>
>>close c1;

--- SQL operation complete.
>>values('FUNKY_OPT_UNIQUE', current_timestamp);

(EXPR)            (EXPR)                    
----------------  --------------------------

FUNKY_OPT_UNIQUE  2018-09-11 06:40:17.000355

--- 1 row(s) selected.
>>
>>open c1;

--- SQL operation complete.
>>
>>close c1;

--- SQL operation complete.
>>values('FUNKY_OPT_UNIQUE', current_timestamp);

(EXPR)            (EXPR)                    
----------------  --------------------------

FUNKY_OPT_UNIQUE  2018-09-11 06:40:48.000240

--- 1 row(s) selected.
>>
>>
>>obey TEST106(qc1351);
>>log LOG106;
>>prepare s1 from select * from t106a;

--- SQL command prepared.
>>obey TEST106(make_cancel_stmt);
>>log;
>>
>>obey cancel_cmd;
>>control query cancel qid MXID11000020942212403407567430015000000000206U3333300_934_S1;

*** ERROR[8031] Server declined cancel request for query ID MXID11000020942212403407567430015000000000206U3333300_934_S1. The query is not in OPEN or FETCH or EXECUTE state.

--- SQL operation failed with errors.
>>
>>
>>-- this one removes histograms for t106a so do it at the end.
>>obey TEST106(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
+>  ;

--- 200000 row(s) inserted.
>>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
+>  ;

--- 200000 row(s) inserted.
>>
>>
>>update statistics for table t106a clear;

--- SQL operation complete.
>>
>>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)
+>;

--- SQL command prepared.
>>
>>obey TEST106(make_cancel_stmt);
>>log;
>>
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'ustats_test, launch canceler, start');

--- 1 row(s) inserted.
>>
>>sh sqlci -i"TEST106(do_cancel)" & ;
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'ustats_test, launch canceler, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                     (EXPR)                    
-----------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE  2018-09-11 06:41:17.000817

--- 1 row(s) selected.
>>log;
>>
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'ustats_test, execute s1, start');

--- 1 row(s) inserted.
>>
>>execute s1;

*** ERROR[8007] The operation has been canceled.

*** ERROR[8811] Trying to close a statement that is either not in the open state or has not reached EOF.

--- SQL operation failed with errors.
>>
>>select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                     (EXPR)                    
-----------------------------------------  --------------------------

subject - FUNKY_OPT_UNIQUE                 2018-09-11 06:41:30.000938

--- 1 row(s) selected.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6),
+>'ustats_test, execute s1, finished');

--- 1 row(s) inserted.
>>
>>obey TEST106(synch_cancel);
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'foreground awaits synch_cancel');

--- 1 row(s) inserted.
>>
>>-- 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;

(EXPR)                                     (EXPR)                    
-----------------------------------------  --------------------------

done with synch_cancel - FUNKY_OPT_UNIQUE  2018-09-11 06:41:30.000975

--- 1 row(s) selected.
>>log;
>>
>>
>>obey TEST106(append_cancel_log);
>>log;
>>------------------------------------------;
>>------ Contents of cancel session --------;
>>------------------------------------------;
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has begun');

--- 1 row(s) inserted.
>>
>>sh touch ${REGRRUNDIR}/test106_synch;
>>
>>select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler going to sleep - FUNKY_OPT_UNIQUE  2018-09-11 06:41:17.000801

--- 1 row(s) selected.
>>
>>sh sleep 13;
>>
>>select 'canceler woke up - FUNKY_OPT_UNIQUE', 
+>CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler woke up - FUNKY_OPT_UNIQUE         2018-09-11 06:41:30.000824

--- 1 row(s) selected.
>>
>>set session default CANCEL_ESCALATION_INTERVAL '5';

--- SQL operation complete.
>>log;
Execute End Time         -1
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel will cancel');

--- 1 row(s) inserted.
>>
>>obey cancel_cmd;
>>control query cancel qid MXID11000020942212403407567430015000000000206U3333300_939_S1;

--- SQL operation complete.
>>
>>insert into T106_interleaved_log values
+>(CURRENT_TIMESTAMP (6), 
+>'do_cancel has canceled');

--- 1 row(s) inserted.
>>
>>select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

(EXPR)                                      (EXPR)                    
------------------------------------------  --------------------------

canceler - FUNKY_OPT_UNIQUE                 2018-09-11 06:41:30.000907

--- 1 row(s) selected.
>>log;
>>
>>
>>
>>
>>
>>select msg, 'FUNKY_OPT_UNIQUE', ts
+>  from T106_interleaved_log order by ts;

MSG                                                           (EXPR)            TS
------------------------------------------------------------  ----------------  --------------------------

positive_test1, launch canceler, start                        FUNKY_OPT_UNIQUE  2018-09-11 06:34:08.000319
positive_test1, launch canceler, finish                       FUNKY_OPT_UNIQUE  2018-09-11 06:34:08.000404
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:34:08.000422
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2018-09-11 06:34:15.000320
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:34:15.000500
positive_test1, execute, start                                FUNKY_OPT_UNIQUE  2018-09-11 06:34:15.000512
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2018-09-11 06:34:28.000478
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2018-09-11 06:34:28.000490
positive_test1, execute, finished                             FUNKY_OPT_UNIQUE  2018-09-11 06:34:28.000504
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:34:28.000517
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:34:28.000556
positive_test2, launch canceler, start                        FUNKY_OPT_UNIQUE  2018-09-11 06:34:30.000773
positive_test2, launch canceler, finished                     FUNKY_OPT_UNIQUE  2018-09-11 06:34:30.000790
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:34:30.000803
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2018-09-11 06:34:37.000416
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:34:37.000552
positive_test2, execute s1, start                             FUNKY_OPT_UNIQUE  2018-09-11 06:34:37.000558
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2018-09-11 06:34:53.000562
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2018-09-11 06:34:53.000575
positive_test2, execute s1, finished                          FUNKY_OPT_UNIQUE  2018-09-11 06:34:53.000593
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:34:53.000603
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:34:53.000639
update_test1, launch canceler, start                          FUNKY_OPT_UNIQUE  2018-09-11 06:34:55.000820
update_test1, launch canceler, finish                         FUNKY_OPT_UNIQUE  2018-09-11 06:34:55.000842
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:34:55.000854
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2018-09-11 06:35:02.000598
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:35:02.000704
update_test1, execute, start                                  FUNKY_OPT_UNIQUE  2018-09-11 06:35:02.000714
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2018-09-11 06:35:15.000766
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2018-09-11 06:35:15.000785
update_test1, execute, finished                               FUNKY_OPT_UNIQUE  2018-09-11 06:35:26.000947
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:35:26.000957
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:35:26.000997
delete_test1, launch canceler, start                          FUNKY_OPT_UNIQUE  2018-09-11 06:35:29.000127
delete_test1, launch canceler, finish                         FUNKY_OPT_UNIQUE  2018-09-11 06:35:29.000146
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:35:29.000155
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2018-09-11 06:35:35.000856
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:35:36.000012
delete_test1, execute, start                                  FUNKY_OPT_UNIQUE  2018-09-11 06:35:36.000020
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2018-09-11 06:35:49.000012
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2018-09-11 06:35:49.000023
delete_test1, execute, finished                               FUNKY_OPT_UNIQUE  2018-09-11 06:35:58.000200
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:35:58.000214
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:35:58.000267
insert_test1, launch canceler, start                          FUNKY_OPT_UNIQUE  2018-09-11 06:36:01.000618
insert_test1, launch canceler, finish                         FUNKY_OPT_UNIQUE  2018-09-11 06:36:01.000638
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:36:01.000648
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2018-09-11 06:36:08.000437
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:36:08.000616
insert_test1, execute, start                                  FUNKY_OPT_UNIQUE  2018-09-11 06:36:08.000623
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2018-09-11 06:36:24.000600
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2018-09-11 06:36:24.000613
insert_test1, execute, finished                               FUNKY_OPT_UNIQUE  2018-09-11 06:36:26.000068
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:36:26.000078
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:36:26.000116
insert_test2, launch canceler, start                          FUNKY_OPT_UNIQUE  2018-09-11 06:36:28.000332
insert_test2, launch canceler, finish                         FUNKY_OPT_UNIQUE  2018-09-11 06:36:28.000359
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:36:28.000371
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2018-09-11 06:36:36.000054
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:36:36.000178
insert_test2, execute, start                                  FUNKY_OPT_UNIQUE  2018-09-11 06:36:36.000187
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2018-09-11 06:36:52.000227
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2018-09-11 06:36:52.000239
insert_test2, execute, finished                               FUNKY_OPT_UNIQUE  2018-09-11 06:36:53.000322
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:36:53.000331
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:36:53.000367
upsert_test1, launch canceler, start                          FUNKY_OPT_UNIQUE  2018-09-11 06:36:55.000615
upsert_test1, launch canceler, finish                         FUNKY_OPT_UNIQUE  2018-09-11 06:36:55.000637
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:36:55.000646
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2018-09-11 06:37:02.000376
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:37:02.000505
upsert_test1, execute, start                                  FUNKY_OPT_UNIQUE  2018-09-11 06:37:02.000514
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2018-09-11 06:37:15.000529
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2018-09-11 06:37:15.000540
upsert_test1, execute, finished                               FUNKY_OPT_UNIQUE  2018-09-11 06:37:15.000547
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:37:15.000556
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:37:15.000598
cpu_bound_esps, launch canceler, start                        FUNKY_OPT_UNIQUE  2018-09-11 06:37:17.000965
cpu_bound_esps, launch canceler, finished                     FUNKY_OPT_UNIQUE  2018-09-11 06:37:17.000989
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:37:17.000998
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2018-09-11 06:37:24.000862
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:37:24.000972
cpu_bound_esps, execute s1, start                             FUNKY_OPT_UNIQUE  2018-09-11 06:37:24.000977
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2018-09-11 06:37:41.000023
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2018-09-11 06:37:41.000039
cpu_bound_esps, execute s1, finished                          FUNKY_OPT_UNIQUE  2018-09-11 06:37:52.000257
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:37:52.000268
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:37:52.000309
escalation1, launch canceler, start                           FUNKY_OPT_UNIQUE  2018-09-11 06:37:54.000648
escalation1, launch canceler, finished                        FUNKY_OPT_UNIQUE  2018-09-11 06:37:54.000667
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:37:54.000677
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2018-09-11 06:38:01.000406
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:38:01.000537
escalation1, execute s1, start                                FUNKY_OPT_UNIQUE  2018-09-11 06:38:01.000541
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2018-09-11 06:38:17.000572
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2018-09-11 06:38:17.000584
escalation1, execute s1, finished                             FUNKY_OPT_UNIQUE  2018-09-11 06:38:28.000785
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:38:28.000793
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:38:28.000835
ustats_test, launch canceler, start                           FUNKY_OPT_UNIQUE  2018-09-11 06:41:10.000830
ustats_test, launch canceler, finished                        FUNKY_OPT_UNIQUE  2018-09-11 06:41:10.000847
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:41:10.000857
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2018-09-11 06:41:17.000715
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:41:17.000844
ustats_test, execute s1, start                                FUNKY_OPT_UNIQUE  2018-09-11 06:41:17.000849
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2018-09-11 06:41:30.000889
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2018-09-11 06:41:30.000901
ustats_test, execute s1, finished                             FUNKY_OPT_UNIQUE  2018-09-11 06:41:30.000942
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2018-09-11 06:41:30.000951
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2018-09-11 06:41:30.000991

--- 110 row(s) selected.
>>
>>obey TEST106(clnup);
>>
>>drop table t106a;

--- SQL operation complete.
>>drop table t106c;

--- SQL operation complete.
>>log T106_INTERLEAVED_LOG;