Migrate Off-Row LOB Data to In-Row Storage

Some data definition language (DDL) and utility operations that work on an entire table copy all table data by rewriting the data row to match that of the target schema.

There are several ways to convert LOBs to use in-row storage:

update set column = column
alter table schema changes such as add not null, modify datatype or nullability, or drop column – row contents and layout are reorganized to accommodate the schema change. The entire row is rebuilt.

alter table ... partition by changes the partitioning schema by distributing the data rows to different partitions. Rows are reformatted as part of the data copy. This operation, however, does not change the table schema.
reorg rebuild – causes rows to be rebuilt as part of the data movement.
bcp bulk copy utility – supports tables that use in-row LOB columns.

You can migrate existing data using any of these methods, reducing space usage for the text pages and moving to in-row LOB storage.

In-Row LOB Columns and Bulk Copy
The SAP ASE bcp utility supports tables that use in-row LOB columns, storing LOB columns in-row as long as the off-row data fits your defined in-row size, and the resulting row meets the page-size limitation.
Methods for Migrating Existing Data
Since the results are all very similar, the migration method you choose depends on your situation and preference.
Guidelines for Selecting the In-Row LOB Length
The choice of the in-row LOB length affects the storage space used for the data pages, LOB pages, and the number of rows that can fit on a data page.
Identifying In-Row LOB Length Selection
Identify in-row LOB length selection to estimate the different amounts of LOB storage savings.

Parent topic: In-Row Off-Row LOB

Related concepts

In-Row LOB Columns and Bulk Copy