SQL-J Language Reference
Page 42 of 121

PROPERTIES clause

A PROPERTIES clause, or list of properties, is an optional part of an SQL-J CREATE TABLE statement, CREATE INDEX statement, CONSTRAINT clause, FROM clause, TableExpression, or INSERT statement.

Properties specified in a PROPERTIES clause fall into one of the following categories:

Syntax

The basic syntax of a PROPERTIES clause is as follows:

PROPERTIES propertyName = value [, propertyName = value]*

NOTE: If you specify the same property multiple times within a PROPERTIES clause, an exception is thrown.

Storage-Related Properties

For CREATE TABLE, CREATE INDEX, and CONSTRAINT, a PROPERTIES clause allows you to specify storage properties for the table or index being created and overrides the properties currently set on a database-wide or system-wide basis. These properties include:

For indexes, you can set only cloudscape.storage.initialPages and cloudscape.storage.pageSize. For tables, you can set cloudscape.storage.initialPages, cloudscape.storage.minimumRecordSize, cloudscape.storage.pageSize, or cloudscape.storage.pageReservedSpace.

NOTE: Any mistyped or unused properties are ignored; Cloudscape does not provide any error checking.

For more information, see Conglomerate-Specific Properties in Tuning Cloudscape.

-- set the page size to 262144 and the reserved space to 0%
-- for this table only
CREATE TABLE Maps
    (country_ISO_code CHAR(2),
    mapIsBig BOOLEAN,
    map SERIALIZE(JBMSTours.serializabletypes.Picture))
PROPERTIES cloudscape.storage.pageSize=262144,     cloudscape.storage.pageReservedSpace=0

-- set the page size for the index to 2048
CREATE UNIQUE BTREE INDEX Maps_Unique ON
    maps(country_ISO_code, mapIsBig)
PROPERTIES cloudscape.storage.pageSize = 2048

To verify properties used for a specific table or index, use the static methods of COM.cloudscape.database.PropertyInfo (aliased as PropertyInfo). For more information, see Verifying Conglomerate-Specific Properties in the Cloudscape Developer's Guide.

Optimizer Override Properties

For a PROPERTIES clause in a FROM clause, you can specify:

For a PROPERTIES clause in a TableExpression, you can specify for a specific table:

For more information, see Chapter 6, "Optimizer Overrides", in Tuning Cloudscape.

Bulk Insert Properties

You can use a PROPERTIES clause to set the insertMode to bulkInsert or replace in an INSERT statement.

  • insertMode=bulkInsert

    Setting this property to bulkInsert enables fast load of bulk data if the table is empty and is not in a target database. The entire table is locked.

    If the table being inserted into also appears in the FROM clause, bulkInsert is ignored and Cloudscape performs an ordinary insert.

  • insertMode=replace

    Setting this property to replace enables fast load of bulk data if the table is not empty, is not a target database, and is not a published table in a source database. The new data replaces any existing data. If you did not set this property, you would have to first delete all the rows from the table, and Cloudscape would log all deletes and would have to delete each from every index on the table. Instead, when you set insertMode to replace, Cloudscape uses minimal logging, and a separate delete step is not required.

    The entire table is locked.

    If the table in which the data is being replaced also appears in the FROM clause, an exception is raised.

    If the table being inserted into has any primary or unique constraint that are referenced by foreign keys in other tables, and those constraints have not been turned off with the SET CONSTRAINTS statement, at the end of the bulk insert (replace), Cloudscape checks the constraints. If any of the foreign key constraints are not satisfied, an exception is thrown, and the statement is rolled back. Any constraints which have been turned off by the user are not checked until the user explicitly turns them back on.

    If any column is defined to have a default value of AUTOINCREMENT, Cloudscape resets its internal counter to the initial value defined for the column; since Cloudscape is replacing data, values in the column with the autoincrement default "start over" at the beginning (by default, 1).

For more information on importing external data, see the Cloudscape Tools and Utilities Guide.