Chat now with support
Chat with Support

SharePlex 11.2 - Reference Guide

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

Verify config for PostgreSQL

The verify config command is intended for use as a preventive measure to avoid certain activation and replication problems. It is intended to be used to test activation to ensure that it will complete successfully.

The verify config command verifies tables only.

This command can be used to:

  • Verify the syntax of the entries in the configuration file.
  • Report an error if the source object is not supported for replication by SharePlex.
  • Report if a host name specified in a route is unreachable.
  • Report if there are duplicate specifications for a single object.
  • Report if an object specification will be skipped and the reason why.
What the verify config command does not support

The verify config command does not:

  • Verify activation time.
  • Verify target objects or the target database name.
Verifying added or changed objects in an active configuration

To verify objects that you want to add to an active configuration or objects that you want to change (such as routing changes), it is suggested that you copy and modify the active configuration and then run the verify command against that copy.

Viewing the results of the verification

The verify config command retains control of the sp_ctrl interface until the verification is completed.

The command will read the entire config file and logging errors.

The results of the verify are displayed to the screen within sp_ctrl. If you would like to view detailed results you may:

  • Issue the verify config command in sp_ctrl using the detail option
  • Navigate to the results file directly using the path displayed to the screen after issuing the verify config command.

Usage

Supported sources: 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: source system
Related commands: activate config

Syntax

Basic command Command options
verify config filename detail
Supported wildcard syntax

SharePlex supports the following SQL wildcards for tablename

  • Percent (%) wildcard to specify a string.
  • Underscore (_) wildcard to specify a single-character.

For more information on supported wildcard syntax, see Use Wildcards to Specify Multiple Tables.

Syntax description
Component Description
filename

filename is the name of the configuration to be verified.

detail

This option will display a greater level of detail to the screen.

Example:

sp_ctrl(sysA)> verify config myconfig detail

In this example, the myconfig file will be verified and the results will be displayed with a higher level of detail.

View Partitions for PostgreSQL

Use the view partitions command to view the row partitions in one partition scheme or all partition schemes in a horizontally partitioned replication configuration.

For more information about how to configure horizontally partitioned replication, see the SharePlex Administration Guide.

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
Issues on: source system
Related commands: add partition, drop partition, drop partition scheme, modify partition

Syntax

Basic command

view partitions for {scheme_name | all}

Syntax description
Component Description
scheme_name Show the row partitions for the specified partition scheme.
all

Show all row partitions, grouped according to the names of their partition schemes.

Examples

sp_ctrl> view partitions for scheme1

sp_ctrl> view partitions all

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

    Related Documents

    The document was helpful.

    Select Rating

    I easily found the information I needed.

    Select Rating