ij> xa_datasource 'wombat';
ij> ---------------------------------------------
----- a single connection and 1 phase commit
-----
xa_connect ;
ij> xa_start xa_noflags 0;
ij> xa_getconnection;
ij(XA)> -- Global transactions can not have hold cursor over commit. And hence we need to make sure the holdability is false for all jdks
----- In jdk13 and lower, this Brokered Connection has its holdability false over commit so we are fine. 
----- In jdk14 and higher, this Brokered Connection has its holdability true over commit. In order to set it to false, we have NoHoldForConnection 
----- NoHoldForConnection uses setHoldability api on Connection to set the holdability to false. But this api exists only for jdk14 and higher
----- And that is why, in jkd13 master, we see an exception nosuchmethod 
NoHoldForConnection;
ij(XA)> drop table foo;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'FOO' because it does not exist.
ij(XA)> create table foo (a int);
0 rows inserted/updated/deleted
ij(XA)> insert into foo values (0);
1 row inserted/updated/deleted
ij(XA)> select * from foo;
A          
-----
0          
ij(XA)> run resource '/org/apache/derbyTesting/functionTests/tests/store/global_xactTable.view';
ij(XA)> create view global_xactTable as
select 
    cast(global_xid as char(2)) as gxid,
    status,
    case when first_instant is NULL then 'NULL' else 'false' end as readOnly,
    cast (username as char(10)) as username,
    type 
from syscs_diag.transaction_table;
0 rows inserted/updated/deleted
ij(XA)> select * from global_xactTable where gxid is not null order by gxid;
GXID |STATUS |READ& |USERNAME |TYPE                          
-----
(0 |ACTIVE |false |APP |UserTransaction               
ij(XA)> xa_end xa_success 0;
ij(XA)> xa_commit xa_1phase 0;
ij(XA)> xa_datasource 'wombat' shutdown;
ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: Database 'wombat' shutdown.
ij(XA)> ---------------------------------------------
----- two interleaving connections and prepare/commit prepare/rollback
-----
xa_datasource 'wombat';
ij(XA)> xa_connect user 'sku' password 'testxa' ;
ij(XA)> xa_start xa_noflags 1;
ij(XA)> xa_getconnection;
ij(XA)> -- Global transactions can not have hold cursor over commit. And hence we need to make sure the holdability is false for all jdks
----- In jdk13 and lower, this Brokered Connection has its holdability false over commit so we are fine. 
----- In jdk14 and higher, this Brokered Connection has its holdability true over commit. In order to set it to false, we have NoHoldForConnection 
----- NoHoldForConnection uses setHoldability api on Connection to set the holdability to false. But this api exists only for jdk14 and higher
----- And that is why, in jkd13 master, we see an exception nosuchmethod 
NoHoldForConnection;
ij(XA)> insert into APP.foo values (1);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_suspend 1;
ij(XA)> xa_start xa_noflags 2;
ij(XA)> insert into APP.foo values (2);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_suspend 2;
ij(XA)> xa_start xa_resume 1;
ij(XA)> insert into APP.foo values (3);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_suspend 1;
ij(XA)> xa_start xa_resume 2;
ij(XA)> insert into APP.foo values (4);
1 row inserted/updated/deleted
ij(XA)> select * from APP.global_xactTable where gxid is not null order by gxid;
GXID |STATUS |READ& |USERNAME |TYPE                          
-----
(1 |ACTIVE |false |SKU |UserTransaction               
(2 |ACTIVE |false |SKU |UserTransaction               
ij(XA)> xa_end xa_success 2;
ij(XA)> xa_start xa_resume 1;
ij(XA)> insert into APP.foo values(5);
1 row inserted/updated/deleted
ij(XA)> xa_end xa_success 1;
ij(XA)> xa_prepare 1;
ij(XA)> xa_prepare 2;
ij(XA)> -- both transactions should be prepared
select * from APP.global_xactTable where gxid is not null order by gxid;
GXID |STATUS |READ& |USERNAME |TYPE                          
-----
(1 |PREPARED |false |SKU |UserTransaction               
(2 |PREPARED |false |SKU |UserTransaction               
ij(XA)> xa_recover xa_startrscan;
Recovered 2 in doubt transactions
Transaction 1 : {ClientXid: formatID(2), gtrid_length(6), bqual_length(6), data(776F6D62 6174776F 6D626174)}
Transaction 2 : {ClientXid: formatID(1), gtrid_length(6), bqual_length(6), data(776F6D62 6174776F 6D626174)}
ij(XA)> xa_recover xa_noflags;
Recovered 0 in doubt transactions
ij(XA)> xa_commit xa_2Phase 1;
ij(XA)> xa_rollback 2;
ij(XA)> -- check results
xa_start xa_noflags 3;
ij(XA)> select * from APP.global_xactTable where gxid is not null order by gxid;
GXID |STATUS |READ& |USERNAME |TYPE                          
-----
(3 |IDLE |NULL |SKU |UserTransaction               
ij(XA)> select * from APP.foo;
A          
-----
0          
1          
3          
5          
ij(XA)> xa_end xa_success 3;
ij(XA)> xa_prepare 3;
ij(XA)> -- should fail with XA_NOTA because we prepared a read only transaction 
xa_commit xa_1Phase 3;
IJ ERROR: XAER_NOTA 
ij(XA)> -- DERBY-246 xa_end after connection close should be ok.  
----- Also to reuse xaconnection in the same global transaction
xa_getconnection;
ij(XA)> xa_start xa_noflags 4;
ij(XA)> create table APP.derby246  (i int);
0 rows inserted/updated/deleted
ij(XA)> insert into APP.derby246 values(1);
1 row inserted/updated/deleted
ij(XA)> disconnect;
ij> xa_getconnection;
ij(XA)> insert into APP.derby246 values(2);
1 row inserted/updated/deleted
ij(XA)> disconnect;
ij> xa_end xa_success 4;
ij> xa_prepare 4;
ij> xa_commit xa_2phase 4;
ij> -- now connect with a local connection to make sure locks are released 
----- and our values are there. Should see two rows
connect 'wombat';
ij(CONNECTION1)> select * from APP.derby246;
I          
-----
1          
2          
ij(CONNECTION1)> disconnect;
ij>