Chat now with support
Chat with Support

SharePlex 9.2.5 - Reference Guide

About this guide Conventions used in this guide SharePlex commands SharePlex parameters General SharePlex utilities Database Setup utilities SharePlex environment variables

add partition

Use the add partition command to add a row partition to a partition scheme when configuring horizontally partitioned replication. Issue an add partition command for each row partition that you want to create.

This command captures all of the information required to create the partition, including the following required components:

  • The partition scheme name. To create a new partition scheme, specify the name in the add partition command that creates the first row partition for that scheme. SharePlex automatically creates the partition scheme. Then, specify that name when adding additional row partitions to that partition scheme.
  • The hash value or the column condition specification that creates the row partition.
  • The routing for the rows that are specified in the row partition.

Reactivate the configuration file if the command affects a table that is already being replicated. SharePlex will only lock tables for which there are configuration changes.

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

Usage

Supported sources: Oracle
Supported targets: All
Authorization level: Operator (2)
Issues on: source system
Related commands: modify partition, drop partition, drop partition scheme, view partitions

Syntax

Basic command Command options Remote options

add partition to scheme_name

set

{condition = column_condition |

hash = hash_value}

and

route = routing_map

[and name = partition_name]

[and tablename = target_table]

[and description = description]

Not available

Syntax description

Component Description
to scheme_name

to is a required keyword indicating the row partition is being added to scheme_name.

scheme_name is the name of the partition scheme. The partition scheme is created by the first add partition command that you issue, which will also specify the first set of rows to partition.

If you are making heavy use of horizontal partitioning, it may help to establish naming conventions for your partition schemes.

set

Required keyword that starts the definition of the row partition.

condition = column_condition

Creates a row partition based on a column condition. The condition must be in quotes. Use standard WHERE conditional syntax such as ((region_id = West) and region_id is not null).

The condition and hash components are mutually exclusive.

hash = value

Creates a row partition based on a hash value. The specified value determines the number of row partitions in the partition scheme.

The condition and hash components are mutually exclusive.

route = routing_map

The route for this partition. This can be one of the following:

Partition based on a column condition:

Specify any standard SharePlex routing map, for example: sysB@o.myora or sysB:q1@o.myora or sysB@o.myora+sysC@o.myora (compound routing map).

If the target is JMS, Kafka, or a file, then the target should be specified as x.jms, x.kafka, or x.file, for example: sysA:hpq1@x.kafka.

To route a partition to multiple target tables that have different names, do the following:

  • Issue a separate add partition command for each different target name. Use the tablename option to specify the name.
  • In the configuration file, specify any of these target tables as the target table in the entry that uses this partition scheme. SharePlex will detect the other names when the configuration is activated.
  • Set the SP_ORD_FIRST_FIND parameter to 0 so that SharePlex checks all of the column conditions in the partition scheme. By default SharePlex assumes that any given row change will satisfy only one column condition in the partition scheme.

Partition based on a hash:

Use the following format to direct SharePlex to create a named post queue for each partition:

host:basename|#{o.SID | r.database}

where:

  • host is the name of the target system.
  • basename is the base name that is assigned to all queues.
  • |# directs SharePlex to number the queues sequentially by appending the base name with an integer, starting with 1 to the value set with hash.
  • o.SID for an Oracle target or r.database for an Open Target target.
name = name

(Recommended) A short name for this partition. This option is only useful for partitions based on column conditions. A name eliminates the need to type out long column conditions in the event that you need to modify or drop the partition in the future.

tablename = owner.table

(Optional) Use this option when there are multiple target tables and one or more have different names. Issue a separate add partition command for each name.

The table name must be fully qualified. If case-sensitive, the name must be specified in quotes.

Example:

add partition to scheme1 set name = p1 and condition = "C1 > 200" and route = sysb:p1@o.orasid and tablename = myschema.mytable

description = description (Optional) Description of this partition.

Examples

Row partitions based on column conditions

Route different sets of rows through different post queues:

sp_ctrl> add partition to scheme1 set name = q1 and condition = "C1 >= 200" and route = sysb:q1@o.orasid

sp_ctrl> add partition to scheme1 set name = q2 and condition = "C1 < 200" and route = sysb:q2@o.orasid

Route different sets of rows to different target systems and different table names from the source:

sp_ctrl> add partition to scheme1 set name = east and condition = "area = east" and route = sys1e@o.orasid and tablename = ora1.targ

sp_ctrl> add partition to scheme1 set name = west and condition = "area = west" and route = sys2w@o.orasid and tablename = ora2.targ

Row partitions based on a hash value

Divide rows into four partitions, each processing through a different post queue:

sp_ctrl> add partition to scheme1 set hash = 4 and route = sysb:hash|#@o.ora112

analyze config

Use the analyze config command to run an analysis of the tables in a configuration file. This command gathers information about the activity of the tables.

Important! Do not activate the configuration before you run the analysis, and make certain there are no other active configurations when you run it. The use of this command is similar to an actual activation.

The analyze process writes out its results based upon the data gathered at the time that was specified in the command, and then the replication stream cleans itself up.

