This section describes the replicate database issues and considerations specific to the IBM DB2 Universal Database server on a UNIX or Microsoft Windows platform.
As a replicate data server in a replication system, DB2 Universal Database interacts with the DirectConnect for DRDA database gateway. DirectConnect for DRDA is responsible for accepting commands from the replicate Replication Server, and applying those commands to a database residing in a DB2 Universal Database server.
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 DB2 Universal Database server and granted authority to apply transactions in the replicate database. The Maintenance User ID must have the following permissions in the DB2 Universal Database replicate database:
CREATE TABLE authority to create tables used for Replication Server processing.
UPDATE authority on all replicate tables.
The only significant intrusions or impacts to the DB2 Universal Database replicate database are the database objects created by the hds_udb_setup_for_replicate.sql script to support Replication Server replicate database operations.This script 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.
Be sure to confirm that the INSERT statements
for this table (in the hds_udb_setup_for_replicate.sql script)
specify the proper character set and sort order for your DB2 Universal
Database server.
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.
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 DRDA database gateway server, and the db_name is the name of the replicate database residing in DB2 Universal 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 DRDA database gateway server is listening. The interfaces file entry name must match the server_name portion of the Replication Server database connection.
Replication of large object (LOB) datatypes (BLOB, CLOB, and LVARCHAR) is not supported directly from Replication Server to DirectConnect for DRDA.
For information about a possible workaround for this
LOB data replication limitation, see “Large object replication”.
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 DB2 Universal Database replicate database. These include:
hds_udb_setup_for_replicate.sql – a script to be applied to the DB2 Universal Database replicate database. This 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_udb_funcstrings.sql and hds_udb_udds.sql – scripts to be applied to Replication Server System Database (RSSD).
The hds_udb_udds.sql script adds the user-defined datatypes (UDDs) that define the attributes of DB2 Universal Database native datatypes to the RSSD. The UDDs are required to ensure datatypes received from primary transactions are properly formatted for application to the DB2 Universal Database replicate database.
You might need to modify the hds_udb_udds.sql script
to reference the correct RSSD database name.
The hds_udb_funcstrings.sql script replaces several default Replication Server function strings with custom function strings designed to communicate with DB2 Universal Database and access the tables and procedures created by the hds_udb_setup_for_replicate.sql script. These function strings are added to the Replication Server default rs_udb_function_class.
You may 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, Informix BYTE should be translated to DB2 Universal Database CHAR FOR BIT DATA).
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 DB2 Universal Database replicate database are:
hds_clt_ase_to_udb.sql – translates Adaptive Server datatypes to DB2 Universal Database datatypes.
hds_clt_db2_to_udb.sql – translates DB2 for OS/390 datatypes to DB2 Universal Database (for UNIX and Windows) datatypes.
hds_clt_informix_to_udb.sql – translates Informix datatypes to DB2 Universal Database datatypes.
hds_clt_msss_to_udb.sql – translates Microsoft SQL Server datatypes to DB2 Universal Database datatypes.
hds_clt_oracle_to_udb.sql – translates Oracle datatypes to DB2 Universal Database datatypes.
hds_udb_connection_sample.sql – a script to be applied to the replicate Replication Server.
The hds_udb_connection_sample.sql script provides a template for creating the Replication Server database connection for a DB2 Universal Database replicate database using the pre-defined DB2 Universal Database function-string class provided with Replication Server.
You must modify the hds_udb_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 a DB2 Universal Database 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.
