There are several configuration parameters on both the source and target Adaptive Server that affect the performance of the migration process.
On the source Adaptive Server:
cis packet size – should be equal to max page size of the target Adaptive Server.
number of user connections – should be high enough to accommodate the migration of multiple tables simultaneously according to the value of COPY_THREADS and INDEX_THREADS.
max parallel degree – should be set to a value that is larger than the largest number of partitions in a single table. Data migration is done in parallel, and if max parallel degree is not set to a value large enough to accommodate the partitioned tables, the tables do not migrate.
number of worker processes – data migration for partitioned tables requires one worker thread per partition. Therefore, if t partitioned tables with p partitions each are migrating simultaneously, configure a total of t multiplied by p worker threads on the source Adaptive Server.
cis bulk insert batch size – controls the number of rows after which the data transfer transaction is committed. The default value is 0. Using the default value is the safest way to ensure data integrity while migrating data, but it can result in a large number of page and row locks on the source Adaptive Server. To reduce the number of locks, increase this value.
If you increase the value of cis bulk insert batch size, only a partial data migration completes if an error occurs during the process. In this situation, manually truncate the target table and restart sybmigrate.
cis bulk insert array size – controls the number of rows that are copied in bulk at one time. The default is 50 rows per batch. For faster data migration, increase this value.
If the table contains text or image columns, the data is transferred one row at a time, regardless of the value for cis bulk insert array size.
The following configuration parameters on the target Adaptive Server affect the performance of sybmigrate:
max network packet size – should be set to a value that is at least equal to max page size.
number of user connections – should be set to accommodate the migration of multiple tables in parallel and partitioned tables.
For parallel data transfer for partitioned tables, worker processes are required on the source Adaptive Server, but user connections are required on the target Adaptive Server. If you are migrating partitioned tables, set the number of user connections on the target Adaptive Server to the same value as number of worker processes on the source Adaptive Server.
number of sort buffers – the default value of 500 is sufficient during the migration process. You can increase this value when sybmigrate rebuilds the indexes, especially if you are migrating indexes on partitioned tables.
