Variable-length columns in DOL tables

For a single, variable-length column in a DOL table, the minimum overhead for each row is:

• Six bytes for the initial row overhead.

• Two bytes for the row length.

• Two bytes for the column offset table at the end of the row. Each column offset entry is two bytes. There are n such entries, where n is the number of variable-length columns in the row.

The total overhead is 10 bytes. There is no adjust table for DOL rows. The actual variable-length column size is:

column length + 10 bytes overhead

DOL tables with variable-length columns must have an offset of fewer than 8191 bytes for all inserts to succeed. For example, this insert fails because the offset for columns c2, c3, and c4 is 9010, which exceeds the maximum of 8191 bytes:

create table t1(
	c1 int not null,
	c2 varchar(5000) not null
	c3 varchar(4000) not null
	c4 varchar(10) not null
	... more fixed length columns)
cvarlen varchar(nnn)) lock datarows

Adaptive Server allows data-only locked (DOL) columns to use a row offset of up to 32767 bytes for wide, variable-length DOL rows if it is configured for a logical page size of 16K.

Enable wide, variable-length DOL rows for each database using:

sp_dboption database_name, 'allow wide dol rows', true

sp_dboption 'allow wide dol rows' has no effect on user databases with logical page sizes smaller than 16K; Adaptive Server cannot create a wide, variable-length DOL rows on pages smaller than 16384 bytes.

This example configures the pubs2 database on a server using a page size of 16K to use wide, variable-length DOL rows:

1. Enable wide, variable-length rows for the pubs2 database:

   sp_dboption pubs2, 'allow wide dol rows', true
   

2. Create the book_desc table, which includes wide, variable-length DOL columns that begin after the row offset of 8192:

   create table book_desc
   (title varchar(80) not null,
   title_id varchar(6) not null,
   author_desc char(8192) not null,
   book_desc varchar(5000) not null)
   lock datarows
   

You must use the version of bcp shipped with Adaptive Server version 15.7 and later to bulk-copy-in data that contains wide, variable-length DOL rows. You must configure the database receiving the data to accept wide, variable-length DOL rows (that is, bcp does not copy wide rows into databases for which you have not enabled allow wide dol rows).

See the Installation Guide for your platform for details about downgrading an Adaptive Server that uses wide, variable-length rows.

Database and transaction log dumps retain their setting for allow wide dol rows, which is imported into the database into which you are loading the dump (if this database does not already have the option set).

For example, if you load a transaction log dump named my_table_log.dmp, which has allow wide dol rows set to true, into database big_database, for which you have not set allow wide dol rows, my_table.log retains its setting of true for allow wide dol rows after the load to big_database completes.

However, if the database or transaction log dump does not have allow wide dol rows set, but the database into which you are loading the dump does, allow wide dol rows remains set.

You cannot load a dump of a database that has allow wide dol rows enabled to versions of Adaptive Server earlier than 15.7.

You can use proxy tables with wide, variable-length DOL rows.

When you create proxy tables (regardless of their row length), the controlling Adaptive Server is the one on which you execute the create table or create proxy table command. Adaptive Server executes these commands on the server to which you are connected. However, Adaptive Server executes data manipulation statements (insert, delete) on the server on which the data is stored, and the user’s local server only formats the request and then sends it; the local server does not control anything.

Adaptive Server creates proxy tables as though they are created on the local server, even though the data resides on remote servers. If the create proxy_table command creates a DOL table that contains wide, variable-length rows, the command succeeds only if the database in which you are creating the proxy table has allow wide dol rows set to true.

When you insert or update data in proxy tables, Adaptive Server ignores the local database’s setting for allow wide dol rows. The server where the data resides determines whether an insert or update succeeds

The following restrictions apply whether you are using alter table to change a locking scheme or using select into to copy data into a new table.

For servers that use page sizes other than 16K pages, the maximum length of a variable-length column in an APL table is less than that for a DOL table, so you can convert the locking scheme of an APL table with a maximum sized variable-length column to DOL. You cannot, however, convert a DOL table containing at least one maximum sized variable-length column to an APL table.

On servers that use 16K pages, APL tables can store substantially larger sized variable-length columns than DOL tables. You can convert tables from DOL to APL, but you cannot convert from APL to DOL.

These restrictions on locking-scheme conversions occur only if data in the source table exceeds the limits of the target table. If this occurs, Adaptive Server raises an error message while transforming the row format from one locking scheme to the other. If the table is empty, no such data transformation is required, and the lock-change operation succeeds. However, subsequent inserts or updates to the table, users may see errors caused by limitations on the column or row size for the target schema of the altered table.

For DOL tables that use variable-length columns, arrange the columns so that the longest columns are placed toward the end of the table definition. This allows you to create tables with much larger rows than if the large columns appear at the beginning of the table definition. For instance, in a 16K page server, the following table definition is acceptable:

create table t1 (
	c1 int not null,
	c2 varchar(1000) null,
	c3 varchar(4000) null,
	c4 varchar(9000) null) lock datarows

However, the following table definition typically is unacceptable for future inserts. The potential start offset for column c2 is greater than the 8192-byte limit because of the preceding 9000-byte c4 column:

create table t2 (
	c1 int not null,
	c4 varchar(9000) null,
	c3 varchar(4000) null,
	c2 varchar(1000) null) lock datarows

The table is created, but future inserts may fail.