>>
>>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  2015-07-10 17:06:19.088376

--- 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  2015-07-10 17:06:19.593994

--- 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  2015-07-10 17:06:25.701964

--- 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  2015-07-10 17:06:27.328307

--- 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  2015-07-10 17:06:27.705202

--- 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     2015-07-10 17:06:28.699544

--- 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   2015-07-10 17:06:38.788316

--- 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       2015-07-10 17:06:38.813948

--- 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                  2015-07-10 17:06:51.861646

--- 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   2015-07-10 17:06:51.912524

--- 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  2015-07-10 17:06:38.714664

--- 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         2015-07-10 17:06:51.735687

--- 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 MXID11000021348212303307894875569000000000206U3333300_183_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                 2015-07-10 17:06:51.817032

--- 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   2015-07-10 17:07:03.399656

--- 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                  2015-07-10 17:07:16.484716

--- 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   2015-07-10 17:07:16.521981

--- 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  2015-07-10 17:07:03.311535

--- 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         2015-07-10 17:07:16.361158

--- 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 MXID11000021348212303307894875569000000000206U3333300_222_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                 2015-07-10 17:07:16.472310

--- 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  2015-07-10 17:07:18.686506

--- 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     2015-07-10 17:07:18.705866

--- 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   2015-07-10 17:07:28.373358

--- 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       2015-07-10 17:07:28.394035

--- 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                  2015-07-10 17:07:48.064685

--- 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   2015-07-10 17:07:48.096812

--- 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  2015-07-10 17:07:28.302277

--- 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         2015-07-10 17:07:41.320811

--- 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 MXID11000021348212303307894875569000000000206U3333300_237_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                 2015-07-10 17:07:41.407319

--- 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  2015-07-10 17:07:50.195393

--- 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     2015-07-10 17:07:50.215880

--- 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   2015-07-10 17:07:59.487739

--- 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       2015-07-10 17:07:59.508758

--- 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                  2015-07-10 17:08:25.759995

--- 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   2015-07-10 17:08:25.795455

--- 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  2015-07-10 17:07:59.486860

--- 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         2015-07-10 17:08:12.509301

--- 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 MXID11000021348212303307894875569000000000206U3333300_253_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                 2015-07-10 17:08:12.606156

--- 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  2015-07-10 17:08:29.298685

--- 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     2015-07-10 17:08:29.318388

--- 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   2015-07-10 17:08:38.782601

--- 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       2015-07-10 17:08:38.800701

--- 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                  2015-07-10 17:08:56.574479

--- 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   2015-07-10 17:08:56.608716

--- 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  2015-07-10 17:08:38.771967

--- 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         2015-07-10 17:08:51.799087

--- 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 MXID11000021348212303307894875569000000000206U3333300_268_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                 2015-07-10 17:08:54.940529

--- 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  2015-07-10 17:08:59.019859

--- 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     2015-07-10 17:08:59.038540

--- 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   2015-07-10 17:09:08.728176

--- 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       2015-07-10 17:09:08.748097

--- 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                  2015-07-10 17:09:25.863895

--- 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   2015-07-10 17:09:25.898587

--- 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  2015-07-10 17:09:08.679253

--- 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         2015-07-10 17:09:21.705227

--- 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 MXID11000021348212303307894875569000000000206U3333300_306_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                 2015-07-10 17:09:24.803186

--- 1 row(s) selected.
>>log;
>>
>>
>>
>>obey TEST106(upsert_test1);
>>
>>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  2015-07-10 17:09:28.259737

--- 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     2015-07-10 17:09:28.282876

--- 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   2015-07-10 17:09:37.705479

--- 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       2015-07-10 17:09:37.727412

--- 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                  2015-07-10 17:09:53.821321

--- 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   2015-07-10 17:09:53.952650

--- 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  2015-07-10 17:09:37.708310

--- 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         2015-07-10 17:09:50.732984

--- 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 MXID11000021348212303307894875569000000000206U3333300_321_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                 2015-07-10 17:09:53.825816

--- 1 row(s) selected.
>>log;
>>
>>
>>
>>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   2015-07-10 17:10:06.471498

--- 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                  2015-07-10 17:10:30.700079

--- 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   2015-07-10 17:10:30.735707

--- 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  2015-07-10 17:10:06.408419

--- 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         2015-07-10 17:10:19.430779

