This section describes the replicate database issues and considerations specific to the Oracle data server in a Sybase replication system.
As a replicate data server, Oracle interacts with the DirectConnect for Oracle database gateway. The DirectConnect for Oracle is responsible for accepting commands from the replicate Replication Server, and applying those commands to an Oracle database.
To apply transactions in a replicate database, Replication Server requires a Maintenance User ID that you specify in the Replication Server create connection command. The Maintenance User ID must be defined at the Oracle data server and granted authority to apply transactions in the replicate database. The Maintenance User ID must have the following permissions in the Oracle replicate database:
CREATE TABLE authority to create tables used for Replication Server processing.
UPDATE authority on all replicate tables and EXECUTE authority on all replicate stored procedures.
The only significant intrusions or impacts to the Oracle replicate database are the database objects created by the hds_oracle_setup_for_replicate.sql script, which creates two tables in the replicate database to support Replication Server operations:
RS_INFO, which contains information about the sort order and character set used by the replicate database. When using Replication Server version 12.0 or later, the replicate database sort order and character set must be recorded in the RS_INFO table.
You should confirm that the INSERT statements
for this table (in the hds_oracle_setup_for_replicate.sql script)
specify the proper character set and sort order for your Oracle
data server.
The Replication Server rs_get_charset and rs_get_sortorder functions retrieve the character set and sort order from the RS_INFO table in the replicate database.
RS_LASTCOMMIT, which contains information about replicated transactions applied to the replicate database. Each row in the RS_LASTCOMMIT table identifies the most recent committed transaction that was distributed to the replicate database from a primary database. Replication Server uses this information to ensure that all transactions are distributed.
The Replication Server rs_get_lastcommit function retrieves information about the last transaction committed in the replicate database. For non-Sybase replicate databases, the rs_get_lastcommit function is replaced in the database-specific function-string class by the query required to access the RS_LASTCOMMIT table in the replicate database.
A Replication Server database connection name is made up of two parts: a data server name (server_name) and a database name (db_name). The server_name is the name of the DirectConnect for Oracle database gateway server, and the db_name is the name of the Oracle SID for the replicate database.
The replicate Replication Server looks for an interfaces file entry for the database gateway server_name specified in the Replication Server database connection. The replicate Replication Server logs in to the replicate data server using the user_name and password specified in the database connection.
You must make an entry in the Replication Server interfaces file to identify the host and port where the DirectConnect for Oracle database gateway server is listening. The interfaces file entry name must match the server_name portion of the Replication Server database connection.
The heterogeneous datatype support (HDS) feature of Replication Server provides a number of sample SQL scripts that help you set up the HDS feature in the replicate Replication Server and the Oracle replicate database. These include:
hds_oracle_setup_for_replicate.sql – a script to be applied to the Oracle replicate database.
The hds_oracle_setup_for_replicate.sql script creates the RS_INFO and RS_LASTCOMMIT tables in the replicate database. The script includes GRANT statements that need to be changed before execution to reference the correct user ID name of the Maintenance User defined in the Replication Server database connection for the replicate database. The Maintenance User ID must have UPDATE authority on the tables.
Sybase recommends using the Maintenance User ID to execute
this script.
hds_oracle_funcstrings.sql and hds_oracle_udds.sql – scripts to be applied to Replication Server System Database (RSSD).
The hds_oracle_udds.sql script adds the user-defined datatypes (UDDs) that define the attributes of Oracle native datatypes to the RSSD. The UDDs are required to ensure datatypes received from primary transactions are properly formatted for application to the Oracle replicate database.
You might need to modify the hds_oracle_udds.sql script
to reference the correct RSSD database name.
The hds_oracle_funcstrings.sql script 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 created by the hds_oracle_setup_for_replicate.sql script. These function strings are added to the Replication Server default rs_oracle_function_class.
You might need to modify the script to reference the
correct RSSD database name.
Class-level datatype translation scripts to be applied to RSSD.
Class-level translations identify primary datatypes and the replicate datatypes the data should be translated into (for example, DB2 TIMESTAMP should be translated to Oracle DATE).
These translations can affect Replication Server performance.
Only the translations needed for your specific primary database
and replicate database should be applied to the RSSD.
Class-level translation scripts supplied for the Oracle replicate database are:
hds_clt_ase_to_oracle.sql – translates Adaptive Server datatypes to Oracle datatypes.
hds_clt_db2_to_oracle.sql – translates DB2 for OS/390 datatypes to Oracle datatypes.
hds_clt_udb_to_oracle.sql – translates DB2 Universal Database (for UNIX and Windows) datatypes to Oracle datatypes.
hds_clt_informix_to_oracle.sql – translates Informix datatypes to Oracle datatypes.
hds_clt_msss_to_oracle.sql – translates Microsoft SQL Server datatypes to Oracle datatypes.
hds_oracle_connection_sample.sql – a script to be applied to the replicate Replication Server.
The hds_oracle_connection_sample.sql script provides a template for creating the Replication Server database connection for an Oracle replicate database using the pre-defined Oracle function-string class provided with Replication Server.
You must modify the hds_oracle_connection_sample.sql template to
include your actual server, database, and Maintenance User names before
executing the script.
The following system management issues are specific to an Oracle replicate data server:
The create connection command’s dsi_sql_data_style parameter was used in previous versions of Replication Server to provide some data translations for the replicate database.
With the introduction of the heterogeneous datatype support (HDS) feature in Replication Server version 12.0, the create connection command’s dsi_sql_data_style parameter is no longer valid. Do not use this parameter with Replication Server version 12.0 or later. The default setting should be " " (blank space).
The Component Integration Services (CIS) feature allows users to access both Adaptive Server databases and non-Sybase databases on different servers, through a local Adaptive Server connection.
CIS allows Replication Server to connect to an Adaptive Server database, using default Adaptive Server function strings and connection properties, while CIS handles the data conversion and manipulation, and all communications with the DirectConnect database gateway.
See “Component Integration Services” for a description of the advantages and disadvantages of this approach.
The following issues must be considered when using an Oracle replicate data server:
To capture the century and time components of a temporal value sent to an Oracle replicate database, you must map temporal datatypes to the rs_oracle_datetime datatype instead of the rs_oracle_date datatype. However, you should use the Replication Server datetime datatype to declare replication definition columns with the DATE datatype in an Oracle primary database rather than mapping to the rs_oracle_datetime datatype.
The Oracle default date display format is DD-MON-YY. The Replication Agent presents this format without time values, unless the value of the Replication Agent pdb_convert_datetime configuration parameter is set to true.
Because the Oracle data server does not support multiple databases within the same server instance, the effect of executing the Replication Server rs_usedb function is null. (The function string created by the hds_oracle_funcstrings.sql script performs no operation against the Oracle database for this function call.)
In DirectConnect for Oracle version 12.0 or later, an additional trace flag allows the replicate Replication Server to control transaction commit boundaries when applying transactions to an Oracle replicate database.
Setting the value of the DirectConnect autocommit trace flag to 0 (zero) in the DirectConnect for Oracle configuration file allows Replication Server to control when a COMMIT command should be sent to Oracle. When the value of the autocommit trace flag is not set, DirectConnect for Oracle commits each individual operation (INSERT, UPDATE, and DELETE) sent by the replicate Replication Server.
Having DirectConnect commit each operation can cause a problem at the replicate database if an error occurs in the middle of a multiple operation transaction. In that event, the replicate Replication Server may attempt to re-send the entire transaction, while DirectConnect has already committed each individual operation. To avoid this problem, set the value of the DirectConnect autocommit trace flag to 0 (zero).
