These parameters control properties of the SharePlex compare command.
SP_DEQ_IGNORE_ORACLE_ERROR_NUM
This parameter allows the user to specify an Oracle Error Number to ignore. The specified errror number will be ignored up to 500 times. This parameter is mainly for debugging purposes and should not be use with the repair or repair1 option.
Default: 0 (none)
Range of valid values: Any valid Oracle Error Number, e.g. 1406
Takes effect: immediately available for the next comparison
SP_DEQ_IP_MAPPING
This parameter maps the IP addresses when Network Address Translation (NAT) is implemented between the source and target systems. When NAT is implemented, the target IP address on the source machine (for example, 192.168.32.10) and the IP address on the target machine (for example, 213.18.123.103) can be different, which can cause confusion when the compare and repair commands are issued. When you set the parameter SP_DEQ_IP_MAPPING on the target machine, the IP address 192.168.32.10 is considered equivalent to the IP address 213.18.123.103, thus allowing Post to process the compare message and the compare sp_declt client process to finish the Compare process.
Set the SP_DEQ_IP_MAPPING parameter on the target system as in the following example:
sp_ctrl> set param SP_DEQ_IP_MAPPING "192.168.32.10:213.18.123.103"
Note: Use the appropriate IP addresses for your source and target machines. The IP addresses listed above are only an example.
In addition, the SP_DEQ_IP_MAPPING parameter accepts multiple mappings, separated by a comma, as in the following example:
sp_ctrl> set param SP_DEQ_IP_MAPPING "192.168.32.10:213.18.123.103,10.2.3.4:50.64.70.88"
Default: None
Range of valid values: one or more valid IP addresses, separated with a comma
Takes effect: When the next compare command is issued
SP_DEQ_LOG_FILESIZE
This parameter controls the size of the SQL log file that is generated by the compare server and the compare client.
Default: 50 MB
Range of valid values: Any size greater than 0.
Takes effect: Immediately available for the next comparison.
SP_DEQ_MALLOC
This parameter controls the fetch batch size. The batch size controls the number of rows that SharePlex selects at once for comparison. Larger batch sizes increase processing speed but require more memory. The value is divided equally by the number of compare threads to be used, and then the batch size is recalculated based on all column sizes added together.
Default: 500 (MB)
Range of valid values: 50 to 32,767 (MB)
Takes effect: Immediately available for the next comparison.
SP_DEQ_PARALLELISM
This parameter manages the select statement Degree of Parallelism hint.
When SP_DEQ_PARRALLISM is set to zero, no parallel hint will be used. A user-provided hint from the command line will have higher precedence.
Default: 2
Range of valid values: 0 to 500
Takes effect: immediately available for the next comparison.
SP_DEQ_PARTIAL_REPAIR_CLR_OOS
This parameter enables the repair process to clear out-of-sync messages from the statusdb for a table after a compare-repair of only a subset of rows of that table, such as row subsets specified with a WHERE clause or a Oracle [sub]partition name (including horizontally partitioned replication).
Important! Set this parameter on the target system. It has no effect on the source system.
This parameter works as follows:
- Value of 0 (disabled, the default): The out-of-sync messages are retained in the statusdb after a subset of the rows is compared and repaired. The messages are retained so that users are aware there may be out-of-sync rows that were not qualified by the selection criteria.
- Value of 1 (enabled): The out-of-sync messages are cleared after a subset of the rows is compared and repaired. Enable this parameter only if you know that the rows in the subset are the only ones in the table that are out-of-sync.
NOTE: To be certain all of the out-of-sync rows in a table are repaired, run the compare-repair for the entire table.
Default: 0 (disabled)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately available for the next comparison.
SP_DEQ_PART_TABLE_UPDATE
This parameter affects the behavior of the repair using and repair commands when they are issued for Oracle partitioned target tables.
- When this parameter is set to the default of 0, partitioned Oracle target tables are repaired using INSERTs and DELETEs only. Repairs requiring UPDATEs are converted to a DELETE followed by an INSERT to prevent errors when an UPDATE could cause a row to change partitions and row movement is not enabled for the table.
- When this parameter is set to 1, partitioned Oracle tables are repaired using INSERTs, UPDATEs, and DELETEs as appropriate. Use this mode only when you know UPDATEs will not result in a row changing partitions in the target table or when row movement is enabled for the target table.
Set this parameter on the target system.
Default: 0 (do not repair with UPDATEs)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately available for the next comparison
SP_DEQ_READ_BUFFER_SIZE
This parameter applies only to LOB and LONG columns during a compare/repair operation. It adjusts the size of the buffer that holds the fetched LOB or LONG data when those columns are being compared and repaired. The value of the parameter should be adjusted based on the available system memory and data size.
Default: 1 MB
Range of valid values: any integer between 1 and 100 (values in MB)
Takes effect: Immediately available for the next comparison
SP_DEQ_ROW_LOCK_THRESHOLD
This parameter controls whether the repair process locks the entire target table or only the out-of-sync rows when performing a repair. When the number of out-of-sync rows is less than the specified value, only the out-of-sync rows are locked for the repair. This enables the Post process to continue posting to other rows in the table. If the number of out-of-sync rows on the source table is less than this value, they are locked; otherwise, a brief lock on the table is taken to ensure a read-consistent value.
Default: 1000 out of sync rows
Range of valid values: Any positive integer
Takes effect: Immediately available for the next comparison
SP_DEQ_SKIP_LOB
This parameter determines whether or not LOBs are included in the compare/repair processing.
- When the parameter is set to the default of 0, the compare processes include LOBs in their processing.
- When the parameter is set to 1, only non-LOB columns are compared and repaired. If LOBs are not modified once inserted, you can speed up processing by setting this parameter to 1.
Set this parameter on the source system.
Default: 0
Range of valid values: 0 or 1 (flag)
Takes effect: Immediately available for the next comparison
SP_DEQ_THREADS
This parameter controls the number of processing threads used by the compare commands on the source system. Each thread creates a sp_declt process on the target machine.
The default of 2 threads has proven to be the best number for UP machines, but you might obtain performance improvements with up to 15 threads. Too many threads causes diminishing benefits, especially if it causes large tables to be compared at the same time.
The value for the thread count is independent of the number of tables to be compared, and SharePlex will not generate more threads than there are tables to be compared. If the machine has only one processor, set this parameter to a value of 1. Do not set it to more than the number of processors on the system.
Default: 2 threads
Range of valid values: 1 to 15
Takes effect: immediately available for the next comparison
SP_DEQ_TIMEOUT
This parameter controls the connection time out for the compare and repair commands. Because the sp_desvr process relies on the replication queues to instantiate the sp_declt process(es) on the target system, the sp_declt process cannot start until all previous messages in the queues have been processed. If the delay is longer than the time out specified with this parameter, sp_desvr exits and returns an error. If you know the queues are more than 30 minutes backlogged, you can increase this parameter as needed.
Default: 1800 seconds (30 minutes)
Range of valid values: 121 seconds or greater
Takes effect: immediately available for the next comparison
SP_DEQ_USE_SP_CKSUM
This parameter determines whether or not Oracle ora_hash() is used in compare.
- When this parameter is set to the default value of 0, Oracle ora_hash() is used.
- When this parameter is set to 1, the SharePlex internal checksum method is used.
Default: 0
Range of valid values: 0 or 1
Takes effect: immediately available for the next comparison