Java DB

Apache Derby

Derby Reference Manual

Derby Getting Started
Derby Reference Manual
Derby Developer's Guide
Derby Performance Tuning
Derby Server and Admin Guide
Derby Tools and Utilities
Derby Reference Manual
-SQL language reference

-Capitalization and special characters
-SQL identifiers

-Rules for SQL92 identifiers
-SQL92Identifier

-Qualifying dictionary objects

-column-Name
-correlation-Name
-new-table-Name
-schemaName
-Simple-column-Name
-synonym-Name
-table-Name
-view-Name
-index-Name
-constraint-Name
-cursor-Name
-TriggerName
-
-RoleName
-Interaction with the dependency system
-ALTER TABLE
-CALL (PROCEDURE)
-CREATE statements

-CREATE FUNCTION
-CREATE INDEX
-CREATE PROCEDURE
-CREATE ROLE
-CREATE SCHEMA
-CREATE SEQUENCE
-CREATE SYNONYM
-CREATE TABLE
-CREATE TRIGGER
-CREATE TYPE
-CREATE VIEW

-DECLARE GLOBAL TEMPORARY TABLE
-DELETE
-DROP statements

-DROP FUNCTION
-DROP INDEX
-DROP PROCEDURE
-DROP ROLE
-DROP SCHEMA
-DROP SEQUENCE
-DROP SYNONYM
-DROP TABLE
-DROP TRIGGER
-DROP TYPE
-DROP VIEW

-GRANT
-INSERT
-LOCK TABLE
-RENAME statements

-RENAME COLUMN
-RENAME INDEX
-RENAME TABLE

-REVOKE
-SET statements

-SET ISOLATION
-SET ROLE
-SET SCHEMA

-SELECT
-UPDATE

-CONSTRAINT clause

-
-Table-level constraint
-References specification
-searchCondition

-FOR UPDATE clause
-FROM clause
-GROUP BY clause
-HAVING clause
-ORDER BY clause
-The result offset and fetch first clauses
-USING clause
-WHERE clause
-WHERE CURRENT OF clause

-SQL expressions

-SelectExpression
-TableExpression

-

-NEXT VALUE FOR expression
-VALUES expression
-Expression precedence
-Boolean expressions
-Dynamic parameters

-Dynamic parameters example
-Where dynamic parameters are allowed

-JOIN operations

-INNER JOIN operation
-LEFT OUTER JOIN operation
-RIGHT OUTER JOIN operation
-CROSS JOIN operation
-NATURAL JOIN operation

-Query
-ScalarSubquery
-TableSubquery

-Built-in functions

-Standard built-in functions
-Aggregates (set functions)
-ABS or ABSVAL function
-ACOS function
-ASIN function
-ATAN function
-ATAN2 function
-AVG function
-BIGINT function
-CASE expressions
-CAST function
-CEIL or CEILING function
-CHAR function
-COALESCE function
-Concatenation operator
-COS function
-COSH function
-COT function
-COUNT function
-COUNT(*) function
-CURRENT DATE function
-CURRENT_DATE function
-CURRENT ISOLATION function
-CURRENT_ROLE function
-CURRENT SCHEMA function
-CURRENT TIME function
-CURRENT_TIME function
-CURRENT TIMESTAMP function
-CURRENT_TIMESTAMP function
-CURRENT_USER function
-DATE function
-DAY function
-DEGREES function
-DOUBLE function
-EXP function
-FLOOR function
-HOUR function
-IDENTITY_VAL_LOCAL function
-INTEGER function
-LCASE or LOWER function
-LENGTH function
-LN or LOG function
-LOG10 function
-LOCATE function
-LTRIM function
-MAX function
-MIN function
-MINUTE function
-MOD function
-MONTH function
-NULLIF expressions
-PI function
-RADIANS function
-RANDOM function
-RAND function
-ROW_NUMBER function
-RTRIM function
-SECOND function
-SESSION_USER function
-SIGN function
-SIN function
-SINH function
-SMALLINT function
-SQRT function
-SUBSTR function
-SUM function
-TAN function
-TANH function
-TIME function
-TIMESTAMP function
-TRIM function
-UCASE or UPPER function
-USER function
-VARCHAR function
-XMLEXISTS operator
-XMLPARSE operator
-XMLQUERY operator
-XMLSERIALIZE operator
-YEAR function

-Built-in system functions

-SYSCS_CHECK_TABLE
-SYSCS_GET _DATABASE _PROPERTY
-SYSCS_GET _RUNTIMES TATISTICS
-SYSCS_G ET_USER _ACCESS
-SYSCS_G ET_XPLA IN_MODE
-SYSCS_G ET_XPLA N_SCHEMA

-Built-in system procedures

-SYSCS_B ACKUP_D ATABASE
-SYSCS_BAC KUP_DATAB ASE_NOWAIT
-SYSCS_BACKUP_DAT ABASE_AND_ENABLE _LOG_ARCHIVE_MODE
-SYSCS_BACKUP_DATAB ASE_AND_ENABLE_LOG ARCHIVE_MODE_NOWAIT
-SYSCS_EMP TY_STATEM ENT_CACHE
-SYSCS_CH ECKPOINT _DATABASE
-SYSCS_COMPRESS_TABLE
-SYSCS_INP LACE_COMP RESS_TABLE
-SYSCS_DISA BLE_LOG_AR CHIVE_MODE
-SYSCS_EXPORT_TABLE
-SYSCS_EXPOR T_TABLE_LOB S_TO_EXTFILE
-SYSCS_EXPORT_QUERY
-SYSCS_EXPOR T_QUERY_LOB S_TO_EXTFILE
-SYSCS_IMPORT_DATA
-SYSCS_IMPOR T_DATA_LOBS FROM_EXTFILE
-SYSCS_IMPORT_TABLE
-SYSCS_IMPORT _TABLE_LOBS_ FROM_EXTFILE
-SYSCS_F REEZE_D ATABASE
-SYSCS_U NFREEZE DATABASE
-SYSCS_REL OAD_SECUR ITY_POLICY
-SYSCS_SET _DATABASE _PROPERTY
-SYSCS_SET _RUNTIMES TATISTICS
-SYSCS_SET _STATISTI CS_TIMING
-SYSCS_S ET_XPLA N_SCHEMA
-SYSCS_S ET_XPLA IN_MODE
-SYSCS_S ET_USER _ACCESS
-SYSCS_U PDATE_S ATISTICS

-SYSCS_DIAG diagnostic tables and functions
-Data types

-Built-In type overview
-Numeric types

-Numeric type overview
-Numeric type promotion in expressions
-Storing values of one numeric data type in columns of another numeric data type
-Scale for decimal arithmetic

-Data type assignments and comparison, sorting, and ordering Part 2
-BIGINT data type
-BLOB data type
-CHAR data type
-CHAR FOR BIT DATA data type
-CLOB data type
-DATE data type
-DECIMAL data type
-DOUBLE data type
-DOUBLE PRECISION data type
-FLOAT data type
-INTEGER data type
-LONG VARCHAR data type
-LONG VARCHAR FOR BIT DATA data type
-NUMERIC data type
-REAL data type
-SMALLINT data type
-TIME data type
-TIMESTAMP data type
-User-defined types
-VARCHAR data type
-VARCHAR FOR BIT DATA data type
-XML data type

-Argument matching
-SQL reserved words
-Derby support for SQL-92 features
-Derby system tables

-SYSALIASES
-SYSCHECKS
-SYSCOLPERMS
-SYSCOLUMNS
-SYSCONGLOMERATES
-SYSCONSTRAINTS
-SYSDEPENDS
-SYSFILES
-SYSFOREIGNKEYS
-SYSKEYS
-SYSPERMS
-SYSROLES
-SYSROUTINEPERMS
-SYSSCHEMAS
-SYSSEQUENCES
-SYSSTATISTICS
-SYSSTATEMENTS
-SYSTABLEPERMS
-SYSTABLES
-SYSTRIGGERS
-SYSVIEWS