The analysis is written to a file in the log subdirectory of the variable-data directory. The name of the file is:

o.datasource-analysis.actid

The analyze process maintains information about the activity of each object in replication, as well as transaction information. The transaction information can be used to identify groups of tables that are interrelated in such a way that they should be replicated in the same replication stream (same set of queues and processes).

The analysis lists each group of related tables, the total number of operations per table , and the total number of operations for the group. For example:

>cat o.w111a64f-analysis.1575

Activity Analysis

 

Group 1 of related tables: 1000 total operations in group

"TEST"."SS2_TEST1" 346

"TEST"."SS2_TEST2" 348

"TEST"."SS2_TEST3" 306

 

Group 2 of related tables: 1124 total operations in group

"TEST"."SRC_TEST1" 232

"TEST"."SRC_TEST2" 177

"TEST"."SRC_TEST3" 178

"TEST"."SRC_TEST4" 175

"TEST"."SRC_TEST5" 188

"TEST"."SRC_TEST6" 174

 

Tablename Inserts Updates Deletes Rollbacks Total
"TEST"."SS2_TEST2" 146 169 33 0 348
"TEST"."SS2_TEST1" 140 176 30 0 346
"TEST"."SS2_TEST3" 116 158 32 0 306
"TEST"."SS2_TEST1" 75 114 29 14 232
"TEST"."SS2_TEST5" 61 94 22 11 188
"TEST"."SS2_TEST3" 69 73 28 8 178
"TEST"."SS2_TEST2" 69 77 21 10 177
"TEST"."SS2_TEST4" 54 89 19 13 175
"TEST"."SS2_TEST6" 61 79 25 9 174

To view the current state of analysis

Use the show analyze command to view the state of the analysis:

sp_ctrl (alvspxl11:8567)> show analyze detail

 

Host: alvspxl11.quest.com

Operations  
Source Status Processed Since Total Backlog
------ ------------ ----------- ------- ------ -------
o.w111a64f Running 1497 17-Mar-12 10:41:54 1496 0
           

Last operation processed:

Redo log: 295 Log offset: 32327800

UPDATE of "TEST"."SRC_TEST3" at 03/17/12 0:59:17

 

Activation id : 1573
Operations processed : 1497
Transactions processed : 398
Analysis complete : 20-Mar-12 10:41:54

To terminate the analysis before completion

To terminate the analysis before it is complete, use the abort config or deactivate config command, or modify the SP_ANL_RUN_TIME parameter.

Usage

Supported sources: Oracle
Supported targets: All
Authorization level: Administrator (1)
Issued for: source system
Related commands: abort config, copy config, create config, deactivate config, edit config, list config, purge config, remove config, rename config, show config, verify config, view config 

Syntax

Basic command Command options Remote options
analyze config filename n {minutes | hours | days}

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

Syntax description

Component Description
filename 

The name of the configuration file that you want to analyze. Configuration names are case-sensitive.

Example:

sp_ctrl(sysA)>analyze config sales

n {minutes | hours | days) The number of minutes, hours, or days worth of activity to analyze.

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

analyze config testconf 5 days

 

sp_ctrl (alvspxl11:8567)> show analyze

Process Source Target State PID
------ ------------ ----------- ------- ----
Capture o.w111a64f   Running 2968
Analyze o.w111a64f   Running 2976

 

append status

Use the append status command to view the status of the last copy or append command job run. The append status command can be used to view detailed status on a copy or append job or a portion of a copy or append job, or to view status on all copy and append jobs for which SharePlex has history.

For details and examples about using the append status command, see the job status command.

Usage

Supported sources: Oracle
Supported targets: Oracle
Authorization level: Viewer
Issued for: source or target
Related commands: copystatus

Syntax

Basic command Command options Remote options
job status

[job_id]

[Job_id.table_id]

[all]

[full]

[detail]

[status]

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

Syntax description

Component Description
job_id

Displays status history for the job with the specified SharePlex-assigned job ID.

Example:

sp_ctrl(sysA)>job status 2828.2

job_id.table_id

Displays status history for the job with the specified SharePlex-assigned job ID and table.

Example:

sp_ctrl(sysA)>job status 2828.HR.SRC_TEST3

all

Displays a summary line for every job with history in the database.

Example:

sp_ctrl(sysA)>job status all

full

Displays the status of every object in the job. By default, the job status command displays the status of those objects not completed, or completed with an exceptional status.

Example:

sp_ctrl(sysA)>job status 2828 full

detail

Displays detail information for every object reported upon. By default, the job status command displays a summary line for every object reported upon. Note that the detail information is the same as is displayed for the job_id.table_id option.

Example:

sp_ctrl(sysA)>job status detail

[status]

Displays status history for previous jobs with the specified status.

Example:

sp_ctrl(sysA)>job status "Error"

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

 

authlevel

Use the authlevel command to determine your authorization level for issuing SharePlex commands on a system.

The following is an example of the display:

User is a viewer (level=3)

Usage

Supported sources: Oracle and SQL Server
Supported targets: All
Authorization level: Viewer (3)
Issued for: source or target system
Related commands: none

Syntax

Basic command Remote options
authlevel

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

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