Chat now with support
Chat with Support

SharePlex 11.4 - Reference Guide

About this guide Conventions used in this guide Revision History SharePlex Commands for Oracle SharePlex parameters SharePlex Commands for PostgreSQL SharePlex Parameters for PostgreSQL General SharePlex utilities Oracle Cloud Infrastructure SharePlex environment variables

Show Capture for PostgreSQL

Use the show capture command to view statistics for the Capture process.

Basic command

The basic show capture command shows an overview of the process, such as the datasource, whether the process is running or stopped, and other basic information.

Detailed statistics

To view detailed statistics for the Capture process, use the show capture command with the [detail] option. That option shows detailed statistics that can help you assess the performance of the process, decide whether tuning parameters need to be adjusted, and detect problems or bottlenecks.

Detailed statistics for PostgreSQL Capture
Statistic Description
Host The name of the local machine (source system).
System time The current time according to the system clock.
Source The name of the source PostgreSQL database.
Status

The status of the Capture process (running or stopped).

Since The time that Capture started.
PostgreSQL current WAL LSN The LSN (Log Sequence Number) number of the WAL file log to which PostgreSQL is writing.
Capture current WAL LSN

The LSN (Log Sequence Number) number of the WAL file log that Capture is reading.

This value needs to show the latest LSN value read by Capture, independent of whether data is coming from a replicated table or not. In idle condition, it should match PostgreSQL's current WAL LSN.

Last WAL file record processed The record being processed by Capture or the last one processed if Capture is not currently replicating data.
Capture state

The state of the process, in relation to the replication work it performs: It can be one of the following:

  • WAITING: Capture is waiting for records from WAL sender
  • PROCESSING: Capture is processing a WAL file log record for replication.
  • STOPPED BY ERROR: Capture is stopped by error and error is mentioned in the EVENT logs
Activation ID The internal identifying number of the configuration activation, which identifies the associated processes and queues. This value needs to be displayed immediately after activation even before DML replication starts.
Error count The number of records that were skipped due to PostgreSQL errors since Capture started. Data from skipped records is not reflected in the target database.
Operations captured The number of DML operations that Capture successfully processed for replication since it started.
Transactions captured The number of committed PostgreSQL transactions whose operations Capture successfully replicated since it started.
Concurrent sessions The number of PostgreSQL sessions being processed at the same time.
HWM concurrent sessions The largest number of concurrent PostgreSQL sessions since Capture started.
Checkpoints performed The number of checkpoints to save the state of Capture since Capture started. Frequent checkpointing generates additional overhead on the system, but infrequent checkpoints cause SharePlex to recover less quickly from a system or instance failure. By default, Capture checkpoints every 40,000 messages or 120 seconds, but it can be adjusted with the SP_OCT_CHECKPOINT_FREQ and SP_OCT_CHECKPOINT_TIME parameters.
Total operations processed The number of all PostgreSQL operations and SharePlex internal operations processed by Capture since it started, including records captured for replication and records for objects not in the configuration (both replicated and not-to-be replicated records)
Total transactions completed

The number of committed PostgreSQL transactions processed by Capture since it started, including transactions captured for replication and transactions for objects not in the replication configuration (both replicated and not-to-be replicated transactions)

Total Kbytes read The size in kilobytes of the data that was processed by Capture since it started.
XLOG records in progress The number of records that Capture is processing.
XLOG records processed The total number of XLOG records processed.
XLOG records ignored The number of records that Capture ignored because they are not associated with objects in the configuration.
Replication    Type of replication (Physical or Logical)
Capture current TIMELINE_ID Displays the current Timeline ID (applicable only for Physical replication)

 

For an example of the sample statistics for PostgreSQL Capture, see the example below:

Usage

Supported source:

PostgreSQL (on-prem), Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL

Supported targets: PostgreSQL, Oracle, SQL Server, Kafka, Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL
Issued for: source and target systems
Related commands: show post

Syntax

Basic command Command options
show capture

[detail] [fordatasource]

