The heterogeneous datatype support (HDS) feature of Replication Server provides configuration information that allows you to set up the HDS feature in the replicate Replication Server and the Oracle replicate database.
The configuration information is provided as part of the installation and as part of the connection profile:
• Replication Server installation:
• Create function strings, error classes, and user defined datatypes • Connection profile:
• Apply class-level datatype translations to RSSD • Create objects in the Oracle replicate database • Set connection properties
You can connect using ExpressConnect for Oracle. When using ExpressConnect for Oracle, the version or option name of the connection profile should be “eco”. • Additional settings:
•
• Settings for Command Batching • Settings for Dynamic SQL
See also
• Class-Level Datatype Translations to RSSD on page 107
• Objects in the Oracle Replicate Database and Connection Properties on page 107 • Command Batching Settings on page 108
• Dynamic SQL Settings on page 110
Replication Server Installation
Replication Server installation automatically installs the required function strings and classes to support replication.
Function Strings, Error Classes, and User Defined Datatypes Function strings are added to the Replication Server default
rs_oracle_function_class.
The function string replaces several default Replication Server function strings with custom function strings designed to communicate with an Oracle data server and access the tables and procedures.
Warning! ExpressConnect for Oracle does not support the use of custom function strings for text and image processing.
Connection Profiles
Connection profiles allow you to configure your connection with a predefined set of properties that match your primary and replicate database replication requirements.
Syntax
create connection to data_server.database using profile connection_profile;version set username [to] user
[other_create_connection_options] [display_only]
Parameters
data_server – The data server that holds the database to be added to the replication system. database – The database to be added to the replication system.
connection_profile – Indicates the connection profile that you want to use to configure a connection, modify the RSSD, and build replicate database objects.
version – Specifies the connection profile version to use.
user – The login name of the Replication Server maintenance user for the database. Replication Server uses this login name to maintain replicated data. You must specify a user name if network-based security is not enabled.
other_create_connection_options – Use the other create connection options to set connection options not specified in the profile, such as setting your password, or to override options specified in the profile, such as specifying a custom function string class to override the function string class provided in Replication Server. See the Replication Server Reference Manual > Replication Server Commands > create connection for a complete list of the other options for create connection command.
display_only – Use display_only with the using profile clause to display the commands that will be executed and the names of the servers upon which the commands will be executed. See the client and Replication Server logs for the result of using display_only.
Class-Level Datatype Translations to RSSD
Class-level translations identify primary datatypes and the replicate datatypes the data must be translated into (for example, DB2 UDB TIMESTAMP should be translated to Oracle DATE). Class-level translation is supplied for the Oracle replicate database by the appropriate named connection profile:
• rs_ase_to_oracle – installs Adaptive Server-to-Oracle class-level translations. • rs_db2_to_oracle – translates DB2 UDB for z/OS datatypes to Oracle datatypes. • rs_udb_to_oracle – translates DB2 UDB (for UNIX and Windows) datatypes to
Oracle datatypes.
• rs_msss_to_oracle – translates Microsoft SQL Server datatypes to Oracle datatypes.
To see all the available profiles, use the admin show_connection_profiles command. An example of a script using ExpressConnect for Oracle version profile for an Adaptive Server Enterprise (ASE) to Oracle replication environment:
create connection to oracleSID_name.oracleSID_name using profile rs_ase_to_oracle;eco
set username rs_maint_user set password rs_maint_user_pwd go
Objects in the Oracle Replicate Database and Connection Properties The connection profile creates the RS_INFO, RS_LASTCOMMIT, and
RS_TICKET_HISTORY tables in the replicate database, as well as the RS_TRIGGERS_CONTROL package.
The connection profiles set these connection properties: set error class rs_oracle_error_class
set function string rs_oracle_function_class
Additional Settings
Learn about the additional settings provided to support replication. The settings include:
• ExpressConnect settings • Command Batching settings • Trigger Firing settings • Oracle Flashback settings • Dynamic SQL settings ExpressConnect Settings
Replication Server provides Oracle connection profiles, which instruct the Replication Server connection about the settings and function strings needed for appropriate database-specific
behaviors (such as datatype transformation, commit processing, and rs_ticket support) for an Oracle replication connection.
When creating or altering a Replication Server connection to Oracle, use the appropriate Oracle connection profile (for example, the profile for ASE-to-Oracle replication or the profile for Oracle-to-Oracle replication).
Also, the replication of stored procedures in Oracle may require additional customer-provided function strings. By default, Replication Server generates ASE syntax, which may not be understood by the target database. Function strings can be added to adjust this syntax to be appropriate for the target database. For example, to transform a function call
econn_test_basic_proc with one character type and one money type parameter, you must create a function string as follows:
create function string econn_test_basic_proc.econn_test_basic_proc for
rs_oracle_function_class with overwrite output language
‘call econn_test_basic_proc(?charcolp!param?, ?moneycolp!param?)’ In this example, the function string causes the keyword call to be placed in front of any function replication definition and function named econn_test_basic_proc in the
rs_oracle_function_class. An example of another function string that would generate a syntax acceptable to Oracle is:
create function string econn_test_basic_proc.econn_test_basic_proc for
rs_oracle_function_class with overwrite output language ‘begin econn_test_basic_proc(?charcolp!param?, ?moneycolp!param?);; end;;’ In this example, the function string prepends the same function replication definition and function with the keyword begin and appends the character string “;; end;;”
Warning! ExpressConnect for Oracle does not support the use of custom function strings for text and image processing.
Array Processing in ExpressConnect for Oracle
Ensure that array processing in ExpressConnect for Oracle is applied only to tables that have a table-level replication definition. The performance enhancement provided by array
processing within the ExpressConnect for Oracle connection requires the information included in the table-level replication definition.
Command Batching Settings
Command batching allows Replication Server to send multiple commands to the data server as a single command batch.
You can put multiple commands in a language function-string output template, separating them with semicolons (;). If the database is configured to allow command batches, which is the default, Replication Server replaces the semicolons with that connection’s DSI command separator character before sending the function string in a single batch to the data server.
The separator character is defined in the dsi_cmd_separator option of the alter connection
command. If the connection to the database is not configured to allow batches, Replication Server sends the commands in the function string to the data server one at a time. To enable or disable batching for a database, use the alter connection command.
To use command batching, enter: batch = on
batch_begin = off
When set batch is “on,” you must also specify the following configuration: dsi_cmd_separator set = ;
As a result of a placeholder command that is used in the rs_begin function string, setting
batch_begin to “on” may cause problems with starting DSI. Set batch_begin to “off” to allow the rs_begin and the rs_commit commands to be sent independently of the batches of commands, and ensures correct SQL in all transferred commands:
use_batch_markers = on
Oracle requires BEGIN and END markers for batches of commands. By configuring
use_batch_markers to “on,” the markers are automatically added from the rs_batch_start
and rs_batch_end function strings. See the Replication Server Administration Guide Volume 2 > Command Batching for Non-ASE Servers.
Trigger Firing Settings
Replication Server supports disabling trigger execution for Oracle at the session or connection level.
You can control trigger firing each time Replication Server executes PL/SQL commands against the replicate database. Controlling trigger execution at the replicate database eliminates data duplication and data inaccuracy errors that were caused by the absence of trigger control at the replicate database side.
For every trigger to be controlled at the replicate database, re-create the trigger and add the trigger control statement at the beginning of your trigger action.
Controlling Trigger Firing
Control trigger firing through RS_TRIGGER_CONTROL package, which is automatically installed when a connection to the replicate Oracle database is created through connection profiles.
1. Set the connection parameter dsi_keep_triggers to off so that Replication Server sets the RS_TRIGGERS_CONTROL enable flag when connecting to the replicate database. 2. Add the trigger control PL/SQL code to the first line of your trigger action:
if RS_TRIGGER_CONTROL.IS_ENABLED then return;end if; This indicates that a trigger is fired by Replication Server and prevents the trigger from executing the actual application logic.
See the Replication Server Reference Manual. Oracle Flashback Settings
Replication Agent supports Oracle Flashback at the table and transaction levels.
Use Oracle Flashback to query historical data, perform change analysis, and perform self- service repair to recover from logical corruptions while the database is online. Oracle customers can use flashback to undo the previous data change thereby minimizing application outages caused by operator or user errors, such as accidental deletion of valuable data, deletion of the wrong data, and dropping the wrong table.
Replication Agent supports two kinds of flashback:
• Flashback a dropped table. This replicates the flashback DDL commands like drop table,
flashback table to before drop, and purge recyclebin to target Oracle. To replicate purge dba_recyclebin, use DCO 15.0 ESD#3 or later, and assign the sysdba privilege to the DDL user.
• Flashback a table to a specific timestamp or SCN. This replicates the DML changes to the target Oracle database.
To flashback a table to a specific timestamp or SCN:
• Use the pdb_setreptable command to mark the table which needs to be flashbacked to a specific state.
To replicate flashback DDL statements:
• Enable recycle bin at both primary and replicate database: alter system set recyclebin=on
• When using ECDA, set the rep_sparse_parse parameter of the ECDA Option for Oracle to 1. The default value of this parameter is 0 when ECDA Option for Oracle 15.0 ESD #3 is used.
• Enable DDL replication by using the pdb_setrepddl enable command. Dynamic SQL Settings
The use of Dynamic SQL for Oracle is supported with Replication Server using ExpressConnect.