Use the target command to configure Post to support a specific type of target. This is known as the target configuration. The properties stored in the target configuration control how Post processes, formats, and outputs replicated data.
The target command also provides options to reset or view target configuration settings.
The target command can be used to control a target configuration both at the global level or, if the queue option is used, at the per-process level.
Important: Make certain to stop and then restart the Post process after using this command.
Usage
| Supported sources: |
Oracle |
| Supported targets: |
All |
| Authorization level: |
Operator (2) |
| Issues on: |
target system |
| Related commands: |
connection |
Syntax
|
target {o. | r. | x. | c.}target
[queue queuename]
{command_option} |
{
{ set category property |
reset [category [property] ] |
show [category ] } |
switch
} |
Not available |
Syntax description
| {o. | r. | x. | c.}target |
The target for which you are configuring Post. The letter preceding the name of the target identifies the type of target, whether an Oracle database, Open Target database, JMS, Kafka broker, file, or change-history target.
Possible targets are:
o.SID
where: SID is the ORACLE_SID of an Oracle database.
r.database
where: database is the name (not the DSN) of an Open Target database.*
x.jms
where: jms is a Java Message Service (JMS) queue or topic.
x.kafka
where: kafka is an Apache Kafka broker.
x.file
where: file is a structured file that contains data in one of the supported formats (see format category).
c.SID
where: SID is the ORACLE_SID of a target Oracle database that is configured as a change-history target.
| * Note: The SharePlex-supported datastore types, data types, and operations are listed in the SharePlex Release Notes. | |
| queue queuename |
Constrains the action of the command to the SharePlex Post process that is associated with the specified queue. This action overrides any global settings for the target property you are setting. Without the queue option, the target command affects all Post processes in the local SharePlex instance.
Example: the following sets metadata properties for the Post process that is associated with the myjms1 queue.
target x.jms queue myjms1 set metadata time, userid, trans, size |
|
{
set category property |
reset [category [property]] |
show [category] |
switch
} |
- set applies a configuration property to the target configuration.
- reset clears configuration settings back to their defaults (if one exists). It can be constrained to a specific category and (where applicable) a specific property.
Note:reset does not reset one property value to another. Use another set command for that.
- show displays configuration settings.
- category is any category of configuration properties. The properties are grouped by category for efficient management through commands. See Category descriptions.
- property is the configuration property that you want to set, reset, or show. There are different properties per category.
- switch applies only to file targets (x.file). Use this option to create a new active output file and save the current one as a serially numbered aged file. After issuing this command, the switch occurs after Post processes a new record. This option takes no input and is not associated with any configuration categories.
|
Category descriptions
| cdc |
Overrides the global settings for an Oracle change-history target that are set with the SP_OPO_TRACK_PREIMAGE parameter for a specific table. See CDC category. |
| characterset |
Directs the Post process to convert replicated data to the specified character set before being posted to an Open Target target database. See Characterset category. |
| file |
Sets the properties of a file target. See file category for more information. |
| format |
Sets the format of the data that is written to the target. See format category for more information. |
| json |
Sets the properties of JSON output when format record=json. See JSON category for more information. |
| jms |
Sets the properties of a JMS target. See JMS category for more information. |
| kafka |
Sets the properties of a Kafka target. See kafka category for more information. |
| metadata |
Adds metadata properties to the data that is replicated by SharePlex. See Metadata category for more information. |
| resources |
Sets parameters that affect resources on the target system. See Resources category |
| rule | filter |
Specifies a conditional statement that applies a tracking rule or filter to a specific table in an Oracle change-history target. See Rule | Filter category |
| source |
Sets source identifier properties for an Oracle change-history target. See Source category for more information. |
| sql |
Sets the properties of SQL output when format record=sql. See SQL category for more information. |
CDC category
Supported targets
Oracle change-history target
Command options
target c.SID [queue queuename]
table tablename
{
set cdc preimage={yes | no}
reset cdc
show cdc
}
Usage
Overrides the global setting for updates that is set with the SP_OPO_TRACK_PREIMAGE parameter for a specific table. This controls whether the before image of update operations are tracked in the history table. Valid values are yes to track before images of updates, or no to exclude update before images.
Characterset category
Supported targets
Open Target databases
Command options
target r.database [queue queuename]
{
set characterset character_set |
reset characterset character_set |
show characterset character_set
}
Usage
Directs the Post process to send replicated data to the local Oracle client to be converted to the specified character set before being posted to the target database. The specified character set overrides the default character set of Unicode that is used by Post when posting character data to Open Target targets.
UTF8 to UTF8 emoji replication works fine for Oracle to Open Poster. However, when you are inserting emoji characters into an Oracle source database that is not in UTF8 format (like CESU8), in such scenarios, to replicate emoji characters to a non-Oracle target, the following configuration settings need to be applied to the target SharePlex instance:
-
Install the Oracle client on the target for data conversion.
-
Set the SP_OPX_NLS_CONVERSION parameter to 1 (default).
-
Set the target character set to AL32UTF8 using the target command for converting data to the valid UTF8 byte sequence required by the target DB.
Note: To use this option, there must be an Oracle client on the target system. For more information, see the Open Target Checklist section in the SharePlex Installation and Setup Guide.
| character_set |
The Oracle equivalent of the target character set, specified by its Oracle name. |
Unicode |
Example
To post the data in the GB2312 character set on the target, specify the Oracle ZHS16GBK character set.
target r.mymss set characterset ZHS16GBK
file category
Supported targets
File
Command options
target x.file [queue queuename]
{
set file property=value |
reset file [property] |
show file
}
Usage
Sets the properties of a file target.
|
location=pathname |
Path name under the SharePlex variable-data directory where you want the file to be created |
opx |
|
max_records=number |
Maximum size of the active file, measured by the number of records, before switching files. |
50,000 |
|
max_size=megabytes |
Maximum size of the file, measured in megabytes, before switching files. |
50 |
|
max_time=seconds |
Maximum number of seconds to wait before switching files. |
300 |
|
record_length=number |
Maximum size of a record, in number of characters |
132 |
Example
target x.file set file max_size=320
format category
Supported targets
File
Kafka
Command options
target x.{file | kafka} [queue queuename]
{
set format property=value |
reset format [property] |
show format
}
Usage
Sets the format of the data that is written to the target.
|
date=format |
Date format |
yyyy-MM-dd HH:mm:ss |
|
decimal=character |
Decimal character |
. (period) |
|
enotation=notation |
Exponential notation |
14 |
|
record=record_format |
Format of the output records. Valid values are sql, xml, or json. |
xml |
|
timestamp=format |
Timestamp format |
yyyy-MM-ddTHH:mm:ss.ffffffffff |
Example
target x.file set format record=sql
target x.kafka set format record=json
Descriptions of output formats
The following are descriptions of the output formats provided by format record=record_format.
SQL record format
Every transaction in a SQL-formatted file is headed by a comment that includes the transaction sequence within the SQL file and a unique transaction ID. A comment line at the end of the SQL file has the number of lines in the file. For example, the following is a SQL file with one transaction. In this example the transaction id is 2-113319. The file has nine lines.
/installed/vardir> cat opx/0000000010_20140305140820_legacy.sql
-- 0000000001 2-113319 03052014140813 03052014140813
DELETE FROM "ROBIN"."TEST_TYPES" WHERE ORA_NUMBER = '22345' AND ROWNUM = 1;
INSERT INTO "ROBIN"."TEST_TYPES" (ORA_NUMBER, ORA_DATE, ORA_RAW, ORA_ROWID,
ORA_FLOAT, ORA_CHAR, ORA_VARCHAR2, ORA_TIMESTAMP, ORA_TIMESTAMP_TZ,
ORA_TIMESTAMP_LTZ) VALUES('22345', '08132066000000', '0123456789ABCDEF'
, 'AAAAAAAAAAAAAAAAAA', '12350', 'Character ', 'Variable data'
, '10201998021300.22000', '06172002080000.00000', '06172002160000.00000');
COMMIT;
-- EOF 0000000009
XML record format
The XML format is separated into operation and schema "types" for easier consumption. They are actually the same when viewed from an XSD perspective and are not distinct types. The template XML represents all possible attributes and elements. The individual XML represents the bare minimum output for each supported operation.
After startup, the first time that Post writes a change record for any given table, it first writes a schema record for that table. Each schema record contains the table name and details of interest for each columns. A schema record is written only once for each table during a Post run, unless there is a change to that schema, and then a new schema record is written. If Post stops and starts, schema records are written again, once for each table as Post receives a change record for it.
Schema record template
<?xml version="1.0" encoding="UTF-8" ?>
<?opentarget version="1.0" ?>
<opentarget>
<txn
id="xs:integer"
oracleTxnId="xs:string"
commitTime="xs:dateTimeStamp" />
<tbl
name="xs:string"
utcOffset="xs:integer"
<cmd ops="schema">
<schema>
<col
name="xs:string"
xmlType="xs:string"
key="xs:boolean"
nullable="xs:boolean"
length="xs:integer"
/>
</schema>
</cmd>
</tbl>
</opentarget>
Table 1: Explanation of schema template (* = optional)
|
txn |
|
Transaction metadata |
|
|
id |
ID of current transaction |
|
|
oracleTxnId * |
Oracle transaction ID |
|
|
commitTime* |
Transaction commit timestamp |
|
tbl |
|
Table metadata |
|
|
name |
Fully qualified name of the table |
|
|
utcOffset |
UTC offset in the log |
|
cmd |
|
Operation metadata (In the case of a schema, there are no operations.) |
|
|
ops |
Type of record generated for this table. For a schema, the value is schema. |
| schema |
|
Column metadata |
| col |
|
Metadata for a column (One of these elements appears for every record in the table.) |
|
|
name |
Name of the column |
|
|
xmlType |
XML data type |
|
|
key |
Key flag (true, false) |
|
|
nullable |
Nullable flag |
|
|
length |
Length of the column |
Operation record template
<?xml version="1.0" encoding="UTF-8" ?>
<?opentarget version="1.1" ?>
<opentarget>
<txn
id="xs:integer"
msgIdx="xs:integer"
msgTot="xs:integer"
oracleTxnId="xs:string"
commitTime="xs:dateTimeStamp"
userId="xs:string" /> <tbl
name="xs:string"
<cmd ops="xs:string">
<row id="xs:string">
<col name="xs:string"></col>
<lkup>
<col name="xs:string"></col>
</lkup>
</row>
</cmd>
</tbl>
</opentarget>
Table 2: Explanation of operation template (* = optional)
| txn |
|
Transaction metadata for the operation |
| |
id |
ID of current transaction |
| |
msgIdx |
Index of current record in the transaction |
| |
msgTot* |
Total number of messages in transaction |
| |
oracleTxnId * |
Oracle transaction ID, taken from the System Change Number (SCN) |
| |
commitTime* |
Transaction commit timestamp |
| |
userId * |
User ID that performed the operation |
| tble |
|
Table metadata |
| |
name |
Fully qualified table name |
| cmd |
|
Operation metadata |
| |
ops |
Operation type (insert, update, delete, truncate) |
| row |
|
Metadata of the row that changed in the operation |
| |
id |
Oracle ROWID |
| col |
|
Change data for a column (One of these elements appears for every changed column in the operation.) |
| |
name |
Column name with the after value for that column |
| lkup |
|
Before image for use in update and delete operations |
| col |
|
Before image of column (One of these elements appears for every changed column in the operation.) |
| |
name |
Column name with the before value or the key value (depending on the operation) for that column |
Note: The id and msgIdx attributes together uniquely identify an operation.
Supported data types
See the SharePlex Release Notes for a chart that shows how Oracle data types are converted to XML.
Sample XML records
Source table
This is the table for which the sample operations are generated.
SQL> desc products
|
Name |
Null? |
Type |
|
PRODUCT_ID |
NOT NULL |
NUMBER |
|
DESCRIPTION |
|
VARCHAR2(600) |
|
PRICE |
|
NUMBER |
Source DML operations
insert into products values (230117, ‘Hamsberry vintage tee, cherry’, 4099);
commit;
update products set price=3599 where product_id=230117 and price=4099;
commit;
delete products where product_id=230117;
commit;
truncate table products;
Schema record
<?xml version="1.0" encoding="UTF-8"?>
<?opentarget version="1.1"?>
<opentarget>
<txn id="2218316945" commitTime="2014-10-10T13:18:43" userId="85" oracleTxnId="3.10.1339425" />
<tbl name="MFG.PRODUCTS" utcOffset="-5:00">
<cmd ops="schema">
<schema>
<col name="PRODUCT_ID" xmlType="decimal" key="true" nullable="false" length="22" />
<col name="DESCRIPTION" xmlType="string" key="false" nullable="true" length="600" />
<col name="PRICE" xmlType="decimal" key="false" nullable="true" length="22" />
</schema>
</cmd>
</tbl>
</opentarget>
Insert record
<?xml version="1.0" encoding="UTF-8"?>
<?opentarget version="1.1"?>
<opentarget>
<txn id="2218316945" msgIdx="1" msgTot="1" commitTime="2014-10-10T13:18:43" userId="85" oracleTxnId="3.10.1339425" />
<tbl name="MFG.PRODUCTS">
<cmd ops="ins">
<row id="AAAmDbAAEAAApRrAAA">
<col name="PRODUCT_ID">230117</col>
<col name="DESCRIPTION">Hamsberry vintage tee, cherry</col>
<col name="PRICE">4099</col>
</row>
</cmd>
</tbl>
</opentarget>
Update record
<?xml version="1.0" encoding="UTF-8"?>
<?opentarget version="1.1"?>
<opentarget>
<txn id="2218318728" msgIdx="1" msgTot="1" commitTime="2014-10-10T13:19:12" userId="85" oracleTxnId="1.17.970754" />
<tbl name="MFG.PRODUCTS">
<cmd ops="upd">
<row id="AAAmDbAAEAAApRrAAA">
<col name="PRICE">3599</col>
<lkup>
<col name="PRODUCT_ID">230117</col>
<col name="PRICE">4099</col>
</lkup>
</row>
</cmd>
</tbl>
</opentarget>
Delete record
<?xml version="1.0" encoding="UTF-8"?>
<?opentarget version="1.1"?>
<opentarget>
<txn id="2218319446" msgIdx="1" msgTot="1" commitTime="2014-10-10T13:19:25" userId="85" oracleTxnId="5.23.1391276" />
<tbl name="MFG.PRODUCTS">
<cmd ops="del">
<row id="AAAmDbAAEAAApRrAAA">
<lkup>
<col name="PRODUCT_ID">230117</col>
</lkup>
</row>
</cmd>
</tbl>
</opentarget>
Truncate record
<?xml version="1.0" encoding="UTF-8"?>
<?opentarget version="1.1"?>
<opentarget>
<txn id="2218319938" commitTime="1988-01-01T00:00:00" userId="85" oracleTxnId="11.4.939801" />
<tbl name="MFG.PRODUCTS">
<cmd ops="trunc" />
</tbl>
</opentarget>
JSON record format
Note: This is default output with the exception that target x.kafka set json indent=2 was used to make the output more readable.
This is the table for which the sample operations are generated.
SQL> desc products
|
Name |
Null? |
Type |
|
PRODUCT_ID |
NOT NULL |
NUMBER |
|
DESCRIPTION |
|
VARCHAR2(600) |
|
PRICE |
|
NUMBER |
Source DML operations
insert into products values (230117, ‘Hamsberry vintage tee, cherry’, 4099);
commit;
update products set price=3599 where product_id=230117 and price=4099;
commit;
delete products where product_id=230117;
commit;
truncate table products;
Schema record
{
"meta":{
"op":"schema",
"table":""
},
"schema":{
"name":"BILL.PRODUCTS",
"utcOffset":"-7:00",
"PRODUCT_ID":{
"jsonType":"decimal",
"num":1,
"key":1,
"nullable":1,
"length":22,
"precision":0,
"scale":0,
"src_name":"PRODUCT_ID"
},
"DESCRIPTION":{
"jsonType":"string",
"num":2,
"key":1,
"nullable":1,
"length":600,
"precision":0,
"scale":0,
"src_name":"DESCRIPTION"
},
"PRICE":{
"jsonType":"decimal",
"num":3,
"key":1,
"nullable":1,
"length":22,
"precision":0,
"scale":0,
"src_name":"PRICE"
}
}
}
Insert record
{
"meta":{
"op":"ins",
"table":"BILL.PRODUCTS"
},
"data":{
"PRODUCT_ID":230117,
"DESCRIPTION":"Hamsberry vintage tee, cherry",
"PRICE":4099
}
}
Update record
{
"meta":{
"op":"upd",
"table":"BILL.PRODUCTS"
},
"data":{
"PRICE":3599
},
"key":{
"PRODUCT_ID":230117,
"DESCRIPTION":"Hamsberry vintage tee, cherry",
"PRICE":4099
}
}
Delete record
{
"meta":{
"op":"del",
"table":"BILL.PRODUCTS"
},
"data":{
"PRODUCT_ID":230117,
"DESCRIPTION":"Hamsberry vintage tee, cherry",
"PRICE":3599
}
}
Truncate record
{
"meta":{
"op":"truncate",
"table":"BILL.PRODUCTS"
}
}
JMS category
Supported targets
JMS
Command options
target x.jms [queue queuename]
{
set jms property=value |
reset jms [property] |
show jms
}
Usage
Sets the properties of a JMS target.
|
factory_class=factory_class |
Required Fully qualified class name of the factory class. Sets the JNDI environmental property java.naming.factory.initial to specify the class name of the initial context factory for the provider. |
None |
|
provider_url=url |
Required RMI URL with no object name component. This sets the JNDI environmental property java.naming.provider.url to specify the location of the registry that is being used as the initial context.
Use the correct format depending your JMS Provider and type of URL. For example, if using LDAP your URL might be similar to the following:
ldap://hostname.company.com/contextName
Ask your JMS Provider Administrator for the JMS Provider URL. |
None |
|
lib_location=path |
Required Path to the directory where you installed the client library files. |
None |
|
destination={queue | topic} |
Messaging domain. Valid values are queue (port-to-port) or topic (publisher-subscriber model). |
queue |
|
factory_name=factory_name |
Name of a JNDI connection factory lookup. You can specify multiple names with a comma-separated list, for example: (jndi.name1, jndi.name2). |
None |
|
user=user |
Name of the user that is attaching to JMS. If authentication is not required, omit this and the password option. |
None |
|
password=password |
Password of the JMS user. |
None |
|
queuename=JMS_topic_queuename |
Name of the JMS queue or topic. |
OpenTarget |
|
persistent={yes | no} |
yes logs messages to disk storage as part of send operations.
no prevents logging. |
yes |
| session_transacted ={yes | no} |
no directs Post to issue a JMS commit for every replicated message, making each one immediately visible and consumable. This is the default.
yes directs Post to operate in a transactional manner. In this mode, Post issues a JMS commit (to make messages visible and consumable) at intervals based on the following:
|
no |
| properties |
Use this option if the JMS provider that you are using cannot consume messages that contain the default set of properties supplied by SharePlex. It enables you to remove or add properties. Supply the properties as a comma-delimited list.
- To add a property, specify it as name=value.
- To remove a SharePlex property, prefix the name with a dash. For example this string removes two SharePlex properties: -JMSXDeliveryCount,-JMSXGroupSeq.
|
None |
| client_id |
Use this option if the JMS provider that you are using cannot consume messages that contain the default SharePlex client ID. Set this value to the client ID that your provider accepts. |
None |
| commit_frequency |
Use this option when Post is configured to post to a JMS server in transactional style (issue a JMS commit at intervals, rather than after every message as directed by the session_transacted property). This parameter specifies the interval between JMS commits. It works in conjunction with the SP_OPX_READRELEASE_INTERVAL parameter. Valid values are 1 to any positive integer. |
1500 |
Example
target x.jms set jms queuename=SharePlexJMS
JSON category
Supported targets
File (format record=json)
Kafka (format record=json)
Command options
target x. {file | kafka } [queue queuename]
{
set json property=value |
reset json [property] |
show json
}
Usage
Sets the properties of JSON output when format record =json.
|
before |
-
yes includes the before image of the changed row.
It produces a record similar to the following, with the before image at the end of the line: {"meta":{"op":"upd","table":"BILL.JSON"},"data":{"C2":"Bye"},
"key":{"C1":"1","C2":"Hello"}}
{"meta":{"op":"upd","table":"BILL.JSON"},
"data":{"C1":"2"},"key":{"C1":"1"}}
-
no excludes the before image of the changed row. It roduces a record similar to the following: {"meta":{"op":"upd","table":"BILL.JSON"},"data":{"C2":"Bye"}}
{"meta":{"op":"upd","table":"BILL.JSON"},"data":{"C1":"2"}}
Disclaimer: In case of the Update operations, use of "before" with "yes" value is required in order to view the key(s) for the updated record(s). |
yes |
|
commit |
-
yes includes the commit records. It produces a record similar to the following: {"meta":{"op":"commit","table":""}}
- no omits commit records.
IMPORTANT! When commit is set to yes, there can only be one target topic. |
yes |
| ddl |
-
yes includes ALTER TABLE commands. It produces a record similar to the following: {"meta":{"op":"ddl","table":"BILL.JSON"},
"sql":{"ddl":"alter table \"BILL\".\"JSON\""}
- No omits ALTER TABLE commands.
|
yes |
| eol |
yes includes an end-of-line character at the end of the JSON document.
no excludes an end-of-line character. |
yes |
|
indent |
Controls the level of indentation of the records.
-
A value of 0 places the entire record on one line, as follows:
{"meta":{"op":"ins","table":"BILL.JSON"},
"data":{"C1":"1","C2":"Hello ",
"C3":"There","C4":"2017-03-20T09:46:34",
"C5":"2017-03-20T09:46:34.735370000"}}
-
A value greater than 0 indents the metadata (if included) and data lines by the specified number of characters.
It splits the record logically onto multiple lines, as in the following example (indent= 4) : {
"meta":{
"op":"ins",
"table":"BILL.JSON"
},
"data":{
"C1":"1",
"C2":"Hello",
"C3":"There",
"C4":"2017-03-20T10:02:37",
"C5":"2017-03-20T10:02:37.456949000"
}
}
|
0 |
| meta |
-
yes includes the metadata section of the JSON record. It produces a record similar to the following: {"meta":{"op":"ins","table":"BILL.JSON"},
"data":{"C1":"1","C2":"Hello","C3":"There",
"C4":"2017-03-20T10:04:44",
"C5":"2017-03-20T10:04:44.957758000"}}
{"meta":{"op":"upd","table":"BILL.JSON"},"data":{"C2":"Bye"}}
{"meta":{"op":"upd","table":"BILL.JSON"},"data":{"C1":"2"}}
{"meta":{"op":"del","table":"BILL.JSON"},"data":{"C1":"2"}}
{"meta":{"op":"commit","table":""}}
-
no omits the metadata section and produces a record similar to the following: {"data":{"C1":"1","C2":"Hello","C3":"There",
"C4":"2017-03-20T10:05:09",
"C5":"2017-03-20T10:05:09.268094000"}}
{"data":{"C2":"Bye"}}
{"data":{"C1":"2"}}
{"data":{"C1":"2"}}
{}
Note: To specify which metadata fields appear in the record, use the set metadata property command. See Metadata category.
Example:
target x.kafka set metadata time,userid,op,scn |
yes |
| schema |
- yes includes schema records.
-
no omits schema records.
A schema record for objects in replication is produced when a new SharePlex
configuration is activated or when a table has a DDL change.
A value of yes (with indent=4) produces a record similar to the following: {
"meta":{
"op":"schema",
"table":""
},
"schema":{
"name":"BILL.JSON",
"utcOffset":"-7:00",
"C1":{
"jsonType":"decimal",
"num":1,
"key":1,
"nullable":0,
"length":22,
"precision":0,
"scale":0,
"src_name":"C1"
},
"C2":{
"jsonType":"string",
"num":2,
"key":0,
"nullable":1,
"length":10,
"precision":0,
"scale":0,
"src_name":"C2"
},
"C3":{
"jsonType":"string",
"num":3,
"key":0,
"nullable":1,
"length":10,
"precision":0,
"scale":0,
"src_name":"C3"
} }
} |
yes |
Example
target x.kafka set json meta=no
kafka category
Supported targets
Kafka
Command options
target x.kafka [queue queuename]
{
set kafka property=value |
reset kafka [property] |
show kafka
}
Usage
Sets the properties of a Kafka target.
| broker=broker |
Required. The host and port number of the Kafka broker, or a comma delimited list of multiple brokers. This list is the bootstrap into the Kafka cluster. So long as Post can connect to one of these brokers, it will discover any other brokers in the cluster. |
localhost:9092 |
| client_id=ID |
Optional. A user-defined string that Post will send in each request to help trace calls. |
None |
| compression.code={none, gzip, snappy} |
Optional. Controls whether data is compressed in Kafka. Options are none, gzip or snappy. |
None |
| partition={number | rotate | rotate trans| messagekey} |
Required. One of the following:
|
Notes:
-
The LOB and CLOB columns are not considered Kafka partition keys.
-
For a table without a primary key, unique key, composite key, or unique index, all columns (except LOB and CLOB columns) will be considered key columns. When performing an alter query on such a table, the DDL statement will be replicated to all partitions, and subsequent DML statements will be sent to specific partitions based on the existing columns.
-
If the replication table has no key defined, SharePlex will consider all table columns as Kafka messagekey. For non-key tables, it is recommended to use SharePlex user-defined keys. For more information, see the Define a Unique Key: PostgreSQL to PostgreSQL section in the SharePlex Admin Guide.
-
In cases where multiple tables are involved in replication, if we want a specific table to have a different partition type, while the remaining tables are partitioned based on the messagekey, we can define a named post queue for those specific tables.
For example:
target x.kafka queue <queue_name> set kafka partition={number/rotate/rotate trans}
For the rest of the tables, use the below command:
target x.kafka set kafka partition=messagekey |
|
Important:
When partitioning is based on the messagekey, messages that do not contain key information will be mapped according to Kafka's internal hash function. These messages may include commit, schema, rollback, savepoint, and DDL statements.
During replication, if the number of partitions is increased, the existing mapping of keys to partitions will no longer remain valid.
For tables with a few columns serving as indexes and no other constraints defined, use those indexes as unique keys in the SharePlex config file.
For example, the following table has a unique index defined on two columns: ID and NAME.
create table mytable(ID NUMBER(25,2),NAME CHAR(200),COL_VARCHAR2 VARCHAR2(400),COL_RAW RAW(1000));
CREATE INDEX indx_mytable ON mytable(ID,NAME);
In the SharePlex config file, define the index columns as a unique key.
| datasource:o.SID |
| src.mytable |
!key(ID,NAME) |
host |
For more information, see the Define a Unique Key: Oracle to Oracle section in the SharePlex Admin Guide.
For tables with no constraints or indexes defined, users can define unique keys during configuration in SharePlex.
For a table that has a composite key, if any of the key values are modified, the modification message will be placed in the current partition, and subsequent messages may or may not be assigned to the same partition. | |
0 |
| request.required.acks=value |
Optional. This is a Kafka client parameter. By default it is set to a value of -1, which means all. Consult the Kafka documentation about this subject, because all really means all in-sync replicas. This parameter can be used in conjunction with the min.insync.replicas broker parameter to tune behavior between availability and data consistency.
Important: It is possible for data to be lost between a Kafka producer (SharePlex in this case) and a Kafka cluster, depending on these settings. |
-1 |
| topic=topic_name |
Required. The name of the target Kafka topic.
This string may contain the special sequences %o or %t. The %o sequence is replaced by the owner name of the table that is being replicated. The %t sequence is replaced by the table name of the table that is being replicated. This feature may be used in conjunction with a Kafka server setting of auto.create.topics.enabled set to 'true'. Also view your server settings for default.replication.factor and num.partitions because these are used as defaults when topics are auto created.
Important! If using multiple topics, you must also set the following properties with the target command:
-
The output must be in JSON. Set the record property of the format category to json:
target x.kafka set format record=json
-
Commits must be disabled. Set the commit property of the json category to no:
target x.kafka set json commit=no | |
shareplex |
* To avoid latency, if Post detects no more incoming messages, it sends the packet to Kafka immediately without waiting for the threshold to be satisfied.
Example
sp_ctrl> target x.kafkaset kafka broker=host1:9092,host2:9092,host3:9092
sp_ctrl> target x.kafkaset kafka topic=shareplex
Metadata category
Supported targets
Oracle replication targets
Oracle change-history targets
HANA replication targets
File
JMS
Kafka
Command options
target {o.SID | c.SID | r.database | x.file | x.jms | x.kafka} [queue queuename]
{
set metadata [colname:]property[, ...] |
reset metadata |
show metadata
}
Usage
The target command with set metadata can be used to override the default metadata properties that are set for a database target, an XML or JSON file target, a JMS target, or a Kafka target. All of these target types provide metadata in the Post output.
- Oracle and HANA replication targets: Metadata can be configured for Oracle and HANA replication targets. The metadata columns must exist on the target. SharePlex does not add the metadata columns.
- Oracle change-history target: If no target specification is made, Post updates the default metadata columns, which must exist in their default names before starting replication. In the following table, the default column names are listed if a property is included in the change-history target by default. Use set metadata only to add additional metadata or to assign a custom name to a column (use the colname option).
- For targets that support XML input (JMS, Kafka, file) the metadata is formatted per the XML schema. Certain metadata is included in the output by default, as shown in the following table, without the need to use set metadata. Use set metadata only to add additional metadata.
- For targets that support JSON input (Kafka), the op and table properties are included by default unless you set json meta=no.
For more information about configuring SharePlex to support a change history target database, see the SharePlex Administrator Guide.
To set multiple metadata properties, specify them as a comma-separated list.
| time |
Time the operation was applied on the source, as supplied by Oracle.
Important! In Oracle version 11g and later, the time appears only in transactional boundary records, for example the start of a transaction. Based on the linear design of both the Oracle redo logs and the way that SharePlex replicates operations in transactional order, SharePlex applies the timetamp contained in the originating record to subsequent records until it encounters a record with a different timestamp.
Default column name: SHAREPLEX_SOURCE_TIME
data type: TIMESTAMP
This property is included by default in XML output and change-history tables. |
Oracle replication target
Change-history target
JSON and XML output |
| userid |
User ID that performed the operation
Default column name: SHAREPLEX_SOURCE_USERID
data type: NUMBER
This property is included by default in XML output and change-history tables. |
Oracle replication target
Change-history target
JSON and XML output |
| op |
Type of operation (INSERT, UPDATE, DELETE, TRUNCATE, DROP COLUMN, UPDATE BEFORE, UPDATE AFTER). For JMS implementations, this field is always added to the XML output as a field named SPOps. To add it to the JMS properties, issue an explicit target command with the op option.
Default column name: SHAREPLEX_SOURCE_OPERATION
data type: VARCHAR2
This property is included by default in XML and JSON output and change-history tables. |
Oracle replication target
Change-history target
JSON and XML output |
| scn |
Source SCN for when the operation was applied
Default column name: SHAREPLEX_SOURCE_SCN
data type: NUMBER
This property is included by default in change-history tables. |
Oracle replication target
Change-history target
HANA replication target
JSON output |
| rowid |
ROWID of the row that changed
Default column name: SHAREPLEX_SOURCE_ROWID
data type: ROWID
This property is included by default in change-history tables. |
Oracle replication target
Change-history target
JSON output |
| trans |
Transaction ID for the operation. For JMS implementations, this field is added to both the XML output and the JMS properties as a field named SPTxnId.
Default column name: SHAREPLEX_SOURCE_TRANS
data type: VARCHAR2
This property is included by default in change-history tables. |
Oracle replication target
Change-history target
JSON output |
| seq |
Order of the operation within the transaction
Default column name: SHAREPLEX_OPERATION_SEQ
data type: NUMBER
This property is included by default in change-history tables. |
Oracle replication target
Change-history target
JSON output |
| host |
Name or IP address of the source host
Default column name: SHAREPLEX_SOURCE_HOST
data type: VARCHAR2
This property is included by default in change-history tables. |
Oracle replication target
Change-history target
JSON output |
| posttime |
The time that the operation was posted to the target. |
JSON output |
| queue |
Name of the SharePlex queue
Default column name: SHAREPLEX_QUEUENAME
data type: VARCHAR2
This property is included by default in change-history tables. |
Oracle replication target
Change-history target
JSON output |
| source |
User-defined source identifier that was set with the set source option of target. See Source category.
Default column name: SHAREPLEX_SOURCE_ID
data type: VARCHAR2
This property is included by default in change-history tables. |
Oracle replication target
Change-history target
JSON output |
| changeid |
Unique sequential ID of the operation
Default column name: SHAREPLEX_CHANGE_ID
data type: NUMBER
This property is included by default in change-history tables. |
Oracle replication target
Change-history target |
| size |
Number of operations in the transaction.
|
JSON output
Kafka |
| table |
The name of the target table. The name of the target table will be added to the JMS properties as a field named SPTblName.
This property is included by default in JSON output. |
JMS
JSON and XML output |
| idx |
The index of this operation within the transaction. This will be added to the JMS properties as a field named SPMsgIdx. It will be formatted in the JMS properties as m/n where n is the total number of operations in the transaction and m is the operation number within the transaction. |
JMS |
JMS and Kafka set metadata example
The following sets all Post processes to add the IP address or hostname of the source, name of the SharePlex queue, and the number of operations in the transaction to the output in the JMS target.
target x.jms set metadata host, queue, size
or...
target x.kafka set metadata host, queue, size
Oracle set metadata example
The following example sets the Post process associated with the myqueue1 queue to add the time of the operation and the userid that executed it to the Timestamp and User columns, both of which are user-defined names that are case-sensitive.
target c.targSID queue myqueue1 set metadata time:"Timestamp", userid:"User"
Resources category
Supported targets
All targets
Command options
target {o. | r. | x. | c.} target [queue queuename]
{
set resources property=value |
reset resources [property] |
show resources [for datastore]
}
Usage
Sets parameters that affect resources on the target system.
|
commit_frequency=number_of_operations |
Specifies a maximum number of operations after which Post issues a commit. Can be any integer greater than 1. Can be used to divide a large transaction into two or more smaller ones, each containing, at most, the specified number_of_operations. This option can work around resource limits that affect large transactions, such as the number of row locks permitted per transaction.
Note: The "commit_frequency" property is supported only for the JMS target. |
50000 |
| max_active_statements=number_of_cursors |
Specifies the number of concurrent active SQL statements that Post can process to a target database in one session. Can be any integer up to, and including, the maximum number of active statements permitted by the database or client driver. It is only used by Post when the SP_OPX_SQL_CACHE_DISABLE parameter is set to 0 (enabled). |
16 |
Examples
target r.mydb queue q1 set resources commit_frequency=10000
target r.mydb queue q1 set resources max_active_statements=10
Rule | Filter category
Supported targets
Oracle change-history target
Command options
target c.SID [queue queuename]
table tablename
{
set {rule | filter} { 'column_condition' | !filename }
reset {rule | filter}
show {rule | filter}
}
Usage
Specifies a conditional statement that applies a tracking rule or filter to a specific table. Applies to UPDATE operations. Applies conditional logic that qualifies the data that you want to track to a target table, while excluding data that is not of interest. There can be one rule or filter per target table, but you can combine nested expressions with parentheses and the AND, OR, and NOT logical connectives to create a wide variety of conditions.
| rule | filter |
- rule directs Post to insert a row only if the UPDATE operation matches the column_condition. If the UPDATE does not match the column condition, the row is discarded.
- filter directs Post to insert a row only if the UPDATE does not match the column_condition. If the UPDATE does match the column condition, the row is discarded.
|
| table tablename |
The name of the target table for which you are setting the rule or filter. |
|
column_condition |
The conditional logic that must be satisfied by the UPDATE operation. column_condition represents the entire conditional statement, which must be enclosed within single quotes. A column condition can be one or any combination of the following expressions, each enclosed within parentheses:
(column is changed)
(column is not changed)
([column1, column2, ...] matches change_list)
([column1, column2, ...] contains change_list)
Where:
- column is the name of a target column. Use a comma to separate column names in a list, and enclose a list within brackets.
- is changed means that the specified column(s) are changed in the UPDATE operation.
- is not changed means that the specified column(s) are not changed in the UPDATE operation.
- change_list is a logical representation of the source columns that changed.
- matches change_list is satisfied when the condition is an exact match to the change list (no fewer columns than the change list, no more columns than the change list, same names).
- contains change_list is satisfied when the condition contains all of the columns in the change list. The change list can be a subset of the columns in the condition, but it cannot contain more columns than the change list. Names must match.
|
| !filename |
Enables you to store a large column condition in a text file, and then specify the file, rather than the entire column condition syntax, when you run the target command. |
Note: Batch and direct load operations are not supported by the rule/filter feature.
Examples
These commands show some examples of how to set different rules by combining expressions:
target c.mySIDtable mytable set rule '(col3 is not changed) and (col5 is not changed)'
target c.mySIDtable mytable set rule '([col1, col3] matches change_list)'
target c.mySIDtable mytable set rule 'not ([col2, col5] contains change_list)'
The following table shows the different ways that rule and filter work to include or discard rows.
| rule = '(c2 is not changed)' |
c2 |
no |
| rule = '(c2 is not changed)' |
c3 |
yes |
| rule = '(c2 is changed)' |
c2 |
yes |
| rule = '(c2 is changed or c4 is changed)' |
c1 |
no |
| rule = '(c2 is changed or c4 is changed)' |
c1,c3,c5 |
no |
| rule = '(c2 is changed or c4 is changed)' |
c2 |
yes |
| rule = '(c2 is changed or c4 is changed)' |
c3,c4,c5 |
yes |
| rule = '([c3,c6,c7,c8] matches change_list)' |
c3,c6,c7,c8 |
yes |
| rule = '([c3,c6] matches change_list)' |
c3,c6,c7 |
no |
| rule = '([c3,c6,c7] contains change_list)' |
c3,c6 |
yes |
| rule = '([c3,c6] contains change_list)' |
c3,c8 |
no |
| |
|
|
| Filter |
Updated columns |
Row inserted? |
| filter = '(c2 is not changed)' |
c2 |
yes |
| filter = '(c2 is not changed)' |
c3 |
no |
| filter = '(c3 is changed and c4 is changed)' |
c1 |
yes |
| filter = '(c3 is changed and c4 is changed)' |
c3,c4 |
no |
| filter = '(c3 is changed and c4 is changed)' |
c1,c2,c3 |
yes |
| filter = '([c3,c6,c7] matches change_list)' |
c3,c6 |
yes |
| filter = '([c3,c6,c7] matches change_list)' |
c3,c6,c7 |
no |
| filter = '([c3,c6] contains change_list)' |
c3,c6 |
no |
| filter = '([c3,c6,c7] contains change_list)' |
c3,c8 |
yes |
Source category
Supported targets
Oracle change-history target
Command options
target c.SID [queue queuename]
{
set source sourceID |
reset source |
show source
}
Usage
Sets the user-defined source identifier for the SHAREPLEX_SOURCE_ID metadata column. Use target with the set metadata source option to configure Post to populate this column. Specify any single alphanumeric string.
Example
The following shows the use of set source to support a configuration where the data from multiple source databases is being replicated to the same target database. This target configuration causes each Post process to update the SHAREPLEX_SOURCE_ID column with a different source ID, depending on the source database, to differentiate the data in the target database. This example uses four Post processes.
target c.target1 queue source718 set source east
target c.target1 queue source92 set source south
target c.target1 queue source101 set source west
target c.target1 queue source75 set source north
The Post process for queue “source718” will update column SHAREPLEX_SOURCE_ID with the value "east" for every insert or update to a table in its stream. The Post process for queue "source92" will update column SHAREPLEX_SOURCE_ID with the value "south", and so on for the other two Post processes.
SQL category
Supported targets
File (format record=sql)
Command options
target x.file [queue queuename]
{
set sql property=value |
reset sql [property] |
show sql
}
Usage
Sets the properties of SQL output when formatrecord=sql.
|
add_rownum={yes | no} |
yes to include or no to exclude row numbers |
yes |
|
begin_transaction={yes | no} |
yes to include or no to exclude begin transaction records |
no |
|
comment=character |
Character that marks a comment |
-- |
|
concatenate=character |
Character that concatenates strings |
|| |
|
end_transaction={yes | no} |
yes to include or no to exclude end transaction records |
no |
|
legacy={yes | no} |
Required in SharePlex 8.6. Use legacy SQL date and timestamp format of:
MMDDYYYYHH24MISS and MMDDYYYYHH24MISS.FFFFFF(yes/no) |
no |
|
name_delimiter=character |
Character that delimits SID, table, owner, column names. This value overrides the value that is returned when Post queries the ODBC driver for the setting of SQL_DELIMITER. |
none |
|
record_terminator=character |
Character that terminates the SQL |
; |
Example
target x.file set sql comment=#
