Replication Server automatically sets the Sybase-recommended default values of several parameters. You can change the values of these parameters to tune replication performance.
RTL automatically sets the Sybase-recommended default values for dsi_cdb_max_size, dsi_compile_max_cmds, dsi_bulk_threshold, dsi_command_convert, and dsi_compile_retry_threshold. However, you can specify your own values to tune performance in your replication environment:
See Replication Server Reference Manual > Replication Server Commands > alter connection for full descriptions of the parameters.
dsi_bulk_threshold specifies the number of net-row change commands after compilation has occurred on a table for a command type, that when reached, triggers Replication Server to use bulk copy-in on that table for the same command type. The default is 20 net row change commands.
Default is 20 net row change commands.
alter connection to IQSRVR.iqdb set dsi_bulk_threshold to ‘15’ go
dsi_cdb_max_size specifies, in megabytes (MB), the maximum size of a net-change database that Replication Server can generate during RTL processing.
Default is 1024MB.
alter connection to IQSRVR.iqdb set dsi_cdb_max_size to ‘2048’ go
Replication Server uses full incremental compilation for real-time loading to Sybase IQ. With full incremental compilation, if the number of commands in the compiled transaction segment within a net-change database instance exceeds the dsi_compile_max_cmds threshold, or if the net-change database instance size exceeds the dsi_cdb_max_size threshold, Replication Server instructs the net-change database instance to send its transaction to the replicate database and release the memory that the instance consumed.
dsi_compile_max_cmds specifies, in number of commands, the maximum size of a group of transactions and commands that Replication Server can compile into one compiled transaction. When RTL reaches the maximum group size for the current group that it is compiling, RTL starts a new group. Replication Server creates a net-change database instance to store the compiled transaction. Replication Server increases the net-change database size to accommodate the maximum number of commands that dsi_compile_max_cmds allows for a group. When Replication Server reaches the maximum group size for the current group that it is compiling, Replication Server transfers the compiled transaction to the worktables in the replicate database, releases the memory consumed by that specific net-change database instance, starts a new group and creates a new net-change database instance for the new group.
If there is no more data to read, and even if the group does not reach the maximum number of commands, RTL completes grouping the current set of transactions into the current group.
Default is 10,000 commands.
alter connection to IQSRVR.iqdb set dsi_compile_max_cmds to ‘50000’ go
dsi_compile_retry_threshold specifies a threshold value for the number of commands in a group. If the number of commands in a group containing failed transactions is smaller than the value of dsi_compile_retry_threshold, Replication Server does not retry processing the group in RTL mode, and saves processing time, thus improving performance. Instead, Replication Server switches to continuous replication mode for the group. Continuous replication mode sends each logged change to the replicate database according to the primary database log order.
Default is 100 commands.
You need not suspend and resume database connections when you set dsi_compile_retry_threshold. The parameter takes effect immediately after you execute the command.
alter connection to IQSRVR.iqdb set dsi_compile_retry_threshold to '200' go
See Replication Server Administration Guide Volume 2 > Exceptions and Error Handling > Data Server Error Handling > Row Count Validation > Control Row Count Validation.
dsi_command_convert specifies how to convert a replicate command. A combination of these operations specifies the type of conversion:
Combinations of operations for dsi_command_convert include i2none, u2none, d2none, i2di, t2none, and u2di. The operation before conversion precedes the “2” and the operations after conversion are after the “2”. For example:
d2none – do not replicate the delete command. With this option, you need not customize the rs_delete function string if you do not want to replicate delete operations.
i2di,u2di – convert both insert and update to delete followed by insert, which is equivalent to an autocorrection. If you disable row count validation by setting dsi_row_count _validation off, Sybase recommends that you set dsi_command_convert to i2di,u2di to avoid duplicate key errors and allow autosynchronization of databases during replication.
t2none – do not replicate truncate table.
Default for dsi_command_convert is none, which means there is no command conversion.
alter connection to IQSRVR.iqdb set dsi_command_convert to ‘i2di,u2di’ go