ij> --
--   Licensed to the Apache Software Foundation (ASF) under one or more
--   contributor license agreements.  See the NOTICE file distributed with
--   this work for additional information regarding copyright ownership.
--   The ASF licenses this file to You under the Apache License, Version 2.0
--   (the "License"); you may not use this file except in compliance with
--   the License.  You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
--   Unless required by applicable law or agreed to in writing, software
--   distributed under the License is distributed on an "AS IS" BASIS,
--   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--   See the License for the specific language governing permissions and
--   limitations under the License.
--
-- testing Transaction table
maximumdisplaywidth 9000;
ij> connect 'wombat' as c1;
ij(C1)> set isolation to rr;
0 rows inserted/updated/deleted
ij(C1)> -- Only look at user transactions.  Depending on timing of background 
-- threads for post commit and checkpoint there may be system and 
-- and internal transactions that vary from machine to machine.
create view xactTable as
select username, type, status,
case when first_instant is NULL then 'readonly' else 'not readonly' end as readOnly, cast(sql_text as varchar(512)) sql_text
  from syscs_diag.transaction_table
    where type = 'UserTransaction';
0 rows inserted/updated/deleted
ij(C1)> commit;
ij(C1)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C1)> create table foo (a int);
0 rows inserted/updated/deleted
ij(C1)> create index fooi on foo (a);
0 rows inserted/updated/deleted
ij(C1)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C1)> autocommit off;
ij(C1)> select * from foo;
A          
-----------
ij(C1)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |ACTIVE  |readonly    |select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S' 
order by lockname, mode, cnt, state;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE|1    |S   |FOO                                                                                                                             |Tablelock           |GRANT
ij(C1)> commit;
ij(C1)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table ;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij(C1)> insert into foo values (1), (3), (5), (7), (9);
5 rows inserted/updated/deleted
ij(C1)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |ACTIVE  |not readonly|select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ROW  |1    |X   |FOO                                                                                                                             |(1,10)              |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,11)              |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,7)               |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,8)               |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,9)               |GRANT
TABLE|2    |IX  |FOO                                                                                                                             |Tablelock           |GRANT
ij(C1)> commit;
ij(C1)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij(C1)> insert into foo values (6), (10);
2 rows inserted/updated/deleted
ij(C1)> -- make another connection
connect 'wombat' as c2;
ij(C2)> set isolation to rr;
0 rows inserted/updated/deleted
ij(C2)> autocommit off;
ij(C2)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |ACTIVE  |not readonly|NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ROW  |1    |X   |FOO                                                                                                                             |(1,12)              |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,13)              |GRANT
TABLE|2    |IX  |FOO                                                                                                                             |Tablelock           |GRANT
ij(C2)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |ACTIVE  |readonly    |select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |ACTIVE  |not readonly|NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ROW  |1    |X   |FOO                                                                                                                             |(1,12)              |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,13)              |GRANT
TABLE|2    |IX  |FOO                                                                                                                             |Tablelock           |GRANT
ij(C2)> autocommit off;
ij(C2)> select * from foo where a < 2;
A          
-----------
1          
ij(C2)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |ACTIVE  |readonly    |select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |ACTIVE  |not readonly|NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ROW  |1    |X   |FOO                                                                                                                             |(1,12)              |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,13)              |GRANT
ROW  |1    |S   |FOO                                                                                                                             |(1,3)               |GRANT
ROW  |1    |S   |FOO                                                                                                                             |(1,7)               |GRANT
TABLE|1    |IS  |FOO                                                                                                                             |Tablelock           |GRANT
TABLE|2    |IX  |FOO                                                                                                                             |Tablelock           |GRANT
ij(C2)> insert into foo values (2), (4);
2 rows inserted/updated/deleted
ij(C2)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |ACTIVE  |not readonly|select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |ACTIVE  |not readonly|NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ROW  |1    |X   |FOO                                                                                                                             |(1,12)              |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,13)              |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,14)              |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,15)              |GRANT
ROW  |1    |S   |FOO                                                                                                                             |(1,3)               |GRANT
ROW  |1    |S   |FOO                                                                                                                             |(1,7)               |GRANT
TABLE|1    |IS  |FOO                                                                                                                             |Tablelock           |GRANT
TABLE|2    |IX  |FOO                                                                                                                             |Tablelock           |GRANT
TABLE|2    |IX  |FOO                                                                                                                             |Tablelock           |GRANT
ij(C2)> -- this should block and result in a timeout
select * from foo;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(C2)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |ACTIVE  |not readonly|NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C2)> -- when last statement finished rolling back, this transaction should be IDLE;
select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ROW  |1    |X   |FOO                                                                                                                             |(1,12)              |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,13)              |GRANT
TABLE|2    |IX  |FOO                                                                                                                             |Tablelock           |GRANT
ij(C2)> -- this should also block
drop table foo;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(C2)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |ACTIVE  |not readonly|NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ROW  |1    |X   |FOO                                                                                                                             |(1,12)              |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,13)              |GRANT
TABLE|2    |IX  |FOO                                                                                                                             |Tablelock           |GRANT
ij(C2)> commit;
ij(C2)> disconnect;
ij> set connection c1;
ij(C1)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |ACTIVE  |not readonly|select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state
from syscs_diag.lock_table
where tableType <> 'S'
order by lockname, mode, cnt, state;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ROW  |1    |X   |FOO                                                                                                                             |(1,12)              |GRANT
ROW  |1    |X   |FOO                                                                                                                             |(1,13)              |GRANT
TABLE|2    |IX  |FOO                                                                                                                             |Tablelock           |GRANT
ij(C1)> drop table foo;
0 rows inserted/updated/deleted
ij(C1)> commit;
ij(C1)> select * from xactTable order by username, sql_text, status, type;
USERNAME                                                                                                                        |TYPE                          |STATUS  |READONLY    |SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |select * from xactTable order by username, sql_text, status, type                                                                                                                                                                                                                                                                                                                                                                                                                                                               
APP                                                                                                                             |UserTransaction               |IDLE    |readonly    |NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ij(C1)> select l.type, lockcount as cnt, mode, tablename, lockname, state
from   syscs_diag.lock_table l right outer join syscs_diag.transaction_table t
       on l.xid = t.xid where l.tableType <> 'S' and t.type='UserTransaction'
