This section describes the replicate database issues and considerations specific to the IBM DB2 Universal Database on the OS/390 platform in a Sybase replication system.
As a replicate data server in a gateway environment, DB2 for OS/390 interacts with the DirectConnect for OS/390 database gateway. The DirectConnect for OS/390 gateway is responsible for accepting commands from the replicate Replication Server and applying those commands to a DB2 replicate database.
As a replicate data server in a “gatewayless” environment, DB2 for OS/390 interacts with MainframeConnect for DB2 UDB through the AMD2 CICS transaction. AMD2 accepts commands from Replication Server and applies those commands to a DB2 database. Then, AMD2 retrieves the results from those commands and returns the results to Replication Server.
The gatewayless environment requires a TCP/IP
connection to the mainframe. For more information about gatewayless
connections, see the Sybase Open ServerConnect Installation
and Administration Guide.
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 to the DB2 for OS/390 data server and granted authority to apply transactions in the replicate database. The Maintenance User ID must have the following permissions in the DB2 replicate database:
CREATE TABLE authority to create tables used for Replication Server processing
UPDATE authority to all replicate tables and EXECUTE authority to all replicate stored procedures
The only significant intrusions or impacts to the DB2 replicate database are the database objects created by the hds_db2_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_db2_setup_for_replicate.sql script)
specify the proper character set and sort order for your data 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. To do so, use the Replication Server rs_get_charset and rs_get_sortorder functions to 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.
Replication Server version 12.0 or later must use the RS_LASTCOMMIT table
instead of the LTMLASTCOMMIT table that may have
been installed if Replication Agent for DB2 UDB for OS/390
was installed.
A Replication Server database connection name is made up of two parts: a data server name (server_name) and a database name (db_name).
When using the DirectConnect for OS/390 database gateway, the server_name is the name of the database gateway server, and the db_name is the name of the DB2 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 OS/390 database gateway server is listening. The interfaces file entry name must match the server_name portion of the Replication Server database connection.
With a gatewayless connection from Replication Server to DB2 for OS/390 using the MainframeConnect for DB2 UDB, the server_name is the mainframe host name, and the db_name is the name of the DB2 replicate database. The interfaces file entry for the server_name maps to the mainframe IP address and port.
Gatewayless replication to DB2 for OS/390 requires that you:
Use the Mainframe Connect IPS version 12.5 or later.
Define the rs_get_textptr and rs_writetext function strings using the writetext method. See the Replication Server Reference Manual for more information.
Run the following Replication Server HDS scripts:
hds_clt_xxx_to_db2.sql (class-level translations for the primary database)
hds_db2_funcstrings.sql
hds_db2_udds.sql
The following replication limitations exist with a DB2 for OS/390 replicate data server:
Replication of large object (LOB) datatypes (BLOB, CLOB, and DBCLOB) is supported over a gatewayless connection using MainframeConnect for DB2 UDB version 12.5 or later.
Replication of large object (LOB) datatypes (BLOB, CLOB, and DBCLOB) is not supported directly by DirectConnect for OS/390.
Replication Server cannot send a DB2 binary value as a binary string because the DirectConnect for OS/390 database gateway performs an ASCII to EBCDIC translation on the value. Therefore, all binary or varbinary datatypes replicated to DB2 for OS/390 must be mapped to the rs_db2_char_for_bit or rs_db2_varchar_for_bit datatype.
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 for OS/390 replicate database. These include:
hds_db2_setup_for_replicate.sql – a script to be applied to the DB2 for OS/390 replicate database.
The hds_db2_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_db2_funcstrings.sql and hds_db2_udds.sql – scripts to be applied to Replication Server System Database (RSSD).
The hds_db2_udds.sql script adds the user-defined datatypes (UDDs) that define the attributes of DB2 native datatypes to the RSSD. The UDDs are required to ensure that datatypes received from primary transactions are properly formatted for application to the DB2 for OS/390 replicate database.
You may need to modify the hds_db2_udds.sql script
to reference the correct RSSD database name.
The hds_db2_funcstrings.sql script replaces several default Replication Server function strings with custom function strings designed to communicate with DB2 for OS/390 and access the tables and procedures created by the hds_db2_setup_for_replicate.sql script. These function strings are added to the Replication Server default rs_db2_function_class.
You may need to modify the script to reference the correct
RSSD database name.
If you are using a DirectConnect for OS/390 database gateway for replication to DB2 for OS/390, you must set the following properties in the DirectConnect db2.cfg Access Service configuration file:
SQLTransformation=passthrough TransactionMode=long
If you use function strings from the rs_db2_function_class in
a Replication Server version prior to 12.0, do not run
the hds_db2_funcstrings.sql script,
as it will overwrite the function strings you are currently using.
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, Oracle DATE should be translated to DB2 TIMESTAMP.
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 for OS/390 replicate database are:
hds_clt_ase_to_db2.sql – translates Adaptive Server datatypes to DB2 datatypes.
hds_clt_udb_to_db2.sql – translates DB2 Universal Database (for UNIX and Windows) datatypes to DB2 for OS/390 datatypes.
hds_clt_informix_to_db2.sql – translates Informix datatypes to DB2 datatypes.
hds_clt_msss_to_db2.sql – translates Microsoft SQL Server datatypes to DB2 datatypes.
hds_clt_oracle_to_db2.sql – translates Oracle datatypes to DB2 datatypes.
hds_db2_connection_sample.sql – a script to be applied to the replicate Replication Server.
The hds_db2_connection_sample.sql script provides a template for creating the Replication Server database connection for a DB2 for OS/390 replicate database using the pre-defined DB2 function-string class provided with Replication Server.
You must modify the hds_db2_connection_sample.sql template
to include your actual server, database, and Maintenance User names
before executing the script.
To replicate large-object (LOB) datatypes, you must create rs_writetext and rs_get_textptr function strings. These are not included in the Replication Server HDS sample scripts.
For descriptions of the rs_writetext and rs_get_textptr function strings, see the Replication Server Reference Manual.
The following system management issues are specific to a DB2 for OS/390 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 DB2 for OS/390 database.
With the introduction of heterogeneous datatype support (HDS) 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.
