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
PostgreSQL parameters control and tune various aspects of replication for PostgreSQL databases.
SP_CAP_INCLUDE_UNCHANGED_COL
This parameter controls whether or not unchanged columns are included in the after image of an UPDATE operation. By default, SharePlex only includes the changed values in the after image.
Default: 0 (off)
Range of valid values: 0 or 1
Takes effect: immediately
SP_CAP_REDUCED_KEY
This parameter controls which parts of an operation in the WAL file are sent by Capture to the target for use by Post. Different SharePlex features may require more or less data to be made available to the Post process.
- If set to 0, this parameter directs Capture to send all of the data that PostgreSQL writes to the WAL files other than TEXT. This setting sends the most data across the network, but is required to support certain SharePlex features.
- If set to 1 (the default), this parameter directs Capture to send the key values and, for UPDATEs, the before and after values of columns that were changed in the operation. This setting is the default. 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. For more information about how SharePlex uses before and after values, see the SharePlex Administration Guide.
- If set to 2, this parameter directs Capture to send the key values and, for UPDATEs, the after values of the columns that changed to the target. This setting sends the least amount of data across the network and also enables Post to only use the key values when posting data, which improves performance.
Default: 1
Range of Valid Values: 0, 1, 2
Takes effect: Immediately
SP_CAP_ONELINE_DEBUG
This parameter controls the oneline debug feature for the Capture 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_cap_oneline_debug 230230, 351626
The following items are logged:
- 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 time when the operation occurred (PostgreSQL)
- The LSN value 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_CAP_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 show capture command. This parameter works in conjunction with the SP_CAP_CHECKPOINT_TIME parameter.
Default: 40000 messages
Range of valid values: any positive integer
Takes effect: immediately
SP_CAP_CHECKPOINT_TIME
This parameter works in conjunction with the SP_CAP_CHECKPOINT_FREQ parameter. It defines the time delay, in seconds, before the Capture process checkpoints. If the value set for this parameter is reached before the value set for SP_CAP_CHECKPOINT_FREQ, it triggers the checkpoint. (Check pointing saves the state of the process in case it is needed for failure recovery.)
Default: 120 seconds
Range of valid values: any positive integer
Takes effect: immediately
SP_CAP_DENIED_SESSION_PG
This parameter is applicable for SharePlex PostgreSQL Capture only. It controls whether all transactions for a user session should be ignored or filtered by the Capture process.
If set to 0, Capture will not ignore any transactions, whether or not a user session is bound to the origin name.
If set to 1, Capture will ignore all transactions for a user session which are bound to the origin name in the format ‘sp_deny_anyString’. The sp_deny_ prefix must be used, and you can insert a string in place of 'anyString'. For example:sp_deny_1.
The user needs to create (if it does not exist) and bind the PostgreSQL user session to the origin name by calling the PostgreSQL functions ‘pg_replication_origin_create ()’ and ‘pg_replication_origin_session_setup ()’.
Examples:
IMPORTANT: Ignoring transactions on the source machine may lead to an out-of-sync condition.
Default: 0
Range of valid values: 0 or 1
Takes effect: immediately
How to use this parameter:
Before the designated user performs the transaction in the source database, issue the following command on source sp ctrl: (For PostgreSQL as a source and target).
sp_ctrl>set param SP_CAP_DENIED_SESSION_PG 1
NOTE: The parameter is live. After setting this parameter, users need to bind the PostgreSQL session to the origin by calling the pg_replication_origin_session_setup() function. All operations executed on source database after this will be ignored until the parameter is unset. The SP_CAP_DENIED_SESSION_PG is a live parameter and can be changed any time.
To unset the parameter, run the below command:
sp_ctrl>reset param SP_CAP_DENIED_SESSION_PG
Perform the following steps to bind more than 10 PostgreSQL sessions simultaneously to the different origins:
-
Navigate to PostgreSQL data directory (cd /var/lib/pgsql/13/data/).
-
Edit the postgresql.conf file by uncommenting the max_replication_slots Parameter (It is by default commented, user need to uncomment it & set it to more than maximum number of simultaneous sessions user want to bind to different origins).
-
Restart the database.
|
Notes:
-
The syntax for adding a valid origin is sp_deny_name, the sp_deny_ prefix is required before the user-defined name of origin.
-
The max_replication_slots value should be more than simultaneous user sessions who are going to do session setup. [For eg, if user is going to do origin setup for 100 sessions, set the max_replication_slots value greater than 100 ] |
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_OCT_REDUCED_KEY parameter. If you want the WHERE clause to include the values of the keys and all of the columns other than TEXT, set both parameters to 0.
- 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_OCT_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.
Default: 0
Range of Valid Values: 0, 1, 2
Takes effect: When Post is restarted
SP_OPX_XML_MARK_NULL
This parameter controls how Null values are displayed when the user selects XML as the target output format for Kafka. JSON format should always be NULL. Set the SP_OPX_XML_MARK_NULL parameter to 1 on the Kafka target to display the null value as xsi:nil="true".
Default: 1
Range of Valid Values: 0 or 1
Takes effect: Process restart
SP_OPX_CREATE_ORIGIN_PG
This parameter is applicable for Open Post only when the target is PostgreSQL. It is used to create a replication origin in the PostgreSQL database and bind the Post user session with the created origin. It is useful in bi-directional replication(peer to peer relication) to prevent operations posted by the Post process from looping back. If enabled, the Capture process ignores such operations posted by the Post process. By default, it is disabled.
Note: If this parameter is enabled, each Post process creates an origin, and the number of origins created depends upon the max_replication_slots parameter of the PostgreSQL database. As a result, if the number of post-processes exceeds this parameter value, increase it and restart the database.
Default: 0 (Disabled)
Range of valid values: 0 or 1
Takes effect: When Post is restarted
SP_OPX_LOG_CONFLICT
This parameter enables the logging of information about successful conflict resolution procedures to the shareplex_conf_log table. This applies only to the prepared routines that are provided by SharePlex.
-
A setting of 1 enables the logging of conflict resolution to the shareplex_conf_log table.
Note: A setting of 1 will not update the column existing_timestamp (when existing data is not replaced) in the shareplex_conf_log table.
-
A setting of 2 enables the logging of conflict resolution to the shareplex_conf_log table with Post query for additional meta data.
Using LeastRecentRecord or MostRecentRecord prepared routines Post will query the target database for the timestamp column of the existing record. The query result is logged into the existing_timestamp column of the shareplex_conf_log table.
Note: A setting of 2 may affect the performance of Post as a result of making the query.
Default: 0 (Disabled)
Range of valid values: 0, 1, or 2
Takes effect: when Post is restarted
SP_OPO_HINTS_LIMIT
This parameter controls the maximum number of hints (table/index combinations) that can be listed in the hints file. Use hints only if you see that Post is doing full table scans on tables where there are defined indexes. Using a large number of hints can reduce the performance of the Post process.
Default: 100 hints
Range of valid values: any positive integer
Takes effect: when Post is restarted
SP_ORD_FIRST_FIND
This parameter controls how the Read process checks column conditions to determine whether or not a replicated row change satisfies them.
- At the default of 1, when a row change satisfies a column condition, SharePlex does not check any other column conditions to see if that row change also satisfies any of them.
- At a value of 0, SharePlex sends the data to all target systems where the column conditions are satisfied.
For more information about horizontal partitioning, see the SharePlex Administration Guide.
Default: 1 (on)
Range of valid values: 0 or 1 (flag)
Takes effect: when Read is restarted
SP_ORD_HP_IN_SYNC
This parameter is used for horizontally partitioned replication to ensure that data is replicated properly when a value for a column in a column condition changes so that the row no longer satisfies the condition. It enables SharePlex to automatically correct the following:
- UPDATEs that cause a row to meet a different column condition than the one created for that row, sending the changes to a different location. An example would be an UPDATE to a row for which the column condition is region=East that changes the value of the region column to WEST. Such operations will fail because the original INSERT statement for that row was replicated to the original location (the Eastern region), so the row does not exist in the new location (the Western region) when Post attempts the update there.
-
UPDATEs that cause a row to meet a column condition (and be replicated) when the row was not supposed to be replicated. An example would be when the region column is updated from the value of HEADQUARTERS (for which a row is not replicated) to the value of WEST. Such operations will fail because the original INSERT statement for that row (into the headquarters system) was not replicated to the Western region’s system, so Post cannot perform the update there.
- UPDATEs that cause a row to no longer meet any column condition. An example would be when the region column is updated from the value of WEST to the value of HEADQUARTERS. The original INSERT statement was replicated to the Western region’s system, but the update to the new value is not replicated, because the new value does not meet a column condition (headquarters data is not shared). The rows are now out of synchronization, but there are no errors.
When this parameter is enabled, SharePlex automatically corrects rows for which UPDATEs cause the preceding conditions. SharePlex converts the UPDATE to a DELETE and, if needed, an INSERT.
To convert an UPDATE statement (which normally only uses the changed columns and the key) to an INSERT statement, SharePlex needs values for all of the columns. Enabling SP_ORD_HP_IN_SYNC directs SharePlex to send all of the columns in a row to the Post process when there is an UPDATE to a table using horizontally partitioned replication, so that an INSERT can be constructed.
Set this parameter on the source system before you activate the configuration. If replication is active, set the parameter and then reactivate the configuration so that SharePlex can rebuild its object cache.
If you know that the columns in column conditions for tables using horizontally partitioned replication will never change, leave this parameter set to 0, because using it incurs processing overhead.
This parameter is not compatible with SP_OCT_REDUCED_KEY and SP_OPO_REDUCED_KEY [any value: 1 or 2] as it overrides the behavior of both the parameters.
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: when Read is restarted
List of additional supported parameters for PostgreSQL
