SharePlex can post replicated Oracle data to a file formatted as SQL or XML. This data is written as a sequential series of operations as they occurred on the source, which can then be posted in sequential order to a target database or consumed by an external process or program.
This chapter guides you through the configuration process to support replication to this target.
Note: For the platforms, datatypes and operations that are supported when using SharePlex to replicate to a SQL or XML file, see the SharePlex Release Notes.
On the source, create a SharePlex configuration file that specifies capture and routing information. The structure that is required in a configuration file varies, depending on your replication strategy, but this shows you the required syntax for routing data to a SQL or XML file.
Datasource:o.SID | ||
src_owner.table | !file[:tgt_owner.table] | host |
where:
src_owner.table is the owner and name of the source table.
Note: For more information, see Create configuration files.
The following example replicates the parts table in schema PROD from Oracle instance ora112 to a file on target system sysprod.
Datasource:o.ora112
PROD.parts !file sysprod
To output data in XML format
By default, SharePlex formats data that it writes to a file in XML format, and you do not need to run target setup unless you want to change properties of the output file (see View and change target settings.)
To output data in SQL format
Issue the following required target commands to output the records in SQL. Note: Use all lower-case characters.
target x.file [queue queuename] set format record=sql
target x.file [queuequeuename] set sql legacy=yes
where: queue queuename constrains the action of the command to the SharePlex Post process that is associated with the specified queue.
See View and change target settings for descriptions of these settings and other optional properties that you can set.
To view current property settings for output to a file, use the following command:
target x.file show
To change a setting, use the following target command.
target x.file [queue queuename] set [category] property=value
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
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.
<?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 6: Explanation of schema template (* = optional)
Element | Attribute | Description |
---|---|---|
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 |
<?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 7: Explanation of operation template (* = optional)
Element | Attribute | Description |
---|---|---|
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.
See the SharePlex Release Notes for a chart that shows how Oracle datatypes are converted to XML.
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 |
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;
<?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>
<?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>
<?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>
<?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>
<?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>
Post writes to a series of files. The active working file is prepended with the label of current_ and is stored in the opx/current subdirectory of the variable-data directory.
Output Format | Name of Current File |
---|---|
SQL |
current_legacy.sql |
XML |
current_prodsys.XML |
Important: Do not open or edit the current_ file.
Post uses the max_records, max_size and max_time parameters to determine the point at which to start a new active file. When this switch occurs, Post moves the processed data to a sequenced file in the opx subdirectory of the variable-data directory. The file names include the name of the post queue, the time and date, and an incrementing ID.
SQL files:
/installed/vardir> ls -1 opx
0000000000_20140305130858_legacy.sql
0000000001_20140305131130_legacy.sql
0000000002_20140305131212_legacy.sql
0000000003_20140305133835_legacy.sql
0000000004_20140305134028_legacy.sql
XML files:
/installed/vardir> ls -1 opx
0000000000_20140305130858_prodsys.XML
0000000001_20140305131130_prodsys.XML
0000000002_20140305131212_prodsys.XML
0000000003_20140305133835_prodsys.XML
0000000004_20140305134028_prodsys.XML
To force a file switch
The current file cannot be viewed or consumed without stopping Post. To access the data in the current file, you can use the target command with the switch option to move the data to a sequenced file, from which it can then be consumed or viewed. After issuing this command, the switch occurs after Post processes a new record.
target x.file [queue queuename] switch
© 2022 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy