CREATE Statements

The CREATE FUNCTION statement allows you to create Java functions, which you can then use in an expression.

Syntax

CREATE FUNCTION function-Name ( [ FunctionParameter [, FunctionParameter] ] * )
RETURNS DataType [ FunctionElement ] *

[ schema-Name. ] SQL92Identifier

If schema-Name is not provided, the current schema is the default schema. If a qualified procedure name is specified, the schema name cannot begin with SYS.

[ parameter-Name ] DataType

PararameterName must be unique within a function.

The syntax of DataType is described in Data Types.

Note:

Long data-types such as LONG VARCHAR, LONG VARCHAR FOR BIT DATA, CLOB, and BLOB are not allowed as parameters in a CREATE FUNCTION statement.

{ 
SPECIFIC [ schema-Name. ] SQL92Identifier
| LANGUAGE { JAVA }
| EXTERNAL NAME string
| PARAMETER STYLE JAVA
| { NO SQL | CONTAINS SQL | READS SQL DATA }
| { RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT }
 }

LANGUAGE

JAVA- the database manager will call the function as a public static method in a Java class.

EXTERNAL NAME string

String describes the Java method to be called when the function is executed, and takes the following form:

class_name.method_name

The External Name cannot have any extraneous spaces.

PARAMETER STYLE

JAVA - The function will use a parameter-passing convention that conforms to the Java language and SQL Routines specification. INOUT and OUT parameters will be passed as single entry arrays to facilitate returning values. Result sets are returned through additional parameters to the Java method of type java.sql.ResultSet[] that are passed single entry arrays.

Derby does not support long column types (for example Long Varchar, BLOB, and so on). An error will occur if you try to use one of these long column types.

NO SQL, CONTAINS SQL, READS SQL DATA

Indicates whether the function issues any SQL statements and, if so, what type.

CONTAINS SQL

Indicates that SQL statements that neither read nor modify SQL data can be executed by the function. Statements that are not supported in any function return a different error.

NO SQL

Indicates that the function cannot execute any SQL statements

READS SQL DATA

Indicates that some SQL statements that do not modify SQL data can be included in the function. Statements that are not supported in any stored function return a different error. This is the default value.

RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT

Specifies whether the function is called if any of the input arguments is null. The result is the null value.

RETURNS NULL ON NULL INPUT

Specifies that the function is not invoked if any of the input arguments is null. The result is the null value.

CALLED ON NULL INPUT

Specifies that the function is invoked if any or all input arguments are null. This specification means that the function must be coded to test for null argument values. The function can return a null or non-null value. This is the default setting.

The function elements may appear in any order, but each type of element can only appear once. A function definition must contain these elements:

• LANGUAGE
• PARAMETER STYLE
• EXTERNAL NAME

Example

CREATE FUNCTION TO_DEGREES(RADIANS DOUBLE) RETURNS DOUBLE
PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
EXTERNAL NAME 'java.lang.Math.toDegrees'

A CREATE INDEX statement creates an index on a table. Indexes can be on one or more columns in the table.

Syntax

CREATE [UNIQUE] INDEX index-Name
ON table-Name ( Simple-column-Name [ ASC | DESC ]
    [ , Simple-column-Name [ ASC | DESC ]] * )

The maximum number of columns for an index key in Derby is 16.

An index name cannot exceed 18 characters.

A column must not be named more than once in a single CREATE INDEX statement. Different indexes can name the same column, however.

Derby can use indexes to improve the performance of data manipulation statements (see Tuning Derby). In addition, UNIQUE indexes provide a form of data integrity checking.

Index names are unique within a schema. (Some database systems allow different tables in a single schema to have indexes of the same name, but Derby does not.) Both index and table are assumed to be in the same schema if a schema name is specified for one of the names, but not the other. If schema names are specified for both index and table, an exception will be thrown if the schema names are not the same. If no schema name is specified for either table or index, the current schema is used.

By default, Derby uses the ascending order of each column to create the index. Specifying ASC after the column name does not alter the default behavior. The DESC keyword after the column name causes Derby to use descending order for the column to create the index. Using the descending order for a column can help improve the performance of queries that require the results in mixed sort order or descending order and for queries that select the minimum or maximum value of an indexed column.

If a qualified index name is specified, the schema name cannot begin with SYS.

Indexes and Constraints

Unique, primary key, and foreign key constraints generate indexes that enforce or "back" the constraint (and are thus sometimes called backing indexes). If a column or set of columns has a UNIQUE or PRIMARY KEY constraint on it, you can not create an index on those columns. Derby has already created it for you with a system-generated name. System-generated names for indexes that back up constraints are easy to find by querying the system tables if you name your constraint. For example, to find out the name of the index that backs a constraint called FLIGHTS_PK:

SELECT CONGLOMERATENAME FROM SYS.SYSCONGLOMERATES,
SYS.SYSCONSTRAINTS WHERE
SYS.SYSCONGLOMERATES.TABLEID = SYSCONSTRAINTS.TABLEID 
AND CONSTRAINTNAME = 'FLIGHTS_PK'

Examples

CREATE INDEX OrigIndex ON Flights(orig_airport);
-- money is usually ordered from greatest to least,
-- so create the index using the descending order
CREATE INDEX PAY_DESC ON SAMP.EMPLOYEE (SALARY);
 -- use a larger page size for the index 
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','8192');
CREATE INDEX IXSALE ON SAMP.SALES (SALES);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize',NULL);

Page Size and Key Size

Note:

The size of the key columns in an index must be equal to or smaller than half the page size. If the length of the key columns in an existing row in a table is larger than half the page size of the index, creating an index on those key columns for the table will fail. This error only occurs when creating an index if an existing row in the table fails the criteria. After an index is created, inserts may fail if the size of their associated key exceeds the criteria.

Statement Dependency System

Prepared statements that involve SELECT, INSERT, UPDATE, UPDATE WHERE CURRENT, DELETE, and DELETE WHERE CURRENT on the table referenced by the CREATE INDEX statement are invalidated when the index is created. Open cursors on the table are not affected.

The CREATE PROCEDURE statement allows you to create Java stored procedures, which you can then call using the CALL PROCEDURE statement.

Syntax

CREATE PROCEDURE procedure-Name ( [ ProcedureParameter [, ProcedureParameter] ] * )
[ ProcedureElement ] *

[ schema-Name. ] SQL92Identifier

If schema-Name is not provided, the current schema is the default schema. If a qualified procedure name is specified, the schema name cannot begin with SYS.

[ { IN | OUT | INOUT } ] [ parameter-Name ] DataType

The default value for a parameter is IN. ParameterName must be unique within a procedure.

The syntax of DataType is described in Data Types.

Note:

Long data-types such as LONG VARCHAR, LONG VARCHAR FOR BIT DATA, CLOB, and BLOB are not allowed as parameters in a CREATE PROCEDURE statement.

{ 
SPECIFIC [ schema-Name. ] SQL92Identifier
| [ DYNAMIC ] RESULT SETS INTEGER
| LANGUAGE { JAVA }
| EXTERNAL NAME string
| PARAMETER STYLE JAVA
| { NO SQL | MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA }
 }

DYNAMIC RESULT SETS integer

Indicates the estimated upper bound of returned result sets for the procedure. Default is no (zero) dynamic result sets.

LANGUAGE

JAVA- the database manager will call the procedure as a public static method in a Java class.

EXTERNAL NAME string

String describes the Java method to be called when the procedure is executed, and takes the following form:

class_name.method_name

The External Name cannot have any extraneous spaces.

PARAMETER STYLE

JAVA - The procedure will use a parameter-passing convention that conforms to the Java language and SQL Routines specification. INOUT and OUT parameters will be passed as single entry arrays to facilitate returning values. Result sets are returned through additional parameters to the Java method of type java.sql.ResultSet [] that are passed single entry arrays.

Derby does not support long column types (for example Long Varchar, BLOB, and so on). An error will occur if you try to use one of these long column types.

NO SQL, CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA

Indicates whether the stored procedure issues any SQL statements and, if so, what type.

CONTAINS SQL

Indicates that SQL statements that neither read nor modify SQL data can be executed by the stored procedure. Statements that are not supported in any stored procedure return a different error. MODIFIES SQL DATA is the default value.

NO SQL

Indicates that the stored procedure cannot execute any SQL statements

READS SQL DATA

Indicates that some SQL statements that do not modify SQL data can be included in the stored procedure. Statements that are not supported in any stored procedure return a different error.

MODIFIES SQL DATA

Indicates that the stored procedure can execute any SQL statement except statements that are not supported in stored procedures.

The procedure elements may appear in any order, but each type of element can only appear once. A procedure definition must contain these elements:

• LANGUAGE
• PARAMETER STYLE
• EXTERNAL NAME

Example

CREATE PROCEDURE SALES.TOTAL_REVENUE(IN S_MONTH INTEGER,
IN S_YEAR INTEGER, OUT TOTAL DECIMAL(10,2))
PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA EXTERNAL NAME 
'com.acme.sales.calculateRevenueByMonth'

A schema is a way to logically group objects in a single collection and provide a unique namespace for objects.

Syntax

CREATE SCHEMA schema-Name

The CREATE SCHEMA statement is used to create a schema. A schema name cannot exceed 30 characters. Schema names must be unique within the database.

Examples

-- Create a schema for employee-related tables 
CREATE SCHEMA EMP;
 -- Create a schema for airline-related tables 
CREATE SCHEMA Flights
 -- Create a table called "Availability" in each schema 
CREATE TABLE FLIGHTS.AVAILABILITY
    (FLIGHT_ID CHAR(6) NOT NULL, SEGMENT_NUMBER INT NOT NULL,
    FLIGHT_DATE DATE NOT NULL, ECONOMY_SEATS_TAKEN INT,
    BUSINESS_SEATS_TAKEN INT, FIRSTCLASS_SEATS_TAKEN INT, 
    CONSTRAINT FLT_AVAIL_PK
    PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER, FLIGHT_DATE));
 
CREATE TABLE EMP.AVAILABILITY
    (HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL, ROOMS_TAKEN INT,
    CONSTRAINT HOTELAVAIL_PK PRIMARY KEY (HOTEL_ID, BOOKING_DATE));

A CREATE TABLE statement creates a table. Tables contain columns and constraints, rules to which data must conform. Table-level constraints specify a column or columns. Columns have a data type and can specify column constraints (column-level constraints).

For information about constraints, see CONSTRAINT clause.

You can specify a default value for a column. A default value is the value to be inserted into a column if no other value is specified. If not explicitly specified, the default value of a column is NULL. See Column Default.

You can specify storage properties such as page size for a table by calling the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure.

If a qualified table name is specified, the schema name cannot begin with SYS.

Syntax

CREATE TABLE table-Name
    ( {column-definition | Table-Level Constraint}
    [ , {column-definition | Table-Level Constraint} ] * )

column-definition:  

Simple-column-Name DataType
    [ Column-Level-Constraint ]*
    [ generated-column-spec ]
    [ Column-Level-Constraint ]*

generated-column-spec:  

[ [ WITH ] DEFAULT {ConstantExpression | NULL }
|
[ GENERATED ALWAYS AS IDENTITY 
[ ( START WITH IntegerConstant 
[ ,INCREMENT BY IntegerConstant] ) ]  ]  ]

The syntax of Data-Type is described in Data Types.

The syntaxes of Column-Level-Constraint and Table-Level Constraint are described in CONSTRAINT clause.

The IDENTITY keyword can only be specified if the data type associated with the column is one of the following exact integer types.

• SMALLINT
• INT
• BIGINT

Column Default

For the definition of a default value, a ConstantExpression is an expression that does not refer to any table. It can include constants, date-time special registers, current schemas, users, and null.

Identity column attributes

For SMALLINT, INT, and BIGINT columns with identity attributes, Derby automatically assigns increasing integer values to the column. Identity column attributes behave like other defaults in that when an insert statement does not specify a value for the column, Derby automatically provides the value. However, the value is not a constant; Derby automatically increments the default value every time a row is inserted. Also, unlike other defaults, you are not allowed to insert a value directly into or update an identity column.

By default, the initial value of an identity column is 1, and the amount of the increment is 1. You can specify non-default values for both the initial value and the interval amount when you define the column with the key words START WITH and INCREMENT BY. And if you specify a negative number for the increment value, Derby decrements the value with each insert. If this value is 0, or positive, Derby increments the value with each insert.

The maximum and minimum values allowed in identity columns are determined by the data type of the column. Attempting to insert a value outside the range of values supported by the data type raises an exception.

Table 1. Maximum and Minimum Values for Columns with Generated Column Specs

Automatically generated values in an identity column are unique. Use a primary key or unique constraint on a column to guarantee uniqueness. Creating an identity column does not create an index on the column.

The IDENTITY_VAL_LOCAL function is a non-deterministic function that returns the most recently assigned value for an identity column. See IDENTITY_VAL_LOCAL for more information.

Note:

Specify the schema, table, and column name using the same case as those names are stored in the system tables--that is, all upper case unless you used delimited identifiers when creating those database objects.

Derby keeps track of the last increment value for a column in a cache. It also stores the value of what the next increment value will be for the column on disk in the AUTOINCREMENTVALUE column of the SYS.SYSCOLUMNS system table. Rolling back a transaction does not undo this value, and thus rolled-back transactions can leave "gaps" in the values automatically inserted into an identity column. Derby behaves this way to avoid locking a row in SYS.SYSCOLUMNS for the duration of a transaction and keeping concurrency high.

When an insert happens within a triggered-SQL-statement, the value inserted by the triggered-SQL-statement into the identity column is available from ConnectionInfo only within the trigger code. The trigger code is also able to see the value inserted by the statement that caused the trigger to fire. However, the statement that caused the trigger to fire is not able to see the value inserted by the triggered-SQL-statement into the identity column. Likewise, triggers can be nested (or recursive). An SQL statement can cause trigger T1 to fire. T1 in turn executes an SQL statement that causes trigger T2 to fire. If both T1 and T2 insert rows into a table that cause Derby to insert into an identity column, trigger T1 cannot see the value caused by T2's insert, but T2 can see the value caused by T1's insert. Each nesting level can see increment values generated by itself and previous nesting levels, all the way to the top-level SQL statement that initiated the recursive triggers. You can only have 16 levels of trigger recursion.

Examples

CREATE TABLE HOTELAVAILABILITY
    (HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL,
    ROOMS_TAKEN INT DEFAULT 0, PRIMARY KEY (HOTEL_ID, BOOKING_DATE));
 -- the table-level primary key definition allows you to
-- include two columns in the primary key definition 
PRIMARY KEY (hotel_id, booking_date))
 -- assign an identity column attribute to an INTEGER
-- column, and also define a primary key constraint
-- on the column 
CREATE TABLE PEOPLE
    (PERSON_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY
    CONSTRAINT PEOPLE_PK PRIMARY KEY, PERSON VARCHAR(26));
 -- assign an identity column attribute to a SMALLINT
-- column with an initial value of 5 and an increment value
-- of 5. 
CREATE TABLE GROUPS
    (GROUP_ID SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY 
    (START WITH 5, INCREMENT BY 5), ADDRESS VARCHAR(100), PHONE VARCHAR(15));

Note:

For more examples of CREATE TABLE statements using the various constraints, see CONSTRAINT clause.

A trigger defines a set of actions that are executed when a database event occurs on a specified table. A database event is a delete, insert, or update operation. For example, if you define a trigger for a delete on a particular table, the trigger's action occurs whenever someone deletes a row or rows from the table.

Along with constraints, triggers can help enforce data integrity rules with actions such as cascading deletes or updates. Triggers can also perform a variety of functions such as issuing alerts, updating other tables, sending e-mail, and other useful actions.

You can define any number of triggers for a single table, including multiple triggers on the same table for the same event.

You can create a trigger in any schema except SYS. The trigger need not reside in the same schema as the table on which it is defined.

If a qualified trigger name is specified, the schema name cannot begin with SYS.

Syntax

CREATE TRIGGER TriggerName
AFTER 
{ INSERT | DELETE | UPDATE [ OF column-Name [, column-Name]* ]
ON table-Name
[ ReferencingClause ]
FOR EACH { ROW | STATEMENT } MODE DB2SQL 
Triggered-SQL-statement

ReferencingClause:  

REFERENCING
{
{ OLD | NEW } [ AS ] correlation-Name [ { OLD | NEW } [ AS ] correlation-Name ] | 
{ OLD_TABLE | NEW_TABLE } [ AS ] Identifier [ { OLD_TABLE | NEW_TABLE }
[AS] Identifier ] 
}

Before or After: When Triggers Fire

Triggers fire after all constraints have been satisfied and after the changes have been applied to the target table. Also called After triggers, they can be either row or statement triggers (see Statement versus Row Triggers).

Insert, Delete, or Update: What Causes the Trigger to Fire

A trigger is fired by one of the following database events, depending on how you define it (in Syntax above, see the third line):

• INSERT
• UPDATE
• DELETE

You can define any number of triggers for a given event on a given table. For update, you can specify columns.

Referencing Old and New Values: The Referencing Clause

Many triggered-SQL-statements need to refer to data that is currently being changed by the database event that caused them to fire. The triggered-SQL-statement might need to refer to the new (post-change or "after") values.

Derby provides you with a number of ways to refer to data that is currently being changed by the database event that caused the trigger to fire. The easiest way to refer to the changed data in the triggered-SQL-statement is use the transition variables or transition tables.

The referencing clause allows you to provide a correlation name or alias for these transition variables by specifying OLD/NEW AS correlation-Name .

For example, if you add the following clause to the trigger definition:

REFERENCING OLD AS DELETEDROW

you can then refer to this correlation name in the triggered-SQL-statement:

DELETE FROM HotelAvailability WHERE hotel_id = DELETEDROW.hotel_id

The OLD and NEW transition variables map to a java.sql.ResultSet with a single row.

Note:

Only row triggers (see Statement versus Row Triggers) can use the transition variables. INSERT row triggers cannot reference an OLD row. DELETE row triggers cannot reference a NEW row.

For statement triggers, transition tables serve as a table identifier for the triggered-SQL-statement or the trigger qualification. The referencing clause allows you to provide a correlation name or alias for these transition tables by specifying OLD_TABLE/NEW_TABLE AS correlation-Name

For example:

REFERENCING OLD_TABLE AS DeletedHotels

allows you to use that new identifier (DeletedHotels) in the triggered-SQL-statement:

DELETE FROM HotelAvailability WHERE hotel_id IN
    (SELECT hotel_id FROM DeletedHotels)

The old and new transition tables map to a java.sql.ResultSet with cardinality equivalent to the number of rows affected by the triggering event.

Note:

Only statement triggers (see Statement versus Row Triggers) can use the transition tables. INSERT statement triggers cannot reference an OLD table. DELETE statement triggers cannot reference a NEW table.

The referencing clause can designate only one new correlation or identifier and only one old correlation or identifier. Row triggers cannot designate an identifier for a transition table and statement triggers cannot designate a correlation for transition variables.

Statement versus Row Triggers

You must specify whether a trigger is a statement trigger or a row trigger:

• statement triggers

  A statement trigger fires once per triggering event and regardless of whether any rows are modified by the insert, update, or delete event.

• row triggers

  A row trigger fires once for each row affected by the triggering event. If no rows are affected, the trigger does not fire.

Note:

An update that sets a column value to the value that it originally contained (for example, UPDATE T SET C = C) causes a row trigger to fire, even though the value of the column is the same as it was prior to the triggering event.

Triggered-SQL-statement

The action defined by the trigger is called the triggered-SQL-statement (in Syntax above, see the last line). It has the following limitations:

• It must not contain any dynamic parameters (?).
• It must not create, alter, or drop the table upon which the trigger is defined.
• It must not add an index to or remove an index from the table on which the trigger is defined.
• It must not add a trigger to or drop a trigger from the table upon which the trigger is defined.
• It must not commit or roll back the current transaction or change the isolation level.
• It must not execute a CALL statement.

The triggered-SQL-statement can reference database objects other than the table upon which the trigger is declared. If any of these database objects is dropped, the trigger is invalidated. If the trigger cannot be successfully recompiled upon the next execution, the invocation throws an exception and the statement that caused it to fire will be rolled back.

For more information on triggered-SQL-statements, see the Derby Developer's Guide.

Order of Execution

When a database event occurs that fires a trigger, Derby performs actions in this order:

• It performs constraint checking (primary key, unique key, foreign key, check).
• It performs the insert, update, or delete.
• It fires after triggers.

When multiple triggers are defined for the same database event for the same table for the same trigger time (before or after), triggers are fired in the order in which they were created.

Examples

-- Statements and after triggers:
 
CREATE TRIGGER FLIGHTSDELETE
AFTER DELETE ON FLIGHTS
REFERENCING OLD_TABLE AS DELETEDFLIGHTS
FOR EACH STATEMENT MODE DB2SQL
DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN
(SELECT FLIGHT_ID FROM DELETEDFLIGHTS);
 
CREATE TRIGGER FLIGHTSDELETE3
AFTER DELETE ON FLIGHTS
REFERENCING OLD AS OLD
FOR EACH ROW MODE DB2SQL
DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;

Note:

You can find more examples in the Derby Developer's Guide.

Trigger Recursion

The maximum trigger recursion depth is 16.

Related Information

Special system functions that return information about the current time or current user are evaluated when the trigger fires, not when it is created. Such functions include:

• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• CURRENT_USER
• SESSION_USER
• USER

Views are virtual tables formed by a query. A view is a dictionary object that you can use until you drop it.

Views are not updatable.

If a qualified view name is specified, the schema name cannot begin with SYS.

Syntax

CREATE VIEW view-Name
    [ ( Simple-column-Name [, Simple-column-Name] * ) ]
AS Query 

A view definition can contain an optional view column list to explicitly name the columns in the view. If there is no column list, the view inherits the column names from the underlying query. All columns in a view must be uniquely named.

Examples

CREATE VIEW SAMP.V1 (COL_SUM, COL_DIFF)
    AS SELECT COMM + BONUS, COMM - BONUS
    FROM SAMP.EMPLOYEE;
 
CREATE VIEW SAMP.VEMP_RES (RESUME)
    AS VALUES 'Delores M. Quintana', 'Heather A. Nicholls', 'Bruce Adamson';
 
CREATE VIEW SAMP.PROJ_COMBO 
    (PROJNO, PRENDATE, PRSTAFF, MAJPROJ) 
    AS SELECT PROJNO, PRENDATE, PRSTAFF, MAJPROJ
    FROM SAMP.PROJECT UNION ALL 
SELECT PROJNO, EMSTDATE, EMPTIME, EMPNO 
    FROM SAMP.EMP_ACT 
    WHERE EMPNO IS NOT NULL;

Statement Dependency System

View definitions are dependent on the tables and views referenced within the view definition. DML (data manipulation language) statements that contain view references depend on those views, as well as the objects in the view definitions that the views are dependent on. Statements that reference the view depend on indexes the view uses; which index a view uses can change from statement to statement based on how the query is optimized. For example, given:

CREATE TABLE T1 (C1 DOUBLE PRECISION);
 
CREATE FUNCTION SIN (DATA DOUBLE) 
    RETURNS DOUBLE EXTERNAL NAME 'java.lang.Math.sin'
    LANGUAGE JAVA PARAMETER STYLE JAVA;
 
CREATE VIEW V1 (C1) AS SELECT SIN(C1) FROM T1;

the following SELECT:

SELECT * FROM V1

is dependent on view V1, table T1, and external scalar function SIN.