Important things to consider when preparing replication for Open Target (4274042)

1. SharePlex Installation Guide checklist on Open Target (SharePlex 8.6.5 or 8.6.6, page 27-30):

Character set conversion -           

SharePlex posts data to open target in UTF8 format.  If source is US7ASCII or Unicode and you are not replicating LOB, then no character set conversion needed and you don’t need to install Oracle client on target server and SP_OPX_NLS_CONVERSION can be set to 0 to disable conversion on target.

You can use SPX-release#-build#-rh-40-amd64-m64.tpm

Otherwise for other character sets or if you are replicating LOB, install Oracle client on target and SP_OPX_NLS_CONVERSION must be set to 1 on target to enable conversion.

You must use SharePlex-release#-build#-oracle#-rh-40-amd64-m64.tpm

Choose the right ODBC driver -              

For SQL Server, do not choose the SQL server Native Client odbc driver to avoid SQL server Datetime field overflow issue.

“error_code=0 state=22008 msg=[Microsoft][SQL Server Native Client 10.0]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.”

For replication to PostgreSQL, prior to SharePlex 8.6.6 only EnterpriseDB PPAS 9.4 and 9.5 are supported and SharePlex can only use the EDB odbc driver for connection to target DB.

When setting up DSN, make sure you refer to EDB odbc driver /xx/PostgresPlus/connectors/odbc/lib/edb-odbc.so

Install EDB odbc driver version 9.3.400.1 from EDB 9.5 PPAS Stack Builder Plus, earlier version EDB odbc driver 9.3.400 from Stack Builder 9.4 is not supported.

In upcoming release of 8.6.6 (page 33 release notes), SharePlex supports PostgreSQL (Community Edition), Fujitsu Symfoware Server PostgreSQL in addition to EDB PPAS.

Case Sensitivity in table name  –

If target table name is lower case, you need to use double quote in config file as source Oracle table is usually upper case.

For example:

datasource:o.SID

test.mytable    "test"."mytable"            targethost@r.targetDB

if you set up like below without double quote, then SharePlex assumes target owner and table name are both UPPER CASE. Always use double quote if you are not sure.

test.mytable      test.mytable              targethost@r.targetDB

Disable triggers on target DB – Triggers should be disabled on target

Review Open Target Setup requirements in SharePlex Installation Guide (SharePlex 8.6.6, page 104 to 113)

Additional Requirements for Hana - Grant necessary privileges to SharePlex user on target DB

GRANT USER ADMIN TO sp_user;

GRANT TABLE ADMIN TO sp_user;

GRANT CATALOG READ TO sp_user;

GRANT DATA ADMIN TO sp_user WITH ADMIN OPTION;

GRANT ROLE ADMIN TO sp_user WITH ADMIN OPTION;

Additionally, log in as the owner of each schema that contains objects that you want to replicate, then make the following grants on the schema:

GRANT CREATE ANY ON SCHEMA schema_name TO sp_user;

GRANT DEBUG ON SCHEMA schema_name TO sp_user;

GRANT DELETE, DROP, EXECUTE, INDEX, INSERT, SELECT, UPDATE ON SCHEMA schema_name TO sp_user;

Additional Requirements for SQL Server - If set up DSN to use NT authentication for SQL Server 2012 or later, grant the NTAuthority\SYSTEM user the sysadmin fixed server role.

Additional Requirement for Fujitsu Symfoware PostgreSQL - If the person who is running the setup is not a Fujitsu Enterprise Postgres owner, set the environment variable LD_LIBRARY_PATH to include the path to the lib subdirectory in the Fujitsu

For example:

export LD_LIBRARY_PATH= /opt/symfoserver64/lib:$LD_LIBRARY_PATH

Additional Recommendations for Kafka – Install upcoming 8.6.6 if you need support for clustered Kafka brokers, posting to single or multiple partitions, control over partition assignment, multiple topics, and controls over topic naming.

2. DDL Support

SharePlex currently has limited ddl support which includes alter table add column, drop column and truncate for Open Target. 

When setting up replication to both oracle and non-oracle, consider creating separate SharePlex port in case you have different requirements for ddl replication.

For open target, make sure these ddl related parameters are turned off on source DB

                                sp_ctrl> stop capture

                                sp_ctrl> set param SP_OCT_AUTOADD_ENABLE 0

                                sp_ctrl> set param SP_OCT_REPLICATE_ALL_DDL 0

                                sp_ctrl> start capture                             

Datatype mappings are automatically done by SharePlex from Oracle to Open Target DB, check SharePlex release notes for list of default open target datatype mappings for different target DB.

You can set up custom datatype mapping for alter table add column with ‘target set datatype’ command, otherwise it will use default mapping.

                                sp_ctrl> help target set datatype

                                sp_ctrl> target r.xxx queue p1 set datatype number=float

                                sp_ctrl> stop post

                                sp_ctrl> start post                  

3. Supplemental Logging

Source:

Enable supplemental logging for Primary Key/Unique Key, and enable below parameter to use Oracle Supplemental logged keys

                                sp_ctrl> set param SP_OCT_USE_SUPP_KEYS 1

4. Performance Tuning Tips

• Create separate post queues to improve post speed with more parallel processing. Balance out the work load evenly among the post queues.

          Example:

          datasource:o.SID

          owner.table_name1 owner.table_name1 sourcehost:exp1*targethost:q1@r.targetDB

          owner.table_name2 owner.table_name2 sourcehost:exp2*targethost:q2@r.targetDB

• If heavy transactions are concentrated on one table, consider setting up Horizontal Partitioning with column condition or with Hash Partitioning to improve post speed.
• SharePlex open target filters out rollbacks and replicates only committed transaction. When the transaction is huge, it needs to wait for the commit to filter out rollbacks before processing.  If possible, reduce the size of large transaction that’s over one million to smaller transactions with more frequent commit on source side.
• Add Primary Key or appropriate index, and collect stats on target DB for faster sql execution on target DB.
• For SQL Server, drop identity column on key column on target table as turning on IDENTITY_INSERT will reduce post performance drastically.
• Turn on open post utilization timers when post is slow and check the csv file generated under var dir/log on target to see where post is spending time on.     

 Sp_ctrl> set param SP_OPX_UTILIZATION_TIMERS queue <queuename> 1

Parameters:

SP_QUE_POST_SHMSIZE – increase this memory setting for each post queue if utilization timers show post spending a lot of time on queue read (default is 32mb).

SP_OPX_MSGS_IN_MEMORY – This parameter controls the max number of messages allowed in the sql internal queue area for open post.  If the internal queue is full, the post main thread cannot write more messages to it until the messages are lower than the value set (default is 5000 messages).  Increase it if you have a lot of backlog messages in post queue and you have done tuning on target DB already.  This will increase the memory usage as well.

SP_OPX_COMMIT_REDUCE_MSGS - set this value to a higher number so that SharePlex can take advantage of commit reduction feature for insert operations.

5. Monitoring

SharePlex Manager can be installed to monitor replication to Open Target.

In addition, sp_nt_mon can be installed to monitor replication to sql server on windows.

Additional Knowledge Base Articles on Open Target

Video KB219912 – How to configure replication to Hana target

Video KB224909 - How to configure replication target to Kafka

KB212609 - How to set up SharePlex replication to Kafka

Video KB211594 – How to configure replication to Postgres (PPAS) target

Video KB219939 – How to configure replication to Teradata target

Video KB145678 – SharePlex for SQL Server setup