These parameters are used by the SharePlex Post process when applying data to an Open Target (non-Oracle) target.

SP_OPX_BATCH_ENABLE

To maximize performance when applying multiple transactions of the same operation, the Post process can group the transactions and apply as a batch. The SP_OPX_BATCH_ENABLE parameter controls this feature and is enabled by default. SP_OPX_MAX_BATCH_MBYTES controls the maximum size of the batch.

Default: 1 (Enabled)

Range of valid values: 0 or 1

Takes effect: Immediately

SP_OPX_COMMIT_REDUCE_MSGS

Sets the threshold for the Commit Reduction component of the Post Enhanced Performance feature. The Commit Reduction feature enables you to configure Post to combine smaller transactions into larger ones. This reduces the number of commits and acknowledgments that must be processed. The smaller the transaction, the bigger the performance gain.

When the specified number of messages is reached, Post issues a commit. The commits of transactions whose transactional borders are within this span of messages are skipped, and those transactions are all committed as one transaction. Commit reduction is on by default. To disable it, set this parameter to a value of 1.

The parameter setting is not an absolute threshold. SharePlex will not break up a transaction across different combined transactions. Therefore, Post may need to exceed that threshold in order to include all of the operations and the commit of the last transaction in the group.

 

Default: 100 messages

Range of valid values: Any positive integer.

Takes effect: when Post is restarted

SP_OPX_CONT_ON_ERR

This parameter controls whether or not Post stops when it encounters specific ODBC errors. When this flag is set to the default of 0, Post stops for all ODBC errors. To configure Post to ignore certain errors and continue processing, list them in one of the following files (depending on the database) and then set SP_OPX_CONT_ON_ERR to 1.

mysqlmsglist

postgresmsglist

sqlservermsglist

sybasemsglist

tdmsglist

These files are located in the data subdirectory of the variable-data directory. For more information about how to configure these files, see the SharePlex Administration Guide.

For each error that it ignores, Post will log an error in the SharePlex errlog.sql file and then resume posting. If Post receives an error that is not specified in the file, the process stops.

 

Default: 0 (stop on all errors)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

SP_OPX_DISABLE_OBJECT_NUM

This parameter prevents Post from posting replicated DML and DDL operations to the target, based on the object ID of the source table. You can set this parameter if the data in a source table is invalid or corrupted, if the source table contains data types that are not supported on the target, or for any other reason that you do not want operations for a table to be reflected in the target database.

You can set this parameter for one or more tables. It prevents further replication activity on the target table(s) of a source table until you have time to resynchronize the data and reactivate the configuration file. The Post process discards all replicated messages for these tables from the post queue, and the messages do not accumulate in the queue.

Use the parameter with caution. If it is enabled and DDL or DML is executed for the source table(s), the target data will be out of date because the changes are not posted. If there are dependencies on the table(s), such as a foreign key in other tables outside the replication configuration, disabling posting will prevent the dependencies from being satisfied.

This parameter is disabled by default. To enable it, issue the following command on the target system, where the numbers shown are the object IDs of the source tables to exclude from posting.

Separate each object ID with a comma, and allow no spaces between them. To use spaces between entries, enclose the entire list within quotes, as in the following example:

sp_ctrl(sysB)> set param SP_OPX_DISABLE_OBJECT_NUM "498438, 1000, 497109"

Invalid object IDs will be ignored.

When you are ready to begin posting to the target table again, set SP_OPX_DISABLE_OBJECT_NUM to 0.

 

Default: 0 (off)

Range of valid values: list of valid object IDs separated by commas

Takes effect: immediately

SP_OPX_MAX_BATCH_MBYTES

This parameter controls the maximum size of a batched DML operation. To maximize its performance when applying SQL operations to the target, the Post process groups smaller replicated operations into one larger batched operation. Post groups replicated operations when the operations are identical and the SQL is similar. Post continues to add operations (with matching criteria) to the batch until the batch size specified by SP_OPX_MAX_BATCH_MBYTES is reached.

The SP_OPX_BATCH_ENABLE parameter enables this feature (enabled by default).

Default: 1 megabyte

Range of valid values: 1 to any positive integer

Takes effect: Immediately

SP_OPX_MSGS_IN_MEMORY

This parameter controls the amount of memory that the Post process uses. The Open Target Post process uses an internal queue to store transaction data temporarily. This parameter controls the maximum amount of memory that this internal queue uses.

 

Default: default 5000 messages

Range of valid values: integer, no maximum

Takes effect: when Post is restarted

SP_OPX_NLS_CONVERSION

This parameter controls character set conversion between an Oracle source and a non-Oracle target.

