When you create or alter tables with Java-SQL columns, you can specify any installed Java class as a column datatype. You can also specify how the information in the column is to be stored. Your choice of storage options affects the speed with which Adaptive Server references and updates the fields in these columns.
Column values for a row typically are stored “in-row,” that is, consecutively on the data pages allocated to a table. However, you can also store Java-SQL columns in a separate “off-row” location in the same way that text and image data items are stored. The default value for Java-SQL columns is off-row.
If a Java-SQL column is stored in-row:
Objects stored in-row are processed more quickly than objects stored off-row.
An object stored in-row can occupy up to approximately 16K bytes, depending on the page size of the database server and other variables. This includes its entire serialization, not just the values in its fields. A Java object whose runtime representation is more than the 16K limit generates an exception, and the command aborts.
If a Java-SQL column is stored off-row, the column is subject to the restrictions that apply to text and image columns:
Objects stored off-row are processed more slowly than objects stored in-row.
An object stored off-row can be of any size—subject to normal limits on text and image columns.
An off-row column cannot be referenced in a check constraint.
Similarly, do not reference a table that contains an off-row column in a check constraint. Adaptive Server allows you to include the check constraint when you create or alter the table, but issues a warning message at compile time and ignores the constraint at runtime.
You cannot include an off-row column in the column list of a select query with select distinct.
You cannot specify an off-row column in a comparison operator, in a predicate, or in a group by clause.
Partial syntax for create table with the in row/off row option is:
create table...column_name datatype
[default {constant_expression | user | null}]
{[{identity | null | not null}]
[off row | [ in row [ ( size_in_bytes ) ] ]...
size_in_bytes specifies the maximum size of the in-row column. The value can be as large as 16K bytes. The default value is 255 bytes.
The maximum in-row column size you enter in create table must include the column’s entire serialization, not just the values in its fields, plus minimum values for overhead.
To determine an appropriate column size that includes overhead and serialization values, use the datalength system function. datalength allows you to determine the actual size of a representative object you intend to store in the column.
For example:
select datalength (new class_name(...))
where class_name is an installed Java-SQL class.
Partial syntax for alter table is:
alter table...{add column_name datatype
[default {constant_expression | user | null}]
{identity | null} [ off row | [ in row ]...
You cannot change the column size of an in-row column
using alter column in this Adaptive Server release.
