An Adaptive Server Enterprise 12.5 server with a 2K page configuration cannot be automatically upgraded to a 4K, 8K or 16K configuration, nor can the dump / 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 Enterprise. Version 12.5 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 the metadata has been transferred from one server to another, the migration tool is used to coordinate the data transfer. Do this by creating 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 is local, then the data transfer is achieved through worker threads, one per partition.
Allow bulk transfer to existing tables: since the remote tables will already be in place, thanks to the migration tool, it is necessary to enable data transfer via select into even if the target table already exists. This is done with new syntax: select <column_list> into existing table <table_name> from ... The option existing table is new, and allows the command to operate on tables that have previously been created. 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 Adaptive Server Enterprise to another Adaptive Server Enterprise, CIS buffers rows internally, and asks the Open Client bulk library to transfer them as a block. The size of the array is controlled by a new configuration parameter cis bulk insert array size. The default is 50 rows, and the property is dynamic, allowing it to be changed without server reboot.
To achieve performance levels required, the bulk interface currently in use by CIS has been modified to support bulk insert array binding. This allows CIS to buffer a specified number of rows in local memory, and transfer them all with a single bulk transfer command. An Adaptive Server Enterprise configuration property has been implemented to specify the size of the bulk insert array:
sp_configure "cis bulk insert array size", n
where n is an integer value greater than 0. The default is 50.