order by lockname, mode, cnt, state;
TYPE |CNT  |MODE|TABLENAME                                                                                                                       |LOCKNAME            |STATE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij(C1)> commit;
ij(C1)> -- ensure the system vti can not be modified.
drop table syscs_diag.transaction_table;
ERROR X0Y56: 'DROP TABLE' is not allowed on the System table '"SYSCS_DIAG"."TRANSACTION_TABLE"'.
ij(C1)> alter table syscs_diag.transaction_table add column x int;
ERROR 42X62: 'ALTER TABLE' is not allowed in the 'SYSCS_DIAG' schema.
ij(C1)> update syscs_diag.transaction_table set xid = NULL;
ERROR 42Y25: 'SYSCS_DIAG.TRANSACTION_TABLE' is a system table.  Users are not allowed to modify the contents of this table.
ij(C1)> delete from syscs_diag.transaction_table where 1 = 1;
ERROR 42Y25: 'SYSCS_DIAG.TRANSACTION_TABLE' is a system table.  Users are not allowed to modify the contents of this table.
ij(C1)> insert into syscs_diag.transaction_table(xid) values('bad');
ERROR 42Y25: 'SYSCS_DIAG.TRANSACTION_TABLE' is a system table.  Users are not allowed to modify the contents of this table.
ij(C1)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SYSCS_DIAG', 'TRANSACTION_TABLE', 1);
ERROR 38000: The exception 'java.sql.SQLException: 'ALTER TABLE' is not allowed in the 'SYSCS_DIAG' schema.' was thrown while evaluating an expression.
ERROR 42X62: 'ALTER TABLE' is not allowed in the 'SYSCS_DIAG' schema.
ij(C1)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SYSCS_DIAG', 'TRANSACTION_TABLE', 1, 1, 1);
0 rows inserted/updated/deleted
ij(C1)> -- ensure the old syntax still works until it is deprecated
select xid from new org.apache.derby.diag.TransactionTable() AS t where 1 = 0;
XID            
---------------
ij(C1)> update new org.apache.derby.diag.TransactionTable() set xid = NULL;
ERROR 42X08: The constructor for class 'org.apache.derby.diag.TransactionTable' cannot be used as an external virtual table because the class does not implement 'java.sql.PreparedStatement'. 
ij(C1)> delete from new org.apache.derby.diag.TransactionTable() where 1 = 0;
ERROR 42X08: The constructor for class 'org.apache.derby.diag.TransactionTable' cannot be used as an external virtual table because the class does not implement 'java.sql.PreparedStatement'. 
ij(C1)> disconnect;
ij>