지금 지원 담당자와 채팅
지원 담당자와 채팅

SharePlex 12.0 - Reference Guide

About this guide Conventions used in this guide SharePlex Commands for Oracle SharePlex parameters SharePlex Commands for PostgreSQL SharePlex Parameters for PostgreSQL Heterogenous compare-repair commands General SharePlex utilities Oracle Cloud Infrastructure SharePlex environment variables

show post

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 queuequeuename or fordatasourcedatadest 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 Oracle 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 flush
  • 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.
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
  • TRUNCATE
  • COMMIT
  • DDL statement
  • INSERT_MULTIPLE or DELETE_MULTIPLE (array operations).
  • SharePlex internal operation.

Last transaction posted

Last transaction processed

 Identifying information for the last transaction that was posted. This information is specific to the type of datastore that originated the data.
Last file switch Shown if the target is output to a file. Shows the record identifier for the record after which the active file was switched to a new one.
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.

Number of messages read released

 Shown for an Oracle target. Shows the difference between the number of operations posted and the actual number of operations removed from the queue. This field is used mainly by Technical Support staff when Post is stalled.
Number of threads Shown for an Oracle target. Shows the number of processing threads in a multithreaded Post, including the main and timekeeper threads.

Number of Oracle connections

Shown for an Oracle target. Shows the number of connections that a multithreaded Post has open.
Concurrency (Active sessions) Shown for an Oracle target. Shows the number of concurrent transactions that multithreaded Post is processing.
Peak number of sessions Shown for an Oracle target. Shows the highest number of concurrent transactions that multithreaded Post processed since it was started.

Operations posted

Operations processed

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

Transactions posted

Transactions processed

 The number of committed transactions that Post applied to the target since it was started.
