This section describes the primary database issues and considerations specific to the DB2 Universal Database server on a UNIX or Microsoft Windows platform in a Sybase replication system.
As a primary data server, DB2 Universal Database interacts with Sybase Replication Agent. An instance of the Sybase Replication Agent configured for DB2 Universal Database is referred to as a Replication Agent for UDB.
The Replication Agent for UDB identifies and transfers information about data-changing operations or transactions from a DB2 Universal Database primary data server to a primary Replication Server.
The Replication Agent interacts with the primary Replication Server and with the RSSD of the primary Replication Server, if so configured.
The Replication Agent for UDB requires a DB2 Universal Database login that has permission to access data and create new objects in the primary database.
The DB2 login must have the following permissions in the primary database:
SELECT and ALTER TABLE authority on any table that is to be replicated
CREATE TABLE, ALTER TABLE, and DROP TABLE for Replication Agent system tables (or transaction log objects)
The DB2 login must also have READ authority on the DB2 log data sets.
The performance and operation of DB2 Universal Database primary data servers in a Sybase replication system might be affected by the following:
The DB2 transaction log is affected in the following ways:
You must set the RECOVERY MODE parameter of the DB2 primary database to LOGRETAIN before you create any Replication Agent for UDB transaction log objects (by using the pdb_xlog command).
Replication requires a before and after image of each row that is changed. When you mark a primary table for replication, the Replication Agent for UDB sets the table’s DATA CAPTURE option to DATA CAPTURE CHANGES. As the number of tables marked for replication increases, so does the space requirement for the DB2 transaction log.
When you create Replication Agent for UDB transaction log objects using the pdb_xlog command, the following tables are created in the primary database:
Transaction log (xlog) system table
Marked objects table
BLOB columns table
Proc-Active table
RS_MARKER shadow table
RS_DUMP shadow table
Replication Agent for UDB requires the following to connect to a DB2 Universal Database primary data server:
If the Replication Agent for UDB is installed on a different host machine from the DB2 Universal Database server, you must install the DB2 Universal Database Administration Client on the Replication Agent host machine.
If the Replication Agent for UDB software is installed on the same host machine as the DB2 Universal Database server, a separate client is not required.
In either case, you must configure an ODBC data source in the DB2 Administration Client, then use the database name and database alias specified for that ODBC data source when you configure connectivity for the Replication Agent.
If the Replication Agent for UDB is installed on a UNIX machine, you must source the db2cshrc file before attempting to start the Replication Agent instance. The db2cshrc file is in the DB2DIR/sqllib directory, where DB2DIR is the path where you installed the DB2 software.
To configure the DB2 Universal Database Administration Client for the JDBC version required by Sybase Replication Agent 12.6, you must run the usejdbc2 script in the DB2DIR/sqllib/java12 directory, where DB2DIR is the path where you installed the DB2 software.
Sybase Replication Agent is a Java program. Some operating systems
may require patches to support Java. Refer to the Sybase Replication
Agent Administration Guide and the Sybase Replication Agent
release bulletin for more information.
The values of the following Replication Agent for UDB configuration parameters must be set as described:
pds_username – the DB2 user ID that the Replication Agent uses to log in to the primary database. This user ID must be granted permissions as described in “DB2 Universal Database primary database permissions”.
pds_password – the password for the user ID that the Replication Agent uses to log in to the primary database.
pds_connection_type – must be set to UDBODBC for connectivity to a DB2 primary database.
The value of the pds_connection_type parameter
is set automatically when the Replication Agent for UDB instance
is created. Refer to the Sybase Replication Agent Administration
Guide for more information about creating Replication
Agent instances.
pds_host_name – the name of the host machine on which the DB2 data server resides.
pds_port_number – the client socket port number where the DB2 data server listens for connections.
pds_database_name – the name of the primary database on the DB2 data server from which transactions will be replicated.
pds_datasource_name – the ODBC database alias for the primary database in the DB2 data server.
A separate Replication Agent for UDB instance is required
for each database from which transactions are replicated.
A valid user ID must exist on the data server platform, and it must be granted permissions to the primary database. The Replication Agent for UDB uses this user ID to log in to DB2 Universal Database. This user ID must have the following permissions:
SELECT and ALTER TABLE authority on all tables marked for replication
CREATE TABLE, ALTER TABLE, and DROP TABLE authority for Replication Agent system tables
READ authority on the DB2 log data sets
The values of the following Replication Agent configuration parameters must be set as described so that the Replication Agent for UDB can connect to the primary Replication Server:
rs_host_name – the name of the host machine on which the primary Replication Server resides.
rs_charset – character set used to communicate with Replication Agent. It must match the character set used by Replication Server. If rs_charset does not match, Replication Agent will not replicate anything.
rs_port_number – the client socket port number where the primary Replication Server listens for connections.
rs_username – the user ID that the Replication Agent uses to log in to the primary Replication Server. This user ID must be defined and granted connect source permission in the Replication Server.
rs_password – the password for the user ID that the Replication Agent uses to log in to the primary Replication Server.
rs_source_ds – the server name of the primary data server specified in the Replication Server database connection.
rs_source_db – the database name of the primary database specified in the Replication Server database connection.
Sybase Replication Agent uses TCP/IP and the
Sybase JDBC driver (jConnect for JDBC, which is included in Replication
Agent installation) to communicate with other Sybase servers. The
Replication Agent does not rely on the Sybase interfaces file for
connectivity information.
The values of the following Replication Agent configuration parameters must be set as described so that the Replication Agent for UDB can connect to the RSSD of the primary Replication Server:
rssd_host_name – the name of the host machine for the data server that contains the RSSD.
rssd_port_number – the client socket port number where the RSSD data server listens for connections.
rssd_username – the user ID that the Replication Agent uses to log in to the RSSD. This user ID must be defined and granted select permission in the RSSD.
rssd_password – the password for the user ID that the Replication Agent uses to log in to the RSSD.
rssd_database_name – the database name of the RSSD. When logging in to the data server specified in the rssd_host_name parameter, the Replication Agent invokes a use command for the database name specified in the rssd_database_name parameter.
Sybase Replication Agent does not support stored procedure replication for DB2 Universal Database.
Replication Server Manager has the following limitations with a DB2 Universal Database primary data server:
Replication Server Manager cannot start or stop a Sybase Replication Agent instance.
See the Sybase Replication Agent Administration Guide for more information on starting and stopping a Replication Agent instance.
Replication Server Manager does not provide a method to invoke the Sybase Replication Agent pdb_xlog command. You must log in to the Replication Agent for UDB instance to execute this command.
See the Sybase Replication Agent Administration Guide for more information on logging in to a Replication Agent instance and using the pdb_xlog command.
All the installation issues and configuration parameter details for a DB2 Universal Database primary data server are provided in the Sybase Replication Agent Installation Guide. The following are a few items that may need additional attention:
When you install Sybase Replication Agent, a Java Runtime Environment (JRE) that is compatible with the Replication Agent for UDB may be installed for you. For each operating system, you should download and install the most recent recommended patches specified by your operating system vendor for Java compatibility.
Sybase Replication Agent requires a JDBC driver for connectivity to the DB2 Universal Database server. The JDBC driver for DB2 Universal Database is provided with the DB2 Universal Database Administration Client. See the Sybase Replication Agent Administration Guide for more information about the JDBC driver for DB2 Universal Database.
All configuration parameter values in the Replication Agent configuration file are case sensitive. Be careful when specifying the values of the rs_source_ds and rs_source_db parameters, as Replication Server is also case sensitive. If the same case is not used in both Replication Agent and Replication Server parameters, no connection occurs.
The Replication Agent filter_maint_userid configuration parameter controls whether the Replication Agent forwards transactions performed by the Maintenance User to the primary Replication Server. The Maintenance User name is defined in the Replication Server create connection command for the primary database.
In a bidirectional replication environment (replicating both into and out of the same database), the value of the filter_maint_userid parameter should be set to true. If it is not, transactions replicated to another site could return to be applied at the originating site, creating an endless loop.
The Replication Agent ltl_character_case configuration parameter controls the character case in which the Replication Agent sends database object names to the Replication Server.
For example, if a replication definition is created for all tables named testtab, the table name sent by the Replication Agent must be testtab, or no match occurs. Because Replication Server is case sensitive, a value of TESTTAB does not match the value of testtab.
When you create replication definitions choose a default case (for example, create all replication definitions in either all uppercase or all lowercase), and change the value of the Replication Agent ltl_character_case parameter to match.
See the Sybase Replication Agent Administration Guide for more information on the ltl_character_case parameter.
Be sure that your Sybase environment variable is correctly set:
On a UNIX command line:
java -cp "$SYBASE/jConnect-6_0/classes:$SYBASE /jConnect-6_0/classes /jconn3.jar" IsqlApp -U uid -P pwd -S jdbc:sybase:Tds:host:port [ -C charset ] [ -L language ]
On a Windows command line:
:java -cp "%SYBASE%\jConnect-6_0\classes;%SYBASE%\jConnect-6_0 \classes\jconn3.jar" IsqlApp -U uid -P pwd -S jdbc:sybase:Tds:host :port [ -C charset ] [ -L language ]
where:
uid is the login user ID.
pwd is the password for the login user ID.
host is the name of the host on which the server is running.
If the server is on the same machine, use localhost instead of the actual host name.
port is the server's port number.
charset is the name of a Sybase character set.
language is the name of a language.
Following is the full syntax for IsqlApp help:
c:\>java -cp "%SYBASE%\jConnect-6_0\classes;%SYBASE%\jConnect-6_0\classes \jconn3.jar" IsqlApp -help
The following is displayed:
IsqlApp [-U <username>] [-P <password>] [-S <servername>] [-G <gateway>] [-p <http|https>] [-D <debug-class-list>] [-C <charset>] [-L <language>] [-T <sessionID>] [-v] [-I <input command file>] [-c <command terminator>] [-s <starting tag/section marker>] [-e <ending tag/section marker>]
[-t <tag name> <new value>] [-n <section name to execute>] [-N] [-K <service principal name for kerberos login>] [-F <path to JAAS login config file>] [-V <version {2,3,4,5,6}] -d
To connect to an ASE on the local machine:
java -cp "$SYBASE/jConnect-6_0/classes:$SYBASE/jConnect-6_0/classes /jconn3.jar" IsqlApp -U sa -P sapwd -S jdbc:sybase:Tds:localhost:4100
To connect to a Replication Server on a remote machine:
java -cp "$SYBASE/jConnect-6_0/classes:$SYBASE/jConnect-6_0/classes /jconn3.jar" IsqlApp -U sa -P sapwd -S jdbc:sybase:Tds:dock:4200 -C iso_1
The Replication Agent use_rssd configuration parameter controls whether the Replication Agent sends Log Transfer Language (LTL) that contains only the columns specified in a replication definition, or all of the columns in the primary table.
When the value of the use_rssd parameter is set to false, the Replication Agent sends LTL with data for all of the columns in the primary table. When the value of the use_rssd parameter is set to true, the Replication Agent sends LTL with data for only the columns specified in the replication definition for each primary table.
By sending data for only the columns specified in the replication definition, network traffic is reduced, which can improve performance.
In addition, column names and parameter names are removed from the LTL because the Replication Agent can send information in the order identified by the replication definition. The LTL minimal columns and structured tokens options are also available when the value of the use_rssd parameter is set to true. For more information, see the Sybase Replication Agent Administration Guide.
The Sybase Replication Agent allows you to control how it sends DB2 DATE, TIME, and TIMESTAMP column values to the Replication Server. There are two options:
Send the value as a character string (the default)
Send the value as a Sybase datetime value
The value of the Replication Agent pdb_convert_datetime configuration parameter determines how the Replication Agent handles temporal datatypes.
If you set the pdb_convert_datetime parameter to true, all corresponding datatypes in a replication definition for DATE, TIME, and TIMESTAMP columns are converted to the Sybase datetime datatype.
If you set the pdb_convert_datetime parameter to false, the datatype in a replication definition for a DATE, TIME, or TIMESTAMP column must be either:
A Replication Server user-defined datatype (UDD), or
A character datatype.
The character (char or varchar) datatype specified in a replication definition for for a DATE, TIME, or TIMESTAMP column must have sufficient length to accommodate the column’s default display length.
See the Sybase Replication Agent Administration Guide for more information about the pdb_convert_datetime parameter and a complete list of datatype mapping for DB2 Universal Database datatypes. For more information about UDDs and their use, see the Replication Server Administration Guide.
The Sybase Replication Agent provides a number of commands that return metadata information about the primary database (database names, table names, procedure names, column names, and so on). It does this by issuing specific JDBC calls designed to return this information or by querying the system tables directly.
The Sybase Replication Agent provides a set of sample scripts that you can use to set up simple, single-table replication from DB2 Universal Database to Adaptive Server. These scripts are located in the $SYBASE/RAX-12_6/scripts directory of the Sybase Replication Agent installation.
For more information about the sample scripts and their use, see the Sybase Replication Agent Administration Guide.
