Chat now with support
Chat with Support

SharePlex 11.3 - 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

show export

Use the show export command to view statistics about the Export process. This command keeps a record of the number of messages sent to target systems by all Export processes on the local machine, as well as optional statistic about Export performance. The message count begins over again whenever Export stops and starts again.

Note: Typically, a message approximately corresponds to a SQL operation, but there can be multiple messages for one operation on a LONG or LOB column, and there could be one record for numerous operations in an array insert. A message also can be an internal SharePlex operation.

Usage

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

Syntax

Basic command Command options Remote options

show export

[detail]

[queue queuename]

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

Syntax description
Component

Description
queue queuename Constrains the output to a specific named Export queue.
detail

Shows the following statistics:

  • Highest rate since the Export process was activated and the date/time it occurred
  • Highest rate since Export was started and the date/time it occurred
  • Current rate reported as KB/sec and measured as the total KB in a 60 second time slice
  • Average packet size during the last 60 second time slice
  • Number of packets sent during the last 60 second time slice
  • Average TCP send time during the last 60 second time slice
  • Largest deviation from average during the last 60 second time slice
  • Number of packets that deviated more than 50% during the last 60 second time slice
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

Example
Host   : abcl23
Queue  : abcl23
                           Kbytes
Target     Status          Exported     Since              Total       Backlog
---------- --------------- ------------ ------------------ ---------- ----------
abcl23.abc Running                 1063 18-Aug-14 17:05:29       1082          0

  Since activated
    Transmission rate                       : 5 Kb/sec
    Highest transmission rate               : 10 Kb/sec Mon Aug 18 14:45:49 2014
    Average packet size                     : 680 bytes
    Average send time                       : 68 microseconds

  Since export started
    Transmission rate                       : 4 Kb/sec
    Highest transmission rate               : 2 Kb/sec Mon Aug 18 17:07:18 2014
    Average packet size                     : 615 bytes
    Average send time                       : 16 microseconds

  During the last 27 seconds:
    Transmission rate                       : 25 Kb/sec
    Average packet size                     : 678 bytes
    Number of packets                       : 1081
    Average send time                       : 19 microseconds
    Largest deviation from average          : 6002 microseconds
    Send times deviating by more than %23   : %0.9

show import

Use the show import command to view statistics about the Import process. This command keeps a record of the number of messages received from source systems by all Import processes on the local machine. The message count begins over again whenever Import stops and starts again.

Note: Typically, a message approximately corresponds to a SQL operation, but there can be multiple messages for one operation on a LONG or LOB column, and there could be one record for numerous operations in an array insert. A message also can be an internal SharePlex operation.

Usage

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

Syntax

Basic command Command options Remote options
show import

[queue queuename]

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

Syntax description
Component

Description
queue queuename Constrains the output to a specific named import queue.
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 log

Use the show log command to view the SharePlex Event log or user issued commands through the sp_ctrl interface, instead of opening the log file directly through the operating system. This command can be used for logs smaller than 2 MB in size; otherwise, view the log directly through the filesystem. The default command used without any options displays 60 lines from the Event Log at 15 lines per page on the screen, starting with the oldest entry.

  • To view the current show log default parameters, use the view log options command.
  • To change the defaults, use the set log command.

To override the set log defaults, you can use optional syntax to:

  • Set the order of entries
  • The number of lines extracted from the log
  • The number of lines on the screen.
  • Filter entries by keyword
  • View the user issued commands instead of the full Event Log.

Usage

Supported sources: Oracle
Supported targets: All
Authorization level: Viewer (3)
Issued for: source or target system
Related commands: set log, view log options

Syntax

Basic command Command options Remote options
show log

[event | command | trace | post for datadest | capture for datasource| read for datasource]

[maxlines=number]

[lpp=number]

[reverse]

[forward]

[filter=keyword]

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

Syntax description

Note: To obtain the datasource, datadest, and queuename values in this command, use the lstatus command and view the Queues section of the output.

Component Description
event

Use this option to display the Event Log. This option, if used, must appear in the syntax before any other option. It cannot be used with the [command] option.

Example:

sp_ctrl(sysA)> show log event

command

Use this option to view the user issued commands. This option, if used, must appear in the syntax before any other option. It cannot be used with the [event] option.

Example:

sp_ctrl(sysA)> show log command

post for datadest queue queuename

Use this option to view the Post Log. This option must specify the data destination and the queue name.

Example:

sp_ctrl(sysA)> show log post for mydbqueue q1

capture for datasource

Use this option to view the Capture Log. This option must specify the datasource.

Example:

sp_ctrl(sysA)> show log capture for o.mySID

read for datasource

Use this option to view the Read Log. This option must specify the datasource.

Example:

sp_ctrl(sysA)> show log read for o.mySID

maxlines=number

Use this option to specify the maximum number of lines to extract from the log. Without this option, show log defaults to 60 lines or the value set with the set log command.

Example:

sp_ctrl(sysA)> show log maxlines=50

lpp=number

Use this option to customize the output to fit your monitor screen. It specifies the number of lines to display on your screen. Without this option, show log defaults to 15 lines or the value set with the set log command.

Example:

sp_ctrl(sysA)> show log lpp=34

reverse

Use this option to order the display starting with the most recent entry. Without this option, show log defaults to the forward direction or the value set with the set log command.

Example:

sp_ctrl(sysA)> show log reverse

forward

Use this option to order the display starting with the oldest entry. Without this option, show log defaults to the forward direction or the value set with the set log command.

Example:

sp_ctrl(sysA)> show log forward

filter=keyword

Use this option to filter log entries based on a keyword. There are two ways to use this option:

  • To view only those lines containing the keyword, type filter=keyword

  • To exclude lines containing the keyword, precede the keyword with an exclamation point (!), as in filter=!keyword

    The keyword cannot contain blanks.

Examples:

sp_ctrl(sysA)> show log filter=compare

The preceding example extracts only the compare related messages from the Event Log.

Notice 08-07-08 22:47:21.906001 96492 1 User command: qarun remove log all (from irvqasu21.quest.com)

sp_ctrl(sysA)> show log filter=!Notice

The preceding example excludes all Notice entries from the Event Log but shows all other types of entries.

Info 08-07-08 22:47:19.642379 96490 1 Command server launched, pid = 96490 (connecting from irvqasu21.quest.com)

Info 08-07-08 22:47:20.825598 96492 1 Command server launched, pid = 96492 (connecting from irvqasu21.quest.com)

Info 08-07-08 22:47:22.334040 23710 1 Command server launched, pid = 23710 (connecting from irvqasl03.quest.com)

Info 08-07-08 22:47:23.969925 125996 1 Compare server launched, pid = 125996

Info 08-07-08 22:47:24.632481 125996 1 Compare server completed

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

    Related Documents

    The document was helpful.

    Select Rating

    I easily found the information I needed.

    Select Rating