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), 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, Microsoft SQL Server, or Oracle), 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, there may be a temporary loss of transactional integrity 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 system 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. Some DirectConnect database gateways do not provide this feature.
DirectConnect for Microsoft SQL Server provides support for LOB replication into Microsoft SQL Server and SQL Server 2000 databases. See “Microsoft SQL Server replicate data servers” for more information.
In the case of a replicate database in DB2 for OS/390, you can use the Sybase MainframeConnect for DB2 UDB 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. See “DB2 Universal Database replicate data servers” for more information.
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), Informix, and Oracle requires the RPC method, with a separate stored procedure in the replicate database for each LOB column.
The Component Integration Services (CIS) feature of Adaptive
Server provides text pointer handling for Oracle LONG and LONG
RAW datatypes. See “Oracle replicate data servers” for more information.
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. See the Replication Server Reference Manual for more information about the rs_writetext function string.
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 transaction, and also append data from the multiple RPC invocations.
The DirectConnect gateway for the replicate database must support RPC processing. Refer to the appropriate DirectConnect documentation for more information about RPC handling.
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.