-XPLAIN style tables

-SYSXPLAIN_STATEMENTS
-
-SYSXPLAIN_RESULTSETS
-
-SYSXPLAIN_SCAN_PROPS
-SYSXPLAIN_SORT_PROPS

-Derby exception messages and SQL states

-SQL error messages and exceptions Part 2 Part 3 Part 4 Part 5 Part 6 Part 7

-JDBC reference

-java.sql.Driver interface

-java. sql. Driver. getPropertyInfo method

-java. sql. DriverManager. getConnection method

-Derby database connection URL syntax
-Syntax of database connection URLs for applications with embedded databases
-Additional SQL syntax
-Attributes of the Derby database connection URL

-java.sql.Connection interface

-java. sql. Connection. setTransactionIsolation method
-java. sql. Connection. setReadOnly method
-java. sql. Connection. isReadOnly method
-Connection functionality not supported

-java. sql. DatabaseMetaData interface

-DatabaseMetaData result sets
-java. sql. DatabaseMetaData. getProcedureColumns method
-Parameters to getProcedureColumns
-Columns in the ResultSet returned by getProcedureColumns
-java. sql. DatabaseMetaData. getBestRowIdentifier method

-java.sql.Statement interface

-ResultSet objects
-Autogenerated keys

-java. sql. CallableStatement interface

-CallableStatements and OUT Parameters
-CallableStatements and INOUT Parameters

-java. sql. PreparedStatement interface

-Prepared statements and streaming columns

-java.sql.ResultSet interface

-ResultSets and streaming columns

-java. sql. ResultSetMetaData interface
-java.sql.SQLException class
-java.sql.SQLWarning class
-java.sql.Savepoint interface
-Mapping of java. sql. Types to SQL types

-Mapping of java. sql. Blob and java. sql. Clob interfaces

-Notes on mapping of java. sql. Blob and java. sql. Clob interfaces

-JDBC Package for Connected Device Configuration/Foundation Profile (JSR 169)
-JDBC 4.0-only features

-Refined subclasses of SQLException
-java. sql. Connection interface: JDBC 4. 0 features
-java. sql. DatabaseMetaData interface: JDBC 4. 0 features
-java. sql. Statement interface: JDBC 4. 0 features
-javax. sql. DataSource interface: JDBC 4. 0 features
-java.sql.SQLXML interface
-JDBC escape keyword for call statements
-JDBC escape syntax
-JDBC escape syntax for LIKE clauses
-JDBC escape syntax for fn keyword
-JDBC escape syntax for outer joins
-JDBC escape syntax for time formats
-JDBC escape syntax for date formats
-JDBC escape syntax for timestamp formats

-Setting attributes for the database connection URL

-bootPassword= key attribute
-collation= collation attribute
-create= true attribute
-createFrom= path attribute
-databaseName= nameofDatabase attribute
-dataEncryption= true attribute
-drop= true attribute
-encryptionKey= key attribute
-encryptionProvider= providerName attribute
-encryptionAlgorithm= algorithm attribute
-failover= true attribute
-logDevice= logDirectoryPath attribute
-newEncryptionKey= key attribute
-newBootPassword= newPassword attribute
-password= userPassword attribute
-restoreFrom= path attribute
-rollForwardRecoveryFrom= path attribute
-securityMechanism= value attribute
-shutdown= true attribute
-slaveHost= hostname attribute
-slavePort= portValue attribute
-startMaster= true attribute
-startSlave= true attribute
-stopMaster= true attribute
-stopSlave= true attribute
-territory= ll_CC attribute
-traceDirectory= path attribute
-traceFile= path attribute
-traceFileAppend= true attribute
-traceLevel= value attribute
-upgrade= true attribute
-user= userName attribute
-ssl= sslMode attribute
-Creating a connection without specifying attributes

