DERBY 4509 related query

[Jayaram Subramanian <rs...@gmail.com>]

Hi,
In the process of converting autoincrement.sql to JUNIT, i encountered the
following the set of statements

create table t1 (x int, yyy int generated always as identity (start with
0));
autocommit off;
insert into t1 (x) values (1);
insert into t1 (x) values (2);
select * from t1;
-- should see only locks on t1, no locks on system catalogs.
select * from syscs_diag.lock_table order by tablename, type desc, mode,
cnt;
What is the output to be validated for the query against the
syscs_diag.lock_table? When i tried running the queries in IJ the
syscs_diag.lock_table query returned me a recordset with 31 rows...  The
comment indicates locks on t1 and no locks on system catalogs.. How can i
distinguish  lock and no lock?

With Regards
Jayaram

Re: DERBY 4509 related query

[Bryan Pendleton <bp...@amberpoint.com>]

> I think you can check this by going through the result and verify that
> all rows have tablename T1.

Or, equivalently, issue:

   select count(*) from syscs_diag.lock_table where tablename <> 'T1'

and assert that the returned count is equal to 0.

Definitely it would be nice to have some comments in the test case,
no matter what solution you choose, explaining what is being tested,
along the lines of

   // Verify that the generation of values for column YYY is not
   // holding locks on any system catalogs, or on any tables other than T1.

thanks,

bryan

Re: DERBY 4509 related query

[Knut Anders Hatlen <Kn...@Sun.COM>]

Jayaram Subramanian <rs...@gmail.com> writes:

> Hi,
> In the process of converting autoincrement.sql to JUNIT, i encountered the
> following the set of statements
>  
> create table t1 (x int, yyy int generated always as identity (start with  0));
> autocommit off;
> insert into t1 (x) values (1);
> insert into t1 (x) values (2);
> select * from t1;
> -- should see only locks on t1, no locks on system catalogs.
> select * from syscs_diag.lock_table order by tablename, type desc, mode, cnt;
> What is the output to be validated for the query against the
> syscs_diag.lock_table? When i tried running the queries in IJ the
> syscs_diag.lock_table query returned me a recordset with 31 rows...  The
> comment indicates locks on t1 and no locks on system catalogs.. How can i
> distinguish  lock and no lock?

Hi Jayaram,

I think you can check this by going through the result and verify that
all rows have tablename T1. You check no lock on system catalogs by
checking that there is no row with a tablename that equals one of the
system tables, but no explicit check for that is needed when you already
know that all rows have tablename T1.

-- 
Knut Anders