Enterprise Replication Data Types

Home | Previous Page | Next Page Introducing Enterprise Replication > Overview of Enterprise Replication Administration > Enterprise Replication Considerations >

Enterprise Replication Data Types

Enterprise Replication supports built-in data types and user-defined data types, including row types and collection types. This section describes how Enterprise Replication handles special data types:

For general information on data types, refer to the IBM Informix Guide to SQL: Reference.

Important:

Enterprise Replication does not support replication of simple large objects stored on optical devices.

Important:

For non-master replicates, Enterprise Replication does not verify the data types of columns in tables that participate in replication. The replicated column in a table on the source database server must have the same data type as the corresponding column on the target server. The exception to this rule is cross-replication between simple large objects and smart large objects.

If you use SERIAL or SERIAL8 data types, you must be careful when defining serial columns. For more information, see SERIAL Data Types and Primary Keys.

Replicating on Heterogeneous Hardware

Enterprise Replication supports all primitive data types across heterogeneous hardware. If you define a replicate that includes non-primitive data types (for example, BYTE and TEXT data), the application must resolve data-representation issues that are architecture dependent.

If you use floating-point data types with heterogeneous hardware, you might need to use IEEE floating point or canonical format for the data transfers. For more information, see Using the IEEE Floating Point or Canonical Format.

Replicating Simple and Smart Large Objects

Enterprise Replication replicates:

Simple large object data types (TEXT and BYTE)
You can store simple large objects either in the tblspace with the rest of the table columns (in a dbspace) or in a blobspace.
Smart large object data types (BLOB and CLOB)
You must store smart large objects in sbspaces.

For more information about database storage, see the IBM Informix Dynamic Server Administrator's Guide.

Replicating Simple Large Objects from Tblspaces

Simple large object data that is stored in tblspaces (rather than in blobspaces) is placed in the logical log. Enterprise Replication reads the logical log to capture and evaluate the data for potential replication.

Replicating Large Objects from Blobspaces or Sbspaces

Enterprise Replication does not retrieve simple large object data that is stored in blobspaces and smart large object data that is stored in sbspaces from the logical log. Instead, Enterprise Replication retrieves the large object data directly from the blobspace or sbspace before sending the data to the target database server.

It is possible that a transaction subsequent to the transaction being replicated can modify or delete a simple or smart large object that Enterprise Replication is trying to retrieve. If Enterprise Replication encounters a row whose large object (simple or smart) has been modified or deleted by a subsequent transaction, Enterprise Replication does not send the data in the large object.

In most cases, the subsequent transaction that modified or deleted the large object will also be replicated, so the data again becomes consistent once that transaction is replicated. The data in the large object is inconsistent for only a short time.

Keep in mind that if you specify sending only the columns that changed, the data might not get updated during the next update of the row. For more information, see Replicating Only Changed Columns.

Tip:

Enterprise Replication allows cross-replication between simple large objects and smart large objects. For example, you can replicate a simple large object on the source database server to a smart large object on the target server or vice versa.

Conflict Resolution for Simple and Smart Large Objects

By default, Enterprise Replication performs all conflict detection and resolution at the row level. However, in some cases, simple large object data that is stored in a tblspace (rather than in a blobspace) is accepted by the target server even if the row is rejected. This does not apply to simple large object data that is stored in blobspaces or smart large object data that is stored in sbspaces.

Time-Stamp Conflict Resolution for Simple Large Objects

When a replicated BYTE or TEXT column is modified on the source database server, Enterprise Replication records the value of cdrserver and cdrtime for that column. (For more information on cdrserver and cdrtime, see Preparing Tables for Conflict Resolution.) If the column on the target database server is also stored in a tablespace (rather than in a blobspace), Enterprise Replication evaluates the cdrserver and cdrtime values in the source and target columns and uses the following logic to determine if the data is to be applied:

If the column of the replicated data has a time stamp that is greater than the time stamp of the column on the local row, the data for the column is accepted for replication.
If the server ID and time stamp of the replicated column are equal to the server ID and time stamp on the column on the local row, the data for the column is accepted for replication.
If there is no SPL conflict-resolution rule and the time stamps are equal, then Enterprise Replication applies the data to the row with the lowest CDR server ID.

SPL Conflict Resolution for Simple Large Objects

If the replicate is defined with an SPL conflict-resolution rule, the SPL routine must return the desired action for each BYTE or TEXT column. When the routine is invoked, information about each BYTE or TEXT column is passed to the routine as five separate fields. The following table describes the fields.

Argument	Description
Column size (INTEGER)	The size of the column (if data exists for this column). NULL if the column is NULL.
BLOB flag [CHAR(1)]	For the local row, the field is always NULL. For the replicated row: `D` indicates BYTE or TEXT data is sent from the source database server. `U` indicates BYTE or TEXT data is unchanged on the source database server.
Column type [CHAR(1)]	`P` indicates tablespace data. `B` indicates blobspace data.
ID of last update server [CHAR(18)]	The ID of the database server that last updated this column for tablespace data. For blobspace data: NULL
Last update time (DATETIME YEAR TO SECOND)	For tablespace data: The date and time when the data was last updated. For blobspace data: NULL

