By accident information about the various diagnostic tables was removed
from the initial contribution of the Derby documentation. These are
virtual tables that retrieve various types of information from a running
database.
The intention is that these tables in a future release will have a
regular table name, rather than the old Cloudscape Java VTI syntax.
Something like
select * from SYSCS_DIAG.LOCK_TABLE
Below is a listing of the diagnostic tables, note they can be used in
joins like any other table expression, e.g. joining the lock table with
the transaction table can be useful. The javadoc comments for the
various classes has more information on the columns and their meanings.
Dan.
Lock Table - list of transaction row and table locks in the database
select * from new org.apache.derby.diag.LockTable() AS LT
Transaction Table - list of active transactions in the table
select * from new org.apache.derby.diag.TransactionTable() AS TT
Statement Cache - list of SQL statements in the cache
select * from new org.apache.derby.diag.StatementCache() AS SC
Error Messages - list of all the error messages and SQL states
select * from new org.apache.derby.diag.ErrorMessages() AS EM
SpaceTable - space usage information about a table
select * from new org.apache.derby.diag.SpaceTable('APP', 'T1') AS ST
Error Log Reader - Extract information from the error log, I think this
requires some properties to be set to be useful.
select * from new org.apache.derby.diag.ErrorLogReader() AS ELR
Statement Duration - uses information in the error log to determine how
long statements took to execute - again requires some extra properties
select * from new org.apache.derby.diag.StatementDuration() AS SD
