The SharePlex Post process can connect and write to a JMS (Java Messaging Service) queue or topic. The data is written as XML records that include the data definitions, the operation type, and the changed column values. 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.
Note: For the platforms, datatypes and operations that are supported when using SharePlex to replicate to JMS, see the SharePlex Release Notes.
When replicating data to JMS, configure the source database and SharePlex on the source system as follows.
On the source system, enable PK/UK supplemental logging in the Oracle source database. SharePlex must have the Oracle key information to build an appropriate key on the target.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;
On the source system, set the SP_OCT_USE_SUPP_KEYS parameter to a value of 1. This parameter directs SharePlex to use the columns set by Oracle's supplemental logging as the key columns when a row is updated or deleted. When both supplemental logging and this parameter are set, it ensures that SharePlex can always build a key and that the SharePlex key will match the Oracle key.
See the SharePlex Reference Guide for more information about this parameter.
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 through a JMS queue or topic.
Datasource:o.SID | ||
src_owner.table | !jms[:tgt_owner.table] | host |
where:
host is the name of the target system.
Note: For more information, see Create configuration files.
Datasource:o.ora112
MY_SCHEMA.MY_TABLE !jms:"MySchema2"."MyTable2" sysprod
These instructions establish the Java provider on the target and configure the SharePlex Post process to connect to a JMS structure. SharePlex supports Active MQ and IBM MQ targets.
These instructions configure the SharePlex Post process to post to an Active MQ queue or topic.
NoteS:
The following directory naming conventions are used in the examples in this procedure:
Name | Directory |
---|---|
SharePlex | The SharePlex installation directory |
amq | The working directory for Active MQ within the SharePlex directory. |
To configure post to the amq target
Create an amq directory under SharePlex/lib/providers.
shareplex/lib/providers/amq
Copy the following provider JAR files and HS dependencies to the amq directory.
shareplex/lib/providers/amq/activemq-all.jar
shareplex/lib/providers/amq/slf4j.jar
Create a soft link under the SharePlex/.app-modules/openbridge directory to point to the JRE 1.7 binary:
Issue the following command:
MV jre/ jre1.6/
Issue the following command:
ln –s /home/xxx/java7/jre1.7.0_09 jre
The following is an example:
hostname/home/xxx/prod80/.app-modules/openbridge> ls -ltr
total 24
drwxr-xr-x 2 xxx dba 4096 Jan 11 14:04 temp/
drwxr-xr-x 8 xxx dba 4096 Jan 11 14:04 jre1.6/
drwxr-xr-x 2 xxx dba 4096 Jan 11 14:05 plugins/
drwxr-xr-x 2 xxx dba 4096 Jan 11 14:05 configuration/
-r—1 xxx dba 160 Jan 11 14:05 bridge.ini
-rwxr-xr-x 1 xxx dba 711 Jan 11 14:05 bridge*
lrwxrwxrwx 1 xxx dba 31 May 10 15:08 jre -> /home/xxx/java7/jre1.7.0_09/
Issue the target command to configure Post to post to the JMS queue or topic. The following are example commands.
See View and change JMS settings for command explanations and options.
If posting to a JMS queue:
sp_ctrl> target x.jmsset jms factory_class=org.apache.activemq.jndi.ActiveMQInitialContextFactory
sp_ctrl> target x.jms set jms provider_url=tcp://w2k3-64bit:61616
sp_ctrl> target x.jms set jms lib_location=shareplex/lib/providers/amq
sp_ctrl> target x.jms set jms queuename=SHAREPLEX.Q1
If posting to a JMS topic:
sp_ctrl> target x.jmsset jms factory_class=org.apache.activemq.jndi.ActiveMQInitialContextFactory
sp_ctrl> target x.jms set jms provider_url=tcp://w2k3-64bit:61616
sp_ctrl> target x.jms set jms lib_location=shareplex/lib/providers/amq
sp_ctrl> target x.jmsset jms destination=topic
sp_ctrl> target x.jms set jms queuename=SHAREPLEX.Q1
These instructions configure the SharePlex Post process to post to an IBM MQ queue or topic.
The following directory naming conventions are used in the examples in this procedure:
Name | Directory |
---|---|
MQClient | The IBM MQ client installation directory |
SharePlex | The SharePlex installation directory |
ibmmq | The working directory for IBM MQ within the SharePlex directory. |
To configure post to the IBM MQ target
Copy the .bindings file that is generated by the IBM MQ Explorer utility from the middle-tier server to the MQClient/lib directory on the SharePlex target server.
Note: The Post process uses this file to obtain information about the IBM MQ middle-tier server.
If using IBM MQ 8.x, create a soft link to the JRE that ships with the MQ 8.x distribution. (The JRE 1.6. that ships with SharePlex is compatible with MQ 7.x but not 8.x.)
$ mv jre jre.old
$ ln –s /MQClient/java/jre64/jre jre
Create an ibmmq directory within the SharePlex/lib/providers directory.
Copy the .bindings file and all of the .jar files from the MQClient/java/lib directory to the SharePlex/lib/providers/ibmmq directory.
$ cp MQClient/java/lib/.bindings SharePlex/lib/providers/ibmmq
$ cp MQClient/java/lib/*.jar SharePlex/lib/providers/ibmmq
Create a soft link to the MQClient/lib directory from the ibmmq directory that you created.
$ cd SharePlex/lib/providers/ibmmq
$ ln –sMQClient/libibmmq
Issue the target command to configure Post to post to the JMS queue or topic. The following are example commands.
See View and change JMS settings for command explanations and additional options.
If posting to a JMS queue:
sp_ctrl> target x.jmsset jms factory_name=SharePlexQcf
sp_ctrl> target x.jmsset jms lib_location=ibmmq
sp_ctrl> target x.jms set jms provider_url=file:/MQClient/app/mqm83client/lib
sp_ctrl> target x.jms set jms queuename=SHAREPLEX.Q1
sp_ctrl> target x.jms set jms user=myusername
sp_ctrl> target x.jms set jms password=mypassword
If posting to a JMS topic:
sp_ctrl> target x.jmsset jms factory_name=SharePlexQcf
sp_ctrl> target x.jmsset jms lib_location=ibmmq
sp_ctrl> target x.jms set jms provider_url=file:/MQClient/app/mqm83client/lib
sp_ctrl> target x.jms set jms queuename=SHAREPLEX.T1
sp_ctrl> target x.jms set jms destination=topic
sp_ctrl> target x.jms set jms user=myusername
sp_ctrl> target x.jms set jms password=mypassword
To view current property settings for output to JMS, use the following command:
target x.jms show
To change a property setting, use the following command.
target x.jms [queue queuename] set jms property=value
where:
Table 3: JMS target properties
Property | Input Value | Default |
---|---|---|
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. |
None |
lib_location=path |
Required Path to the directory where you installed the JAR 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_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.
|
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 of the target command). 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 |
If the JMS process aborts suddenly, or if the machine that it is running on aborts, row changes may be written twice to the JMS target. The consumer must manage this by detecting and discarding duplicates.
Every record of every row-change operation in a transaction has the same transaction ID and is also marked with a sequence ID. These attributes are id and msgIdx, respectively, under the txn element in the XML output (see About the XML).
The transaction ID is the SCN at the time the transaction was committed and the sequence ID is the index of the row change in the transaction. These two values are guaranteed to be the same if they are re-written to the JMS queue in a recovery situation.
If desired, you can configure the target to include additional metadata with every row-change record by using the following command:
target target [queue queuename] set metadata property[, property]
Table 4: Optional JMS metadata properties
Property | Description |
---|---|
time | The time the operation was applied on the source. |
userid | The ID of the database user that performed the operation. |
trans | The ID of the transaction that included the operation. |
size | The number of operations in the transaction. |
target x.jms set metadata time, userid, trans, size
To reset the metadata
target x.jms [queue queuename] reset metadata
To view the metadata
target x.jms [queue queuename] show metadata
If you configured Post to process large amounts of transactions to JMS through multiple named Post queues, you may need to allocate more memory to the Post JMS bridge. The default settings are 256MB and 512MB.
To increase the JMS bridge memory
Open the openbridge.ini file and set the following parameters in the format shown:
-Xms=512m
-Xmx=1024m
-XX:PermSize=512m
-XX:MaxPermSize=1024m
You can configure Post to log whether each write to a queue or topic succeeded or failed. This is controlled with the debugging setting.
To enable debugging
Open the openbridge.ini file and set the -OB_debugToLog parameter to true.
-OB_debugToLog=true
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 5: 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 6: 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>
© 2021 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy