Large object (LOB) datatypes (such as BLOB, CLOB, IMAGE, and TEXT) provide support for the longest streams of character and binary data in a single column. Their size poses unique challenges, both as primary and replicate data.
At the primary database, the impact of LOB datatypes is on the transaction logging function. For Replication Agents that read the transaction log (that is, Replication Agent for DB2 UDB for OS/390, and Sybase Replication Agent for UDB and Oracle), the log resources must be adequate to support retention of the changes in LOB data, including before images and after images.
For Replication Agents that use a trigger-based method to capture data (that is, Sybase Replication Agent for Informix and Microsoft SQL Server), LOB datatypes are not allowed for trigger or procedure access, which is the normal logging process. The data involved in changes to primary LOB columns is captured separately from other data in a transaction. LOB data is sent to the Replication Server after all the other data in a transaction. Therefore, a temporary loss of transactional integrity may occur as the LOB data is retrieved when the other transaction data is sent to the Replication Server. For example, the LOB data could have changed between the time the transaction was committed and the time the LOB data was selected from the database.
For more information about the specific impacts and concerns for each primary database, see the appropriate Replication Agent documentation.
Adaptive Server Enterprise uses a text pointer to identify the location of text and image column data. The text pointer is passed to system functions that perform the actual updates to data in these large columns. The same technique is used internally in Replication Server to apply LOB datatypes. Replication Server obtains a text pointer, and data server function calls are made to apply the data to replicate databases.
Replication Server default function strings are designed for an Adaptive Server replicate database. Replication Server executes an rs_textptr_init or rs_get_textptr function string, followed by one or more rs_writetext function strings to apply the LOB data to a replicate database. The default function strings supplied in Replication Server for most non-Sybase data servers do not support LOB replication.
When a non-Sybase database is the replicate database, the database gateway used to communicate with the replicate database must be able to emulate the Adaptive Server text pointer processing. The ECDA option for Microsoft SQL Server and the Mainframe Connect DirectConnect for z/OS option gateways provide this feature.
ECDA option for Microsoft SQL Server provides support for LOB replication into Microsoft SQL Server and SQL Server 2000 databases. For more information, see “Microsoft SQL Server replicate data servers”.
In the case of a replicate database in DB2 for OS/390, you can use the Sybase Mainframe Connect DirectConnect z/OS option to provide a “gatewayless” connection to the replicate database, and use modified rs_get_textptr and rs_writetext function strings to support LOB replication into DB2. For more information, see “DB2 Universal Database replicate data servers”.
You might be able to remove the dependency on text pointers from the Replication Server (or the DirectConnect database gateway) by modifying the Replication Server text pointer function strings and creating a stored procedure in the replicate database.
Each text (LOB) column to be processed by Replication Server has a unique rs_writetext function string created to issue a writetext function call to the replicate database. You can modify the rs_writetext function string to replace the default writetext function call with a remote procedure call (RPC) to a stored procedure you create in the replicate database.
Replicating LOB datatypes to DB2 Universal Database on UNIX and Windows platforms requires the RPC method, with a separate stored procedure in the replicate database for each LOB column.
Replicating LOB datatypes to DB2 for OS/390 requires the writetext method, in which the MainframeConnect AMD2 language handler facilitates replication of LOB datatypes to DB2. For more information about the rs_writetext function string, see the Replication Server Reference Manual.
To implement an RPC workaround, you must create one or more stored procedures in the replicate database (depending on the types of primary keys processed), and you must create one Replication Server function string for each text or image (LOB) column to be replicated.
The stored procedure must perform the following processing:
The stored procedure must be able to identify the row where the text data should be applied. One way to accomplish this is to have the primary key values for the table passed as parameters in the RPC.
The stored procedure must be able to append text data to the data in the column. Replication Server executes the RPC repeatedly, passing 16,384 bytes (16K) of data at each invocation until all the data is sent.
The stored procedure must handle both insert and update operations. During insert operations, the Replication Server inserts all other (non-LOB) column data into a row, leaving the LOB columns null. Then, Replication Server invokes the RPC to insert LOB data. During update operations, the Replication Server updates all other (non-LOB) column data, but it does not update the LOB column—any existing data remains. The stored procedure must be able to replace the existing LOB data on an update operation, and also to append data from the multiple RPC invocations.
The DirectConnect gateway for the replicate database must support RPC processing. For more information about RPC handling, refer to the appropriate DirectConnect documentation.
For more information about Replication Server processing of text and image columns and the rs_writetext system function, see the Replication Server Administration Guide and the Replication Server Reference Manual.