When replicating to an Open Target target (non-Oracle target), SharePlex supports replication from any Oracle Unicode character set and the US7ASCII character set. SharePlex posts data to Open Target in the Unicode character set, and therefore if the source data is Unicode or US7ASCII, no conversion on the target is required.

However, if the following are true, conversion is required on the target:

• If the character set of the source data is anything other than Oracle Unicode or US7ASCII, you must install an Oracle client on the target to perform the conversion to Unicode for posting to the target.
• If the data must be posted to the target database in any character set other than Unicode, you must install an Oracle client on the target to perform the conversion and use the target command to identify the target character set for Post to use.
• If you are replicating LOB data, conversion is required regardless of what the source character set is.

To perform conversion with an Oracle client on Linux:

1. Install an Oracle Administrator client on the target system. The client must be the Administrator installation type. The Instant Client and Runtime installation types are not supported.
2. Set ORACLE_HOME to the client installation. Set ORACLE_SID to an alias or a non-existing SID. SharePlex does not use them and a database does not have to be running.
3. SharePlex using the Linux/Unix installer for your operating system.
4. Make certain the SP_OPX_NLS_CONVERSION parameter is set to the default of 1.

To perform conversion with an Oracle client on Windows:

1. Install an Oracle Administrator client on the target system. The client must be the Administrator installation type. The Instant Client and Runtime installation types are not supported.

2. In the SharePlex Registry key \HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\QuestSoftware\SharePlex\port_number, set ORACLE_HOME to the Oracle client installation location, and set ORACLE_SID to an alias or a non-existing SID. No Oracle database is required. SharePlex only needs to use the client libraries.

3. Install SharePlex using the Windows installer.

4. Make certain the SP_OPX_NLS_CONVERSION parameter is set to the default of 1.

To apply Unicode and US7ASCII data without conversion:

If the source data is Unicode or US7ASCII and you are not replicating LOB data, no conversion or Oracle client is needed. Set the SP_OPX_NLS_CONVERSION parameter to 0 to disable conversion, and then restart Post if it is running.

 

Default: 1

Range of valid values: 0 or 1 (flag)

Takes effect: when Post is restarted.

SP_OPX_ONELINE_DEBUG

This parameter controls the oneline debug feature for the Open Target Post process.

The oneline debug feature enables you to perform debugging for out-of-sync errors without consuming a large amount of the system resources. The oneline debug logs just enough information in one line to detect where in the data stream an out-of-sync condition occurred. Once oneline debug identifies the process that is causing the problem, you can then enable regular debugging for that process.

To enable this parameter, specify the objects that you want to debug by their object ID, and separate each one by a comma.

An example is:

sp_ctrl> set sp_opo_oneline_debug 230230, 351626

The following items are logged:

• The state of the operation, which can be Process (read from queue), Skipped (commit if the Commit Reduction feature is enabled) or Applied (apply to target)
• The transaction identifier used by SharePlex
• The type of DML or DDL operation that was affected
• The object ID of the affected source table
• The row identifier of the affected row. (Oracle rowid)
• The time when the operation occurred (Oracle)
• The log sequence number and offset within the log of the affected operation
• The Oracle SCN for the transaction
• An internal SharePlex code that prevents redundant operations

 

Default: Disabled

Range of valid values: a string that forms a list of objects listed by object ID, separated by commas.

Takes effect: Immediately

SP_OPX_ONELINE_DEBUG_COLUMNS

This parameter controls whether or not column values are logged when SP_OPX_ONELINE_DEBUG is enabled. This is useful for diagnosing out-of sync conditions. To log column values for the objects specified when online debug is enabled, set SP_OPX_ONELINE_DEBUG_COLUMNS to 1.

Default: 0 (disabled)

Range of valid values: 0 or 1

Takes effect: Immediately

SP_OPX_OUT_OF_SYNC_SUSPEND

This parameter controls what SharePlex does when it encounters an out-of-sync condition, based on the following:

• INSERT: The row already exists, resulting in a Unique constraint violation error.
• UPDATE and DELETE: The row is not in the database.

Value of 0 (default)

The default Post behavior when a transaction contains an out-of-sync operation is to continue processing other valid operations in the transaction to minimize latency and keep targets as current as possible. Latency is the amount of time between when a source transaction occurs and when it is applied to the target. Different factors affect the amount of latency in replication, such as unusually high transaction volumes or interruptions to network traffic.

Post logs the SQL statement and data for the out-of-sync operation to the ID_errlog.sql log file, where ID is the database identifier. This file is in the log sub-directory of the variable-data directory on the target system.

Value of 1

A value of 1 directs Post to stop posting when it encounters an out-of-sync condition. Post logs the SQL statement and data for the out-of-sync operation to the ID_errlog.sql log file, and then stops.