--- 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 MXID11000021348212303307894875569000000000206U3333300_337_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                 2015-07-10 17:10:19.515222

--- 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  2015-07-10 17:10:42.359509

--- 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                 2015-07-10 17:11:06.522144

--- 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  2015-07-10 17:11:06.564411

--- 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  2015-07-10 17:10:42.305768

--- 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         2015-07-10 17:10:55.328810

--- 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 MXID11000021348212303307894875569000000000206U3333300_352_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                 2015-07-10 17:10:55.406493

--- 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  2015-07-10 17:11:17.016966

--- 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  2015-07-10 17:11:47.131046

--- 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  2015-07-10 17:11:48.077070

--- 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  2015-07-10 17:12:18.192873

--- 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  2015-07-10 17:12:19.105450

--- 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  2015-07-10 17:12:49.225497

--- 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 MXID11000021348212303307894875569000000000206U3333300_611_S1;

*** ERROR[8031] Server declined cancel request for query ID MXID11000021348212303307894875569000000000206U3333300_611_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  2015-07-10 17:13:47.314100

--- 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                 2015-07-10 17:14:00.677443

--- 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  2015-07-10 17:14:00.717696

--- 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  2015-07-10 17:13:47.295281

--- 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         2015-07-10 17:14:00.323547

--- 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 MXID11000021348212303307894875569000000000206U3333300_616_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                 2015-07-10 17:14:00.465200

