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
Syntax
|
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
| 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
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
| analyze config filename |
n {minutes | hours | days} |
[ on host |
on host:portnumber |
on login/password@host |
on login/password@host:portnumber ] |
Syntax 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.
| 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 |
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
| 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
| 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.
| 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 |
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 |
| Supported targets: |
All |
| Authorization level: |
Viewer (3) |
| Issued for: |
source or target system |
| Related commands: |
none |
Syntax
| 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.
| 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 |