-Derby property reference

-Scope of Derby properties
-Dynamic and static properties
-Derby properties

-derby. authentication. builtin. algorithm
-derby. authentication. ldap. searchAuthDN
-derby. authentication. ldap. searchAuthPW
-derby. authentication. ldap. searchBase
-derby. authentication. ldap. searchFilter
-
-
-derby. connection. requireAuthentication
-derby. database. defaultConnectionMode
-derby. database. forceDatabaseLock
-
-
-
-derby. database. readOnlyAccessUsers
-derby. database. sqlAuthorization
-derby.infolog.append
-derby. jdbc. xaTransactionTimeout
-
-derby. language. logStatementText
-
-
-derby. locks. escalationThreshold
-derby.locks.monitor
-
-derby. replication. logBufferSize
-derby. replication. maxLogShippingInterval
-derby. replication. minLogShippingInterval
-
-
-derby. storage. minimumRecordSize
-
-derby. storage. pageReservedSpace
-
-
-
-
-
-
-derby. stream. error. logSeverityLevel
-derby.system.bootAll
-
-derby.system.home
-derby.user.UserName

-Caching user DNs

-J2EE Compliance: Java Transaction API and javax. sql Interfaces

-Notes on Product Behavior

-Recovered Global Transactions
-XAConnections, user names and passwords

-javax.sql: JDBC Interfaces

-Stand-alone tools and utilities
-JDBC implementation classes

-JDBC driver
-Data Source Classes

-Miscellaneous utilities and interfaces

-Supported territories
-Derby limitations

-Limitations for database manager values
-DATE, TIME, and TIMESTAMP limitations
-Limitations on identifier length
-Numeric limitations
-String limitations
-XML limitations

SYSTRIGGERS system table

Describes the database's triggers.

Column Name	Type	Length	Nullability	Contents
TRIGGERID	CHAR	36	false	unique identifier for the trigger
TRIGGERNAME	VARCHAR	128	false	name of the trigger
SCHEMAID	CHAR	36	false	id of the trigger's schema (join with SYSSCHEMAS. SCHEMAID)
CREATIONTIMESTAMP	TIMESTAMP		false	time the trigger was created
EVENT	CHAR	1	false	'U' for update, 'D' for delete, 'I' for insert
FIRINGTIME	CHAR	1	false	'B' for before 'A' for after
TYPE	CHAR	1	false	'R' for row, 'S' for statement
STATE	CHAR	1	false	'E' for enabled, 'D' for disabled
TABLEID	CHAR	36	false	id of the table on which the trigger is defined
WHENSTMTID	CHAR	36	true	used only if there is a WHEN clause (not yet supported)
ACTIONSTMTID	CHAR	36	true	id of the stored prepared statement for the triggered-SQL-statement (join with SYSSTATEMENTS. STMTID)
REFERENCEDCOLUMNS	org.apache.derby.catalog. ReferencedColumns: This class is not part of the public API.		true	descriptor of the columns referenced by UPDATE triggers
TRIGGERDEFINITION	LONG VARCHAR		true	text of the action SQL statement
REFERENCINGOLD	BOOLEAN		true	whether or not the OLDREFERENCINGNAME, if non-null, refers to the OLD row or table
REFERENCINGNEW	BOOLEAN		true	whether or not the NEWREFERENCINGNAME, if non-null, refers to the NEW row or table
OLDREFERENCINGNAME	VARCHAR	128	true	pseudoname as set using the REFERENCING OLD AS clause
NEWREFERENCINGNAME	VARCHAR	128	true	pseudoname as set using the REFERENCING NEW AS clause

Any SQL text that is part of a triggered-SQL-statement is compiled and stored in SYSSTATEMENTS. ACTIONSTMTID and WHENSTMTID are foreign keys that reference SYSSTATEMENTS.STMTID. The statements for a trigger are always in the same schema as the trigger.

Parent topic: Derby system tables

javadb@jdbcurl.com