--- 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  2015-07-10 17:06:28.605534
positive_test1, launch canceler, finish                       FUNKY_OPT_UNIQUE  2015-07-10 17:06:28.702813
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:06:28.712618
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2015-07-10 17:06:38.626473
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:06:38.804751
positive_test1, execute, start                                FUNKY_OPT_UNIQUE  2015-07-10 17:06:38.816899
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2015-07-10 17:06:51.795368
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2015-07-10 17:06:51.811123
positive_test1, execute, finished                             FUNKY_OPT_UNIQUE  2015-07-10 17:06:51.865571
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:06:51.881210
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:06:51.925973
positive_test2, launch canceler, start                        FUNKY_OPT_UNIQUE  2015-07-10 17:06:54.121258
positive_test2, launch canceler, finished                     FUNKY_OPT_UNIQUE  2015-07-10 17:06:54.135947
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:06:54.144872
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2015-07-10 17:07:03.218202
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:07:03.414289
positive_test2, execute s1, start                             FUNKY_OPT_UNIQUE  2015-07-10 17:07:03.421669
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2015-07-10 17:07:16.425344
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2015-07-10 17:07:16.465426
positive_test2, execute s1, finished                          FUNKY_OPT_UNIQUE  2015-07-10 17:07:16.489121
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:07:16.500205
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:07:16.558636
update_test1, launch canceler, start                          FUNKY_OPT_UNIQUE  2015-07-10 17:07:18.691347
update_test1, launch canceler, finish                         FUNKY_OPT_UNIQUE  2015-07-10 17:07:18.710009
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:07:18.718706
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2015-07-10 17:07:28.214420
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:07:28.388345
update_test1, execute, start                                  FUNKY_OPT_UNIQUE  2015-07-10 17:07:28.397823
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2015-07-10 17:07:41.385593
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2015-07-10 17:07:41.400965
update_test1, execute, finished                               FUNKY_OPT_UNIQUE  2015-07-10 17:07:48.069219
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:07:48.078175
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:07:48.109026
delete_test1, launch canceler, start                          FUNKY_OPT_UNIQUE  2015-07-10 17:07:50.200100
delete_test1, launch canceler, finish                         FUNKY_OPT_UNIQUE  2015-07-10 17:07:50.221685
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:07:50.230056
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2015-07-10 17:07:59.371817
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:07:59.504170
delete_test1, execute, start                                  FUNKY_OPT_UNIQUE  2015-07-10 17:07:59.512807
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2015-07-10 17:08:12.579070
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2015-07-10 17:08:12.595650
delete_test1, execute, finished                               FUNKY_OPT_UNIQUE  2015-07-10 17:08:25.765256
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:08:25.774591
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:08:25.809037
insert_test1, launch canceler, start                          FUNKY_OPT_UNIQUE  2015-07-10 17:08:29.303373
insert_test1, launch canceler, finish                         FUNKY_OPT_UNIQUE  2015-07-10 17:08:29.322696
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:08:29.330814
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2015-07-10 17:08:38.663779
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:08:38.796777
insert_test1, execute, start                                  FUNKY_OPT_UNIQUE  2015-07-10 17:08:38.804614
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2015-07-10 17:08:54.896102
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2015-07-10 17:08:54.930493
insert_test1, execute, finished                               FUNKY_OPT_UNIQUE  2015-07-10 17:08:56.579137
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:08:56.589386
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:08:56.622206
insert_test2, launch canceler, start                          FUNKY_OPT_UNIQUE  2015-07-10 17:08:59.024743
insert_test2, launch canceler, finish                         FUNKY_OPT_UNIQUE  2015-07-10 17:08:59.042423
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:08:59.050281
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2015-07-10 17:09:08.564653
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:09:08.742892
insert_test2, execute, start                                  FUNKY_OPT_UNIQUE  2015-07-10 17:09:08.751928
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2015-07-10 17:09:24.776254
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2015-07-10 17:09:24.795329
insert_test2, execute, finished                               FUNKY_OPT_UNIQUE  2015-07-10 17:09:25.871938
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:09:25.880384
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:09:25.911426
upsert_test1, launch canceler, start                          FUNKY_OPT_UNIQUE  2015-07-10 17:09:28.265459
upsert_test1, launch canceler, finish                         FUNKY_OPT_UNIQUE  2015-07-10 17:09:28.287050
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:09:28.294726
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2015-07-10 17:09:37.613861
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:09:37.723289
upsert_test1, execute, start                                  FUNKY_OPT_UNIQUE  2015-07-10 17:09:37.731436
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2015-07-10 17:09:53.801878
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2015-07-10 17:09:53.819783
upsert_test1, execute, finished                               FUNKY_OPT_UNIQUE  2015-07-10 17:09:53.825158
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:09:53.832495
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:09:53.970360
cpu_bound_esps, launch canceler, start                        FUNKY_OPT_UNIQUE  2015-07-10 17:09:56.333247
cpu_bound_esps, launch canceler, finished                     FUNKY_OPT_UNIQUE  2015-07-10 17:09:56.347915
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:09:56.355395
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2015-07-10 17:10:06.308852
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:10:06.491893
cpu_bound_esps, execute s1, start                             FUNKY_OPT_UNIQUE  2015-07-10 17:10:06.499750
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2015-07-10 17:10:19.492506
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2015-07-10 17:10:19.508940
cpu_bound_esps, execute s1, finished                          FUNKY_OPT_UNIQUE  2015-07-10 17:10:30.705276
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:10:30.716871
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:10:30.748949
escalation1, launch canceler, start                           FUNKY_OPT_UNIQUE  2015-07-10 17:10:33.132692
escalation1, launch canceler, finished                        FUNKY_OPT_UNIQUE  2015-07-10 17:10:33.149281
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:10:33.158984
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2015-07-10 17:10:42.217942
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:10:42.376461
escalation1, execute s1, start                                FUNKY_OPT_UNIQUE  2015-07-10 17:10:42.381161
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2015-07-10 17:10:55.385636
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2015-07-10 17:10:55.400533
escalation1, execute s1, finished                             FUNKY_OPT_UNIQUE  2015-07-10 17:11:06.526582
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:11:06.539511
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:11:06.580843
ustats_test, launch canceler, start                           FUNKY_OPT_UNIQUE  2015-07-10 17:13:38.014277
ustats_test, launch canceler, finished                        FUNKY_OPT_UNIQUE  2015-07-10 17:13:38.035897
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:13:38.045217
do_cancel has begun                                           FUNKY_OPT_UNIQUE  2015-07-10 17:13:47.211662
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:13:47.339719
ustats_test, execute s1, start                                FUNKY_OPT_UNIQUE  2015-07-10 17:13:47.345734
do_cancel will cancel                                         FUNKY_OPT_UNIQUE  2015-07-10 17:14:00.422546
do_cancel has canceled                                        FUNKY_OPT_UNIQUE  2015-07-10 17:14:00.458124
ustats_test, execute s1, finished                             FUNKY_OPT_UNIQUE  2015-07-10 17:14:00.681401
foreground awaits synch_cancel                                FUNKY_OPT_UNIQUE  2015-07-10 17:14:00.693030
foreground finished synch_cancel                              FUNKY_OPT_UNIQUE  2015-07-10 17:14:00.731016

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

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

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