Full rollbacks Shown for an Oracle target. Shows the number of rolled back transactions processed by Post.
Full rollback operations posted Shown for an Oracle target. Shows the number of operations that Post applied before the transcation was rolled back. (Post does not wait for a commit to start applying operations that it has in the queue.)
Full rollback operations skipped Shown for an Oracle target. Shows the number of operations that Post skipped because it received an early indication that the transaction was rolled back.
Transactions <= 2 Shown for an Oracle target. Shows the number of transactions that contain two or fewer operations.
Transactions > 10000 Shown for an Oracle target. Shows the number of transactions that contain more than 10,000 operations.
Largest transaction Shown for an Oracle target. Shows the size of the largest transaction processed.
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.
Insert batch operations / average
  • Shown for an Oracle target. Shows the number of INSERT_BATCH operations processed / average number of INSERT operations in one INSERT_BATCH statement.
    • Delete batch operations / average Shown for an Oracle target. Shows the number of DELETE_BATCH statements processed / average number of DELETE operations in one DELETE_BATCH statement.
    Other operations Shown for an Oracle target. Shows the number of operations, other than INSERT, UPDATE or DELETE, processed by Post since it was started.
    LOB changes Shown for an Oracle target. Shows the number of LOB change operations.
    Key cache hit count Shown for an Oracle target. Shows the number of times that the SharePlex internal key cache was used by the Post process to generate UPDATE statements. SharePlex uses the key cache to post data to the target instance quickly.
    SQL cache hit count Shown for an Oracle target. Shows the ratio of the total number of messages that were executed without parsing and binding divided by the total number of INSERT, UPDATE and DELETE operations. For more information on the SQL Cache feature of SharePlex, see the SharePlex Administration Guide.
    File switches Shown if the target is output to a file. Shows the total number of file switches performed by Post.

    Usage

    Supported sources: Oracle
    Supported targets: All
    Authorization level: Viewer (3)
    Issued for: target system
    Related commands: show capture, show read, show export, show import

    Syntax

    Basic command Command options Remote options
    show post

    [detail]

    [queue queuename]

    [for datasource-datadest]

    [sessions]

    [session=n]

    [ on host |

    on host:portnumber |

    on login/password@host |

    on login/password@host:portnumber ]

    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

    queue queuename

    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

    for datasource-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 o.SID where SID is an ORACLE_SID.

    • datadest is expressed as one of the following, depending on the target:

      o.ORACLE_SID

      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 o.oraA-r.ssB

    sessions

    For Oracle targets, this option displays statistics for all of 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

    session=n

    (Oracle only) This option displays statistics shown in the show post sessions output plus additional details about the state of the thread, the number of messages waiting to be processed by the thread, and the status of the overall transaction.

    • session= is a required part of the syntax.

    • n is any session number displayed with the show post sessions command. Leave no space between any of the components.

    This option can appear in any order with any other option.

    Example:

    sp_ctrl(sysB)> show post session=1234 queue queuename

    Remote options

    These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.

    Option Description
    on host

    Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on SysA

    on host:portnumber

    Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on SysA:8304

    on login/password@host

    Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax.

    Example:sp_ctrl(sysB)>status on john/spot5489@SysA

    on login/password@host:portnumber

    Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304

    show read

    Use the show read command to view statistics about the Read process.

    Basic Command

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

    Detailed statistics

    To view detailed statistics for the Read process, use the show read command with the [detail] option. This 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 Oracle Read
    Statistic Description
    Host The name of the local machine (source system).
    Source The name of the source Oracle instance.
    Status

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

    Operations forwarded The number of operations that Read sent to the Export queue.
    Since The time that Read started.
    Total The number of messages in the capture queue that have yet to be read-released. This number corresponds to the 'Number of messages' returned from running qstatus.
    Backlog The number of messages that are waiting in the capture queue to be processed by Read.
    Last operation forwarded Identifying information about the most recent operation that Read sent to the export queue, or the last operation it sent if Read is inactive. An operation can be data relating to a DML or DDL operation or an internal SharePlex operation.
    Read state

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

    • IDLE: Read is waiting to collect and process the next batch of records from the capture queue. If Read is idle too long, it could mean that Capture is running behind or that it is idle because there is no new data in the capture queue. If that is not the case, and you want to reduce the latency of the Read process, you can adjust the SP_ORD_DELAY_RECORDS parameter to have Read collect and process records faster.
    • PASS1: Read is in the first phase of processing the data and packaging it for routing.
    • PASS2: Read is in its second phase of processing.

    The Read State field is useful if Read appears to be taking too long to process an operation.

    Activation ID The internal identifying number of the configuration activation, which identifies the associated processes and queues.
    Operations forwarded The number of transactional and internal SharePlex operations sent by Read to the export queue since it started.
    Transactions forwarded The number of committed transactions sent by Read to the export queue since it started. Comparing this value with the value for the operations forwarded indicates whether the transactions tend to be small or large. You can use that information to determine why Read appears to be falling behind and why the queues are not emptying (large transactions without a COMMIT).
    Full rollbacks The number of rolled back transactions processed by Read.
    Full rollback operations skipped The number of operations that Read does not forward to the export queue because the transaction was rolled back.
    Cursor cache hit count The number of times Read used a cached cursor.
    Cursor cache miss count The number of times Read could not use a cached cursor.
    Number of open cursors The number of open cursors reserved by Read to access Oracle if necessary.
    Number of active batches The number of transactions currently active that are being processed as a batch transaction. If enabled, Read will combine batch operations so that they can be posted more quickly.
    Batch message total The number of operations that the Read process combines into batch operations.

    Usage

    Supported sources: Oracle
    Supported targets: All
    Authorization level: Viewer (3)
    Issued for: source system
    Related commands: show capture, show export, show import, show post

    Syntax

    Basic command Command options Remote options
    show read

    [detail] [for datasource]

    [ on host |

    on host:portnumber |

    on login/password@host |

    on login/password@host:portnumber ]

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

    This option displays detailed statistics for the Read process.

    Example:

    sp_ctrl(sysA)> show read detail

    for datasource

    This option shows Read statistics for a specific datasource.

    datasource is expressed as o.SID where SID is an ORACLE_SID.

    Examples:

    sp_ctrl(sysA)> show read for o.oraA

    Remote options

    These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.

    Option Description
    on host

    Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on SysA

    on host:portnumber

    Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on SysA:8304

    on login/password@host

    Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax.

    Example:sp_ctrl(sysB)>status on john/spot5489@SysA

    on login/password@host:portnumber

    Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304

    show sql

    Use the show SQL command to view the SQL statement being written by the Post process to post data to the target database. If the post queue is empty, or if Post is not processing a statement, the show SQL command shows the most recent SQL statement processed.

    Use this command when you think replication is taking too long, or when Post stops on an error. Knowing which SQL statement is at fault can help you determine what is wrong and whether or not further action is required.

    The following is an example of the show sql display:

    sp_ctrl (tustin:8852)> show sql

    Last SQL statement of queue tustin and instance o.ora920-0.ora920 on tustin

    insert into “KWONG”.”KCWTAB2” (“C1”,”C2”) values (:V001,:V002)

    Issue this command for the target system.

    Usage

    Supported sources: Oracle
    Supported targets: All
    Authorization level: Viewer (3)
    Issued for: target system
    Related commands: none

    Syntax

    Basic command Command options Remote options
    show sql

    [queue queuename]

    [for datasource-datadest]

    [thread=n]

    [session=n]

    [ on host |

    on host:portnumber |

    on login/password@host |

    on login/password@host:portnumber ]

    Syntax description
    Component Description
    show sql Without options, shows all SQL statements.
    queue queuename

    Use this option to show the SQL statement for a specific named queue.

    • queue is a required part of the syntax.
    • queuename is the name of the queue. If you are unsure what the queue name is, issue the qstatus command.

    Queue names are case-sensitive on all platforms.

    Example:

    sp_ctrl(sysA)> show sql queue q1

    for datasource-datadest

    Use this option to show the SQL for a specific replication stream when you are replicating to or from more than one Oracle instance.

    • for is a required part of the syntax.
    • datasource is expressed as o.SID, where SID is the ORACLE_SID of the source Oracle instance.
    • datadest is expressed as o.SID, where SID is the ORACLE_SID of the target Oracle instance.

    Example:

    sp_ctrl(sysA)> show sql for o.oraA-o.oraB

    thread=n

    Use this option to show the SQL for a post processing thread.

    • thread= is a required part of the syntax.
    • n is a thread number displayed with the show post threads command.

    Leave no spaces between the components. This option can appear in any order with other options.

    Example:

    sp_ctrl(sysB)> show sql thread=1234

    session=n

    Use this option to show the SQL for a user session.

    • session= is a required part of the syntax.

    • n is a session number displayed with the show post threads command. Leave no space between the components. This option can appear in any order with other options.

    Example:

    sp_ctrl(sysB)> show sql session=1234

    Remote options

    These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.

    Option Description
    on host

    Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on SysA

    on host:portnumber

    Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on SysA:8304

    on login/password@host

    Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax.

    Example:sp_ctrl(sysB)>status on john/spot5489@SysA

    on login/password@host:portnumber

    Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304

    show statusdb

    Use the show statusdb command to view the Status Database. Each machine involved in replication has its own Status Database that contains records of key replication events, including those that did not generate an error message or warning at the user interface. This information can alert you to potential problems and help you resolve existing ones.

    The show statusdb display includes the following information:

    • Level: whether the entry is there only for information purposes or whether it is the result of an error or warning condition.
    • Details: the reason for the event.

    When appropriate, the Status Database refers you to the Event Log if there is more information about an entry.

    Usage

    Supported sources: Oracle
    Supported targets: All
    Authorization level: Viewer (3)
    Issued for: source or target system
    Related commands: status, lstatus

    Syntax

    Basic command Command options Remote options
    show statusdb detail

    [ on host |

    on host:portnumber |

    on login/password@host |

    on login/password@host:portnumber ]

    Syntax description
    Component Description
    detail

    This option displays a higher level of detail for the Status Database.

    Example:

    sp_ctrl(sysB)> show statusdb detail

    Remote options

    These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.

    Option Description
    on host

    Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on SysA

    on host:portnumber

    Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on SysA:8304

    on login/password@host

    Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax.

    Example:sp_ctrl(sysB)>status on john/spot5489@SysA

    on login/password@host:portnumber

    Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax.

    Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304

    관련 문서

    The document was helpful.

    평가 결과 선택

    I easily found the information I needed.

    평가 결과 선택