For information on creating stored procedures, see the IBM Informix Guide to SQL: Tutorial.

If the routine returns an action code of A, D, I, or U, the routine parses the return values of the replicated columns. Each BYTE or TEXT column can return a two-character field. For information about the action codes, refer to SPL Conflict-Resolution Rule.

The first character defines the desired option for the BYTE or TEXT column, as the following table shows.

Value	Function
`C`	Performs a time-stamp check for this column as used by the time-stamp rule
`N`	Sets the replicate column to NULL
`R`	Accepts the replicated data as it is received
`L`	Retains the local data

The second character defines the desired option for blobspace data if the data is found to be undeliverable, as the following table shows.

Value	Function
`N`	Sets the replicated column to NULL
`L`	Retains the local data (default)
`O`	Aborts the row
`X`	Aborts the transaction

SPL Conflict Resolution for Smart Large Objects

Enterprise Replication handles conflict resolution for smart large objects using the SPL conflict-resolution rule in the same way as for simple large objects. See Conflict Resolution for Simple and Smart Large Objects.

Distributing BYTE and TEXT Data

If Enterprise Replication processes a row and discovers undeliverable BYTE or TEXT columns, the following actions can occur:

Any undeliverable columns are set to NULL if the replication operation is an INSERT and the row does not already exist at the target.
The old value of the local row is retained if the replication operation is an UPDATE or if the row already exists on the target.

Considerations for Replicating Smart Large Objects

The following conditions apply to replicating smart large objects:

Enterprise Replication does not support replication of smart large object updates performed outside of a row update.
After you update a smart large object that is referenced explicitly in the table schema, you must update the referencing row before Enterprise Replication can replicate the updated smart large object. For example:
```
UPDATE table_name SET smart_large_object_column = x
```
For more information, see the IBM Informix Guide to SQL: Syntax.
Enterprise Replication replicates updates to in-place smart large objects by sending a new copy of the entire smart large object. Enterprise Replication does not send only the logged changes to update smart large objects.
Enterprise Replication does not support sharing out-of-row data (multiple references to a smart large object) during replication. If you try to replicate multiple references to the same smart large object on the source database server, Enterprise Replication does not re-create those references on the target database server. Instead, Enterprise Replication creates multiple smart large objects on the target database server.

Replicating Opaque User-Defined Data Types

Enterprise Replication supports built-in data types and extended data types, including opaque data types and user-defined types (UDTs).

Installing and Registering UDTs

You must install and register UDTs and their associated support routines on all database servers participating in Enterprise Replication prior to starting replication.

If you combine Enterprise Replication with High-Availability Data Replication (HDR), you must install UDTs on both HDR database servers, but only register them on the primary HDR database server (see IBM Informix Dynamic Server Administrator's Guide).

UDT Support Functions

If you plan to replicate opaque user-defined types (UDTs), the UDT designer must provide two support functions, streamwrite() and streamread(). This also applies to UDTs embedded in complex types.

The purpose of these functions is similar to the existing send() and receive() functions provided for client/server transmissions.

For information on writing these support functions, see the section on Enterprise Replication stream support functions in the IBM Informix DataBlade API Programmer's Guide.

When preparing a row that includes any UDT columns to queue to the target system, Enterprise Replication calls the streamwrite() function on each UDT column. The function converts the UDT column data from the in-server representation to a representation that can be shipped over the network. This allows Enterprise Replication to replicate the column without understanding the internal representation of the UDT.

On the target server, Enterprise Replication calls the streamread() function for each UDT column that it transmitted using the streamwrite() function.

Considerations for Replicating Opaque Data Types

The following conditions apply to replicating opaque data types:

The WHERE clause of the SELECT statement of the participant modifier can reference an opaque UDT as long as the UDT is always stored in row.
Any UDRs in a WHERE clause can use only parameters whose values can be extracted fully from the logged row images, plus any optional constants.
All of the columns in the SELECT statement of each participant definition must be actual columns in that table. Enterprise Replication does not support virtual columns (results of UDRs on table columns).
See Participant Modifier for information on the WHERE clause in participant definitions.
You cannot use SPL routines for conflict resolution if the replicate includes any UDTs in the SELECT statement or if the replicate is defined to replicate only changed columns.
See Conflict Resolution and Replicating Only Changed Columns.
Enterprise Replication allows you to define replicates on tables that contain one or more UDT columns as the primary key.
For more information, see the section on primary key constraints in the IBM Informix Guide to SQL: Syntax.

Replicating Table Hierarchies

To replicate tables that form a hierarchy, you must define a separate replicate for each table. If you define a replicate on a super table, Enterprise Replication does not automatically create implicit replicate definitions on the subordinate tables.

Tip:

Enterprise Replication does not require that the table hierarchies be identical on the source and target servers.

You must use conflict resolution uniformly for all tables in the hierarchy. In other words, either no conflict resolution for all tables or conflict resolution for all tables.

Verifying the Data Type of Replicated Columns

By using master replicates you can verify that all participants in a replicate have columns with matching data types. Master replicates also allow verification that each participant contains all replicated columns, and optionally that column names are the same on each participant. See Defining Master Replicates for more information.