These parameters control system-related SharePlex properties.
SP_SYS_HOST_NAME
This parameter is for use in environments where one SharePlex variable-data directory is shared among multiple nodes, such as clusters and systems with alternate network interfaces. It provides SharePlex the correct logical host name (global cluster package name) when any of its processes issues a name lookup, superseding the local system name. The name set with this parameter enables SharePlex to migrate properly during failover.
- Set SP_SYS_HOST_NAME on the primary node and all secondary (adoptive) nodes before you activate the configuration, and configure it into the package that fails over.
- Set this parameter in the .profiles file on Unix and Linux systems and in the Windows Registry on
Windows systems.
- On Unix and Linux systems, set this parameter through sp_ctrl in the shared variable-data directory. Setting SP_SYS_HOST_NAME through sp_ctrl sets it permanently in the variable-data directory, which is part of the failover, and it ensures that the package name exports before sp_cop starts when you are ready to begin replication.
- Set SP_SYS_HOST_NAME only on machines within the cluster.
When you issue sp_ctrl commands from a system affecting a clustered machine, use the name set with SP_SYS_HOST_NAME as the host in the [onhost] option, or set it as the default for sp_ctrl by using the host command.
For more information about how to set up SharePlex on clustered systems, see the SharePlex Installation and Setup Guide.
Default: none
Range of valid values: the character string of the package name
Takes effect: when SharePlex is restarted
SP_SYS_IN_SYNC
This parameter controls the way that SharePlex replicates tables that have key columns where a NULL value is allowed.
- When this parameter is set to the default of 0 (off), SharePlex uses the key to locate rows on the target system, whether or not any of those columns contain NULLs. This can cause an out-of-sync condition for UPDATEs and DELETEs if the key for a row contains NULLs. The row’s uniqueness cannot be assured, and it is possible for SharePlex to change the wrong row on the target system.
- When this parameter is set to 1 (on), it directs SharePlex to use the key, but only if there are no NULLs in the row’s key. If the key contains a NULL value, SharePlex uses all of the columns in the row as a simulated key to ensure that it locates the correct target row.
If you know that the keys in your application will never contain NULLs (although NULLs were declared), leave this parameter off, because it incurs more overhead with the added logic that it uses. Set this parameter on the source system, and set it before you activate the configuration.
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: when Capture and Read are restarted
SP_SYS_JOB_HISTORY_RETENTION
This parameter defines how many days information about past compare, compare using, repair, copy or append jobs will be kept for the job status and/or report commands to display.
Default: 90 days
Range of valid values: 0 or any positive integer
Takes effect: immediately
SP_SYS_JOB_HISTORY_SIZE
This parameter controls the size of the jobs status database, which resides in the data sub-directory of the SharePlex variable-data directory. The value specified determines the maximum size in kilobytes (kB) allowed for the database. When the database size exceeds the value specified, entries are deleted, oldest first. The default value is 0, specifying unlimited.
Default: 0 (unlimited)
Range of valid values: 0 or any positive integer
Takes effect: immediately
SP_SYS_SUSPEND_ON_ERROR
This parameter controls whether or not the Capture or Post process stops when it encounters a system or internal SharePlex error. The default of 1 directs the process to stop when there is an error. A setting of 0 directs the process to continue but generate a message in the Event Log.
Important: If Capture is stopped for too long, it can lose pace with Oracle, and the redo logs can wrap. If that happens, Capture reads the archive logs, but if the archive logs become unavailable, you will need to resynchronize the data.
Default: 1 (stop on errors)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately
SP_SYS_TARGET_COMPATIBILITY
This parameter enables you to preserve compatibility between different versions of SharePlex to allow for smoother migrations and upgrades. As SharePlex continues to evolve to satisfy new user requirements, features or functionality added in a new version are not always compatible with previous versions. This parameter enables replication from a higher version of SharePlex on the source system to a lower version on a target system.
By default, this parameter is set to the version of the installed SharePlex software.
- When replicating from a higher version on a source system to a lower version of SharePlex on a target, set this parameter to a value that most closely matches the version of SharePlex on the target. For example, if SharePlex is version 8.6.3 on the source and 8.6.2 on the target, set this parameter to 8.6.2.
- When replicating between identical versions, leave this parameter set to the default on both systems.
Default: The version of the installed SharePlex software
Range of valid values: SharePlex release versions from 6.0.0 to the current version, up to three numbers long (for example 8.6.3).
Takes effect: when Capture is restarted
SP_SYS_VAR_FULL
This parameter sets a threshold for available space on the disk where the SharePlex variable- data directory is installed. It prevents the queues from exceeding available disk space. If an operation being processed by Capture, Read or Import will cause available disk space to drop below the value set for this parameter, the process stops. Processing resumes when available disk space reaches the threshold set with the SP_SYS_VAR_OK parameter.
Default: 30 MB
Range of valid values: any positive integer greater than the value of SP_SYS_VAR_OK
Takes effect: immediately
SP_SYS_VAR_OK
This parameter sets the amount of available disk space at which Capture, Read or Import resumes processing after it stopped because the value for SP_SYS_VAR_FULL was reached.
Default: 50 MB
Range of valid values: any positive integer
Takes effect: immediately
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 Poster 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 Poster 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, database hashing functions are used.
- When this parameter is set to 1, the SharePlex internal checksum method is used.
Note: If compare and repair operations involve Oracle DB tables with BINARY_FLOAT, BINARY_DOUBLE, or LONG data types, comparisons are automatically done by setting the SP_DEQ_USE_SP_CKSUM parameter to 1. This can lead to increased CPU utilization, depending on the table structure and row size. To avoid this, users must exclude these columns from the comparison.
Default: 0
Range of valid values: 0 or 1
Takes effect: immediately available for the next comparison
SP_DEQ_ORA_DATE_FORMAT
Set the parameter to 1 at both the Oracle source and PostgreSQL target when you have the Oracle DATE data type mapped to the PostgreSQL TIMESTAMP(0) data type, as the Oracle DATE can store the timestamp portion. In this case, values will be read in the 'YYYY-MM-DD HH24:MI' format.
Set the parameter to 0 when you have the Oracle Date data type mapped to the PostgreSQL DATE data type, as only the date portion is stored by PostgreSQL. In this case, values will be read in the 'YYYY-MM-DD' format from the Oracle database, and the timestamp portion will be skipped during the compare/repair process.
If the same table has more than one date column, with one Oracle Date mapped to the PostgreSQL Timestamp(0) and another to the PostgreSQL Date, data with mismatched column formats will be shown as out of sync with either of the set column formats.
For example, when the parameter is set to 1 and the Oracle Date data type is mapped to the PostgreSQL Date data type, the column will be shown as out of sync. If the Oracle Date data type is mapped to the PostgreSQL Timestamp(0) data type, the column will be shown as in sync.
Default: 1
Valid Values: 0 or 1 (flag)
Takes effect: Immediately available for the next comparison
SP_DEQ_ORA_NLS_LANG
This parameter value is used to set the correct NLS_LANG for Oracle DB login, ensuring that data is fetched in the expected character set encoding.
When Oracle DB is on the source side, the NLS_LANG should be set according to the character set expected at the target DB, as repair queries will be executed on the target DB.
For example, if the target DB uses standard UTF-8 encoding, the parameter value on the source SharePlex side should be set to AL32UTF8. If the target DB uses the European 8859-1 character set, the parameter value should be set to the equivalent Oracle charset WE8ISO8859P1.
Refer to the Oracle documentation for valid NLS parameter values.
Default: AMERICAN_AMERICA.AL32UTF8
Valid values: Valid Oracle values for NLS_LANGUAGE, NLS_TERRITORY, and NLS_CHARACTERSET, in the format <NLS_LANGUAGE>_<NLS_TERRITORY>.<NLS_CHARACTERSET>
Takes effect: Immediately available for the next comparison
Copy/Append command parameters
These parameters are used by the sp_sync processes associated with the SharePlex copy/append command.
SP_OSY_COMPRESSION
This parameter adjusts the data compression level.
Default: 6
Range of valid values: 0 to 9 (9 being the highest level of compression, 0 being no compression)
Takes effect: immediately available for the next sync
SP_OSY_LOCK_TIMEOUT
This parameter set the number of seconds that the synchronization process will wait to obtain a table lock.
Default: 2
Range of valid values: 0 to 900
Takes effect: immediately available for the next sync
SP_OSY_POST_TIMEOUT
This parameter sets the number of seconds that the synchronization process will wait for the Post process to be ready and the synchronization to begin.
Default: 1800
Range of valid values: 0 to 36000 (seconds)
Takes effect: immediately available for the next sync
SP_OSY_THREADS
This parameter sets the number of pairs of synchronization processing threads, export/import, between the source and target systems. This parameter is utilized by SharePlex sync processes, e.g. the copy/append commands.
Default: 5
Range of valid values: 1 to 32
Takes effect: immediately available for the next sync
These parameters control properties of the SharePlex analyze config command.
SP_ANL_CHECKPOINT_FREQ
This parameter is one of the parameters that can be used to define the frequency of the checkpoints that are issued by the analyze config command process. The process saves its state to disk at each checkpoint to allow for fast recovery in the event that the process stops or fails before it is finished with the analysis.
Default: 20000 messages
Range of valid values: any positive integer
Takes effect: immediately
SP_ANL_CHECKPOINT_TIME
This parameter is one of the parameters that can be used to define the frequency of the checkpoints that are issued by the analyze config command process. The process saves its state to disk at each checkpoint to allow for fast recovery in the event that the process stops or fails before it is finished with the analysis.
Default: 120 seconds
Range of valid values: any positive integer
Takes effect: immediately
SP_ANL_RUN_TIME
This parameter controls how long the analyze config command runs. The amount of time that the command runs determines the amount of transaction activity that is analyzed. This parameter sets a default that you can override with the n {minutes | hours | days} option at runtime.
Default: 4320 minutes
Range of valid values: 1 to 432000 minutes
Takes effect: immediately
