Creating and altering tables with Java-SQL columns

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:

If a Java-SQL column is stored off-row, the column is subject to the restrictions that apply to text and image columns:

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 ]...

NoteYou cannot change the column size of an in-row column using alter column in this Adaptive Server release.