SharePlex 10.0 - Installation and Setup for Oracle Source

target

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

Syntax

Syntax Description

Category descriptions

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.

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.

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.

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.

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 12: Explanation of schema template (* = optional)

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 13: Explanation of operation template (* = optional)

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

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

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.

Example

target x.jms set jms queuename=SharePlexJMS

json category

Supported targets

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.

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.

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

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.

Examples

target r.mydb queue q1 set resources commit_frequency=10000

target r.mydb queue q1 set resources max_active_statements=10

target r.mydb show resources for teradata

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.

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.

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.

Example

target x.file set sql comment=#