An Adaptive Server with a 2K page configuration cannot be automatically upgraded to a 4K, 8K, or 16K configuration, nor can the dump and load backup facilities provide this upgrade through the Backup Server. Instead, data (and metadata) must be transferred, or migrated, from one server to another. To accomplish this task, use the ddlgen feature of Sybase Central™ for Adaptive Server. Adaptive Server fully supports DDL, and enables the transfer of server schema and configuration data from one server to another. In addition, a migration tool serves as a driver for the data transfer.
Once metadata has been transferred from one server to another, the migration tool is used to coordinate the data transfer. Create proxy tables at the source server for each table on the target server, and then execute a select into statement to effect the transfer from the source table, which is on local disk, to the target, which is a proxy table referencing the target server.
To facilitate this process, three significant changes to the manner in which the select into command is executed have been made:
Parallel data transfer – if the source table is partitioned and local, then the data transfer is achieved through worker threads, one per partition.
Allow bulk transfer to existing tables – since the remote tables are already in place, enable data transfer via select into even if the target table already exists. Using this syntax:
select <column_list> into existing table <table_name> from ...
existing table allows the command to operate on tables that already exitst. A check is made to ensure that the datatypes of the <column_list> match, in type and length, the data types of the target table.
Enable bulk insert arrays – when performing a bulk transfer of data from one Adaptive Server to another, Component Integration Services buffers rows internally, and asks the Open Client bulk library to transfer them as a block. The size of the array is controlled by the configuration parameter cis bulk insert array size. The default is 50 rows, and the property is dynamic, allowing it to be changed without server restart.
To achieve performance levels required, the bulk interface currently in use by Component Integration Services has been modified to support bulk insert array binding. This allows Component Integration Services to buffer a specified number of rows in local memory, and transfer them all with a single bulk transfer command. An Adaptive Server configuration property has been implemented to specify the size of the bulk insert array:
sp_configure “cis bulk insert array size”, no
where n is an integer value greater than 0. The default is 50.
Set commands can override this behavior:
set bulk array size n – similar to the configuration property cis bulk insert array size. set bulk array size n applies to the current session only. New sessions inherit the value specified in the confiugration file, but can override using this set command. n is an integer value greater than 0.
set bulk batch size n – similar to the configuration property cis bulk insert batch size. set bulk batch size n applies to the current session only. New sessions inherit the value specified in the configuration file, but can be overriden using this set command. n is an integer value greater than 0.