When you set this parameter to a value of 1, check the status of the replication processes frequently. Stopping Post causes latency between source and target databases, and it causes data to accumulate in the replication queues, which could cause them to exceed available disk space. SharePlex provides several sp_ctrl commands for checking replication status, and it provides tools for unattended SharePlex monitoring. See the SharePlex Administration Guide for more information about how to monitor SharePlex.

 

Default: 0 (do not stop for out-of-sync conditions)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

SP_OPX_READRELEASE_INTERVAL

This parameter is used when Post is configured to post to a JMS server in transactional style (issue a JMS commit at intervals, rather than after every message as directed by the session_transacted property of the target command).

This parameter controls the read/release interval, which is how often Post purges the data of a transaction from the queue.

 

Default: 1,000

Range of valid values: any positive integer

Takes effect: when Post is restarted

SP_OPX_REDUCED_KEY

This parameter controls the content of the Post WHERE clause. Post uses a WHERE clause to find the row in the target that needs to be changed by a replicated UPDATE from the source. Different SharePlex features may require more or less data to be used in the Post WHERE clause.

• If set to 0, this parameter directs Post to construct a WHERE clause of all of the data that is sent by Capture. The data that Capture sends depends on the setting of the SP_CAP_REDUCED_KEY parameter. If you want the WHERE clause to include the values of the keys and all of the columns other than LONGs or LOBs, set both parameters to 0.

  IMPORTANT! In a peer-to-peer configuration, a setting of 0 is required.

• If set to 1, this parameter directs Post to build a WHERE clause with the key values and the before values of the columns that changed. This setting provides a good balance between replication performance and target data integrity, because it enables Post to perform a before-and-after comparison of the changed columns when constructing the WHERE clause. To be able to use this setting, the SP_CAP_REDUCED_KEY parameter must be set to 0 or 1. For more information about how SharePlex uses before and after values, see the SharePlex Administration Guide.
• If set to 2, this parameter directs Post to build a WHERE clause of only the key columns. This setting can be used to maximize posting performance. Because this setting omits the before-and-after comparison of the changed columns, you should ensure that no process or user can make changes to the target data except SharePlex. Additionally, it is recommended that you perform regular integrity verification by using the compare command.

  Note: When the SP_OPX_REDUCED_KEY parameter is set to 2 in the horizontally partitioned replication, Poster will prepare the WHERE clause with keys and before data of columns that is added for the UPDATE clause.

See also SP_CAP_REDUCED_KEY.

Default: 0

Range of Valid Values: 0, 1, 2

Takes effect: When Post is restarted

SP_OPX_SQL_CACHE_DISABLE

This parameter enables or disables the SQL Cache feature. By default, SQL Cache is on, and this parameter is set to 0. A setting of 1 disables SQL Cache. A setting of 3 disables SQL Cache for batch operations to reduce the amount of memory that Post uses. (A setting of 2 is not available.)

If you disable SQL caching, SharePlex prints the following message to the Event Log: SQL Cache disabled.

For more information about SQL Cache, see the SharePlex Administration Guide.

 

Default: 0 (on)

Range of valid values: 0, 1, or 3

Takes effect: when Post is restarted

SP_OPX_STOP_ON_DDL_ERR

This parameter controls whether or not the Post process stops when there is an error applying DDL. The default of 1 directs Post to stop for errors. An error usually indicates that the source component for which the DDL was executed does not exist in the target database, indicating the likelihood that subsequent DML changes will also fail. Stopping Post prevents the DML failures and enables you to correct the problem to keep the databases synchronized.

 

Default: 1 (on)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

SP_OPX_THREADS

This parameter controls the Transaction Concurrency component of the Post Enhanced Performance feature. When set to a value of 2 or greater, Post runs with the specified number of threads (concurrent operations).

This parameter supports SQL Server and PostgreSQL databases. Typically, it should be set to the number of cores in the processors on the system.

For more information about how to tune the performance of Post, see the SharePlex Administration Guide.

 

Default: 1 thread (disabled)

Range of valid values: Less than the Max_connection value

Takes effect: when Post is restarted

SP_OPX_TRUSTED_SOURCE

This parameter specifies the trusted source system, which contains the data that is considered to be the primary set of data in a peer-to-peer replication environment. This parameter is used in a prepared conflict resolution routine provided by SharePlex. The SP_OPX_TRUSTED_SOURCE parameter should be set to same value on both servers. For more information about conflict resolution and peer-to-peer replication, see the SharePlex Administration Guide.

Default: None

Range of valid values: Any valid host name in the SharePlex replication environment

Takes effect: When the Post process is restarted