Syntax description
Component Description
show capture
  • Shows the state of the Capture process and a summary of the operations captured.
  • detail

    Shows detailed statistics that can help you tune Capture’s performance and diagnose problems.

    Example: sp_ctrl(sysA)> show capture detail

    for datasource

    This option shows Capture statistics only for a specific datasource.

    datasource is expressed as r.database where database is a dbname.

    Example: sp_ctrl(sysA)> show capture for r.dbname

    Show Post for PostgreSQL

    Use the show post command to view statistics for the Post process.

    Basic Show Post command

    The basic show post command shows global statistics for all sessions a Post process. It shows the status of the Post process and the number of messages posted since it started. To filter the output for a specific post queue or datasource (useful when you have multiple replicating data streams), use the queue queuename or for datasource-datadest option.

    Detailed Show Post command

    To view detailed statistics for the Post process, use the show post command with the detail option. That option shows the most recent SQL statement processed, as well as other statistics that can help you assess Post’s performance, decide whether tuning parameters need to be adjusted, and detect problems or bottlenecks.

    The following explains the detailed statistics shown with show post. These statistics vary slightly depending on the type of source and target.

    Statistic Description
    Host The name of the local machine (target system).
    Source The source of the data being processed by Post.
    Queue The Post queue for this Post process. For a default Post queue, it is the name of the source system. For a named queue, it is the user-defined name.
    Target The name of the target of this Post process, for example the name of an PostgreSQL instance or Open Target database.
    Status

    The status of the Post process (running or stopped). Possible statuses are:

    • Running
    • Stopping
    • Stopped by user
    • Stopped due to error

    Operations posted

    Operations processed

    The number of transactional operations and SharePlex internal operations that this Post process processed since it was started.
    Since The time that Post started.
    Total The number of messages in the queue that have yet to be read-released. This number corresponds to the 'Number of messages' returned from running qstatus. (The TOTAL value goes down with time and shows same value as Number of messages in QSTATUS)
    Backlog The number of messages that are waiting in the queue to be processed by Post.

    Last operation posted

    Identifying information for the most current operation that is being posted to the target if Post is active, or the last operation posted if it is inactive. This information is specific to the type of datastore that originated the data. An operation can be:

    • INSERT
    • UPDATE
    • DELETE
    • COMMIT
    • INSERT_MULTIPLE or DELETE_MULTIPLE (array/bulk operations).
    • SharePlex internal operation.

    Last transaction posted

    Identifying information for the last transaction that was posted. This information is specific to the type of datastore that originated the data.
    Post state

    The state of the Post process, in relation to the replication work it performs. It can be one of the following:

    • Waiting: Post is waiting for messages to process.
    • Active: Post is posting changes to the database.
    • Committed: Post is committing the transaction.
    • Idle: Post has no open transactions to process.
    • Rollback: Post is processing a rollback.
    • Recovery: Post is in a crash-recovery mode.
    Activation ID The activation ID of the current configuration. This value needs to be displayed immediately after activation even before DML replication starts.

    Operations processed

    The number of SQL operations that Post applied to the target, whether or not the COMMIT was received.

    Transactions processed

    The number of committed transactions that Post applied to the target since it was started.
    Insert operations The number of INSERT operations processed by Post since it was started.
    Update operations The number of UPDATE operations processed by Post since it was started.
    Delete operations The number of DELETE operations processed by Post since it was started.
    Latency The time taken to process the replication (excluding the time taken by database on source)

    Usage

    Supported source:

    PostgreSQL (on-prem), Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL

    Supported targets: PostgreSQL, Oracle, SQL Server, Kafka, Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL
    Issued for: target system
    Related commands: show capture

    Syntax

    Basic command Command options
    show post

    [detail]

    [queue queuename]

    [fordatasource-datadest]

    [sessions]

    Syntax description
    Component Description
    show post
  • Shows the state of the Process process and a summary of the operations processed.
  • detail

    This option displays detailed statistics for the Post process.

    Example:

    sp_ctrl(sysB)> show post detail

    queuequeuename

    This option filters the show post display for a specific post queue.

    • queue is a required part of the syntax.
    • queuename is the post queue for which you want to see Post statistics. Valid values are:

      • the name of the source system if using default queues.
      • the user-defined queue name, if using named queues.

    If you are unsure what the queue name is, issue the qstatus command. Queue names are case-sensitive on all platforms.

    This option can appear in any order with other options.

    Example:

    sp_ctrl(sysB)> show post queue sysA

    fordatasource-datadest

    This option filters the show post display for a specific data stream.

    • for is a required part of the syntax.
    • datasource is expressed as r.database where database is a dbname.
    • datadest is expressed as one of the following, depending on the target:

      r.database_name

      x.kafka

      x.jms

      x.file

    This option can appear in any order with other options.

    Example:

    sp_ctrl(sysB)> show post for r.dbnameA-r.ssB

    sessions

    For PostgreSQL targets, this option displays statistics for all the threads spawned by the Post process.

    For Open Target, which is single-threaded, this option can be used to view details for that thread.

    This option can appear in any order with other options.

    Example:

    sp_ctrl(sysB)> show post sessions queue queuename

    show_last_posted

    Use the show_last_posted command to view the PostgreSQL LSN of the last transaction that a Post process applied to the target. The command shows all of the last LSNs when using multiple post queues.

    sp_ctrl (sysB)>show last_posted

    $> show_last_posted r.dbname1

    For resume replication from r.dbname1

    On source activate to pglsn=<LSN in hexadecimal format>

    reconcile queue sp01 for r.dbname1-r.dbname1 pglsn <LSN1 in hexadecimal format>

    reconcile queue sp02 for r.dbname1-r.dbname1 pglsn <LSN2 in hexadecimal format>

    reconcile queue sp03 for r.dbname1-r.dbname1 pglsn <LSN3 in hexadecimal format>

    NOTE: This command stops all Post processes in order to obtain the LSN information.

    Usage

    Supported source:

    PostgreSQL (on-prem), Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL

    Supported targets: PostgreSQL, Oracle, SQL Server, Kafka, Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL
    Authorization level: Viewer (3)
    Issued for: target system
    Related commands:

    activate config

    Syntax

    Basic command
    show last_posted

    SharePlex Parameters for PostgreSQL

    PostgreSQL parameters control and tune various aspects of replication for PostgreSQL databases.

    Contents

    Descriptions of User-Configurable Parameters

    This section describes the user-configurable SharePlex parameters for the PostgreSQL database. Parameters not documented in this chapter are internal parameters that should only be modified under guidance of a Quest developer or Technical Support representative.

    Parameters are grouped as follows:

    Capture Parameters

    These parameters are used by the SharePlex Capture process.

    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:

    • select pg_replication_origin_create('sp_deny_1’);

    • select pg_replication_origin_session_setup('sp_deny_1’);

    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:

    1. Navigate to PostgreSQL data directory (cd /var/lib/pgsql/13/data/).

    2. 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).

    3. 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 ]

    Open Target Poster parameters

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

    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 replication) 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

    List of additional supported parameters for PostgreSQL Open Target Poster

    Oracle Poster parameters

    These parameters are used by the SharePlex Poster process when applying data to an Oracle target.

    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

    List of additional supported parameters for Oracle Poster process

    Read parameters

    These parameters are used by the SharePlex Reader process.

    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 Reader process

    Compare/Repair Parameters

    These parameters control properties of the SharePlex Compare and Repair commands.

    SP_DEQ_PG_FORCE_PARALLEL

    This parameter enables the use of parallel queries in a running session for the PostgreSQL database.

    Based on this parameter value, the following value is assigned to PostgreSQL database parameter force_parallel_mode (DB version < 16.0)/ debug_parallel_query (DB version >= 16.x)

    0 - off

    1 - on

    2 - regress

    Default value: 0

    Range of valid values: 0 to 2

    Takes effect: Immediately available for the next comparison.

    Notes:

    • SharePlex cannot control the number of worker threads assigned for parallelism in a SELECT query by the PostgreSQL database optimizer.

    • This parameter is not supported with the pg_hint_plan extension.

    SP_DEQ_PG_PARALLEL_MAX

    This parameter manages the maximum number of parallel worker threads allowed for a query in a running session for the PostgreSQL database.

    Value of this parameter is assigned to PostgreSQL database parameter - max_parallel_workers_per_gather

    Default value: 6

    Range of valid values: 0 to 1024

    Takes effect: Immediately available for the next comparison.

     

    Notes:

    • SharePlex cannot control the number of worker threads assigned for parallelism in a SELECT query by the PostgreSQL database optimizer.

    • This parameter is not supported with the pg_hint_plan extension.

    SP_DEQ_PG_PARALLEL_SETUP_COST

    This parameter is utilized to configure the parallel_setup_cost parameter for the PostgreSQL database in a running session.

    Default value: 10

    Range of valid values: 0 to 2147483647

    Takes effect: Immediately available for the next comparison.

    Notes:

    • SharePlex cannot control the number of worker threads assigned for parallelism in a SELECT query by the PostgreSQL database optimizer.

    • This parameter is not supported with the pg_hint_plan extension.

    SP_DEQ_PG_PARALLEL_TUPLE_COST

    This parameter is used to configure the parallel_tuple_cost parameter for the PostgreSQL database in a running session.

    Default value: 0.001

    Range of valid values: 0.0001 to 3.4E+38

    Takes effect: Immediately available for the next comparison.

    Notes:

    • SharePlex cannot control the number of worker threads assigned for parallelism in a SELECT query by the PostgreSQL database optimizer.

    • This parameter is not supported with the pg_hint_plan extension.

    SP_DEQ_BATCH_ENABLED

    When set to 1, this parameter enables batch DML operations during the repair process. If set to 0, out-of-sync (OOS) records will be corrected by firing one transaction at a time during the repair.

    Default value: 1

    Range of valid values: 0 or 1

    Takes effect: Immediately available for the next comparison.

    SP_DEQ_MAX_BATCH_MBYTES

    This parameter controls the maximum size of a batched DML operation to be executed at once during the repair process. The repair process continues to add operations (I/U/D) until the batch size specified by this parameter value is reached. This parameter is applicable on the target side where the client process runs.

    Default: 1 megabyte

    Range of valid values: 1 to any positive integer

    Takes effect: Immediately available for the next comparison.

    SP_DEQ_COMMIT_FREQ

    This parameter is applicable for repair when batch mode is off. It determines the number of single transactions that should be committed.

    Default value: 1000

    Range of valid values: 0 to 10000

    Takes effect: Immediately available for the next comparison.

    SP_DEQ_USE_SP_CKSUM

    This parameter determines whether SHA256 hash is used in the comparison. When set to the default value of 0, SHA256 hash is utilized. When set to 1, SharePlex selects the exact table data as it is, using an internal checksum method.

    Notes:

    • Setting this parameter to 1 for larger tables may affect performance, as hash_values take significantly less time than processing exact table data.

    • If the table has no key columns and has NULL values in any of its columns, the Compare and Repair operation should be run by setting the parameter SP_DEQ_USE_SP_CKSUM to 1.

    Default value: 0

    Range of valid values: 0 or 1

    Takes effect: Immediately available for the next comparison.

    SP_DEQ_PG_DECLARE_FETCH_SIZE

    This parameter determines whether the driver attempts to return a result set in a single fetch or across multiple fetches.

    If this parameter is set to 0, the driver will perform a single fetch that can improve overall response times but at the cost of additional memory.

    If this parameter is set to a non-zero number, the driver may perform either a single fetch or multiple fetches, depending on the size set for this parameter. The driver will perform a single fetch when the size set by the user for this parameter is greater than the actual size of the result set returned by a select query. Otherwise, it will perform multiple fetches. If multiple fetches are used, the size of the fetch is determined by the value of this parameter. For large result sets, retrieving rows in multiple fetches can improve response time and thereby prevent possible timeouts.

    Additionally, this parameter can be adjusted to trade-off between throughput and response time.

    Default value: 100

    Range of valid values: Between 0 to 2147483647, applicable to both Server and Client processes.

    Takes effect: Immediately after running the Compare or Repair command.

    SP_DEQ_READ_BUFFER_SIZE for PostgreSQL

    This parameter applies only to advanced data type columns during a compare/repair operation. It adjusts the size of the buffer that holds the fetched data related to advanced data types 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

    Export parameters

    These parameters are used by the SharePlex Export process.

    Import parameters

    These parameters are used by the SharePlex Import process.

    Queue parameters

    These parameters control properties of the SharePlex queues.

    sp_cop parameters

    These parameters are used by the SharePlex sp_cop program.

    System parameters

    These parameters control system-related SharePlex properties.

    Related Documents

    The document was helpful.

    Select Rating

    I easily found the information I needed.

    Select Rating